public class SqlOperation
{
#region attribute
/// <summary>
/// Stored in the Web.config Connection string in
/// </summary>
protected static string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["hao"].ConnectionString;
/// <summary>
/// SqlConnection object
/// </summary>
protected static SqlConnection conn = new SqlConnection();
/// <summary>
/// SqlCommand object
/// </summary>
protected static SqlCommand comm = new SqlCommand();
#endregion
#region Internal function
/// <summary>
/// Open database connection
/// </summary>
private static void ConnectionOpen()
{
if (conn.State != ConnectionState.Open)
{
conn.Close();
conn.ConnectionString = connectionstring;
comm.Connection = conn;
try
{
conn.Open();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
/// <summary>
/// Close the database connection
/// </summary>
private static void ConnectionClose()
{
conn.Close();
conn.Dispose();
comm.Dispose();
}
#endregion
/// <summary>
/// perform SQL statements
/// </summary>
/// <param name="SqlString"> To perform the SQL statements </param>
public static void ExecuteSQL(string SqlString)
{
try
{
ConnectionOpen();
comm.CommandType = CommandType.Text;
comm.CommandText = SqlString;
comm.ExecuteNonQuery();
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
}
/// <summary>
/// Executing stored procedures
/// </summary>
/// <param name="ProcedureName"> Stored procedure name </param>
/// <param name="coll"> The set of parameters required by a stored procedure </param>
public static void ExecuteProcedure(string ProcedureName, params SqlParameter[] coll)
{
try
{
ConnectionOpen();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = ProcedureName;
comm.Parameters.Clear();
for (int i = 0; i < coll.Length; i++)
{
comm.Parameters.Add(coll[i]);
}
comm.ExecuteNonQuery();
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
}
/// <summary>
/// perform Sql Query and return the first 1 The first 1 A record, return object , need to be unpacked when using -> unbox
/// </summary>
/// <param name="sqlstr"> The incoming Sql statements </param>
/// <returns> return object The type of the first 1 Line first 1 records </returns>
public static object ExecuteScalar(string SqlString)
{
object obj = new object();
try
{
ConnectionOpen();
comm.CommandType = CommandType.Text;
comm.CommandText = SqlString;
obj = comm.ExecuteScalar();
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return obj;
}
/// <summary>
/// perform SQL statements , Simultaneous transaction processing
/// </summary>
/// <param name="sqlstr"> To perform the SQL statements </param>
public static void ExecuteTransactionSQL(string SqlString)
{
SqlTransaction trans;
trans = conn.BeginTransaction();
comm.Transaction = trans;
try
{
ConnectionOpen();
comm.CommandType = CommandType.Text;
comm.CommandText = SqlString;
comm.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
}
/// <summary>
/// The specified SQL Query, return DataSet
/// </summary>
/// <param name="sqlstr"> To perform the SQL statements </param>
/// <returns>DataSet</returns>
public static DataSet GetDataSetBySQL(string SqlString)
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
try
{
ConnectionOpen();
comm.CommandType = CommandType.Text;
comm.CommandText = SqlString;
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return ds;
}
/// <summary>
/// Returns through a stored procedure DataSet
/// </summary>
/// <param name="ProcedureName"> Stored procedure name </param>
/// <param name="coll">SqlParameter A collection of </param>
/// <returns>DataSet</returns>
public static DataSet GetDataSetByProcedure(string ProcedureName, params SqlParameter[] coll)
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
try
{
ConnectionOpen();
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Clear();
for (int i = 0; i < coll.Length; i++)
{
comm.Parameters.Add(coll[i]);
}
comm.CommandText = ProcedureName;
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return ds;
}
/// <summary>
/// Returns through a stored procedure DataSet
/// </summary>
/// <param name="ProcedureName"> Stored procedure name </param>
/// <returns>DataSet</returns>
public static DataSet GetDataSetByProcedure(string ProcedureName)
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
try
{
ConnectionOpen();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = ProcedureName;
comm.Parameters.Clear();
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return ds;
}
/// <summary>
/// Returns the specified sql The statement DataTable
/// </summary>
/// <param name="sqlstr"> The incoming Sql statements </param>
/// <returns>DataTable</returns>
public static DataTable GetDataTableBySQL(string SqlString)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
try
{
ConnectionOpen();
comm.CommandType = CommandType.Text;
comm.CommandText = SqlString;
da.SelectCommand = comm;
da.Fill(dt);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return dt;
}
/// <summary>
/// Returns according to the stored procedure DataTable
/// </summary>
/// <param name="ProcedureName"> Stored procedure name </param>
/// <param name="coll">SqlParameter A collection of </param>
/// <returns>DataTable</returns>
public static DataTable GetDataTableByProcedure(string ProcedureName, params SqlParameter[] coll)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
try
{
ConnectionOpen();
comm.Parameters.Clear();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = ProcedureName;
for (int i = 0; i < coll.Length; i++)
{
comm.Parameters.Add(coll[i]);
}
da.SelectCommand = comm;
da.Fill(dt);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return dt;
}
/// <summary>
/// Returns according to the stored procedure DataTable
/// </summary>
/// <param name="ProcedureName"> Stored procedure name </param>
/// <returns>DataTable</returns>
public static DataTable GetDataTableByProcedure(string ProcedureName)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
try
{
ConnectionOpen();
comm.Parameters.Clear();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = ProcedureName;
da.SelectCommand = comm;
da.Fill(dt);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return dt;
}
}