c# SQLHelper

100

101

102

103

104

105

106

107

108

109

110

111

112

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;
}