Adapter.Update() does not work?

Question

i use this code for insert something to Customer Table but it dose not work? my database does not update and the value of r always is 0.`

 using (SqlConnection cn = new SqlConnection(con))
        {
            cn.Open();
            string query = string.Format("Select * From Customers");
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommandBuilder cb = new SqlCommandBuilder(adapter);


            adapter.SelectCommand = new SqlCommand(query, cn);
            DataSet db = new DataSet();
            adapter.Fill(db,"Customers");

            string m = "Bon app'" ,city="london";
            query = string.Format("Insert Into Customers (CompanyName , City) Values ('{0}','{1}')",m,city);
            adapter.InsertCommand =new  SqlCommand(query, cn);

            int r= adapter.Update(db,"Customers");

         Console.WriteLine(r);

`

I think you should read more about ado.net. Or you execute the query to do the insert,or if you are using a datadapter you need to add the rows to the dataset — Pikoh, Apr 27 '16 at 12:00

Tim Schmelter · Accepted Answer · 2016-04-27T12:12:35.420

You don't add rows to your DataSet/DataTable, so there is DataAdapter has nothing to insert.

db.Tables[0].Rows.Add(m, city);
int r = adapter.Update(db,"Customers");

Apart from that, do not concatenate strings to build your sql query. Use parameterized queries.

So here's a modified version that uses sql-parameters:

using (SqlConnection cn = new SqlConnection(con))
{
    cn.Open();
    string selectQuery = "Select * From Customers";
    string insertQuery = "Insert Into Customers (CompanyName , City) Values (@CompanyName, @City)";
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = new SqlCommand(selectQuery, cn);
    adapter.InsertCommand = new SqlCommand(insertQuery, cn);
    DataSet db = new DataSet();
    adapter.Fill(db, "Customers");

    var icp = adapter.InsertCommand.Parameters;
    icp.Add("@CompanyName", SqlDbType.NVarChar, 150, "CompanyName"); // optional, restrict length according to database max-length
    icp.Add("@City", SqlDbType.NVarChar, 100, "City"); 

    DataRow newRow = db.Tables["Customers"].Rows.Add();
    newRow.SetField("CompanyName", "Bon app");
    newRow.SetField("City", "london");
    int r = adapter.Update(db, "Customers");
}

Adapter.Update() does not work?

1 Answers1