SQLHelper

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


public abstract class SQLHelper
{
// Fields
//Configuration.ConfigurationManager.
//System.Configuration.ConfigurationManager.app


private static readonly string CONN_STRING = ConfigurationSettings.AppSettings["ConStr"];

// Methods
protected SQLHelper()
{
}

public static DataSet ExecuteAdapter(SqlCommand cmd, CommandType cmdType, string cmdText)
{
DataSet set2;
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConStr"]);;
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
set2 = dataSet;
}
catch
{
conn.Close();
throw;
}
finally
{
conn.Close();
}
return set2;
}

public static int ExecuteNonQuery(SqlCommand cmd, CommandType cmdType, string cmdText)
{
int num2;
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConStr"]);;
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText);
num2 = cmd.ExecuteNonQuery();
}
catch
{
conn.Close();
throw;
}
finally
{
conn.Close();
}
return num2;
}

public static int ExecuteNonQuery(SqlTransaction trans, SqlCommand cmd, CommandType cmdType, string cmdText)
{
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText);
return cmd.ExecuteNonQuery();
}

public static SqlDataReader ExecuteReader(SqlCommand cmd, CommandType cmdType, string cmdText)
{
SqlDataReader reader2;
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConStr"]);;
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText);
reader2 = cmd.ExecuteReader();
}
catch
{
conn.Close();
throw;
}
finally
{
conn.Close();
}
return reader2;
}

public static SqlDataReader ExecuteReader(SqlCommand cmd, CommandType cmdType, string cmdText, SqlConnection con)
{
SqlDataReader reader2;
try
{
PrepareCommand(cmd, con, null, cmdType, cmdText);
reader2 = cmd.ExecuteReader();
}
catch
{
con.Close();
throw;
}
finally
{
con.Close();
}
return reader2;
}

public static object ExecuteScalar(SqlCommand cmd, CommandType cmdType, string cmdText)
{
object obj3;
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConStr"]);;
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText);
obj3 = cmd.ExecuteScalar();
}
catch
{
conn.Close();
throw;
}
finally
{
conn.Close();
}
return obj3;
}

private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.CommandType = cmdType;
}

public static string ReturnString(string str_CommandText)
{
SqlConnection conn=new SqlConnection(ConfigurationSettings.AppSettings["ConStr"]);;
SqlCommand cmd = new SqlCommand();
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = str_CommandText;
string returnstring = Convert.ToString(cmd.ExecuteScalar());
return returnstring;
}

public static SqlTransaction StartTransaction(SqlConnection conn)
{
SqlTransaction newTrans = conn.BeginTransaction();
return newTrans;
}

public static void FinishTransaction(bool successFlag, SqlTransaction trans)
{
if (successFlag)
trans.Commit();
else
trans.Rollback();

//trans.Connection.Close();
}
}