class SqlHelper

{

private string sqlConnString; // 数据库连接字符串

private SqlConnection sqlConn = new SqlConnection(); // SQL数据库连接对象

private ConnectionState sqlConnPreState = ConnectionState.Closed; //原来数据库的连接状态

///

/// 数据库连接字符串属性。

///

public string SqlConnectionString

{

get

{

return sqlConnString;

}

set

{

sqlConnString = value;

sqlConn.ConnectionString = sqlConnString;

}

}

public SqlHelper()

{

}

///

/// 构造函数。

///

/// 数据库连接字符串。

public SqlHelper(string strSqlCon)

{

sqlConnString = strSqlCon;

sqlConn.ConnectionString = sqlConnString;

}

///

/// 析构函数。

///

~SqlHelper()

{

try

{

Close();

sqlConn.Dispose();

}

catch (Exception ex)

{

throw ex;

}

}

///

/// 打开数据库连接。

///

public void Open()

{

try

{

if (sqlConn.State == ConnectionState.Closed)

{

sqlConn.Open();

sqlConnPreState = ConnectionState.Open;

}

}

catch (Exception ex)

{

throw ex;

}

}

///

/// 关闭数据库连接。

///

public void Close()

{

try

{

if (sqlConn.State != ConnectionState.Closed)

{

sqlConn.Close();

sqlConnPreState = ConnectionState.Closed;

}

}

catch (Exception ex)

{

throw ex;

}

}

///

/// 获取查询的数据表。

///

/// 要查询的SQL语句。

/// 传入的参数，无参数时使用NULL。

///

public DataTable GetDataTable(string strSQL, params SqlParameter[] parametes)

{

DataSet ds = new DataSet();

DataTable dt = new DataTable();

try

{

//sqlConn.Open();

if (sqlConn.State == ConnectionState.Closed && sqlConn.State == ConnectionState.Closed) //若原来的状态为关闭且当前连接未打开

{

sqlConn.Open();

}

SqlCommand sqlCmd = sqlConn.CreateCommand();

sqlCmd.CommandText = strSQL;

if (parametes != null)

{

sqlCmd.Parameters.Clear();

sqlCmd.Parameters.AddRange(parametes);

}

SqlDataAdapter da = new SqlDataAdapter(sqlCmd);

da.Fill(ds);

dt = ds.Tables[0];

}

catch (Exception ex)

{

throw ex;

}

finally

{

if (sqlConnPreState == ConnectionState.Closed && sqlConn.State != ConnectionState.Closed ) //若原来的状态为关闭且者当前连接未关闭则关闭

{

sqlConn.Close();

}

}

return dt;

}

///

/// 获取查询的数据集。

///

/// 要查询的SQL语句。

/// 传入的参数，无参数时使用NULL。

///

public DataSet GetDataSet(string strSQL, params SqlParameter[] parametes)

{

DataSet ds = new DataSet();

try

{

//sqlConn.Open();

if (sqlConn.State == ConnectionState.Closed && sqlConn.State == ConnectionState.Closed) //若原来的状态为关闭且当前连接未打开

{

sqlConn.Open();

}

SqlCommand sqlCmd = sqlConn.CreateCommand();

sqlCmd.CommandText = strSQL;

if (parametes != null)

{

sqlCmd.Parameters.Clear();

sqlCmd.Parameters.AddRange(parametes);

}

SqlDataAdapter da = new SqlDataAdapter(sqlCmd);

da.Fill(ds);

sqlConn.Close();

}

catch (Exception ex)

{

throw ex;

}

finally

{

if (sqlConnPreState == ConnectionState.Closed && sqlConn.State != ConnectionState.Closed) //若原来的状态为关闭且者当前连接未关闭则关闭

{

sqlConn.Close();

}

}

return ds;

}

///

/// 返回SqlDataReader对象。该函数需要在外部打开和关闭连接操作。

///

/// 传入的SQL语句。

/// 传入的参数，无参数时使用NULL。

///

public SqlDataReader ExecuteReader(string strSQL, params SqlParameter[] parametes)

{

SqlDataReader reader;

try

{

SqlCommand sqlCmd = sqlConn.CreateCommand();

sqlCmd.CommandText = strSQL;

if (parametes != null)

{

sqlCmd.Parameters.Clear();

sqlCmd.Parameters.AddRange(parametes);

}

reader = sqlCmd.ExecuteReader();

}

catch (Exception ex)

{

throw ex;

}

return reader;

}

///

/// 返回执行T-SQL语句受影响的行数。

///

/// 执行的SQL语句。

/// 传入的参数，无参数时使用NULL。

///

public int ExecuteNonQuery(string strSQL, params SqlParameter[] parametes)

{

int sqlInt = -1;

try

{

if (sqlConn.State == ConnectionState.Closed && sqlConn.State == ConnectionState.Closed) //若原来的状态为关闭且当前连接未打开

{

sqlConn.Open();

}

SqlCommand sqlCmd = sqlConn.CreateCommand();

sqlCmd.CommandText = strSQL;

if (parametes != null)

{

sqlCmd.Parameters.Clear();

sqlCmd.Parameters.AddRange(parametes);

}

sqlInt = sqlCmd.ExecuteNonQuery();

}

catch (Exception ex)

{

throw ex;

}

finally

{

if (sqlConnPreState == ConnectionState.Closed && sqlConn.State != ConnectionState.Closed) //若原来的状态为关闭且者当前连接未关闭则关闭

{

sqlConn.Close();

}

}

return sqlInt;

}

///

/// 执行查询并返回第一行第一列的值。

///

/// 执行的SQL语句。

/// 传入的参数，无参数时使用NULL。

///

public object ExecuteScalar(string strSQL, params SqlParameter[] parametes)

{

object obj;

try

{

if (sqlConn.State == ConnectionState.Closed && sqlConn.State == ConnectionState.Closed) //若原来的状态为关闭且当前连接未打开

{

sqlConn.Open();

}

SqlCommand sqlCmd = sqlConn.CreateCommand();

sqlCmd.CommandText = strSQL;

if (parametes != null)

{

sqlCmd.Parameters.Clear();

sqlCmd.Parameters.AddRange(parametes);

}

obj = sqlCmd.ExecuteScalar();

}

catch (Exception ex)

{

throw ex;

}

finally

{

if (sqlConnPreState == ConnectionState.Closed && sqlConn.State != ConnectionState.Closed) //若原来的状态为关闭且者当前连接未关闭则关闭

{

sqlConn.Close();

}

}

return obj;

}

}