DBHelper 代码

public abstract class DBHelper { protected static string connectionString = ConfigurationManager.ConnectionStrings["SQL"].ConnectionString; /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteCommand(string SQLString) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(SQLString, connection)) { try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); } finally { connection.Close(); } } } } /// <summary> /// 执行带参数的sql语句，返回受影响的行数 /// </summary> /// <param name="sql"></param> /// <param name="values"></param> /// <returns></returns> public static int ExecuteCommand(string sql, params SqlParameter[] values) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(sql, connection)) { try { cmd.Parameters.AddRange(values); connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); } finally { connection.Close(); } } } } /// <summary> /// 执行SQL，返回首行首列 /// </summary> /// <param name="safeSql"></param> /// <returns></returns> public static int GetScalar(string safeSql) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(safeSql, connection)) { try { connection.Open(); int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; } catch (System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); } finally { connection.Close(); } } } } /// <summary> /// 执行带参数Sql，返回首行首列 /// </summary> /// <param name="sql"></param> /// <param name="values"></param> /// <returns></returns> public static int GetScalar(string sql, params SqlParameter[] values) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand(sql, connection)) { try { cmd.Parameters.AddRange(values); connection.Open(); int result = Convert.ToInt32(cmd.ExecuteScalar()); return result; } catch (System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); } finally { connection.Close(); } } } } /// <summary> /// 执行sql，返还SqlDataReader /// </summary> /// <param name="safeSql"></param> /// <returns></returns> public static SqlDataReader GetReader(string safeSql) { SqlConnection connection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(safeSql, connection); try { connection.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return reader; } catch (System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); } finally { //connection.Close(); } //using (SqlConnection connection = new SqlConnection(connectionString)) //{ // using (SqlCommand cmd = new SqlCommand(safeSql, connection)) // { // try // { // connection.Open(); // SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // return reader; // } // catch (System.Data.SqlClient.SqlException E) // { // throw new Exception(E.Message); // } // finally // { // //connection.Close(); // } // } //} } /// <summary> /// 执行带参数SQl返回SqlDataReader /// </summary> /// <param name="sql"></param> /// <param name="values"></param> /// <returns></returns> public static SqlDataReader GetReader(string sql, params SqlParameter[] values) { SqlConnection connection = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(sql, connection); try { cmd.Parameters.AddRange(values); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); return reader; } catch (System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); } finally { //connection.Close(); } //using (SqlConnection connection = new SqlConnection(connectionString)) //{ // using (SqlCommand cmd = new SqlCommand(sql, connection)) // { // try // { // cmd.Parameters.AddRange(values); // connection.Open(); // SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // return reader; // } // catch (System.Data.SqlClient.SqlException E) // { // throw new Exception(E.Message); // } // finally // { // //connection.Close(); // } // } //} } /// <summary> /// 执行sql，返回DataTable /// </summary> /// <param name="safeSql"></param> /// <returns></returns> public static DataTable GetDataSet(string safeSql) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataTable dt; DataSet ds = new DataSet(); try { connection.Open(); using (SqlCommand cmd = new SqlCommand(safeSql, connection)) { SqlDataAdapter command = new SqlDataAdapter(cmd); command.Fill(ds, "ds"); dt = ds.Tables[0]; } } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return dt; } } /// <summary> /// 执行带参数的Sql，返回DataTable /// </summary> /// <param name="sql"></param> /// <param name="values"></param> /// <returns></returns> public static DataTable GetDataSet(string sql, params SqlParameter[] values) { using (SqlConnection connection = new SqlConnection(connectionString)) { DataTable dt; DataSet ds = new DataSet(); try { connection.Open(); using (SqlCommand cmd = new SqlCommand(sql, connection)) { cmd.Parameters.AddRange(values); SqlDataAdapter command = new SqlDataAdapter(cmd); command.Fill(ds, "ds"); dt = ds.Tables[0]; } } catch (System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } return dt; } } private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text;//cmdType; if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } } }