How to use a DataAdapter with stored procedure and parameter

Question

I want to fill a DataGridView control using DataAdapter. But I don't know how to do it since I'm using a stored procedure with parameter. Can someone cite an example please?

Here's a Microsoft [article](http://support.microsoft.com/kb/306574) that gives an example of doing this. — Garett, Aug 20 '10 at 05:10

score 80 · Accepted Answer · answered Aug 20 '10 at 06:30

80

I got it!...hehe

protected DataTable RetrieveEmployeeSubInfo(string employeeNo)
        {
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            try
            {
                cmd = new SqlCommand("RETRIEVE_EMPLOYEE", pl.ConnOpen());
                cmd.Parameters.Add(new SqlParameter("@EMPLOYEENO", employeeNo));
                cmd.CommandType = CommandType.StoredProcedure;
                da.SelectCommand = cmd;
                da.Fill(dt);
                dataGridView1.DataSource = dt;
            }
            catch (Exception x)
            {
                MessageBox.Show(x.GetBaseException().ToString(), "Error",
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                cmd.Dispose();
                pl.MySQLConn.Close();
            }
            return dt;
        }

answered Aug 20 '10 at 06:30

yonan2236

13,371
33
95
141

12

A cleaner approach would be to wrap the IDisposable resources in a using clause instead of a try finally – cweston Jan 11 '11 at 14:23
1

^much cleaner use `Using` block – IteratioN7T Dec 11 '17 at 05:07
1

This works perfectly. But I faced a problem updating the database using the adapter!. I posted a question in stackoverflow but I did not get any reply. Please have a look to my question: https://stackoverflow.com/questions/52839136/c-sharp-winforms-update-database-when-data-loaded-with-sqldataadapter-using-stor – Hilal Al-Rajhi Oct 17 '18 at 15:00
@HilalAl-Rajhi Your link was removed by a moderator - probably because it was a duplicate question. I realize I'm 3 years late to the party, but you basically do it the same as above, but instantiate the SqlParameter outside of the Parameters.Add command, and give it an Input direction, THEN add it: `SqlParameter param = new SqlParameter("@EMPLOYEENO", employeeNo); param.Direction = ParameterDirection.Input; cmd.Parameters.Add(param); ` See https://stackoverflow.com/questions/36405883/sqlclr-stored-procedures-with-input-parameter – vapcguy Feb 28 '21 at 05:59

score 30 · Answer 2 · answered May 26 '12 at 19:50

 SqlConnection con = new SqlConnection(@"Some Connection String");
 SqlDataAdapter da = new SqlDataAdapter("ParaEmp_Select",con);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.Add("@Contactid", SqlDbType.Int).Value = 123;
            DataTable dt = new DataTable();
            da.Fill(dt);
            dataGridView1.DataSource = dt;

score 4 · Answer 3 · edited Jul 06 '13 at 19:02

4

Maybe your code is missing this line from the Microsoft example:

MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

edited Jul 06 '13 at 19:02

Aleksandr Fedorenko

16,594
6
37
44

answered Aug 20 '10 at 06:00

Mamta D

6,310
3
27
41

Ashutosh B Bodake · Answer 4 · 2017-06-05T07:13:00.463

    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder.DataSource = <sql server name>;
        builder.UserID = <user id>; //User id used to login into SQL
        builder.Password = <password>; //password used to login into SQL
        builder.InitialCatalog = <database name>; //Name of Database

        DataTable orderTable = new DataTable();

        //<sp name> stored procedute name which you want to exceute
        using (var con = new SqlConnection(builder.ConnectionString))
        using (SqlCommand cmd = new SqlCommand(<sp name>, con)) 
        using (var da = new SqlDataAdapter(cmd))
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            //Data adapter(da) fills the data retuned from stored procedure 
           //into orderTable
            da.Fill(orderTable);
        }

Thank you for using the "using" syntax. – Eric Burdo Jul 12 '19 at 13:17 — Eric Burdo, Jul 12 '19 at 13:17

Dharmender Gahlot · Answer 5 · 2020-02-06T13:15:46.367

3

Short and sweet...

DataTable dataTable = new DataTable();
try
{
   using (var adapter = new SqlDataAdapter("StoredProcedureName", ConnectionString))
   {
       adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
       adapter.SelectCommand.Parameters.Add("@ParameterName", SqlDbType.Int).Value = 123;
       adapter.Fill(dataTable);
   };
}
catch (Exception ex)
{
    Logger.Error("Error occured while fetching records from SQL server", ex);
}

edited Feb 06 '20 at 13:15

answered Feb 01 '20 at 09:52

Dharmender Gahlot

31
1
3

It would be really appreciated if you could provide an explanation of your code. – Nathan Feb 01 '20 at 10:08
Code explaining how to fill a data table using SQLDataAdapter and stored procedure. – Dharmender Gahlot Feb 02 '20 at 12:42

score 1 · Answer 6 · answered Aug 22 '14 at 10:46

Here we go,

DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con; //database connection
cmd.CommandText = "WRITE_STORED_PROC_NAME"; //  Stored procedure name
cmd.CommandType = CommandType.StoredProcedure; // set it to stored proc
//add parameter if necessary
cmd.Parameters.Add("@userId", SqlDbType.Int).Value = courseid;

SqlDataAdapter adap = new SqlDataAdapter(cmd);
adap.Fill(ds, "Course");
return ds;

score 1 · Answer 7 · answered Apr 14 '16 at 11:29

   public DataSet Myfunction(string Myparameter)
    {
        config.cmd.Connection = config.cnx;
        config.cmd.CommandText = "ProcName";
        config.cmd.CommandType = CommandType.StoredProcedure;
        config.cmd.Parameters.Add("parameter", SqlDbType.VarChar, 10);
        config.cmd.Parameters["parameter"].Value = Myparameter;

        config.dRadio = new SqlDataAdapter(config.cmd);
        config.dRadio.Fill(config.ds,"Table");

        return config.ds;


    }

score 0 · Answer 8 · edited Sep 04 '15 at 10:43

SqlConnection con = new SqlConnection(@"Some Connection String");//connection object
SqlDataAdapter da = new SqlDataAdapter("ParaEmp_Select",con);//SqlDataAdapter class object
da.SelectCommand.CommandType = CommandType.StoredProcedure; //command sype
da.SelectCommand.Parameters.Add("@Contactid", SqlDbType.Int).Value = 123; //pass perametter
DataTable dt = new DataTable();  //dataset class object
da.Fill(dt); //call the stored producer

Jesse Mwangi · Answer 9 · 2020-01-23T07:04:18.960

public class SQLCon
{
  public static string cs = 
   ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
}
protected void Page_Load(object sender, EventArgs e)
{
    SqlDataAdapter MyDataAdapter;
    SQLCon cs = new SQLCon();
    DataSet RsUser = new DataSet();
    RsUser = new DataSet();
    using (SqlConnection MyConnection = new SqlConnection(SQLCon.cs))
       {
        MyConnection.Open();
        MyDataAdapter = new SqlDataAdapter("GetAPPID", MyConnection);
        //'Set the command type as StoredProcedure.
        MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
        RsUser = new DataSet();
        MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@organizationID", 
        SqlDbType.Int));
        MyDataAdapter.SelectCommand.Parameters["@organizationID"].Value = TxtID.Text;
        MyDataAdapter.Fill(RsUser, "GetAPPID");
       }

      if (RsUser.Tables[0].Rows.Count > 0) //data was found
      {
        Session["AppID"] = RsUser.Tables[0].Rows[0]["AppID"].ToString();
       }
     else
       {

       }    
}

How to use a DataAdapter with stored procedure and parameter

9 Answers9

Linked