SqlDataAdapter update datatable

Question

Error updating DatatTable to SqlServer database using SqlDataAdapter because there is still a JSON string in the table.The SQL for creating the table is as follows：

CREATE TABLE [dbo].[Result_Detail] (
    [Device_ID]      INT           NULL, 
    [Rectangle]         VARCHAR (100) NULL,   
    [PK_ID]          INT           IDENTITY (1, 1) PRIMARY KEY NOT NULL
);

target framework： .NET Framework4.5 , Database is SQL Server 2008

but use ' drNew["Rectangle"] = @"aaaa";' instead of 'drNew["Rectangle"] = @"[{""X"":54,""Y"":174},{""X"":92,""Y"":174},{""X"":92,""Y"":193},{""X"":54,""Y"":193}]";' ,it work.

 DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn() { ColumnName = "Device_ID", DataType = typeof(int) });
            dt.Columns.Add(new DataColumn() { ColumnName = "Rectangle", DataType = typeof(string) });
            DataRow drNew = dt.NewRow();
            drNew["Device_ID"] = 1;
            drNew["Rectangle"] = @"[{""X"":54,""Y"":174},{""X"":92,""Y"":174},{""X"":92,""Y"":193},{""X"":54,""Y"":193}]";
            dt.Rows.Add(drNew);
            SqlCommand insertcmd =
               new SqlCommand(@"insert into Result_Detail(Device_ID,Rectangle)
                               values(@Device_ID,@Rectangle)", new SqlConnection(connStr));
            insertcmd.Parameters.Add("@Device_ID", SqlDbType.Int, 11, "Device_ID");
            insertcmd.Parameters.Add("@Rectangle", SqlDbType.VarChar, 100, "Rectangle");
            SqlDataAdapter sqlda = new SqlDataAdapter();
            sqlda.InsertCommand = insertcmd;
            sqlda.Update(dt);

Answer 1

My example code had a typo, and I had also overlooked the SqlConnection. Then again, as Lan says, your original code runs without errors when I try it.

Below is a console-mode program with both your original code and a corrected version of my simplified version. If you compile and run it, you will find that it runs without issues. (Presuming that you create the table, and change the connection string for your environment.)

A few remarks to the code:

1. In the method for your original code, I have added try-catch so that any exception is printed. This seems to be a problem with your code. It fails, but we don't know why. You should implement this in your code.
2. In the method for my simplified code, I'm using using for the connection object. This is important so that the connection is garbage-collected immediately when the block exits. When the connection is handled like in DataTableTest (and your original code), the connection will be garbage-collected at some point, but this may take too long time, and you may exhaust the connection pool. using for connection objects is very much best practice.

Because I need to batch insert data, I use SqlDataAdapter. Update(DataTable)

That's pointless. It may look slick, but under the covers, the SqlDataAdapter will send one INSERT statement for every row. That's very inefficient. The way to go if you want to pass a lot of rows is to use a table-valued parameter (TVP). I did not include an example of this, since this can be done in more than one way, and which is the best depends on your situation. You can pass a DataTable to a TVP, but you would only do it, if you already have a DataTable. Else you would use a List(SqlDataTable). Or, if you are getting the data from another source, you would stream it directly to the TVP. On my web site I have an article about using TVPs: https://www.sommarskog.se/arrays-in-sql-2008.html which gives examples on both methods.

using System;
using System.Data;
using System.Data.SqlClient;

namespace nameless {

   class SomeClass {

     static string connStr = @"Server=.\STHLM;Initial Catalog=tempdb;Integrated Security=SSPI";

     public static void PrintSqlMsgs(SqlErrorCollection msgs) {
         foreach (SqlError e in msgs) {
            Console.WriteLine (
               "Msg {0}, Severity {1}, State: {2}, Procedure {3}, Line no: {4}",
               e.Number.ToString(), e.Class.ToString(), e.State.ToString(),
               e.Procedure, e.LineNumber.ToString()
            );
            Console.WriteLine(e.Message);
         }
      }

      static void DataTableTest() {

          try {
             DataTable dt = new DataTable();
             dt.Columns.Add(new DataColumn() { ColumnName = "Device_ID", DataType = typeof(int) });
             dt.Columns.Add(new DataColumn() { ColumnName = "Rectangle", DataType = typeof(string) });
             DataRow drNew = dt.NewRow();
             drNew["Device_ID"] = 1;
             drNew["Rectangle"] = @"[{""X"":54,""Y"":174},{""X"":92,""Y"":174},{""X"":92,""Y"":193},{""X"":54,""Y"":193}]";
             dt.Rows.Add(drNew);
             SqlCommand insertcmd =
                  new SqlCommand(@"insert into Result_Detail(Device_ID,Rectangle)
                                  values(@Device_ID,@Rectangle)", new SqlConnection(connStr));
             insertcmd.Parameters.Add("@Device_ID", SqlDbType.Int, 11, "Device_ID");
             insertcmd.Parameters.Add("@Rectangle", SqlDbType.VarChar, 100, "Rectangle");
             SqlDataAdapter sqlda = new SqlDataAdapter();
             sqlda.InsertCommand = insertcmd;
             sqlda.Update(dt);
          }
          catch (Exception ex) {
             if (ex is SqlException) {
                SqlException sqlex = (SqlException) ex;
                PrintSqlMsgs(sqlex.Errors);
             }
             else {
                Console.WriteLine(ex.ToString());
             }
          }
      }

      static void PlainInsert() {
         using (SqlConnection cn = new SqlConnection(connStr)) {
            cn.Open();

            SqlCommand insertcmd =
                new SqlCommand(@"insert into Result_Detail(Device_ID,Rectangle)
                               values(@Device_ID,@Rectangle)", cn);
            insertcmd.Parameters.Add("@Device_ID", SqlDbType.Int).Value = 12;
            insertcmd.Parameters.Add("@Rectangle", SqlDbType.VarChar, 100).Value =
                        @"[{""X"":54,""Y"":174},{""X"":92,""Y"":174},{""X"":92,""Y"":193},{""X"":54,""Y"":193}]";
            insertcmd.ExecuteNonQuery();
         }
      }

      static void Main() {
           DataTableTest();
           PlainInsert();
      }
   }

}

Answer 2

It is not clear what your error is (the stack dump appears incomplete). But I don't see why you involve the DataTable in the first place. I would write it as:

SqlCommand insertcmd =
    new SqlCommand(@"insert into Result_Detail(Device_ID,Rectangle)
                   values(@Device_ID,@Rectangle)", new SqlConnection(connStr));
insertcmd.Parameters.Add("@Device_ID", SqlDbType).Value = 1;
insertcmd.Parameters.Add("@Rectangle", SqlDbType.VarChar, 100).Value =
            @"[{""X"":54,""Y"":174},{""X"":92,""Y"":174},{""X"":92,""Y"":193},{""X"":54,""Y"":193}]";
insertcmd.ExecuteNonQuery();

Disclaimer: I'm an SQL Server guy, and I only write .NET code left-handedly.

Answer 3

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM YourTable", connectionString);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
// Make changes to the dataTable
adapter.Update(dataTable);

Replace "YourTable" with the actual table name and "connectionString" with the appropriate connection string for your database.