C#数据访问类

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)

}

• ·数据绑定
• ·对接口的一点理解
• ·remoting 和 多线程 实现即时通讯技术
• ·异常处理机制（JAVA&C#）
• ·使用C#编写一个计时器(原创)
• ·远程桌面会话
• ·如何才是精通C++?
• ·不利于减肥的体质 减肥_美丽顾问
• ·关于LCS问题的研究
• ·Model 1, Model 2和Model