c# mysql oledb_C# oledb 数据库操作类

public classoperateDB

{private static stringconStr;public staticOleDbConnection cnn;

OleDbDataAdapter da;

OleDbCommandBuilder cb;

OleDbCommand cmd;//构造函数

#region initialize

publicoperateDB()

{//

//TODO: 在此处添加构造函数逻辑// cnn=newOleDbConnection();

da=newOleDbDataAdapter();//不用OleDbCommand对象更新到数据库时，必须有下面一行

cb=newOleDbCommandBuilder(da);

cmd=newOleDbCommand();

}#endregion initialize

//连接字符串

#region get&setConnectionString

///

///获取连接字符串///

public stringMyConStr

{get {returnconStr;}set {conStr =value;}

}#endregion get&setConnectionString

//获得表的名称

#region acquireTableNames

///

///获取数据库中的表名集合///

///

publicDataTable tablesCollection()

{

DataTable tbl=newDataTable();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();

}returntbl;

}#endregion acquireTableNames

//填充数据

#region fillTable

///

///填充dataTable的查询///

/// 数据表(必须输入数据库中存在的名称,也可以是视图)

/// SQL语句

/// 记录条数

public int select(DataTable tblName,stringsqlStr)

{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();//}

returni;

}#endregion fillTable

//插入记录

#region insert(use CommandBuilder)

///

///插入记录(用OleDbCommandBuilder)///

/// 数据表

/// 与表中字段对应的新行

/// 影响的行数

public intinsert(DataTable tblName,DataRow newRow)

{

cnn.Open();int i=0;//

//try//{//如何判断OleDbDataAdapter是否已经Dispose//下面如果不生成新的OleDbDataAdapter、OleDbCommandBuilder、OleDbCommand，//而用原来的全局da,cb,cmd,则在一次操作中只能更新一张表

OleDbDataAdapter daIn=newOleDbDataAdapter();

OleDbCommandBuilder cbIn=newOleDbCommandBuilder(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();

returni;

}#endregion insert(use CommandBuilder)

//插入记录

#region insert(use InsideTransaction,DataTable[])

public stringinsert(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

{//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)

///

///填充DataTable(用于事务处理)///

/// 表

/// SQL语句

/// Transaction对象

/// 行数

public int select(DataTable tblName,stringsqlStr,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);returni;

}///

///插入记录(用OleDbDataAdapter.Update方法及OleDbTransaction)///

/// 数据表

/// 新行

/// 事务对象

///

public intinsert(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{

}returnnum;

}#endregion insert(use OutsideTransaction)

//构造插入的Command

#region insertCommand

///

///构造insertCommand///

/// 数据表

/// identity列的名称

///

private static OleDbCommand insertCmd(DataTable dtl,stringidentityCol)

{

OleDbCommand inCmd=newOleDbCommand();

inCmd.Connection=cnn;string sqlStr="";string strValue="";

sqlStr= "INSERT" + dtl.TableName.ToString() + "(";

strValue= ") Values (";for (int i=0;i

{//对于IDENTITY列无需赋值

if (dtl.Columns[i].ToString() !=identityCol)

{

sqlStr+= "[" + dtl.Columns[i].ToString() + "],";

strValue+="?,";

OleDbParameter myPara= newOleDbParameter();

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;returninCmd;

}#endregion insertCommand

//修改

#region update

///

///修改记录///

/// 数据表

/// SQL语句

/// 影响的行数

public int update(DataTable tblName,stringstrUp)

{

cnn.Close();returni;

}#endregion update

//删除

#region del(use CommandBuilder)

///

///删除记录///

/// 数据表

/// SQL语句

/// 影响的行数

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();//}

returnrows;

}#endregion del(use CommandBuilder)

//构造删除的Command

#region delCommand(create OleDbDataAdapter.deleteCommand)

public intdelete(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);returnrows;

}private staticOleDbCommand delCmd(DataTable dtl)

{

OleDbCommand delCmd=newOleDbCommand();

delCmd.Connection=cnn;string sqlStr="";

sqlStr= "delete from" + dtl.TableName.ToString() + "where";for (int i=0;i

{

sqlStr+= "([" + dtl.Columns[i].ToString() + "] = ? OR ? IS NULL AND ["+dtl.Columns[i].ToString()+"] IS NULL) AND";

OleDbParameter myPara= newOleDbParameter();

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= newOleDbParameter();

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;returndelCmd;

}#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)

///

///执行存储过程///

/// 存储过程名字

/// 参数的值

/// 参数名字

/// 参数的类型

///

public DataTable ExecProc(string procName,string[] ParaValue,string[] ParaName,string[] ParaType)

{

OleDbCommand cmdp=newOleDbCommand();

cmdp.Connection=cnn;

cmdp.CommandType=CommandType.StoredProcedure;

cmdp.CommandText=procName;for (int i=0;i

{

OleDbParameter pt=newOleDbParameter();

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=newDataTable();

cnn.Open();

da.SelectCommand=cmdp;

da.Fill(dtl);

cnn.Close();returndtl;

}///

///设置OleDbParameter对象的DbType(把字符串变为相应的OleDbType类型)///

/// 传入参数的字符串

///

private static OleDbType GetOleDbType(stringtype)

{//try//{//return (OleDbType)Enum.Parse(typeof(OleDbType), type, true);//}//catch//{//return OleDbType.Varchar;//}

switch(type)

{case "date":returnOleDbType.DBDate;break;case "num":returnOleDbType.Integer;break;default:returnOleDbType.VarChar;

}

}#endregion execProc(return dataTable)}