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
Dividing 2 Numbers in SQL Server
Apex 5.0: Show a Progress Bar While Database Action Is Performed
Executing SQL Query on Multiple Databases
Performance Difference Between Primary Key and Unique Clustered Index in SQL Server
How to Get String After Character Oracle
Find The Sids of The Suppliers Who Supply Every Part
How to Analyze 'Dbcc Memorystatus' Result in SQL Server 2008
Create a Trigger to Insert Records from a Table to Another One. Get Inserted Values in a Trigger
Sql Query to Convert Columns into Rows
Error: Subquery Must Return Only One Column
How to Handle 'Optional' Where Clause Filters in Sql
Sql Sum by Year Report, Looking for an Elegant Solution
Most Efficient Method for Persisting Complex Types with Variable Schemas in Sql
Oracle Text Escaping with Curly Braces and Wildcards
There Is Already an Object Named '##Temp' in The Database