public class operateDB { private static string conStr; public static OleDbConnection cnn; OleDbDataAdapter da; OleDbCommandBuilder cb; OleDbCommand cmd; //构造函数 #region initialize public operateDB() { // // TODO: 在此处添加构造函数逻辑 // cnn=new OleDbConnection(); da=new OleDbDataAdapter(); //不用OleDbCommand对象更新到数据库时，必须有下面一行 cb=new OleDbCommandBuilder(da); cmd=new OleDbCommand(); } #endregion initialize //连接字符串 #region get&setConnectionString /// <summary> /// 获取连接字符串 /// </summary> public string MyConStr { get {return conStr;} set {conStr = value;} } #endregion get&setConnectionString //获得表的名称 #region acquireTableNames /// <summary> /// 获取数据库中的表名集合 /// </summary> /// <returns></returns> public DataTable tablesCollection() { DataTable tbl=new DataTable(); try { cnn.ConnectionString=conStr; cnn.Open(); tbl = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null, null, null, "TABLE"}); } catch(Exception ce) { Console.WriteLine("产生错误:\n{0}",ce.Message); } finally { cnn.Close(); } return tbl; } #endregion acquireTableNames //填充数据 #region fillTable /// <summary> /// 填充dataTable的查询 /// </summary> /// <param name="tblName">数据表(必须输入数据库中存在的名称,也可以是视图)</param> /// <param name="sqlStr">SQL语句</param> /// <returns>记录条数</returns> public int select(DataTable tblName,string sqlStr) { int i=0; // try // { // tblName.Clear(); da.Dispose(); if (cnn.ConnectionString=="") cnn.ConnectionString=conStr; if (cnn.State!=ConnectionState.Open) cnn.Open(); // OleDbCommand cmd=new OleDbCommand("select * from "+tblName.TableName+" where "+sqlStr,cnn); cmd.Connection=cnn; cmd.CommandType=CommandType.Text; cmd.CommandText="select * from "+tblName.TableName+" where "+sqlStr; da.SelectCommand=cmd; i=da.Fill(tblName); // // // } // catch(Exception ce) // { // Console.WriteLine("产生错误:\n{0}",ce.Message); // } // finally // { //this.da.Dispose(); cnn.Close(); // } return i; } #endregion fillTable //插入记录 #region insert(use CommandBuilder) /// <summary> /// 插入记录(用OleDbCommandBuilder) /// </summary> /// <param name="tblName">数据表</param> /// <param name="newRow">与表中字段对应的新行</param> /// <returns>影响的行数</returns> public int insert(DataTable tblName,DataRow newRow) { cnn.Open(); int i=0; // // try // { //如何判断OleDbDataAdapter是否已经Dispose //下面如果不生成新的OleDbDataAdapter、OleDbCommandBuilder、OleDbCommand， //而用原来的全局da,cb,cmd,则在一次操作中只能更新一张表 OleDbDataAdapter daIn=new OleDbDataAdapter(); OleDbCommandBuilder cbIn=new OleDbCommandBuilder(daIn); OleDbCommand cmdIn=new OleDbCommand("select * from "+tblName.TableName,cnn); daIn.SelectCommand=cmdIn; // foreach (DataTable dt in da.TableMappings) // { // if (dt.TableName!=tblName.TableName) // dt.Clear(); // } tblName.Rows.Add(newRow); i=daIn.Update(tblName); // // } // catch(Exception ce) // { // Console.WriteLine("产生错误:\n{0}",ce.Message); // } // finally // { // cnn.Close(); // } // cnn.Close(); return i; } #endregion insert(use CommandBuilder) //插入记录 #region insert(use InsideTransaction,DataTable[]) public string insert(DataTable[] tbls,DataRow[] newRows) { int[] num=new int[tbls.Length]; int sum=0; bool judge=false; string str=""; if (tbls.Length==newRows.Length) { cnn.Open(); OleDbTransaction tran=cnn.BeginTransaction(); for (int i=0;i<tbls.Length;i++) { // this.select(tbls[i],"1=1",tran); da.InsertCommand=insertCmd(tbls[i],"操作编号"); tbls[i].Rows.Add(newRows[i]); da.InsertCommand.Transaction=tran; try { num[i]=da.Update(tbls[i]); sum+=num[i]; } catch { sum=-1; } if (num[i]==0) judge=true; } if (judge) { tran.Rollback(); str="更新失败"; sum=0; } else { tran.Commit(); str="更新成功"; } } cnn.Close(); return str+",影响了 "+sum.ToString()+" 条记录"; } #endregion insert(use InsideTransaction,DataTable[]) //插入记录 #region insert(use OutsideTransaction) /// <summary> /// 填充DataTable(用于事务处理) /// </summary> /// <param name="tblName">表</param> /// <param name="sqlStr">SQL语句</param> /// <param name="trs">Transaction对象</param> /// <returns>行数</returns> public int select(DataTable tblName,string sqlStr,OleDbTransaction trs) { int i=0; // try // { // tblName.Clear(); da.Dispose(); if (cnn.ConnectionString=="") cnn.ConnectionString=conStr; if (cnn.State!=ConnectionState.Open) cnn.Open(); // OleDbCommand cmd=new OleDbCommand("select * from "+tblName.TableName+" where "+sqlStr,cnn); cmd.Connection=cnn; cmd.CommandType=CommandType.Text; cmd.CommandText="select * from "+tblName.TableName+" where "+sqlStr; da.SelectCommand=cmd; cmd.Transaction=trs; i=da.Fill(tblName); return i; } /// <summary> /// 插入记录(用OleDbDataAdapter.Update方法及OleDbTransaction) /// </summary> /// <param name="tblName">数据表</param> /// <param name="newRow">新行</param> /// <param name="trs">事务对象</param> /// <returns></returns> public int insert(DataTable tblName,DataRow newRow,OleDbTransaction trs) { da.InsertCommand=insertCmd(tblName,"noo"); int num=0; try { tblName.Rows.Add(newRow); da.InsertCommand.Transaction=trs; num=da.Update(tblName); } catch { } return num; } #endregion insert(use OutsideTransaction) //构造插入的Command #region insertCommand /// <summary> /// 构造insertCommand /// </summary> /// <param name="dtl">数据表</param> /// <param name="identityCol">identity列的名称</param> /// <returns></returns> private static OleDbCommand insertCmd(DataTable dtl,string identityCol) { OleDbCommand inCmd=new OleDbCommand(); inCmd.Connection=cnn; string sqlStr=""; string strValue=""; sqlStr = "INSERT " + dtl.TableName.ToString() + "("; strValue = ") Values ("; for (int i=0;i<dtl.Columns.Count;i++) { //对于IDENTITY列无需赋值 if (dtl.Columns[i].ToString() != identityCol) { sqlStr += "[" + dtl.Columns[i].ToString() + "], "; strValue +="?,"; OleDbParameter myPara = new OleDbParameter(); myPara.ParameterName = "@" + dtl.Columns[i].ToString(); myPara.OleDbType = GetOleDbType(dtl.Columns[i].DataType.ToString()); // myPara.Direction = ParameterDirection.Input; myPara.SourceColumn = dtl.Columns[i].ToString(); // myPara.SourceVersion = DataRowVersion.Current; inCmd.Parameters.Add(myPara); } } sqlStr=sqlStr.Substring(0,sqlStr.Length-2); strValue=strValue.Substring(0,strValue.Length-1); sqlStr += strValue + ")"; inCmd.CommandText = sqlStr; return inCmd; } #endregion insertCommand //修改 #region update /// <summary> /// 修改记录 /// </summary> /// <param name="tblName">数据表</param> /// <param name="strUp">SQL语句</param> /// <returns>影响的行数</returns> public int update(DataTable tblName,string strUp) { cnn.Close(); return i; } #endregion update //删除 #region del(use CommandBuilder) /// <summary> /// 删除记录 /// </summary> /// <param name="tblName">数据表</param> /// <param name="strDel">SQL语句</param> /// <returns>影响的行数</returns> public int delete(DataTable tblName,string strDel) //strDel是删除条件 { int rows=0; //用OleDbDataAdapter.Update方法自动更新必须在where中存在主键或唯一值 // try // { // cnn.Open(); rows=tblName.Rows.Count; for (int i=0;i< tblName.Rows.Count;i++) { tblName.Rows[i].Delete(); } //注意，如在da.Update前面用了下面的AcceptChanges方法，因为记录被删除--更新到数据库失败 //tblName.AcceptChanges(); da.Update(tblName); // // } // catch(Exception ce) // { // Console.WriteLine("产生错误:\n{0}",ce.Message); // } // finally // { cnn.Close(); // } /// //用OleDbCommand直接更新 // try // { // string str="delete from "+tblName.TableName+" where "+strDel; // cnn.Open(); // OleDbCommand cmdD=new OleDbCommand(str,cnn); // cmdD.CommandType=CommandType.Text; // rows=cmdD.ExecuteNonQuery(); // } // // catch(Exception ce) // { // Console.WriteLine("产生错误:\n{0}",ce.Message); // } // finally // { // cnn.Close(); // } return rows; } #endregion del(use CommandBuilder) //构造删除的Command #region delCommand(create OleDbDataAdapter.deleteCommand) public int delete(DataTable tblName) { int rows=0; da.DeleteCommand=delCmd(tblName); for (int i=0;i< tblName.Rows.Count;i++) { tblName.Rows[i].Delete(); } rows=da.Update(tblName); return rows; } private static OleDbCommand delCmd(DataTable dtl) { OleDbCommand delCmd=new OleDbCommand(); delCmd.Connection=cnn; string sqlStr=""; sqlStr = "delete from " + dtl.TableName.ToString() + " where "; for (int i=0;i<dtl.Columns.Count;i++) { sqlStr += "([" + dtl.Columns[i].ToString() + "] = ? OR ? IS NULL AND ["+dtl.Columns[i].ToString()+"] IS NULL) AND"; OleDbParameter myPara = new OleDbParameter(); myPara.ParameterName = "or1_" + dtl.Columns[i].ToString(); myPara.OleDbType = GetOleDbType(dtl.Columns[i].DataType.ToString()); myPara.Direction = ParameterDirection.Input; myPara.SourceColumn = dtl.Columns[i].ToString(); myPara.SourceVersion = DataRowVersion.Original; delCmd.Parameters.Add(myPara); int j=delCmd.Parameters.Count; bool b=dtl.Columns[i].AllowDBNull; if (b) { OleDbParameter myPara1 = new OleDbParameter(); myPara1.ParameterName = "or2_" + dtl.Columns[i].ToString(); myPara1.OleDbType = GetOleDbType(dtl.Columns[i].DataType.ToString()); myPara1.Direction = ParameterDirection.Input; myPara1.SourceColumn = dtl.Columns[i].ToString(); myPara1.SourceVersion = DataRowVersion.Original; delCmd.Parameters.Add(myPara1); j=delCmd.Parameters.Count; } } sqlStr=sqlStr.Substring(0,sqlStr.Length-3); delCmd.CommandText = sqlStr; return delCmd; } #endregion delCommand(create OleDbDataAdapter.deleteCommand) #region amendDataBase public void addColumn(DataTable tblName,string strUp) //修改表的结构,更新到数据库 { cnn.Open(); // OleDbCommand cmdS=new OleDbCommand("select * from "+tblName.TableName,cnn); // da.SelectCommand=cmdS; // OleDbCommandBuilder cb=new OleDbCommandBuilder(da); // DataColumn colItem = new DataColumn(strUp,Type.GetType("System.String")); // // tblName.Columns.Add(colItem); //为什么上面的方法不行，只能直接用SQL语句吗? da.Fill(tblName); da.Update(tblName); } #endregion amendDataBase //调用存储过程 #region execProc(return dataTable) /// <summary> /// 执行存储过程 /// </summary> /// <param name="procName">存储过程名字</param> /// <param name="ParaValue">参数的值</param> /// <param name="ParaName">参数名字</param> /// <param name="ParaType">参数的类型</param> /// <returns></returns> public DataTable ExecProc(string procName,string[] ParaValue,string[] ParaName,string[] ParaType) { OleDbCommand cmdp=new OleDbCommand(); cmdp.Connection=cnn; cmdp.CommandType=CommandType.StoredProcedure; cmdp.CommandText=procName; for (int i=0;i<ParaName.Length;i++) { OleDbParameter pt=new OleDbParameter(); ParaName[i]="@"+ParaName[i]; //参数名字 //pt.ParameterName=ParaName[i]; pt.SourceColumn=ParaName[i]; pt.OleDbType=GetOleDbType(ParaType[i]); pt.Value=ParaValue[i]; cmdp.Parameters.Add(pt); } DataTable dtl=new DataTable(); cnn.Open(); da.SelectCommand=cmdp; da.Fill(dtl); cnn.Close(); return dtl; } /// <summary> /// 设置OleDbParameter对象的DbType（把字符串变为相应的OleDbType类型） /// </summary> /// <param name="type">传入参数的字符串</param> /// <returns></returns> private static OleDbType GetOleDbType(string type) { // try // { // return (OleDbType)Enum.Parse(typeof(OleDbType), type, true); // } // catch // { // return OleDbType.Varchar; // } switch (type) { case "date": return OleDbType.DBDate; break; case "num": return OleDbType.Integer; break; default: return OleDbType.VarChar; } } #endregion execProc(return dataTable) }