CODEWITHSUNDEEP
c#sql-serverasp.net-mvc-3
user655336
user655336

Reputation: 9

Pass in parameter in sql

How can I pass parameters for in attribute in C# to sqlserver. I have to execute a stored procedure which expects parameters for in attribute.Please Let me Know how can I do that.....

Upvotes: 0

Views: 1197

Answers (1)

PCurd
PCurd

Reputation: 431

One option to execute stored procedures is to use the SqlCommand object and the SqlParameter object.

You could build a collection of SqlParameter objects to represent your parameters and a function to run an SqlCommand to fill a DataSet, for example.

You could implement this with two functions, one a function that builds a list of SqlParameters which you call once for each parameter to add it to the list and one a function that takes that list and the name of the stored procedure etc and runs the stored procedure.

If you are only doing this one off these functions could be a bit of overhead so I've also shown an example doing it all in one function.

In my example below I've shown how to return a DataSet but you could use cmdt.ExecuteNonQuery() instead as in the standalone example.

A helper function to "build" a parameters list:

private void BuildSqlParameter(string ParameterName, SqlDbType DBType, 
    string Value, ParameterDirection dir, ref List<SqlParameter> paras)
{
     SqlParameter temp;
     temp = new SqlParameter(ParameterName, DBType);
     temp.Value = Value;
     temp.Direction = dir;
     paras.Add(temp);
}

A function to run the stored procedure:

private DataSet GetStoredProcedureWithParameters(SqlConnection SQLC, 
    string StoredProcedureName, List<SqlParameter> paras)
{
    SQLC.Open();
    SqlCommand cmdt = new SqlCommand(StoredProcedureName, WEL);
    cmdt.CommandType = CommandType.StoredProcedure;
    cmdt.Parameters.AddRange(paras.ToArray());
    SqlDataAdapter dat = new SqlDataAdapter(cmdt);
    DataSet dst = new DataSet();
    dat.Fill(dst);
    SQLC.Close();
    return dst;
}

And then call like this: public SqlConnection MySQLConnection = new SqlConnection("Database=MyDB;SERVER=MyServer;Failover Partner=MyServer2;UID=MyID;PWD=MyPassword"); List paras = new List(); BuildSqlParameter("@ParameterName", SqlDbType.VarChar, ParameterValue, ParameterDirection.Input, ref paras); DataSet local = GetStoredProcedureWithParameters(MySQLConnection, "MyStoredProcedureName", paras);

Where:

  • MySQLConnection is an SqlConnection (defined in this example for completeness)
  • MyDB is the name of your database
  • MyServer is the name of your server
  • MyServer2 is the name of a failover server (not necessary)
  • MyID is your SQL Server username
  • MyPassword is your SQL Server password
  • "@ParameterName" is the name of your parameter
  • SqlDbType.VarChar is an example datatype for the second parameter
  • ParameterValue is the variable you wish to pass to the parameter
  • MyStoredProcedureName is the name of the stored procedure you want to run

If you need more than one parameter, just run BuildSqlParameter as many times as you need.

Or, if you just want some one off code do it like this:

MySQLConnection.Open();
SqlCommand cmdt = new SqlCommand("MyStoredProcedureName", 
    MySQLConnection);
cmdt.CommandType = CommandType.StoredProcedure;
SqlParameter para = cmdt.Parameters.Add("@ParameterName", 
    System.Data.SqlDbType.VarChar);
para.Value = ParameterValue;
para.Direction = ParameterDirection.Input;
cmdt.ExecuteNonQuery();
MySQLConnection.Close();

To add more parameters this would be like:

SqlParameter para2 = cmdt.Parameters.Add("@ParameterName2", 
System.Data.SqlDbType.VarChar);
para2.Value = ParameterValue2;
para2.Direction = ParameterDirection.Input;

Where:

  • "@ParameterName2" is the name of your second parameter
  • ParameterValue2 is the variable you wish to pass to the second parameter
  • System.Data.SqlDbType.VarChar is an example datatype for the second parameter

Upvotes: 2

Related Questions