question

Looks like you already asked similar question yesterday. The question was titled "How to send the Datatable from c#.net to sqlserver 2008?" I provided the answer to yesterday's question. However, because there are slight differences betweent the two, here is the complete sample which will demo how to do both inserts and updates of the DataTable from C# Session to the database.

Lets assume that you have a table in your database defined like this:

create table dbo.DestinationTable
(
    record_id int not null identity(1, 1)
        constraint PK_DestinationTable
        primary key clustered, 
    column1 varchar(50) not null, 
    column2 varchar(50) not null, 
    column3 varchar(50) not null
);
go

You already have a way to retrieve the data from this table and display it to your web user via the grid. The grid lets the users modify existing records in the grid as well as add new records there. The grid has the column holding record_id data hidden, so when the user add new record to the grid, the value of the hidden record_id column is null. This helps you to quickly identify which records are new and which ones are existing.

One way to quickly save all user changes in the database is to pass the DataSource of the grid (DataTable) to the stored procedure as a table valued parameter. Because you cannot have the stored procedure authored with table type parameter directly, you need to create a user defined type as table, and then create a stored procedure accepting a parameter of your type:

create type dbo.VarunType as table 
(
    record_id int null, column1 varchar(50), 
    column2 varchar(50), column3 varchar(50)
);
go

create proc dbo.vsp_SaveSessionTable 
(
    @table VarunType readonly
)
as

begin

    -- this table valued parameter which came from C#
    -- session is assumed to possibly have both new
    -- records (record_id is null) to insert, and
    -- existing records to update (by matching record_id)

    begin tran;

    -- update existing records
    update dest
    set 
        column1 = source.column1,
        column2 = source.column2,
        column3 = source.column3
        from dbo.DestinationTable dest inner join @table source
            on dest.record_id = source.record_id;


    -- insert new records
    insert into dbo.DestinationTable (column1, column2, column3)
    select column1, column2, column3
        from @table
        where record_id is null;

    commit tran;

end;
go

Now we are ready for C# code to save data from DataTable. I already noted in my yesterday's answer, but will repeat here what needs to be done. This feature is only available in SQL Server 2008 or better. From you C# code, when you instantiate a SqlCommand, add a parameter to it, which has SqlDbType.Structured type. Then you can simply set the Value of the parameter to the instance of your DataTable. Please note that this type is only available in .NET Framework 3.5 or better, so you should probably opt to use Visual Studio 2008 for your .NET environment. Here is the code:

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

        //

private static bool SaveDataTable(DataTable table)
{    
    bool result = false; // presume failure    
    using (SqlConnection cn = new SqlConnection("your_connection_string"))    
    {        
        cn.Open();        
        using (SqlCommand cm = new SqlCommand())        
        {            
            cm.CommandType = CommandType.StoredProcedure;            
            cm.CommandText = "your_procedure_name_goes here";            
            cm.Connection = cn;            
            SqlParameter parameter = new SqlParamete(
                "@your_parameter_name_goes_here", SqlDbType.Structured); 
            parameter.Value = table;            
            cm.Parameters.Add(parameter);            
            cm.ExecuteNonQuery();           
            result = true;        
        } // instance of command is disposed        

        cn.Close();    
    } // connection instance is disposed    

    return result;
}

Hope this helps.

Oleg