Using SQLdataadapter to Insert a Row

Using SqlDataAdapter to insert a row

Set the select command with a "0 = 1" filter and use an SqlCommandBuilder so that the insert command is automatically generated for you.

var sqlQuery = "select * from Customers where 0 = 1";
dataAdapter = new SqlDataAdapter(sqlQuery, conn);
dataSet = new DataSet();
dataAdapter.Fill(dataSet);

var newRow = dataSet.Tables["Customers"].NewRow();
newRow["CustomerID"] = 55;
dataSet.Tables["Customers"].Rows.Add(newRow);

new SqlCommandBuilder(dataAdapter);
dataAdapter.Update(dataSet);

Using an SQL Data Adapter to update and insert rows doesn't update (only inserts)

No, the DataAdapter will just look at the DataRow's RowState property. If it's Added the InsertCommand will be executed, if it's Modified the UpdateCommand will be executed. You have to load this row into the table from the database first.

You can fill an empty DataTable with a DataAdapter and your SelectCommand including two parameters. If the table is empty you can add the DataRow manually, otherwise you can modify the rows with the updated values (if any) and call dataAdpater.Update(tempTestTable).

Here's an example (untested):

SqlDataAdapter dataAdpater = new SqlDataAdapter(
  "SELECT temptesttableid, Age, Name FROM TempTestTable WHERE Name = @Name",
  connection);

DataTable testTable = new DataTable();
// note that you should use an available csv-parser instead
foreach (string line in File.ReadAllLines(path))
{ 
    string[] columns = line.Split(new char[]{'\t'}, StringSplitOptions.None);
    if(columns.Length >= 2)
    {
        string name = columns[0].Trim();
        string ageStr = columns[1].Trim();
        int age;
        if (int.TryParse(ageStr, out age))
        {
            dataAdpater.SelectCommand.Parameters.AddWithValue("@Name", name);
            int rowsAdded = dataAdpater.Fill(testTable);
            if (rowsAdded == 0)
            {
                testTable.Rows.Add(name, age);
            }
            else
            {
                // update values?
            }
        }
    }
}
dataAdpater.Update(testTable);

Why won't this DataAdapter insert rows into the database?

Each DataTable tracks the RowState of its rows, so manually adding data in a loop works because they are all Added (it has nothing to do with manually creating the DataTable - its the rows). When you load from some other source like Excel, they are not added/new.

If you use a DataAdapter to fill the table, you can tell it not to set the RowState to Unchanged. This is very useful for migrating data from one data store to another:

myDA.AcceptChangesDuringFill = False
...
rows = myDA.Fill(xlDT)

Cannot Add a Row Using SQL and CommandBuilder C#

Change the line with the error to this:

dataSet.Tables["CT_DETIMP"].Rows.Add(impRow);

Can not Insert into Database with SqlDataAdapter and MSSqlServer in c#

You should not call AcceptChanges methods of dataset and DataTable because DataAdapter.Update method affects only changed/added/deleted rows from datatable.

But after calling AcceptChanges all your DataRows will have Unchanged state.

See MSDN for reference about DataAdapter:

When an application calls the Update method, the DataAdapter examines
the RowState property, and executes the required INSERT, UPDATE, or
DELETE statements iteratively for each row, based on the order of the
indexes configured in the DataSet.

Cannot Add new row to database with DataAdapter.insertCommand vb.net

I'm all in favor of using what works and I see the solution above works. The advantage of using a dataadapter however, is that it wraps all the sql commands needed for the CRUD operations of the datatable. You can configure your own commands or let visual studio configure them automatically by using a sqlcommandbuilder. Then once you have the adapter configured all you need to do is something like this

    Dim myds As New DataSet()
    Dim mytable as New datatable()
    myds.Tables.Add(mytable)
    Dim myconstr As String = "ConnectionStringtoDataBase"
    Dim myconn As New SqlConnection(myconstr)

    Dim da as new SqlDataAdapter( “SELECT * FROM StudentTable”,cnn)
    Dim mycb As New SqlCommandBuilder(da)
    da.Fill(mytable)
    Dim myrow As DataRow = mytable.NewRow()
    myrow("ID") = "thestudentid"
    myrow("StudentName") = "John doe"
    myrow("StudentID") = 12345
    myrow("StudentClass") = "VB 101"
    mytable.Rows.Add(myrow)
    da.Update(mytable)

The CommandBuilder will automatically add insert, update and delete commands to the dataadapter (if your select command is one table only, otherwise you'll have to configure it manually).

DataAdapter Update not properly Inserting data into SQL Server?

Since you have called ticketReportDT.AcceptChanges() before updating database it is not going to update anything in the database.

---

Related Topics