CODEWITHSUNDEEP
c#sqlsql-serversqlcommand
Sergio Tapia
Sergio Tapia

Reputation: 41168

How to add a number to a Sqlcommand.Parameters?

Here's my method:

public void EjecutarGuardar(string ProcedimientoAlmacenado, object[] Parametros)
        {
            SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            SqlCommand Command = Connection.CreateCommand();
            Command.CommandText = ProcedimientoAlmacenado;
            Command.CommandType = CommandType.StoredProcedure;
            foreach (object X in Parametros)
            {
                Command.Parameters.Add(X);
            }            

            Connection.Open();
            Command.ExecuteNonQuery();
            Connection.Close();

            Connection.Dispose();
        }

Say I added an int to my object array PARAMETROS, when it reaches the foreach statement I get an error:

The SqlParameterCollection only accepts non-null SqlParameter type objects, not Int32 objects.

So, how can I load all of my parameters outside of this class, and then place them all into a generic array, and pass it on to this method to do it's magic. Any help?

Edit: A friend sent me this code, would it work? I cant understand what it's doing. :S

protected void CargarParametros(SqlCommand Com, System.Object[] Args)
        {                
            for (int i = 1; i < Com.Parameters.Count; i++)
            {
                SqlParameter P = (SqlParameter)Com.Parameters[i];
                if (i <= Args.Length )
                    P.Value = Args[i - 1];
                else
                    P.Value = null;
            }
        }

Upvotes: 1

Views: 7452

Answers (5)

marc_s
marc_s

Reputation: 754963

Your SqlCommand wraps up the stored procedure. In order to call it, you will need to create an instance of a SqlParameter for each parameter that you pass into or get out of the stored procedure. You cannot just simply add your values - how would ADO.NET know which value to assign to which parameter??

Each SqlParameter contains things like:

  • a name
  • its datatype
  • possibly restrictions (on size, length)
  • and possibly a value

So in your case, your statement should look something like this:

SqlCommand Command = Connection.CreateCommand();
Command.CommandText = ProcedimientoAlmacenado;
Command.CommandType = CommandType.StoredProcedure;

foreach (object X in Parametros)
{
    SqlParameter param = new SqlParameter();
    param.ParameterName = Parametros.Name;
    // you need to find a way to determine what DATATYPE the
    // parameter will hold - Int, VarChar etc.
    param.SqlDbType = SqlDbType.Int;  
    param.Value = Parametros.Value;

    Command.Parameters.Add(param);
}

So, just adding values isn't going to work - you need to capture those parameters with their name, data type, length etc. and their values.

Marc

Upvotes: 1

KV Prajapati
KV Prajapati

Reputation: 94645

Use AddWithValue method,

string []para={"@eno","@ename","@edate"};
object []val={11,"A","1-1-2002"};

System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(@"");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandText = "proc_name";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
for(int i=0;i<para.Length;i++){
  cmd.Parameters.AddWithValue(para[i], val[i]);
}

cn.Open();
cmd.ExecuteNonQuery();
cn.Close();

Upvotes: 4

tsilb
tsilb

Reputation: 8037

You can also use an abbreviated version. ADO.NET will know it's a number and will insert the proper datatype if you do this:

Command.Parameters.Add(new SqlParameter("@YourParameterID", 4));

etc.

Also, make sure you're not inserting a NULL into a NOT NULL data field, and is implicitly castable to type SqlParameter.

Upvotes: 0

Sim&#243;n
Sim&#243;n

Reputation: 455

I'm not an expert, but I guess you should name your parameters; so instead of just having an array of object, you should consider having an array of key-value pairs.

Then, you should take a look at one of SqlParameter constructors: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.sqlparameter.aspx

Upvotes: 0

Francis B.
Francis B.

Reputation: 7208

You need to do this:

Sql command example: 

"SELECT * FROM YourTable WHERE FirstColumn = @YourParameterID;"

To add a parameter for this command:

Command.Parameters.Add(new SqlParameter("@YourParameterID", SqlDbType.Int).Value = X);

Upvotes: 0

Related Questions