sqldbhelper

using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Configuration; using System.Data; namespace Common { public abstract class DbHelperSQL { //格式化字符串 public static string inSQL(string formatStr) { string Str = formatStr; if (formatStr != null && formatStr != string.Empty) { Str = Str.Replace("‘", "‘‘"); } return Str; } //获取连接字符串 public static string ConnectionString { get { string _connectionstring = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString; string ConStringEncrypt = ConfigurationManager.AppSettings["ApplicationServices"]; if (ConStringEncrypt =="true") { _connectionstring = DESEncrypt.Encrypt(_connectionstring); } return _connectionstring; } } #region 执行带参数的SQL语句 // 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms) { SqlConnection connection = new SqlConnection(ConnectionString); SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd,connection,null,SQLString,cmdParms); SqlDataReader myReader=cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return myReader; } catch(System.Data.SqlClient.SqlException e) { throw e; } } // 执行SQL语句，返回影响的记录数 public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms) { using (SqlConnection connection = new SqlConnection(ConnectionString)) { using (SqlCommand cmd = new SqlCommand()) { try { PrepareCommand(cmd,connection,null,SQLString,cmdParms); int rows=cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch(System.Data.SqlClient.SqlException e) { throw e; } } } } // 执行查询语句，返回DataSet 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; if (cmdParms != null) { foreach (SqlParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } } #endregion } }

 

sqldbhelper

标签：