DBTask in C#
This namespace is used to do the dataset operations in sql connections.If we want to do sql connection we just call the namespace and give the parameters example
to insert data to sql server 2005 through dataset.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
//This Code is developed by Anish_maroky.
//This namespace is used to do the dataset operations in sql connections.If we want to do sql connection
//we just call the namespace and give the parameters example
//to insert data to sql server 2005 through dataset.
//use the below example codes in .cs file.
//do ds = DBTask_CS.DBTask_CS.ExecuteDataset(System.Configuration.ConfigurationManager.AppSettings["sDBConn"], CommandType.StoredProcedure, "procedure name", objparams);
//if we want to select data form database through dataset
//do ds = DBTask_CS.DBTask_CS.ExecuteDataset(System.Configuration.ConfigurationManager.AppSettings["sDBConn"], CommandType.StoredProcedure, "procedure name");
namespace DBTask_CS
{
public class DBTask_CS
{
#region "private utility methods & constructors"
//This method opens (if necessary) and assigns a connection, transaction, command type and parameters
//to the provided command.
//Parameters:
//-command - the SqlCommand to be prepared
//-connection - a valid SqlConnection, on which to execute this command
//-transaction - a valid SqlTransaction, or ' null'
//-commandType - the CommandType (stored procedure, text, etc.)
//-commandText - the stored procedure name or T-SQL command
//-commandParameters - an array of SqlParameters to be associated with the command or ' null' if no parameters are required
private static void PrepareCommand(SqlCommand command, SqlConnection connection,SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, bool mustCloseConnection)
{
if (command == null)
{
throw new ArgumentNullException("command");
}
if (commandText == null)
{
throw new ArgumentNullException("commandText");
}
if (connection.State != ConnectionState.Open)
{
connection.Open();
mustCloseConnection = true;
}
else
{
mustCloseConnection = false;
}
//Associate the connection with the command
command.Connection = connection;
//Set the command type
command.CommandType = commandType;
command.CommandText = commandText;
//If we were provided a transaction, assign it.Here I am(Anish) provide it as null always
//if ((transaction != null))
//{
if (transaction != null)
{
// throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
//Attach the command parameters if they are provided
if(commandParameters!=null)
{
AttachParameters(command, commandParameters);
}
return;
}
//This method is used to attach array of SqlParameters to a SqlCommand.
//This method will assign a value of DbNull to any parameter with a direction of
//InputOutput and a value of null.
//This behavior will prevent default values from being used, but
//this will be the less common case than an intended pure output parameter (derived as InputOutput)
//where the user provided no input value.
// Parameters:
//-command - The command to which the parameters will be added
//-commandParameters - an array of SqlParameters to be added to command
private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
{
if (command == null)
{
throw new ArgumentNullException("command");
}
if (commandParameters != null)
{
foreach (SqlParameter p in commandParameters)
{
if (p != null)
{
// Check for derived output value with no value assigned
//if ((p.Direction == ParameterDirection.InputOutput || ParameterDirection.Input) && p.Value = null)
if ((p.Direction == ParameterDirection.InputOutput))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
#endregion
#region "ExecuteDataset"
//Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
//the connection string.
//e.g.:
//Dim ds As DataSet = DBTask.ExecuteDataset("", commandType.StoredProcedure, "GetOrders")
//Parameters:
//-connectionString - a valid connection string for a SqlConnection
//-commandType - the CommandType (stored procedure, text, etc.)
//-commandText - the stored procedure name or T-SQL command
//Returns: A dataset containing the resultset generated by the command
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
{
return ExecuteDataset(connectionString, commandType, commandText,(SqlParameter[])null );
}
//Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
//using the provided parameters.
//e.g.:
//Dim ds As Dataset = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24))
//Parameters:
//-connectionString - a valid connection string for a SqlConnection
//-commandType - the CommandType (stored procedure, text, etc.)
//-commandText - the stored procedure name or T-SQL command
//-commandParameters - an array of SqlParamters used to execute the command
//Returns: A dataset containing the resultset generated by the command
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, SqlParameter[] commandParameters)
{
if ((connectionString == null ))
{
throw new ArgumentNullException("connectionString");
}
SqlConnection connection = null;
try
{
connection = new SqlConnection(connectionString);
connection.Open();
return ExecuteDataset(connection, commandType, commandText, commandParameters);
}
finally
{
if (connection != null)
{
if (connection != null)
{
connection.Dispose();
}
}
}
}
//Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in
//the connection string using the provided parameter values. This method will discover the parameters for the
//stored procedure, and assign the values based on parameter order.
//This method provides no access to output parameters or the stored procedure' s return value parameter.
//e.g.:
//Dim ds As Dataset= ExecuteDataset(connString, "GetOrders", 24, 36)
//Parameters:
//-connectionString - a valid connection string for a SqlConnection
//-spName - the name of the stored procedure
//-parameterValues - an array of objects to be assigned as the input values of the stored procedure
//Returns: A dataset containing the resultset generated by the command
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, SqlParameter[] commandParameters)
{
if (connection == null)
{
throw new ArgumentNullException("connectionString");
}
SqlCommand cmd=new SqlCommand();
DataSet ds=new DataSet();
SqlDataAdapter dataAdatpter;
bool mustCloseConnection = false;
//PrepareCommand(cmd, connection, commandType, commandText, commandParameters, mustCloseConnection);
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, mustCloseConnection);
try
{
//Create the DataAdapter & DataSet
dataAdatpter = new SqlDataAdapter(cmd);
//Fill the DataSet using default values for DataTable names, etc
dataAdatpter.Fill(ds);
//Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
}
finally
{
//If (Not dataAdatpter Is Nothing) Then dataAdatpter.Dispose()
}
if (mustCloseConnection)
{
connection.Close();
}
return ds;
}
#endregion
}
}