using System;
using System.Data;
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;
using System.Data.OleDb;
///
/// Oledb_Connection 的摘要说明
///
public class Oledb_Connection
{
private static Oledb_Connection instance;
private static OleDbConnection connection; //构造
private Oledb_Connection(string dataBasename)
{ string connStr = ConfigurationManager.AppSettings["ConnectionString"]; connection = new OleDbConnection(connStr);
}
///
/// 查看数据库是否打开 ///
/// 返回一个数据库连接
public static OleDbConnection GetOleConnection(string dataBasename)
{
instance = new Oledb_Connection(dataBasename); connection.Open(); return connection; }}
2.操作数据库的代码 DataBase.cs文件
using System;
using System.Data;
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;
using System.Data.OleDb;
///
/// DataBase 的摘要说明
///
public class DataBase
{
private OleDbConnection connection = Oledb_Connection.GetOleConnection("RS.mdb");
private OleDbCommand cmd;public DataBase(){ } #region 执行数据操作
///
/// 通用查询,删除、修改、添加操作
///
/// 返回操作是否成功
public bool SqlOperation(string sql)
{
if (connection.State == ConnectionState.Closed) { connection.Open(); } cmd = new OleDbCommand(sql, connection); try { int op = cmd.ExecuteNonQuery(); if (op == 0) { return false; } else { return true; } } catch (Exception ex) { throw ex; } finally { connection.Close(); } } public bool RunProc(string procName, OleDbParameter[] prams) { if (connection.State == ConnectionState.Closed) { connection.Open(); } OleDbCommand cmd = new OleDbCommand(procName, connection); if (prams != null) { foreach (OleDbParameter parameter in prams) cmd.Parameters.Add(parameter); } try { int op = cmd.ExecuteNonQuery(); if (op == 0) { return false; } else { return true; } } catch (Exception ex) { throw ex; } finally { connection.Close(); } } public bool RunProc(string procName,CommandType type, OleDbParameter[] prams) { if (connection.State == ConnectionState.Closed) { connection.Open(); } OleDbCommand cmd = new OleDbCommand(procName, connection); cmd.CommandType = type; if (prams != null) { foreach (OleDbParameter parameter in prams) cmd.Parameters.Add(parameter); } try { int op = cmd.ExecuteNonQuery(); if (op == 0) { return false; } else { return true; } } catch (Exception ex) { throw ex; } finally {connection.Close();} } public DataSet RunProcDataSet(string procName, OleDbParameter[] prams) { if (connection.State == ConnectionState.Closed) { connection.Open(); } OleDbCommand cmd = new OleDbCommand(procName, connection); if (prams != null) { foreach (OleDbParameter parameter in prams) cmd.Parameters.Add(parameter); } try { DataSet ds = new DataSet(); OleDbDataAdapter oda = new OleDbDataAdapter(cmd); oda.Fill(ds); return ds; } catch (Exception ex) { throw ex; } finally {connection.Close();} }
///
/// 通用汇总查询操作
///
/// 返回人数
public string SqlCountOperation(string sql) { cmd = new OleDbCommand(sql, connection); try { return Convert.ToString(cmd.ExecuteScalar()); } catch (Exception ex) { throw ex; } finally { try { connection.Close(); } catch (Exception) { } } }
///
/// 通用数据集合操作
///
/// 返回一个数据集合
public DataSet GetDataSet(string sql)
{ if (connection.State == ConnectionState.Closed) { connection.Open(); } cmd = new OleDbCommand(sql, connection); try { DataSet ds = new DataSet(); OleDbDataAdapter oda = new OleDbDataAdapter(cmd); oda.Fill(ds); return ds; } catch (Exception ex) { throw ex; } finally { try { connection.Close(); } catch (Exception) { } } }
#endregion
}
这两个文件建好,就可以针对不同的功能撰写sql 语句的代码了。比如
///
/// 登录
///
///
public DataSet Login()
{
OleDbParameter[] prams ={ new OleDbParameter("@AdminName",AdminName), new OleDbParameter("@AdminPwd",AdminPwd) }; return database.RunProcDataSet("select * from tb_Admin where AdminName=@AdminName and AdminPwd=@AdminPwd", prams); }
但是,在你使用access数据库的时候,你总会发现这样那样的问题,有时候一脸茫然,怎么查也查不到错误。我就把我遇到的一些棘手的问题列举出来吧,有没有遇到的,欢迎CSDNer补充!!!!!!!!!!
1.oledbParameter参数的顺序问题比如上面的登录操作。如果你上面prams数组中申明的参数和下面使用参数时的顺序反了,那将会是很痛苦的事情,写成了这样:
public DataSet Login()
{
OleDbParameter[] prams ={ new OleDbParameter("@AdminName",AdminName), new OleDbParameter("@AdminPwd",AdminPwd) }; return database.RunProcDataSet("select * from tb_Admin where and AdminPwd=@AdminPwd AdminName=@AdminName", prams);
2.在每个页面的加载事件中写下如下代码,要不然你在修改操作的时候发现也不错也不怎么的,就是数据库中的操作不变,让你郁闷死。
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Load1(); } }
//加载 private void Load1() { }
//修改 protected void btnUpdate_Click(object sender, EventArgs e) { Int32 DoctorId = Convert.ToInt32(Request["key"].ToString()); dc.DoctorID = DoctorId; dc.KeShiID = long.Parse(DropDownList1.Text.Trim().ToString()); dc.LeaveYN = RadioButtonList1.Text.Trim(); dc.RegMoney = Int32.Parse(txtRMoney.Text.Trim()); dc.TreatObj = txtObj.Text.Trim(); dc.XueLi = ddlXueLi.Text.Trim(); dc.DoctorIntrdc = txtIntroduce.Text.Trim(); dc.Doctorlevel = txtLevel.Text.Trim(); dc.DoctorName = txtDoctorName.Text.Trim(); dc.DoctorPhoto = imgDoctor.ImageUrl; if (dc.UpdateDoctor()) { Response.Write(""); Response.Write(""); } else { Response.Write(""); } }
3.sql 语句中的关键字问题。我有一会就写了一个Number的sql语句,执行的时候报错报得很奇怪,怎么查也不出毛病,到access数据库中测试也奇怪报错,报错后关键字会被选中,后来才发现,我晕,原来Number是关键字。
4.access数据库的查询(也就是sql中的视图),做的挺人性化的,用起来还是非常方便。
5.在操作表的时候,如果access数据库表被打开,将会报在什么地方打开了等等错误。
6.web.config配置数据库的时候需要给access数据库绝对地址(貌似这个可以使用相对地址的,我还没有研究,不知道使用什么机制)。比如:
7.access数据库的数据类型和C#中的类型怎么匹配问题。我至今还是搞不清楚是/否类型怎么匹配C#中的类型,只知道用boolen一插入数据就报不知道什么错误,气得我就用varchar,插入是,否。还有一些其它类型,数字类型,日期类型
8.日期类型的代码需要像这样写sql 代码,要有#Time#在其前后
public DataSet GetSickTodayByName(string p) { string selectStr = string.Format("select * from SickToday where SickName='{0}' and SickTime=#{1}#", p, DateTime.Today.Date); return database.GetDataSet(selectStr); }
//access数据库虽然没有用sql server那样得心应手,但access数据库还是有不少优点的。
//暂时我就列出这么多了,如果还有其它的,欢迎补上!!!