question

varun 1 avatar image
varun 1 asked

How to send the Datatable from c#.net to sqlserver 2008?

Hi to Everybody,

I have Table in Session at C#.net.

And i want to insert all the data from session table to sqlserver 2008 table.

I dont know how to insert the couple of rows while that come from the front end that is C#.net or

whatever it may be.

Suppose i have 30 rows in a session table means how can i send that to sqlserver 2008 and how

can i insert all the rows into table as it is stored in the session table

Please help me to solve this problem.

Thanks a lot.

sql-server-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered

The easiest way to do it is to create a stored procedure which expects a table valued parameter. You could not do it in earlier versions, but this feature is available in SQL Server 2008. 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. If you need a complete example, please let me know and I will add it to this answer.

Here is a C# example, showing how to pass data from the table to the SQL Sever 2008 stored procedure (you still have to write the proc expecting table valued parameter and insering data from it to the destination table, something like insert into dest_table select * from @table_valued_param):

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 SqlParameter(
                    "@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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.