c# SQLHelper
private static readonly string connectionString = @"Data source=server;Initial Catalog=dbname;User ID=username;Password=userpass"; public static int ExecuteNonQuery(string sql, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, cn, sql, commandParameters); //finally, execute the command. int retval = cmd.ExecuteNonQuery(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } } public static DataSet ExecuteDataset(string sql, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, cn, sql, commandParameters); //create the DataAdapter & DataSet SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); //fill the DataSet using default values for DataTable names, etc. da.Fill(ds); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); //return the dataset return ds; } } public static object ExecuteScalar(string sql, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, cn, sql, commandParameters); //execute the command & return the results object retval = cmd.ExecuteScalar(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } } /// <summary> /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters /// to the provided command. /// </summary> /// <param name="command">the SqlCommand to be prepared</param> /// <param name="connection">a valid SqlConnection, on which to execute this command</param> /// <param name="transaction">a valid SqlTransaction, or 'null'</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param> private static void PrepareCommand(SqlCommand command, SqlConnection connection, string commandText, SqlParameter[] commandParameters) { //if the provided connection is not open, we will open it if (connection.State != ConnectionState.Open) { connection.Open(); } //associate the connection with the command command.Connection = connection; //set the command text (stored procedure name or SQL statement) command.CommandText = commandText; //set the command type command.CommandType = CommandType.Text; //attach the command parameters if they are provided if (commandParameters != null) { foreach (SqlParameter p in commandParameters) { //check for derived output value with no value assigned if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null)) { p.Value = DBNull.Value; } command.Parameters.Add(p); } } return; }