You must Sign In to post a response.
  • Category: ASP.Net MVC

    Copy store procedure result to dataset or datatable

    I have store procedure which has select query..
    How to copy to datatable using linq ?.?

    Var a= from r in DC.storeprocedure select r;
    Datatable DT= ?????
  • #768117
    filling data table from stored procedure is bit simple here, Just use SqlDataAdapter
    see below snippet

    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;

    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #768119

    Storing the data from Datatable or Dataset is very simple process.
    Once you get the value from the stored procedure, The Data Adapter hold your values. from there you have to get the data and store it in a Dataset.

    Something like below:

    Datatable DT = new DataTable();
    DataAdapater DA = new Data Adapter();

    DA.SelectCommand = new SqlCommand(sql, conn); // Query and Connection
    DA.SelectCommand.CommandType = CommandType.StoredProcedure;

    DataSet DS = new DataSet();
    DA.Fill(DS,"Any_NAME") // You got your Dataset Filled

    //Data Table
    DataTable DT = DS.Tables["Table1"]// Any table in the dataset


  • #768122

    Refer the below.
    Store procedure result to dataset or datatable using Dataadapter

    using (SqlConnection Con = new SqlConnection(sqlCon))
    SqlCommand Com = new SqlCommand();

    Com.CommandText = "SP_Name";
    Com.CommandType = CommandType.StoredProcedure;
    Com.Connection = Con;

    SqlParameter pQuery = new SqlParameter();

    //Setting the properties of Sql parameter
    pQuery.SqlDbType = SqlDbType.VarChar;
    pQuery.Direction = ParameterDirection.Input;
    pQuery.Size = 8000;
    pQuery.ParameterName = "@strQuery";
    pQuery.Value = DBNull.Value;


    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = Com;


    Hope this will help you

    Never lose hope..You never know what tomorrow will bring

  • Sign In to post your comments