How to Use a Dataadapter with Stored Procedure and Parameter

How to use a DataAdapter with stored procedure and parameter

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

How to use DataAdapter to call a stored procedure in C# with variable parameters

First Issue:
Parameters in a stored procedure shouldn't be included along with its name

Second Issue:
Having a space in names of stored procedure isn't a good practice.

And for code behind

using(SqlConnection con = new SqlConnection("Your Connection String Here"))
{ 
    SqlCommand cmd = new SqlCommand("sp_SomeName", con);
    cmd.CommandType = CommandType.StoredProcedure;

    //the 2 codes after this comment is where you assign value to the parameters you
    //have on your stored procedure from SQL
    cmd.Parameters.Add("@MONTH", SqlDbType.VarChar).Value = "someValue";
    cmd.Parameters.Add("@YEAR", SqlDbType.VarChar).Value = "SomeYear";

    SqlDataAdapter da = new SqlDataAdapter(cmd);
    SqlDataSet ds = new SqlDataSet();
    da.Fill(ds); //this is where you put values you get from the Select command to a 
  //dataset named ds, reason for this is for you to fetch the value from DB to code behind

    foreach(DataRow dr in ds.Tables[0].Rows) // this is where you run through the dataset and get values you want from it.
    {
       someTextBox.Text = dr["Month"].ToString(); //you should probably know this code
    }
}

Execute a stored procedure using an SqlDataAdapter in C#

I'm assuming you're using the SqlDataAdapter to populate a DataTable or DataSet object. If this is the case and you want the changes to the DataTable/DataSet to be persisted to your database and you require using stored procs then you'd need to set the UpdateCommand, InsertCommand, and/or DeleteCommand properties.

Example:

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM WHATEVER", connection);
adapter.UpdateCommand = new SqlCommand()
{
    Connection = connection,
    CommandText = "EXEC myStoredProc",
    CommandType = CommandType.StoredProcedure
};
//Don't forget to set your parameters!

Otherwise if you don't need the adapter just use a SqlCommand and call ExecuteNonQuery()

SqlDataAdapter - combine stored procedure and Select statement with parameters

The trick is the exec function.

From the docs

It Executes a command string or character string within a Transact-SQL
batch, or one of the following modules: system stored procedure,
user-defined stored procedure, CLR stored procedure, scalar-valued
user-defined function, or extended stored procedure.

So you could do this:

DataSet ds = new DataSet();
using (SqlConnection connection = new SqlConnection(myConnectionString))
{
    connection.Open();
    SqlDataAdapter cmd = new SqlDataAdapter("SELECT id FROM Table1 WHERE id=@myid; Exec myStoredProcedure @myid", connection);
    cmd.SelectCommand.Parameters.AddWithValue("@myid",myid);
    cmd.Fill(ds);
}

.net: Storing a .net datatable with dataAdapter from a stored procedure

Change your SQL text in your command.

da.SelectCommand = new OdbcCommand(
    "{ call PKG_NAME.GET_CLIENT(?, ?, ?) }",
    conn);

• Your 1st parameter "layer_in" should be OracleType.VarChar to match your stored
  procedure 1st parameter DataType, should it not?
• da.Fill(ds); should be used instead of da.Fill(ds,"RESULT_NAME?"); if you dont know the name of the result set.

Stored procedure with table type parameter returns data but SqlDataAdapter will not fill

I changed the name of the SqlDataAdapter and it works. I'm not sure why, I didn't name it that anywhere else in the file. Cleaning the solution didn't solve this either. At least it works.

.NET: SqlDataAdapter: DataTable: Output Parameters: Return JSON

As I couldn't edit the stored procedure as per @Charlieface's solution, I ended up doing it this way:

public class MyClass
{
    public string Transactions { get; set; }
    public int ReturnCode { get; set; }
    public string ReturnString { get; set; }
}

In my C# code,

MyClass myClass = new MyClass();
//myClass.Transactions = dataTable.AsEnumerable(); //not sure how to convert 
myClass.Transactions = Newtonsoft.Json.JsonConvert.SerializeObject(dataTable); //this gives me a string that I further parse in my javascript code
myClass.ReturnCode = returnCode;
myClass.ReturnString = returnStr;

Javascript Code:

$.ajax({
        url: '/?handler=Transactions&p1=' + someValue1+ '&p2=' + someValue2,
        type: "GET",
        success: function (data) {
            var allData= jQuery.parseJSON(JSON.stringify(data));                 
            var justTransactions= JSON.stringify(eval("(" + allData.Transactions + ")"));                
        },
        error: function (_error) {
            console.log("Error is " + _error);
        }
    });

---

Related Topics