I do not clearly understand how to format the SqlDataAdapter
for output variables when working with C#
Error Message:
Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
Code Example (Stored Procedure works fine):
private DataTable updateOrdEodHold(DataTable tb, out string mnpft,
out string authld, out string trd, out string hld, out string extnow)
{
// Start the connection string.
string connstr = ConfigurationManager.ConnectionStrings
["LocalSqlServer"].ConnectionString;
SqlConnection myConn = new SqlConnection(connstr);
// Declare symbol and assign for Errors Catch Exception.
string Symbol = "";
string sqlComm = "dbo.UpdateOrdEodHold";
DataTable HoldVals = new DataTable();
SqlDataAdapter dataAdp = new SqlDataAdapter(sqlComm, myConn);
dataAdp.SelectCommand.CommandType = CommandType.StoredProcedure;
string ticker = (string)Convert.ToString(tb.Rows[0]["Ticker"]);
// update Symbol for Catch ex
Symbol = ticker.ToString();
String company = (string)Convert.ToString(tb.Rows[0]["Company"]);
String avgprofit = (string)Convert.ToString(tb.Rows[0]["AvgProfit"]);
String extdte = (string)Convert.ToString(tb.Rows[0]["ExitDate"]);
dataAdp.SelectCommand.Parameters.Clear();
dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@ticker",
SqlDbType.VarChar, 10));
dataAdp.SelectCommand.Parameters["@ticker"].Value =
(string)ticker.ToString();
dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@company",
SqlDbType.VarChar, 25));
dataAdp.SelectCommand.Parameters["@company"].Value =
(string)company.ToString();
dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@avgpft",
SqlDbType.VarChar, 10));
dataAdp.SelectCommand.Parameters["@avgpft"].Value =
(string)avgprofit.ToString();
dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@mnpft",
SqlDbType.VarChar, 10));
dataAdp.SelectCommand.Parameters["@mnpft"].Direction =
ParameterDirection.Output;
dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@authld",
SqlDbType.VarChar, 6));
dataAdp.SelectCommand.Parameters["@authld"].Direction =
ParameterDirection.Output;
dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@hld",
SqlDbType.VarChar, 6));
dataAdp.SelectCommand.Parameters["@hld"].Direction =
ParameterDirection.Output;
dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@trd",
SqlDbType.VarChar, 6));
dataAdp.SelectCommand.Parameters["@trd"].Direction =
ParameterDirection.Output;
dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@extnow",
SqlDbType.VarChar, 6));
dataAdp.SelectCommand.Parameters["@extnow"].Direction =
ParameterDirection.Output;
dataAdp.SelectCommand.Parameters.Add(new SqlParameter("@extdte",
SqlDbType.VarChar, 15));
dataAdp.SelectCommand.Parameters["@extdte"].Value =
(string)extdte.ToString();
dataAdp.Fill(HoldVals);
mnpft = HoldVals.Rows[0]["MinProfit"].ToString();
authld = HoldVals.Rows[0]["AutoHold"].ToString();
trd = HoldVals.Rows[0]["Trade"].ToString();
hld = HoldVals.Rows[0]["Hold"].ToString();
extnow = HoldVals.Rows[0]["ExitNow"].ToString();
return HoldVals;
}
SqlAdapter is used to fill a dataset. SqlCommand can be used for any purpose you have in mind related to Create/Read/Update/Delete operations, stored procedure execution and much more.
The Fill method of the DataAdapter is used to populate a DataSet with the results of the SelectCommand of the DataAdapter . Fill takes as its arguments a DataSet to be populated, and a DataTable object, or the name of the DataTable to be filled with the rows returned from the SelectCommand .
Answers. Datareaders are fast compare to DataAdapters/DataSets because of the following reason. DataReader offers better performance because it avoids the performance and memory overhead associated with the creation of the DataSet.
SqlDataAdapter is used in conjunction with SqlConnection and SqlCommand to increase performance when connecting to a SQL Server database. If you are using SQL Server stored procedures to edit or delete data using a DataAdapter , make sure that you do not use SET NOCOUNT ON in the stored procedure definition.
You need to hold a reference to the Output parameter variable so that you can access the value returned to it using parameter.Value once the adapter has executed the command.
//Create the parameter
SqlParameter parameter = new SqlParameter("@mnpft", SqlDbType.VarChar);
//Set the parameter direction as output
parameter.Direction = ParameterDirection.Output;
sqlCommand.Parameters.Add(parameter);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);
sqlAdapter.Fill(dataSet);
//Fetch the output parameter after doing the Fill
string outputValue = Convert.ToString(parameter.Value);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With