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.