数据库操作的封装的类 对Access操作

数据库操作的封装的类  对Access操作

public class DB { System.Data.OleDb.OleDbConnection conn; public DB() { // // TODO: 在此处添加构造函数逻辑 // } public static OleDbConnection Getconn() { OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["ConnectionString"].ToString())); if (conn.State.Equals(ConnectionState.Closed)) { conn.Open(); } return conn; } //================================================= //功能描述：关闭数据库 //时间：2007.11.10 //================================================= private static void closeConnection() { OleDbConnection conn = DB.Getconn(); OleDbCommand cmd = new OleDbCommand(); if (conn.State == ConnectionState.Open) { conn.Close(); conn.Dispose(); cmd.Dispose(); } } //================================================= //功能描述：执行SQL语句 //输入参数：sql，查询的SQL语句 //时间：2007.11.10 //================================================= public static void execnonsql(string sql) { try { OleDbConnection conn = DB.Getconn(); OleDbCommand com = new OleDbCommand(sql, conn); com.ExecuteNonQuery(); } catch (Exception e) { throw new Exception(e.Message); } finally { closeConnection(); } } //================================================= //功能描述：获取DATASET //输入参数：sql，查询的SQL语句 //返回值：DataSet //时间：2007.11.10 //================================================= public static DataSet getdataset(string sql) { try { OleDbConnection conn = DB.Getconn(); OleDbDataAdapter adp = new OleDbDataAdapter(sql, conn); DataSet ds = new DataSet(); adp.Fill(ds, "ds"); return ds; } catch (Exception e) { throw new Exception(e.Message); } finally { closeConnection(); } } //================================================= //功能描述：获取DATASET1 //输入参数：sql，查询的SQL语句 //返回值：DataSet //时间：2007.11.10 //================================================= public static DataSet select(string sql,string tablename) { try { OleDbConnection conn = DB.Getconn(); OleDbDataAdapter adp = new OleDbDataAdapter(sql, conn); DataSet ds = new DataSet(); adp.Fill(ds, tablename); return ds; } catch (Exception e) { throw new Exception(e.Message); } finally { closeConnection(); } } //================================================= //功能描述：获取某个字段数据 //输入参数：sql，查询的SQL语句 //返回值：hang //时间：2007.11.10 //================================================= public static string FindString(string sql) { try { OleDbConnection conn = DB.Getconn(); OleDbCommand com = new OleDbCommand(sql, conn); string hang = Convert.ToString(com.ExecuteScalar()); return hang; } catch (Exception e) { throw new Exception(e.Message); } finally { closeConnection(); } } /// <summary> /// 从数据库中删除数据 /// </summary> /// <param name="deleteCommandText">一个String，是将要由SqlCommand执行的Transact-SQL SELECT 语句</param> /// <returns>返回值是布尔值，表示是否执行成功</returns> public bool DeleteData(string deleteCommandText) { OleDbConnection conn = DB.Getconn(); System.Data.OleDb.OleDbCommand myCmd; myCmd = new System.Data.OleDb.OleDbCommand(deleteCommandText, conn); myCmd.ExecuteNonQuery(); myCmd.Dispose(); conn.Close(); return true; } //================================================= //功能描述：对DATAGRIG进行数据绑定，无排序 //输入参数：sql，查询的SQL语句；dg，需要绑定的DATAGRID控件 //返回值：无 //时间：2007.11.10 //================================================= public static void binddatagrid(string sql, DataGrid dg) { try { DataSet ds = getdataset(sql); dg.DataSource = ds.Tables[0].DefaultView; dg.DataBind(); } catch (Exception e) { throw new Exception(e.Message); } finally { closeConnection(); } } //================================================= //功能描述：对DropDownList进行数据绑定，无排序 //输入参数：sql，查询的SQL语句；dg，需要绑定的DATAGRID控件 //返回值：无 //时间：2007.11.10 //================================================= public static void bindDropDownList(string sql, DropDownList dl, string class_name, string id) { try { DataSet ds = getdataset(sql); dl.DataSource = ds.Tables[0].DefaultView; dl.DataTextField = class_name; dl.DataValueField = id; dl.DataBind(); } catch (Exception e) { throw new Exception(e.Message); } finally { closeConnection(); } } //================================================= //功能描述：对RadioButtonList进行数据绑定，无排序 //输入参数：sql，查询的SQL语句；dg，需要绑定的DATAGRID控件 //返回值：无 //时间：2007.11.10 //================================================= public static void bindRadioButtonList(string sql, RadioButtonList rl, string class_name, string id) { try { DataSet ds = getdataset(sql); rl.DataSource = ds.Tables[0].DefaultView; rl.DataTextField = class_name; rl.DataValueField = id; rl.SelectedIndex = 0; rl.DataBind(); } catch (Exception e) { throw new Exception(e.Message); } finally { closeConnection(); } } //================================================= //功能描述：对GridView进行数据绑定，无排序 //输入参数：sql，查询的SQL语句；dg，需要绑定的DATAGRID控件 //返回值：无 //时间：2007.11.10 //================================================= public static void bindGridView(string sql, GridView dg) { try { OleDbConnection conn = DB.Getconn(); DataSet ds = getdataset(sql); dg.DataSource = ds.Tables[0].DefaultView; dg.DataBind(); } catch (Exception e) { throw new Exception(e.Message); } finally { closeConnection(); } } //================================================= //功能描述：对datalist进行数据绑定，无排序 //输入参数：sql，查询的SQL语句；dl，需要绑定的datalist控件 //返回值：无 //时间：2007.11.10 //================================================= public static void binddatalist(string sql, DataList dl) { try { OleDbConnection conn = DB.Getconn(); DataSet ds = getdataset(sql); dl.DataSource = ds.Tables[0].DefaultView; dl.DataBind(); } catch (Exception e) { throw new Exception(e.Message); } finally { closeConnection(); } } //================================================= //功能描述：对repeater进行数据绑定，无排序 //输入参数：sql，查询的SQL语句；dl，需要绑定的repeater控件 //返回值：无 //时间：2007.11.10 //================================================= public static void bindrepeater(string sql, Repeater rp) { try { OleDbConnection conn = DB.Getconn(); DataSet ds = getdataset(sql); rp.DataSource = ds.Tables[0].DefaultView; rp.DataBind(); } catch (Exception e) { throw new Exception(e.Message); } finally { closeConnection(); } } //================================================= //功能描述：对listbox进行数据绑定 //输入参数：sql，查询的SQL语句；listb，需要绑定的listbox控件 //返回值：无 //时间：2007.11.10 //================================================= public static void bindlistbox(string sql, ListBox listb, string class_name, string id) { try { OleDbConnection conn = DB.Getconn(); DataSet ds = getdataset(sql); listb.DataSource = ds.Tables[0].DefaultView; listb.DataTextField = class_name; listb.DataValueField = id; listb.DataBind(); } catch (Exception e) { throw new Exception(e.Message); } finally { closeConnection(); } } /// <summary> /// 返回 HTML 字符串的编码结果 /// </summary> /// <param name="str">字符串</param> /// <returns>编码结果</returns> public static string HtmlEncode(string str) { return HttpUtility.HtmlEncode(str); } /// <summary> /// 返回 HTML 字符串的解码结果 /// </summary> /// <param name="str">字符串</param> /// <returns>解码结果</returns> public static string HtmlDecode(string str) { return HttpUtility.HtmlDecode(str); } /// <summary> /// 检测是否有Sql危险字符 /// </summary> /// <param name="str">要判断字符串</param> /// <returns>判断结果</returns> public static bool IsSafeSqlString(string str) { return !Regex.IsMatch(str, @"[-|;|,|//|/(|/)|/[|/]|/}|/{|%|@|/*|!|/']"); } /// <summary> /// 检测用户登录。 /// </summary> /// <param name="ID"></param> /// <returns></returns> public static string UserCheck(string username,string userpass) { string strsql = "select count(*) from Member where mem_Name='" + username + "' and mem_Password='"+userpass+"'"; OleDbConnection conn = DB.Getconn(); OleDbCommand com = new OleDbCommand(strsql, conn); string hang = Convert.ToString(com.ExecuteScalar()); return hang; } }