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

Posted by Kasani007 under C# on 1/16/2015 | Points: 10 | Views : 913 | Status : [Member] | Replies : 2
How To Save The DataTable Data In SQL Server Using C# ?




Responses

Posted by: govindtumati-29487 on: 1/17/2015 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Sheonarayan on: 1/18/2015 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
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

Login to post response