DBHelper

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Configuration; using System.Data; namespace AdamWorld.HuntSchool.SQLServerDAL { ///

/// FileName: DBHelper.cs /// CreateDate: 2008-12-29 /// Author: 何伟 ///

public class DBHelper { #region 私有字段 //连接字符串 private static string conString; //数据库连接 private static SqlConnection cnn; #endregion #region 构造方法 //构造方法 public DBHelper() { conString = ConfigurationManager.ConnectionStrings["SQLServer"].ToString(); } #endregion #region 数据库连接 #region 开启连接 ///

/// 开启连接（只读） ///

public static SqlConnection Cnn { //始终保证打开的连接是一个完整的可用的连接 get { if (cnn == null) { cnn = new SqlConnection(conString); } if (cnn.State == ConnectionState.Closed) { cnn.Open(); } if (cnn.State == ConnectionState.Broken) { cnn.Close(); cnn.Open(); } return DBHelper.cnn; } } #endregion #region 关闭连接 ///

/// 关闭连接 ///

public static void closeCnn() { if (cnn.State == ConnectionState.Open) { cnn.Close(); } } #endregion #endregion #region 执行数据库交互命令 #region 执行存储过程 #region 执行有参数的存储过程 ///

/// 执行存储过程进行查询（查） ///

/// 存储过程名 /// 相关参数组 /// 查询结果集 public static DataTable executeProcGet(string procName, SqlParameter[] ps) { SqlCommand cmd = cnn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = procName; cmd.Parameters.AddRange(ps); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { using (DataTable dt = new DataTable()) { sda.Fill(dt); closeCnn(); return dt; } } } ///

/// 执行存储过程进行数据变更（增删改） ///

/// 存储过程名 /// 相关参数组 /// 执行后所影响的表的行数 public static int? executeProcChange(string procName, SqlParameter[] ps) { SqlCommand cmd = cnn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = procName; cmd.Parameters.AddRange(ps); return cmd.ExecuteNonQuery(); } #endregion #region 执行无参数的存储过程 ///

/// 执行存储过程进行查询（查） ///

/// 存储过程名 /// 查询结果集 public static DataTable executeProcGet(string procName) { SqlCommand cmd = cnn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = procName; using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { using (DataTable dt = new DataTable()) { sda.Fill(dt); closeCnn(); return dt; } } } ///

/// 执行存储过程进行数据变更（增删改） ///

/// 存储过程名 /// 执行后所影响的表的行数 public static int? executeProcChange(string procName) { SqlCommand cmd = cnn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = procName; return cmd.ExecuteNonQuery(); } #endregion #endregion #region 执行普通sql语句 ///

/// 执行sql语句进行查询（查） ///

/// sql语句 /// 查询结果集 public static DataTable executeSqlGet(string sql) { SqlCommand cmd = new SqlCommand(sql, cnn); using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) { using (DataTable dt = new DataTable()) { sda.Fill(dt); closeCnn(); return dt; } } } ///

/// 执行sql语句进行数据变更（增删改） ///

/// sql语句 /// 执行后所影响的表的行数 public static int? executeSqlChange(string sql) { SqlCommand cmd = new SqlCommand(sql, cnn); return cmd.ExecuteNonQuery(); } #endregion #region 分页 public static DataTable GetTable(string sql, int start, int size) { using (DataTable dt = new DataTable()) { using (SqlDataAdapter sda = new SqlDataAdapter(sql, cnn)) { sda.Fill(start, size, dt); } return dt; } } #endregion #endregion #region 防SQL注入程序 ///

/// 防SQL注入 ///

/// 文本字段 /// 修正后的字段 public static string Sqlstring(string str) { str = str.Replace("&", "&"); str = str.Replace("<", "<"); str = str.Replace(">", ">"); str = str.Replace("'", "''"); str = str.Replace("*", ""); str = str.Replace("/n", "
"); str = str.Replace("/r/n", "
"); str = str.Replace("select", ""); str = str.Replace("insert", ""); str = str.Replace("update", ""); str = str.Replace("delete", ""); str = str.Replace("create", ""); str = str.Replace("drop", ""); str = str.Replace("delcare", ""); str = str.Replace("--", ""); str = str.Replace("@", ""); if (str.Trim().ToString() == "") { str = null; } return str; } #endregion } }