Asp.Net数据库操作类

using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; /// <summary> /// Public 的摘要说明 /// </summary> public class PublicClass { //定义一个公用成员 public SqlConnection conn; public PublicClass() { // // TODO: 在此处添加构造函数逻辑 // } #region 建立数据库连接 public void OpenConn() { String strconn = System.Configuration.ConfigurationManager.AppSettings["sqlconn"].ToString(); conn = new SqlConnection(strconn); if (conn.State.ToString().ToLower() == "open") { //连接为打开时 } else { //连接为关闭时 conn.Open(); } } #endregion #region 关闭并释放连接 public void CloseConn() { if (conn.State.ToString().ToLower() == "open") { //连接为打开时 conn.Close(); conn.Dispose(); } } #endregion #region 返回DataReader，用于读取数据 public SqlDataReader DataRead(string sql) { OpenConn(); SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader dr = cmd.ExecuteReader(); return dr; } #endregion #region 返回一个数据集 public DataSet MySqlDataSet(string Sql, string tableName) { OpenConn(); SqlDataAdapter da; DataSet ds = new DataSet(); da = new SqlDataAdapter(Sql, conn); da.Fill(ds, tableName); CloseConn(); return ds; } #endregion //返回一个数据集 public DataView MySqlDataSource(string Sql) { OpenConn(); SqlDataAdapter da; DataSet ds = new DataSet(); da = new SqlDataAdapter(Sql, conn); da.Fill(ds, "temp"); CloseConn(); return ds.Tables[0].DefaultView; } #region 执行一个SQL操作:添加、删除、更新操作 //执行一个SQL操作:添加、删除、更新操作 public void MySqlExcute(string sql) { OpenConn(); SqlCommand cmd; cmd = new SqlCommand(sql, conn); cmd.ExecuteNonQuery(); cmd.Dispose(); CloseConn(); } #endregion #region 执行一个SQL操作:添加、删除、更新操作，返回受影响的行 //执行一个SQL操作:添加、删除、更新操作，返回受影响的行 public int MySqlExecuteNonQuery(string sql) { OpenConn(); SqlCommand cmd; cmd = new SqlCommand(sql, conn); int flag = cmd.ExecuteNonQuery(); return flag; } #endregion public object MySqlExecuteScalar(string sql) { OpenConn(); SqlCommand cmd; cmd = new SqlCommand(sql, conn); object obj = cmd.ExecuteScalar(); cmd.Dispose(); CloseConn(); return obj; } /// <summary> /// 返回DataTable对象 /// </summary> /// <param name="sql">sql语句</param> /// <returns></returns> public DataTable MySqlDataTable(string sql) { OpenConn(); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(sql, conn); da.Fill(ds, "table"); CloseConn(); return ds.Tables["table"]; } /// <summary> /// 返回一个数据集的记录数 /// </summary> /// <param name="sql">传递的sql语句必须为一个统计查询</param> /// <returns></returns> public int MySqlRecordCount(string sql) { //注：Sql 语句必须是一个统计查询 OpenConn(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = sql; cmd.Connection = conn; SqlDataReader dr; dr = cmd.ExecuteReader(); int RecordCount = -1; while (dr.Read()) { RecordCount = int.Parse(dr[0].ToString()); } CloseConn(); return RecordCount; } /// <summary> /// 自定义的功能警告 /// </summary> /// <param name="str">弹出信息框内容</param> public void SetAlert(string str) { HttpContext.Current.Response.Write("<mce:script language='JavaScript' type='text/JavaScript'><!--alert('" + str + "');// --></mce:script>"); } //返回上一页 public void AddErro(string message) { HttpContext.Current.Response.Write("<mce:script type="text/javascript"><!--alert('" + message + "');history.back(-1);// --></mce:script>"); } //关闭窗口 public void SetCloseWindow() { HttpContext.Current.Response.Write("<mce:script language='JavaScript' type='text/JavaScript'><!--window.close();// --></mce:script>"); } /// <summary> /// 地址跳转 /// </summary> /// <param name="str">跳转地址</param> public void SetLocation(string str) { HttpContext.Current.Response.Write("<mce:script language='JavaScript' type='text/JavaScript'><!--location='" + str + "';// --></mce:script>"); } public string AjaxSetAlert(string str) { return "<mce:script language='JavaScript' type='text/JavaScript'><!--alert('" + str + "');// --></mce:script>"; } //过滤非法字符 public string FilterStr(string Str) { Str = Str.Trim(); Str = Str.Replace("*", ""); Str = Str.Replace("=", ""); Str = Str.Replace("/", ""); Str = Str.Replace("$", ""); Str = Str.Replace("#", ""); Str = Str.Replace("@", ""); Str = Str.Replace("&", ""); return Str; } //Md5加密算法 public string md5(string str) { return System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(str, "md5").ToLower().Substring(0, 12); } public string RndNum(int VcodeNum) { string Vchar = "0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,P,Q,R,S,T,U,W,X"; string[] VcArray = Vchar.Split(new Char[] { ',' }); //将字符串生成数组 string VNum = ""; int temp = -1; Random rand = new Random(); for (int i = 1; i < VcodeNum + 1; i++) { if (temp != -1) { rand = new Random(i * temp * unchecked((int)DateTime.Now.Ticks)); } int t = rand.Next(31); //数组一般从0开始读取，所以这里为31*Rnd if (temp != -1 && temp == t) { return RndNum(VcodeNum); } temp = t; VNum += VcArray[t]; } return VNum; } }