none
Batch Update using SqlDataAdapter RRS feed

  • Question

  • Hi,

     

    What's wrong with this code doesn't work - doesn't complane.

     

    Thank you - Ashok

     

    public static void BatchUpdate(string connectionString)

    {

    SqlCommand cmd = new SqlCommand();

    DataTable dt = new DataTable();

    try

    {

    SqlConnection con = new SqlConnection(connectionString);

    con.Open();

    cmd.CommandText = "select * from dbo.ProductCategoryData";

    cmd.Connection = con;

    cmd.CommandType = CommandType.Text;

    SqlDataAdapter da = new SqlDataAdapter(cmd);

    DataSet ds = new DataSet();

    da.Fill(ds);

    dt = ds.Tables[0];

     

    // Connect to the AdventureWorks database.

    using (SqlConnection connection = new SqlConnection(connectionString))

    {

    // Create a SqlDataAdapter.

    SqlDataAdapter adapter = new SqlDataAdapter();

     

     

    adapter.InsertCommand = new SqlCommand(

    "INSERT INTO ProductCategory (Name,ProductCategoryID) VALUES (@Name, @ProductCategoryID);",

    connection);

    adapter.InsertCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50, "Name");

    adapter.InsertCommand.Parameters.Add("@ProductCategoryID", SqlDbType.Int, 4, "ProductCategoryID");

    adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

     

    // Set the batch size.

    adapter.UpdateBatchSize = 2;

    // Execute the update.

    adapter.Update(dt);

    }

    }

    catch (Exception ex)

    {

    System.Console.Write(ex.Message);

    }

    }

     

     

    USE [DB]

    GO

    /****** Object: Table [dbo].[ProductCategory] Script Date: 07/12/2007 14:11:14 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[ProductCategory](

    [Name] [varchar](50) NULL,

    [ProductCategoryID] [int] NULL

    ) 

    GO

    SET ANSI_PADDING OFF

    Thursday, July 12, 2007 6:12 PM

Answers

  • After looking into other posting I made it work but still like to check this is right way to do.

    I added this foreach loop just before - adapter.Update(dt);

     

    foreach (DataRow b in dt.Rows)

    {

    b.SetAdded(); //.SetModified();

    }

    Thursday, July 12, 2007 6:41 PM