Passing C# Datatable as a Parameter to Stored Procedure in Ms SQL Server 2008

Passing c# DataTable as a parameter to stored procedure in MS SQL Server 2008

First you need to create a type:

CREATE TYPE dbo.whatever AS TABLE
(
Supp_Id int,
Del_Methode_Id int,
Ord_Amount int,
Promo_Id int,
Discount_Amount Money
);

Now your stored procedure can declare this as a read only input parameter:

CREATE PROCEDURE dbo.do_whatever
@datatable dbo.whatever READONLY
AS
BEGIN
SET NOCOUNT ON;

INSERT dbo.destination_table(column_list)
SELECT column_list FROM @datatable;
END
GO

Why you want to use a cursor here, or think you need one, I'm not sure. You can add an ORDER BY clause to the INSERT...SELECT if you think that will be useful (and there is something meaningful to order by), but otherwise if you really really want a cursor here you should be able to declare one against @datatable just as you would for any other table.

Passing datatable to a stored procedure

You can use a Table Valued Parameter as of SQL Server 2008 / .NET 3.5....

Check out the guide on MSDN

Also, as there other options available, I have a comparisonof 3 approaches of passing multiple values (single field) to a sproc (TVP vs XML vs CSV)

Pass datatable as paramater in stored procedure

You need to create User-defined Table Type first.

-- Create the data type
CREATE TYPE udtt_Table AS TABLE
(
Column1 int,
Column2 varchar(10),
Column3 datetime
)
GO

You can use user-defined table type in your stored procedure like the following,

CREATE PROCEDURE usp_User
(
@UserTable udtt_Table READONLY
)
...
....

C# DataTable into SQL Server stored procedure

Create a user defined table type in SQL Server:

CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50));
GO

And pass this table valued parameter to the stored procedure :

CREATE PROCEDURE [Sade].[SPLocationInsert]
@... INT
,@... VARCHAR(64)
,@... VARCHAR(MAX)
,@... INT
,@... INT
,@... INT
,@... INT
,@... INT
,@PLocationDataList LocationTableType READONLY
AS
SET NOCOUNT ON
-- insert your code here
GO

References:

User defined table types : https://technet.microsoft.com/en-us/library/bb522526(v=sql.105).aspx

Table valued parameters: https://technet.microsoft.com/en-us/library/bb510489(v=sql.105).aspx

Sending a DataTable as a parameter to stored procedure

Out of the box, ADO.NET does not suport this with good reason. A DataTable could take just about any number of columns, which may or may not map up to a real table in your database.

If I'm understanding what you want to do - upload the contents of a DataTable quickly to a pre-defined, real table with the same structure, I'd suggest you investigate SQLBulkCopy.

From the documentation:

Microsoft SQL Server includes a popular command-prompt utility named
bcp for moving data from one table to another, whether on a single
server or between servers. The SqlBulkCopy class lets you write
managed code solutions that provide similar functionality. There are
other ways to load data into a SQL Server table (INSERT statements,
for example), but SqlBulkCopy offers a significant performance
advantage over them.

The SqlBulkCopy class can be used to write data only to SQL Server
tables. However, the data source is not limited to SQL Server; any
data source can be used, as long as the data can be loaded to a
DataTable instance or read with a IDataReader instance.

SqlBulkCopy will fail when bulk loading a DataTable column of type
SqlDateTime into a SQL Server column whose type is one of the
date/time types added in SQL Server 2008.

However, you can define Table Value Parameters in SQL Server in later versions, and use that to send a Table (DateTable) in the method you're asking. There's an example at http://sqlwithmanoj.wordpress.com/2012/09/10/passing-multipledynamic-values-to-stored-procedures-functions-part4-by-using-tvp/



Related Topics



Leave a reply



Submit