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