c# calling Stored Procedures

si_69

Hi All I am trying to create a generic method for calling stored procedures I would like to pass in the Parameters in via an array At the moment i am having trouble adding the parameters to the SqlCommand This is what i have so far Can anyone advise thanks Simon Calling the method string[] paramNames = new string[1]; paramNames[0] = "@date = 2012-1-1"; string err="";

WriteToDatabase("exec LoadData", CommandType.StoredProcedure, paramNames, out err);

Method

public static bool WriteToDatabase(
string sql,
CommandType commandType,
string[] paramNames,
out string errorText)
{
bool success = false;
errorText = "";
try
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
List parameters = new List();

            foreach (string paramName in paramNames) 
            { 
                parameters.Add(new SqlParameter() { ParameterName = paramName }); 
            } 

            using (SqlCommand command = new SqlCommand() 
            { 
                Connection = connection, 
                CommandText = sql, 
                CommandType = commandType, 
                Parameters = parameters 

            }) 
             command.ExecuteNonQuery();   

             connection.Close(); 
        } 


    }

Pete OHanlon

Well, you have supplied as the value for paramName @date = 2012-1-1. That's not a parameter name, that's a parameter name and associated value. Also, what type is your parameter?

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

jschell

si_69 wrote:

I am trying to create a generic method for calling stored procedures

Normally that isn't a good idea. It adds complexity and does not have a significant impact on maintenance costs.

si_69 wrote:

Can anyone advise

So if you still want to do it... 1. You must create a sql statement dynamically with matching parameter place holders corresponding to those in the collection(array.) So you are creating SQL dynamically. 2. You must then create a SqlCommand with at sql. Then you populate it the parameters with the collection.

PIEBALDconsult

It probably shouldn't be static. 1) You should probably not keep creating the Connection and Command -- create one and hold onto it (in the closed state). 2) Where does ConnectionString come from? 3) Here's your code with a few changes. It compiles, but it's untested:

public static bool WriteToDatabase(
string sql,
System.Data.CommandType commandType,
out string errorText,
params System.Tuple<string,object>[] values
)
{
bool success = true;
errorText = null;

 using (System.Data.IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionString)) 
 { 
   try 
   {                        
     using (System.Data.IDbCommand command = connection.CreateCommand() )
     { 
       command.CommandText = sql ;
       command.CommandType = commandType ;

       foreach (System.Tuple<string,object> param in values ) 
       {                  
         System.Data.IDbDataParameter p = command.CreateParameter() ;
         p.ParameterName = param.Item1 ;
         p.Value = param.Item2 ;
         command.Parameters.Add(p); 
       } 

      connection.Open();  
      command.ExecuteNonQuery();   
     } 
   } 
   catch ( System.Exception err )
   {
     success = false ;
     errorText = err.ToString() ;
   }                             
   finally
   {
      connection.Close(); 
   }
 } 
   
 return ( success ) ;

}

 WriteToDatabase
 (
   "blah blah blah" 
 , 
   System.Data.CommandType.Text 
 ,  
   out error
 ,
   new System.Tuple<string,object> ( "@date" , System.DateTime.Now ) 
 ) ;