create bound DataGridView dinamically

G

Guest

I'm managing a DB with a main table and some related tables. Related tables
have all the same structure (same columns names, same keys, ...).
I developed a Windows App in VB.NET 2005 to browse and modify data. The main
table fields are shown on the top of my form with a BindingNavigator.
On the bottom there's a TabControl, each tab containing a DataGridView bound
to a different related table. I used 1 Dataset, many BindingSources and
Tables Adapters.

PROBLEM:
I have to manage many databases, each one having similar schema to the one
above, differing only for the number of related tables.
Instead of having to change and recompile each time my Windows App, I would
like to create a new generalized version that looks at the DB schema and
automatically generates the appropriate number of Tabs in the TabControl and
put a new DatagridView bound to every single related table.

I tryed to do this in many ways without success.
The first problem I found is how to add new tables to the DataSet by code
and then how to create new BindingSources and TableAdapters and manage them.
Any suggestion?
Thank you very much
 
L

Linda Liu [MSFT]

Hi Bruno,
How to add new tables to the DataSet by code and then how to create new
BindingSources and TableAdapters and manage them?

We could create a DataTable, add some data columns in it and add the
DataTable to the DataSet at run time. It's easy and straight forward.

As for TableAdapter, we can only create a strong typed TableAdapter for a
DataTable at design time through Data Source Configuration Wizard. If you
want to create a data adapter at run time, I suggest that you create a
SqlDataAdapter instead. Note that we need to create a SelectCommand, an
InsertCommand, a UpdateCommand and a DeleteCommand for the SqlDataAdapter.

The last step is to create a BindingSource instance, and then set its
DataSource property to the DataSet and DataMember property to the DataTable.

I will illuminate this with a sample. It requires that you add a
DataGridView and two Buttons on the form. To test this sample, you need to
create a table called 'Student' in the DataBase. The structure of the
'Student' table is like below:

ID int NOT NULL (Primary Key),
Name varchar(50) NULL,
Age int NULL

public partial class Form1 : Form
{
DataSet dataset = null;
BindingSource bs = null;
SqlConnection conn = null;
SqlDataAdapter da = null;

private void Form1_Load(object sender, EventArgs e)
{
DataTable table = new DataTable("Student");
DataColumn col1 = new DataColumn("ID", typeof(Int32));
col1.AllowDBNull = false;
col1.Unique = true;
DataColumn col2 = new DataColumn("Name", typeof(string));
col2.AllowDBNull = true;
DataColumn col3 = new DataColumn("Age", typeof(Int32));
col3.AllowDBNull = true;
table.Columns.Add(col1);
table.Columns.Add(col2);
table.Columns.Add(col3);

dataset = new DataSet();
dataset.Tables.Add(table);

conn = new SqlConnection(@"Data Source=.\sqlexpress;Initial
Catalog=TestDataBase;Integrated Security=True");
SqlCommand selectCmd = new SqlCommand("select * from Student",
conn);

string updateStr = "UPDATE [dbo].[Student] SET [ID] = @ID,
[Name] = @name, [Age] = @Age " +
" WHERE (([ID] = @Original_ID) " +
" AND ((@IsNull_Name = 1 AND [Name] IS NULL) OR ([Name] =
@Original_Name))" +
" AND ((@IsNull_Age = 1 AND [Age] IS NULL) OR ([Age] =
@Original_Age)));" +
" SELECT ID, Name, Age FROM Student WHERE (ID = @ID)";

SqlCommand updateCmd = new SqlCommand(updateStr, conn);

SqlParameter updatePara1 = new
SqlParameter("@ID",SqlDbType.Int,0,"ID");
updatePara1.SourceVersion = DataRowVersion.Current;

SqlParameter updatePara2 = new
SqlParameter("@name",SqlDbType.VarChar,0,"Name");
updatePara2.SourceVersion = DataRowVersion.Current;

SqlParameter updatePara3 = new
SqlParameter("@Age",SqlDbType.Int,0,"Age");
updatePara3.SourceVersion = DataRowVersion.Current;

SqlParameter updatePara4 = new
SqlParameter("@Original_ID",SqlDbType.Int,0,"ID");
updatePara4.SourceVersion = DataRowVersion.Original;

SqlParameter updatePara5 = new
SqlParameter("@Original_Name",SqlDbType.VarChar,0,"Name");
updatePara5.SourceVersion = DataRowVersion.Original;

SqlParameter updatePara6 = new
SqlParameter("@IsNull_Name",SqlDbType.Int,0,"Name");
updatePara6.SourceVersion = DataRowVersion.Original;
updatePara6.SourceColumnNullMapping = true;

SqlParameter updatePara7 = new
SqlParameter("Original_Age",SqlDbType.Int,0,"Age");
updatePara7.SourceVersion = DataRowVersion.Original;

SqlParameter updatePara8 = new
SqlParameter("@IsNull_Age",SqlDbType.Int,0,"Age");
updatePara8.SourceVersion = DataRowVersion.Original;
updatePara8.SourceColumnNullMapping = true;

updateCmd.Parameters.Add(updatePara1);
updateCmd.Parameters.Add(updatePara2);
updateCmd.Parameters.Add(updatePara3);
updateCmd.Parameters.Add(updatePara4);
updateCmd.Parameters.Add(updatePara5);
updateCmd.Parameters.Add(updatePara6);
updateCmd.Parameters.Add(updatePara7);
updateCmd.Parameters.Add(updatePara8);

string insertStr = "INSERT INTO [dbo].[Student] ([ID], [Name],
[Age]) VALUES (@ID, @name, @Age);" +
" SELECT ID, Name, Age FROM Student WHERE (ID = @ID)";
SqlCommand insertCmd = new SqlCommand(insertStr, conn);

SqlParameter insertPara1 = new SqlParameter("@ID",
SqlDbType.Int, 0, "ID");
insertPara1.SourceVersion = DataRowVersion.Current;

SqlParameter insertPara2 = new SqlParameter("@name",
SqlDbType.VarChar,0, "Name");
insertPara2.SourceVersion = DataRowVersion.Current;

SqlParameter insertPara3 = new SqlParameter("@Age",
SqlDbType.Int,0, "Age");
insertPara3.SourceVersion = DataRowVersion.Current;

insertCmd.Parameters.Add(insertPara1);
insertCmd.Parameters.Add(insertPara2);
insertCmd.Parameters.Add(insertPara3);

string deleteStr = "DELETE FROM [dbo].[Student] WHERE (([ID] =
@Original_ID) AND ((@IsNull_Name = 1 AND [Name] IS NULL) OR ([Name] =
@Original_Name)) AND ((@IsNull_Age = 1 AND [Age] IS NULL) OR ([Age] =
@Original_Age)))";
SqlCommand deleteCmd = new SqlCommand(deleteStr, conn);

SqlParameter deletePara1 = new SqlParameter("@Original_ID",
SqlDbType.Int, 0, "ID");
deletePara1.SourceVersion = DataRowVersion.Original;

SqlParameter deletePara2 = new SqlParameter("@Original_Name",
SqlDbType.VarChar, 0, "Name");
deletePara2.SourceVersion = DataRowVersion.Original;

SqlParameter deletePara3 = new SqlParameter("@IsNull_Name",
SqlDbType.Int, 0, "Name");
deletePara3.SourceVersion = DataRowVersion.Original;
deletePara3.SourceColumnNullMapping = true;

SqlParameter deletePara4 = new SqlParameter("Original_Age",
SqlDbType.Int, 0, "Age");
deletePara4.SourceVersion = DataRowVersion.Original;

SqlParameter deletePara5 = new SqlParameter("@IsNull_Age",
SqlDbType.Int, 0, "Age");
deletePara5.SourceVersion = DataRowVersion.Original;
deletePara5.SourceColumnNullMapping = true;

deleteCmd.Parameters.Add(deletePara1);
deleteCmd.Parameters.Add(deletePara2);
deleteCmd.Parameters.Add(deletePara3);
deleteCmd.Parameters.Add(deletePara4);
deleteCmd.Parameters.Add(deletePara5);

da = new SqlDataAdapter(selectCmd);
da.UpdateCommand = updateCmd;
da.InsertCommand = insertCmd;
da.DeleteCommand = deleteCmd;
da.TableMappings.Add("Table", "Student");

bs = new BindingSource(dataset, "Student");
this.dataGridView1.DataSource = bs;

}
// fill the DataTable
private void button1_Click(object sender, EventArgs e)
{
conn.Open();
da.Fill(dataset);
conn.Close();
}
// save the changes to the DB
private void button2_Click(object sender, EventArgs e)
{
bs.EndEdit();
DataSet changes = dataset.GetChanges();
if(changes!= null)
{
da.Update(changes);
dataset.AcceptChanges();
}
}
}

Hope this helps.
If you have any question, please feel free to let me know.


Sincerely,
Linda Liu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
 
G

Guest

Hi Linda,
Thanks for your immediate response and clear sample.
--
bruno


Linda Liu said:
Hi Bruno,
How to add new tables to the DataSet by code and then how to create new
BindingSources and TableAdapters and manage them?

We could create a DataTable, add some data columns in it and add the
DataTable to the DataSet at run time. It's easy and straight forward.

As for TableAdapter, we can only create a strong typed TableAdapter for a
DataTable at design time through Data Source Configuration Wizard. If you
want to create a data adapter at run time, I suggest that you create a
SqlDataAdapter instead. Note that we need to create a SelectCommand, an
InsertCommand, a UpdateCommand and a DeleteCommand for the SqlDataAdapter.

The last step is to create a BindingSource instance, and then set its
DataSource property to the DataSet and DataMember property to the DataTable.

I will illuminate this with a sample. It requires that you add a
DataGridView and two Buttons on the form. To test this sample, you need to
create a table called 'Student' in the DataBase. The structure of the
'Student' table is like below:

ID int NOT NULL (Primary Key),
Name varchar(50) NULL,
Age int NULL

public partial class Form1 : Form
{
DataSet dataset = null;
BindingSource bs = null;
SqlConnection conn = null;
SqlDataAdapter da = null;

private void Form1_Load(object sender, EventArgs e)
{
DataTable table = new DataTable("Student");
DataColumn col1 = new DataColumn("ID", typeof(Int32));
col1.AllowDBNull = false;
col1.Unique = true;
DataColumn col2 = new DataColumn("Name", typeof(string));
col2.AllowDBNull = true;
DataColumn col3 = new DataColumn("Age", typeof(Int32));
col3.AllowDBNull = true;
table.Columns.Add(col1);
table.Columns.Add(col2);
table.Columns.Add(col3);

dataset = new DataSet();
dataset.Tables.Add(table);

conn = new SqlConnection(@"Data Source=.\sqlexpress;Initial
Catalog=TestDataBase;Integrated Security=True");
SqlCommand selectCmd = new SqlCommand("select * from Student",
conn);

string updateStr = "UPDATE [dbo].[Student] SET [ID] = @ID,
[Name] = @name, [Age] = @Age " +
" WHERE (([ID] = @Original_ID) " +
" AND ((@IsNull_Name = 1 AND [Name] IS NULL) OR ([Name] =
@Original_Name))" +
" AND ((@IsNull_Age = 1 AND [Age] IS NULL) OR ([Age] =
@Original_Age)));" +
" SELECT ID, Name, Age FROM Student WHERE (ID = @ID)";

SqlCommand updateCmd = new SqlCommand(updateStr, conn);

SqlParameter updatePara1 = new
SqlParameter("@ID",SqlDbType.Int,0,"ID");
updatePara1.SourceVersion = DataRowVersion.Current;

SqlParameter updatePara2 = new
SqlParameter("@name",SqlDbType.VarChar,0,"Name");
updatePara2.SourceVersion = DataRowVersion.Current;

SqlParameter updatePara3 = new
SqlParameter("@Age",SqlDbType.Int,0,"Age");
updatePara3.SourceVersion = DataRowVersion.Current;

SqlParameter updatePara4 = new
SqlParameter("@Original_ID",SqlDbType.Int,0,"ID");
updatePara4.SourceVersion = DataRowVersion.Original;

SqlParameter updatePara5 = new
SqlParameter("@Original_Name",SqlDbType.VarChar,0,"Name");
updatePara5.SourceVersion = DataRowVersion.Original;

SqlParameter updatePara6 = new
SqlParameter("@IsNull_Name",SqlDbType.Int,0,"Name");
updatePara6.SourceVersion = DataRowVersion.Original;
updatePara6.SourceColumnNullMapping = true;

SqlParameter updatePara7 = new
SqlParameter("Original_Age",SqlDbType.Int,0,"Age");
updatePara7.SourceVersion = DataRowVersion.Original;

SqlParameter updatePara8 = new
SqlParameter("@IsNull_Age",SqlDbType.Int,0,"Age");
updatePara8.SourceVersion = DataRowVersion.Original;
updatePara8.SourceColumnNullMapping = true;

updateCmd.Parameters.Add(updatePara1);
updateCmd.Parameters.Add(updatePara2);
updateCmd.Parameters.Add(updatePara3);
updateCmd.Parameters.Add(updatePara4);
updateCmd.Parameters.Add(updatePara5);
updateCmd.Parameters.Add(updatePara6);
updateCmd.Parameters.Add(updatePara7);
updateCmd.Parameters.Add(updatePara8);

string insertStr = "INSERT INTO [dbo].[Student] ([ID], [Name],
[Age]) VALUES (@ID, @name, @Age);" +
" SELECT ID, Name, Age FROM Student WHERE (ID = @ID)";
SqlCommand insertCmd = new SqlCommand(insertStr, conn);

SqlParameter insertPara1 = new SqlParameter("@ID",
SqlDbType.Int, 0, "ID");
insertPara1.SourceVersion = DataRowVersion.Current;

SqlParameter insertPara2 = new SqlParameter("@name",
SqlDbType.VarChar,0, "Name");
insertPara2.SourceVersion = DataRowVersion.Current;

SqlParameter insertPara3 = new SqlParameter("@Age",
SqlDbType.Int,0, "Age");
insertPara3.SourceVersion = DataRowVersion.Current;

insertCmd.Parameters.Add(insertPara1);
insertCmd.Parameters.Add(insertPara2);
insertCmd.Parameters.Add(insertPara3);

string deleteStr = "DELETE FROM [dbo].[Student] WHERE (([ID] =
@Original_ID) AND ((@IsNull_Name = 1 AND [Name] IS NULL) OR ([Name] =
@Original_Name)) AND ((@IsNull_Age = 1 AND [Age] IS NULL) OR ([Age] =
@Original_Age)))";
SqlCommand deleteCmd = new SqlCommand(deleteStr, conn);

SqlParameter deletePara1 = new SqlParameter("@Original_ID",
SqlDbType.Int, 0, "ID");
deletePara1.SourceVersion = DataRowVersion.Original;

SqlParameter deletePara2 = new SqlParameter("@Original_Name",
SqlDbType.VarChar, 0, "Name");
deletePara2.SourceVersion = DataRowVersion.Original;

SqlParameter deletePara3 = new SqlParameter("@IsNull_Name",
SqlDbType.Int, 0, "Name");
deletePara3.SourceVersion = DataRowVersion.Original;
deletePara3.SourceColumnNullMapping = true;

SqlParameter deletePara4 = new SqlParameter("Original_Age",
SqlDbType.Int, 0, "Age");
deletePara4.SourceVersion = DataRowVersion.Original;

SqlParameter deletePara5 = new SqlParameter("@IsNull_Age",
SqlDbType.Int, 0, "Age");
deletePara5.SourceVersion = DataRowVersion.Original;
deletePara5.SourceColumnNullMapping = true;

deleteCmd.Parameters.Add(deletePara1);
deleteCmd.Parameters.Add(deletePara2);
deleteCmd.Parameters.Add(deletePara3);
deleteCmd.Parameters.Add(deletePara4);
deleteCmd.Parameters.Add(deletePara5);

da = new SqlDataAdapter(selectCmd);
da.UpdateCommand = updateCmd;
da.InsertCommand = insertCmd;
da.DeleteCommand = deleteCmd;
da.TableMappings.Add("Table", "Student");

bs = new BindingSource(dataset, "Student");
this.dataGridView1.DataSource = bs;

}
// fill the DataTable
private void button1_Click(object sender, EventArgs e)
{
conn.Open();
da.Fill(dataset);
conn.Close();
}
// save the changes to the DB
private void button2_Click(object sender, EventArgs e)
{
bs.EndEdit();
DataSet changes = dataset.GetChanges();
if(changes!= null)
{
da.Update(changes);
dataset.AcceptChanges();
}
}
}

Hope this helps.
If you have any question, please feel free to let me know.


Sincerely,
Linda Liu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top