none
How can i pass whole datatable into stored procedure ? RRS feed

  • Question

  • I have bulk record into given datatable

    Datatable dt=new Datatable;

    How can i pass datatable as a variable into stored procedure so that i can operate on the data in SP?

    I have tried using open xml method but i think it has some limitations on passing that xml as string .

    Please advice.

    Thanks,

    Ashish

    Tuesday, December 7, 2010 6:41 PM

Answers

  • Hi  Ashish Tripathi,

     

    Welcome to MSDN Forum!

     

    There's a article with a sample code had show us how to pass a datatable to a store procedure, you can reference it:

    http://www.a2zdotnet.com/View.aspx?Id=107

     

     

    Please show us a response to let us know if your problem had been solved or if the information provided is helpful on this question.

     

    Have a nice weekend!


    Mike [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information is found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Sunday, December 12, 2010 1:29 PM
    Moderator

All replies

  • What a stored procedure can do is database-specific. For example, an Oracle database does not run .Net code, so you cannot pass a datatable to it.

    In a SQL 2005 CLR procedure you may be able to use a DataTable in the stored procedure code, but I am not sure. I suggest you to visit the forum of the database engine. For SQL Server, the forums are under the SQL Server category,

    Another way is to save the data back to database so the stored procedure can deal with the data natively. How it can access the data will still depend on the database engine, so you still need to code in SQL, not in C#.



    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    Tuesday, December 7, 2010 8:27 PM
  • If you need to pass a big amount of structured data - you can use xml. SQL server has good xml support, so you will be able to do select statement on your xml parameter.

    You could also look into table-valued parameters in SQL Server 2008 (http://msdn.microsoft.com/en-us/library/bb675163.aspx)

    Tuesday, December 7, 2010 8:34 PM
  • Hi  Ashish Tripathi,

     

    Welcome to MSDN Forum!

     

    There's a article with a sample code had show us how to pass a datatable to a store procedure, you can reference it:

    http://www.a2zdotnet.com/View.aspx?Id=107

     

     

    Please show us a response to let us know if your problem had been solved or if the information provided is helpful on this question.

     

    Have a nice weekend!


    Mike [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information is found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Sunday, December 12, 2010 1:29 PM
    Moderator