C# SqlDataAdaptor Update not updating database
I have what is a relatively simple task in my brain, but it is much more difficult than anticipated, since I cannot get it working. I am working in C#, trying to parse data from a csv file into a DataTable, which I then want to insert into an SQL database. I want to do this so I can work from the database file in the future (I am hoping it will be faster and easier than the CSV file, but please tell me if this is a bad assumption).
I can get the data into the datatable. I then rename the columns to match what is in the database file. Then I create a dataset and add the datatable as "Table1" of the dataset.
However, when I call the "Update" method from the SqlDataAdaptor, the code runs fine, except the data doesn't appear to be updated in the database! I'm guessing I am just making some small dumb mistake, so I apologize, as I am a relative newcomer to C# and OOP in general.
Below is the code I am trying to use:
SqlConnection con;
DataTable dt1;
SqlDataAdapter da;
private void Form1_Load(object sender, EventArgs e)
{
con = new SqlConnection();
con.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=C:dbTestCSV.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
con.Open();
string sql = "SELECT * FROM tblTestCSV";
da = new SqlDataAdapter(sql, con);
SqlCommandBuilder cb;
cb = new SqlCommandBuilder(da);
dt1 = ParseCSV("C:testcsv2.csv");
dt1.Columns[0].ColumnName = "Numbers";
dt1.Columns[1].ColumnName = "Col1";
dt1.Columns[2].ColumnName = "Col2";
dt1.Columns[3].ColumnName = "Col3";
dt1.Columns[4].ColumnName = "Col4";
dt1.Columns[5].ColumnName = "Col5";
foreach (DataRow dr in dt1.Rows)
{
dr.SetModified();
}
DataSet ds1 = new DataSet();
ds1.Tables.Add(dt1);
da.Fill(ds1);
da.Update(ds1,"Table1");
dataGridView1.DataSource = dt1;
con.Close();
da.Dispose();
MessageBox.Show("sql server table data updated!");
}
I have looked through the internet and stackoverflow, but all solutions I have come across I either don't understand or they don't work. Thanks for your time.
Edit: I have changed the code around a bit, so that I am not redeclaring the data adapter after the command builder. However, it is still not updating the database, though the code runs without throwing anything, and the messagebox at the end pops up, followed by my form with the data from dt1.
To clarify, I am not getting data FROM the database. I am trying to get data FROM a CSV file and move it TO a database. Thanks for the help so far. Any other suggestions?
Edit 2: The structure of the DataTable "dt1" is as follows:
Headers: Numbers Col1 Col2 Col3 Col4 Col5
DataTypes: int32 Str Str Str Str Str
With 25 rows.
The table in the dbTestCsv.mdf file is as follows:
Headers: Numbers Col1 Col2 Col3 Col4 Col5
DataTypes: int nvarchar(50) -->
With no rows/data yet.
Edit 3: Heyooo!! Got it working. It was essentially an amalgum of the suggestions here, so thanks everyone!
I think you would better star by creating the DataTable usin common code to fill your dataset then add values which are in the csv file so he can detect that are new according to his first state and then in this case the update would work.
Hope it helps
您需要先使用SqlDataAdapter填充DataSet，然后调用更新方法
e.g. ds1.Fill(da);
So here is the code that finally worked. The data now has ~24000 rows (just to test) and it updates the DB accordingly (takes about 4 seconds to compile/run though). Thanks for all of your help!
SqlConnection con;
DataTable dt1;
SqlDataAdapter da;
private void Form1_Load(object sender, EventArgs e)
{
con = new SqlConnection();
con.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=C:dbTestCSV.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
con.Open();
string sql = "SELECT * FROM tblTestCSV";
da = new SqlDataAdapter(sql, con);
SqlCommandBuilder cb;
cb = new SqlCommandBuilder(da);
dt1 = ParseCSV("C:testcsv3.csv");
dt1.Columns[0].ColumnName = "Numbers";
dt1.Columns[1].ColumnName = "Col1";
dt1.Columns[2].ColumnName = "Col2";
dt1.Columns[3].ColumnName = "Col3";
dt1.Columns[4].ColumnName = "Col4";
dt1.Columns[5].ColumnName = "Col5";
DataSet ds2 = new DataSet();
da.Fill(ds2);
// THIS IS THE KEY BIT!! This adds the rows from the datatable to the dataset one by one.
// I guess the "Update" command in the dataadapter needs this to happen, rather than just
// adding the table "dt1" to the dataset all at once. Can anyone explain why?
foreach (DataRow dRow in dt1.Rows)
{
DataRow dsRow = ds2.Tables["Table"].NewRow();
dsRow.ItemArray = dRow.ItemArray;
ds2.Tables["Table"].Rows.Add(dsRow);
}
da.Update(ds2,"Table");
dataGridView1.DataSource = dt1;
con.Close();
da.Dispose();
}