I have a wrapper class for executing database queries from an ASP.NET application. Please provide suggestions on how I can improve this class. It is working fine. Anyone can use this class in their ASP.NET application.
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
public class SPWrapper
{
private string _ConnectionString = string.Empty;
private Hashtable _ParamHT = new Hashtable();
private QueryMode _QMode = QueryMode.ExecuteNonQuery;
private string _SPName = string.Empty;
private SqlCommand _SQLCmd;
private string outputParams;
private const string prfOut = "#";
private SqlDataReader SPDataReader;
private SqlParameter SPParam;
private SqlTransaction SPTransaction;
public SPWrapper()
{
this._ConnectionString = ConfigurationManager.ConnectionStrings["MS_conn_string"].ToString();
}
public bool DBConnection()
{
try
{
SqlConnection connection = new SqlConnection(this._ConnectionString);
return true;
}
catch (Exception exception)
{
ErrorLog.AddException(exception);
return false;
}
}
public object ExecuteSP()
{
object obj3;
SqlConnection connection = new SqlConnection(this._ConnectionString);
try
{
SqlDataAdapter adapter;
this._SQLCmd = new SqlCommand(this._SPName, connection);
this._SQLCmd.CommandType = CommandType.StoredProcedure;
string pName = string.Empty;
bool flag = false;
if ((this._ParamHT != null) && (this._ParamHT.Count != 0))
{
Hashtable pDataType = (Hashtable) this.GetPDataType();
foreach (object obj2 in this._ParamHT.Keys)
{
pName = obj2.ToString();
if (pName.Substring(0, 1) == "#")
{
flag = true;
pName = pName.Replace("#", "");
}
pName = "@" + pName;
this.SetParam(pName, pDataType[pName].ToString(), this._ParamHT[obj2].ToString(), flag);
flag = false;
}
}
this._SQLCmd.Connection.Open();
DataSet dataSet = new DataSet();
if (this.Mode == QueryMode.ExecuteNonQuery)
{
this._SQLCmd.ExecuteNonQuery();
adapter = new SqlDataAdapter(this._SQLCmd);
this.SetParamHT(adapter);
adapter.Fill(dataSet);
return dataSet;
}
if (this.Mode == QueryMode.ExecuteFunction)
{
SqlDataReader reader = this._SQLCmd.ExecuteReader();
while (reader.Read())
{
return reader[0];
}
return 0;
}
this._SQLCmd.ExecuteScalar();
adapter = new SqlDataAdapter(this._SQLCmd);
this.SetParamHT(adapter);
obj3 = true;
}
catch (SqlException exception)
{
ErrorLog.AddSqlException(exception);
obj3 = false;
}
catch (Exception exception2)
{
ErrorLog.AddException(exception2);
obj3 = false;
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
return obj3;
}
private bool ExistsInHT(string key)
{
return this._ParamHT.ContainsKey(key);
}
public object GetPDataType()
{
object obj2;
SqlConnection connection = new SqlConnection(this._ConnectionString);
try
{
SqlConnection connection2 = new SqlConnection(this._ConnectionString);
SqlCommand selectCommand = new SqlCommand("select dbo.syscolumns.name,type_name(dbo.syscolumns.xusertype) as dt, dbo.syscolumns.isoutparam from dbo.sysobjects inner join dbo.syscolumns on dbo.syscolumns.id = dbo.sysobjects.id where dbo.sysobjects.name = '" + this._SPName + "'", connection2);
connection2.Open();
selectCommand.ExecuteNonQuery();
DataTable dataTable = new DataTable();
Hashtable hashtable = new Hashtable();
new SqlDataAdapter(selectCommand).Fill(dataTable);
connection2.Close();
connection2.Dispose();
if (dataTable.Rows.Count > 0)
{
foreach (DataRow row in dataTable.Rows)
{
hashtable.Add(row[0], row[1]);
}
}
obj2 = hashtable;
}
catch (SqlException exception)
{
ErrorLog.AddSqlException(exception);
obj2 = null;
}
catch (Exception exception2)
{
ErrorLog.AddException(exception2);
obj2 = null;
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return obj2;
}
public void SetParam(string pName, string pDataType, string pValue, bool flag)
{
switch (pDataType.ToLower())
{
case "nvarchar":
this.SPParam = this._SQLCmd.Parameters.Add(pName, SqlDbType.NVarChar, 0xfa0);
if (flag)
{
this.SPParam.Direction = ParameterDirection.Output;
}
if (pValue == "")
{
this.SPParam.Value = DBNull.Value;
}
else
{
this.SPParam.Value = pValue;
}
break;
case "varchar":
this.SPParam = this._SQLCmd.Parameters.Add(pName, SqlDbType.VarChar, 0xfa0);
if (flag)
{
this.SPParam.Direction = ParameterDirection.Output;
}
if (pValue == "")
{
this.SPParam.Value = DBNull.Value;
}
else
{
this.SPParam.Value = pValue;
}
break;
case "text":
this.SPParam = this._SQLCmd.Parameters.Add(pName, SqlDbType.Text);
if (flag)
{
this.SPParam.Direction = ParameterDirection.Output;
}
if (pValue == "")
{
this.SPParam.Value = DBNull.Value;
}
else
{
this.SPParam.Value = pValue;
}
break;
case "char":
this.SPParam = this._SQLCmd.Parameters.Add(pName, SqlDbType.Char);
if (flag)
{
this.SPParam.Direction = ParameterDirection.Output;
}
if (pValue == "")
{
this.SPParam.Value = DBNull.Value;
}
else
{
this.SPParam.Value = pValue;
}
break;
case "int":
this.SPParam = this._SQLCmd.Parameters.Add(pName, SqlDbType.Int);
if (flag)
{
this.SPParam.Direction = ParameterDirection.Output;
}
if (pValue == "")
{
this.SPParam.Value = DBNull.Value;
}
else
{
this.SPParam.Value = pValue;
}
break;
case "smallint":
this.SPParam = this._SQLCmd.Parameters.Add(pName, SqlDbType.SmallInt);
if (flag)
{
this.SPParam.Direction = ParameterDirection.Output;
}
if (pValue == "")
{
this.SPParam.Value = DBNull.Value;
}
else
{
this.SPParam.Value = pValue;
}
break;
case "bigint":
this.SPParam = this._SQLCmd.Parameters.Add(pName, SqlDbType.BigInt);
if (flag)
{
this.SPParam.Direction = ParameterDirection.Output;
}
if (pValue == "")
{
this.SPParam.Value = DBNull.Value;
}
else
{
this.SPParam.Value = pValue;
}
break;
case "numeric":
this.SPParam = this._SQLCmd.Parameters.Add(pName, SqlDbType.Decimal, 0x26);
if (flag)
{
this.SPParam.Direction = ParameterDirection.Output;
}
if (pValue == "")
{
this.SPParam.Value = DBNull.Value;
}
else
{
this.SPParam.Value = Convert.ToDecimal(pValue);
}
break;
case "money":
this.SPParam = this._SQLCmd.Parameters.Add(pName, SqlDbType.Money);
if (flag)
{
this.SPParam.Direction = ParameterDirection.Output;
}
if (pValue == "")
{
this.SPParam.Value = DBNull.Value;
}
else
{
this.SPParam.Value = pValue;
}
break;
case "decimal":
this.SPParam = this._SQLCmd.Parameters.Add(pName, SqlDbType.VarChar, 0x26);
if (flag)
{
this.SPParam.Direction = ParameterDirection.Output;
}
if (pValue == "")
{
this.SPParam.Value = DBNull.Value;
}
else
{
this.SPParam.Value = pValue;
}
break;
case "float":
this.SPParam = this._SQLCmd.Parameters.Add(pName, SqlDbType.Float);
if (flag)
{
this.SPParam.Direction = ParameterDirection.Output;
}
if (pValue == "")
{
this.SPParam.Value = DBNull.Value;
}
else
{
this.SPParam.Value = Convert.ToDouble(pValue);
}
break;
case "datetime":
this.SPParam = this._SQLCmd.Parameters.Add(pName, SqlDbType.DateTime);
if (flag)
{
this.SPParam.Direction = ParameterDirection.Output;
}
if (pValue == "")
{
this.SPParam.Value = DBNull.Value;
}
else
{
this.SPParam.Value = pValue;
}
break;
case "date":
this.SPParam = this._SQLCmd.Parameters.Add(pName, SqlDbType.DateTime);
if (flag)
{
this.SPParam.Direction = ParameterDirection.Output;
}
if (pValue == "")
{
this.SPParam.Value = DBNull.Value;
}
else
{
this.SPParam.Value = pValue;
}
break;
case "bit":
this.SPParam = this._SQLCmd.Parameters.Add(pName, SqlDbType.Bit);
if (flag)
{
this.SPParam.Direction = ParameterDirection.Output;
}
if (pValue == "")
{
this.SPParam.Value = DBNull.Value;
}
else
{
this.SPParam.Value = pValue;
}
break;
default:
this.SPParam = this._SQLCmd.Parameters.Add(pName, SqlDbType.VarChar, 0xfa0);
if (flag)
{
this.SPParam.Direction = ParameterDirection.Output;
}
if (pValue == "")
{
this.SPParam.Value = DBNull.Value;
}
else
{
this.SPParam.Value = pValue;
}
break;
}
}
private void SetParamHT(SqlDataAdapter sda)
{
try
{
for (int i = 0; i < sda.SelectCommand.Parameters.Count; i++)
{
string key = "#" + sda.SelectCommand.Parameters[i].ParameterName.Replace("@", "");
if (this.ExistsInHT(key))
{
this._ParamHT[key] = sda.SelectCommand.Parameters[i].Value.ToString();
}
}
}
catch (Exception exception)
{
ErrorLog.AddException(exception);
}
}
public string ConnectionString
{
get
{
return this._ConnectionString;
}
set
{
this._ConnectionString = value;
}
}
public QueryMode Mode
{
get
{
return this._QMode;
}
set
{
this._QMode = value;
}
}
public Hashtable ParamHT
{
get
{
return this._ParamHT;
}
set
{
this._ParamHT = value;
}
}
public string SPName
{
get
{
return this._SPName;
}
set
{
this._SPName = value;
}
}
public enum QueryMode
{
ExecuteFunction = 5,
ExecuteNonQuery = 1,
ExecuteReader = 2,
ExecuteScalar = 3,
ExecuteXmlReader = 4
}
}