How To Save The DataTable Data In SQL Server Using C# ?

Using sql server Table types we can achieve this.

Create table type(should contain equal number of columns as datatable which you pass)
Next Use that table type as datatype.

Example:-

CREATE TYPE [dbo].[SaveDataTable] AS TABLE(

 column1 datatype,

 clumn2 datatype

)

GO

in stored Procedure-->

create PROCEDURE [dbo].[SpSaveDataTable] 

	-- Add the parameters for the stored procedure here

	@SaveDataTable SaveDataTable readonly

AS

BEGIN

insert into tablaname select * from @SaveDataTable

END

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

DataTable is a C# object under System.Data namespace. I think @Kasani is talking about the C# code for that. If I am correct below steps should be followed.

// Loop through rows of DataTable first.
foreach (DataRow row in dataTableObjectofData.Rows)
{
   // call ADO.NET SQL connection, sql command and execute insert statement below
   var sql = "INSERT into MyTable (col1, col2, col3) VALUES (" + row["col1"].ToString() + ", " + row["col2"].ToString()" + ", " + row["col2"].ToString() + ")";
}

Instead of SQL Insert statement, you can also call stored procedure. To know how to write ADO.NET code to insert a record into database, read this article http://www.dotnetfunda.com/articles/show/2708/3-tier-architecture-in-aspnet-a-complete-article and notice PersonDAL.cs code snippet.

Hope this helps.

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator