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?

Erythrocyte

Here's a Microsoft article that gives an example of doing this. – Goahead

Answer 1

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;
        }

Erythrocyte

A cleaner approach would be to wrap the IDisposable resources in a using clause instead of a try finally – Buttonhook

^much cleaner use Using block – Hatchway

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: #52839636 – Yeargain

@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 #36406383 – Subsume

Answer 2

 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;

Drewdrewett

Answer 3

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

MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

Biometry

Answer 4

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);
}

Lagerkvist

It would be really appreciated if you could provide an explanation of your code. – Therontheropod

Code explaining how to fill a data table using SQLDataAdapter and stored procedure. – Lagerkvist

Answer 5

    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);
        }

Camara

Thank you for using the "using" syntax. – Ceram

Answer 6

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;

Broadax

Answer 7

   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;


    }

Duplicate

Answer 8

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

Franke

Answer 9

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
       {

       }    
}    

Naughty