How to Populate a DataTable from a Stored Procedure

Question

Possible Duplicate    How can i retrieve a table from stored procedure to a datatable       I am trying to populate my datatable  I have created a datatable tmpABCD but i need to populate this with the values from a stored procedure  I am not able to proceed further   SqlConnection sqlcon   new SqlConnection ConfigurationManager ConnectionStrings  DB   ConnectionString   sqlcon Open    SqlCommand cmd   new SqlCommand  usp GetABCD   sqlcon    DataTable dt   new DataTable  tmpABCD     dt Columns Add new DataColumn  A     dt Columns Add new DataColumn  B     dt Columns Add new DataColumn  C     dt Columns Add new DataColumn  D

User · Answer

You can use a SqlDataAdapter       SqlDataAdapter adapter   new SqlDataAdapter        SqlCommand cmd   new SqlCommand  usp GetABCD   sqlcon       cmd CommandType   CommandType StoredProcedure      adapter SelectCommand   cmd      DataTable dt   new DataTable        adapter Fill dt

User · Answer

You don t need to add the columns manually  Just use a DataAdapter and it s simple as   DataTable table   new DataTable    using var con   new SqlConnection ConfigurationManager ConnectionStrings  DB   ConnectionString   using var cmd   new SqlCommand  usp GetABCD   con   using var da   new SqlDataAdapter cmd        cmd CommandType   CommandType StoredProcedure     da Fill table       Note that you even don t need to open close the connection  That will be done implicitly by the DataAdapter      The connection object associated with the SELECT statement must be   valid  but it does not need to be open  If the connection is closed   before Fill is called  it is opened to retrieve data  then closed  If   the connection is open before Fill is called  it remains open

User · Answer

Use the SqlDataAdapter  this would simplify everything     Your code to this point DataTable dt   new DataTable     using var cmd   new SqlCommand  usp GetABCD   sqlcon       using var da   new SqlDataAdapter cmd             da Fill dt           and your DataTable will have the information you are looking for  so long as your stored proceedure returns a data set  cursor

User · Answer

Use an SqlDataAdapter instead  it s much easier and you don t need to define the column names yourself  it will get the column names from the query results   using  SqlConnection sqlcon   new SqlConnection ConfigurationManager ConnectionStrings  DB   ConnectionString         using  SqlCommand cmd   new SqlCommand  usp GetABCD   sqlcon                 cmd CommandType   CommandType StoredProcedure           using  SqlDataAdapter da   new SqlDataAdapter cmd                         DataTable dt   new DataTable                 da Fill dt

[c#] How to Populate a DataTable from a Stored Procedure

Examples related to c#

Examples related to asp.net

Examples related to .net

Examples related to ado.net