//********************************************************************************************************************
//
//本模块是用于数据访问的类
//
//********************************************************************************************************************
using System;
using System.Data;
using System.Data.OleDb;
namespace DataAccess
{
/// <summary>
/// 本类主要用于对数据库的操作
/// </summary>
public class operateDB
{
/// <summary>
/// 静态连接对象
/// </summary>
public static OleDbConnection cnn;
private static OleDbDataAdapter da;
// private static OleDbCommandBuilder cb;
private static OleDbCommand cmd;
private static OleDbCommand cmdGetIdentity;
public static bool isAccessDB; //是否Access数据库
private OleDbConnection cnn1;
private OleDbDataAdapter da1;
private OleDbCommandBuilder cb1;
private OleDbCommand cmd1;
private static OleDbCommand cmdGetIdentity1;
//构造函数
#region initialize
/// <summary>
/// 构造函数
/// </summary>
public operateDB()
{
//
// TODO: 在此处添加构造函数逻辑
//
cnn1=new OleDbConnection();
da1=new OleDbDataAdapter();
//不用OleDbCommand对象更新到数据库时，必须有下面一行
cb1=new OleDbCommandBuilder(da);
cmd1=new OleDbCommand();
cmdGetIdentity = null;
cmdGetIdentity1=new OleDbCommand("SELECT @@IDENTITY",cnn);
}
public static void initializeOperateDB(string strCon)
{
cnn=new OleDbConnection();
da=new OleDbDataAdapter();
cmd=new OleDbCommand();
conStr=strCon;
}
#endregion initialize
//获取或设置连接字符串
#region get&setConnectionString
private static string conStr;
private string _连接字符串;
/// <summary>
/// 获取连接字符串
/// </summary>
public static string MyConStr
{
get {return conStr;}
set {conStr = value;}
}
/// <summary>
/// 获取连接字符串
/// </summary>
public string 连接字符串
{
get {return _连接字符串;}
set {_连接字符串 = value;}
}
#endregion get&setConnectionString
//获得表的名称
#region acquireTableNames
/// <summary>
/// 获取数据库中的表名集合
/// </summary>
/// <returns></returns>
public static 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;
}
public DataTable 获取表名集合()
{
DataTable tbl=new DataTable();
try
{
cnn1.ConnectionString=this._连接字符串;
cnn1.Open();
tbl = cnn1.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] {null, null, null, "TABLE"});
}
catch(Exception ce)
{
Console.WriteLine("产生错误:\n{0}",ce.Message);
}
finally
{
cnn1.Close();
}
return tbl;
}
/// <summary>
/// 获取某表列名的集合
/// </summary>
/// <param name="tbl"></param>
/// <returns></returns>
public static DataTable columnsCollection(DataTable tbl)
{
DataTable temp=new DataTable();
cnn.Open();
temp=cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,new object[]{null,null,tbl.TableName,null});
// temp=cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,null);
cnn.Close();
return temp;
}
public DataTable 获取列名集合(DataTable tbl)
{
DataTable temp=new DataTable();
cnn1.Open();
temp=cnn1.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,new object[]{null,null,tbl.TableName,null});
// temp=cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,null);
cnn1.Close();
return temp;
}
#endregion acquireTableNames
//获得表的结构
#region acquireTableSchema
/// <summary>
/// 取得表的结构（不包含数据）
/// </summary>
/// <param name="tblName">表名字</param>
/// <returns></returns>
public static DataTable schemaTable(string tblName)
{
DataTable dtl = null;
try
{
if (cnn.ConnectionString=="")
cnn.ConnectionString=conStr;
if (cnn.State!=ConnectionState.Open)
cnn.Open();
da.SelectCommand=new OleDbCommand("select * from "+tblName,cnn);
dtl=new DataTable(tblName);
da.FillSchema(dtl,SchemaType.Source);
}
catch
{
}
finally
{
cnn.Close();
}
return dtl;
}
public DataTable 获取表结构(string tblName)
{
DataTable dtl = null;
try
{
if (cnn1.ConnectionString=="")
cnn1.ConnectionString=this._连接字符串;
if (cnn1.State!=ConnectionState.Open && cnn1.State!=ConnectionState.Connecting)
cnn1.Open();
da.SelectCommand=new OleDbCommand("select * from "+tblName,this.cnn1);
dtl=new DataTable(tblName);
da1.FillSchema(dtl,SchemaType.Source);
}
catch
{
dtl = null;
}
finally
{
cnn1.Close();
}
return dtl;
}
#endregion acquireTableSchema
//获的表的主键
#region acquirePrimaryKeys
/// <summary>
/// 获得表的主键列
/// </summary>
/// <param name="DBName">数据库</param>
/// <param name="TableName">表名</param>
/// <returns></returns>
public static DataTable getPrimaryKeys(string DBName,string TableName)
{
DataTable dtlTemp = null;
try
{
cnn.Open();
dtlTemp = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys,
new Object[] {DBName,"dbo",TableName});
operateDB.cnn.Close();
}
catch
{
}
finally
{
cnn.Close();
}
return dtlTemp;
}
public DataTable 获取主键(string DBName,string TableName)
{
DataTable dtlTemp = null;
try
{
cnn1.Open();
dtlTemp = cnn1.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys,
new Object[] {DBName,"dbo",TableName});
}
catch
{
}
finally
{
cnn.Close();
}
return dtlTemp;
}
#endregion
//多表填充OleDbDataAdapter
#region MultiTableDataAdapter
/// <summary>
/// 多表查询的DataAdapter
/// </summary>
/// <param name="sql">SQL命令</param>
/// <returns></returns>
public OleDbDataAdapter setAdapter(string sql)
{
cnn.ConnectionString=conStr;
da.SelectCommand=new OleDbCommand(sql);
da.SelectCommand.Connection=cnn;
return da;
}
#endregion
//填充数据
#region fillTable
/// <summary>
/// 填充dataTable的查询
/// </summary>
/// <param name="tblName">数据表(必须输入数据库中存在的名称,也可以是视图)</param>
/// <param name="sqlStr">SQL语句</param>
/// <returns>记录条数</returns>
public static 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.State!=ConnectionState.Connecting)
cnn.Open();
//OleDbCommand cmd=new OleDbCommand("select * from "+tblName.TableName+" where "+sqlStr,cnn);
cmd.Connection=cnn;
cmd.CommandType=CommandType.Text;
cmd.CommandText=sqlStr;
da.SelectCommand=cmd;
i=da.Fill(tblName);
}
catch(Exception ce)
{
Console.WriteLine("产生错误:\n{0}",ce.Message);
}
finally
{
da.Dispose();
cnn.Close();
}
return i;
}
public int 填充表(DataTable tblName,string sqlStr)
{
int i=0;
try
{
tblName.Clear();
da1.Dispose();
if (cnn1.ConnectionString=="")
cnn1.ConnectionString=this._连接字符串;
if (cnn1.State!=ConnectionState.Open)
cnn1.Open();
cmd1.Connection=cnn1;
cmd1.CommandType=CommandType.Text;
cmd1.CommandText=sqlStr;
da1.SelectCommand=cmd;
i=da1.Fill(tblName);
}
catch(Exception ce)
{
Console.WriteLine("产生错误:\n{0}",ce.Message);
}
finally
{
da.Dispose();
cnn1.Close();
}
return i;
}
public int sqlStatement(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();
cmd.Connection=cnn;
cmd.CommandType=CommandType.Text;
cmd.CommandText=sqlStr;
da.SelectCommand=cmd;
i=da.Fill(tblName);
}
catch(Exception ce)
{
Console.WriteLine("产生错误:\n{0}",ce.Message);
}
finally
{
da.Dispose();
cnn.Close();
}
return i;
}
#endregion fillTable
#region fillDataSet
public static void fillDataSet(DataSet ds)
{
foreach(DataTable dtl in ds.Tables)
{
select(dtl,"SELECT * FROM "+dtl.TableName);
}
}
public void 填充DataSet(DataSet ds)
{
foreach(DataTable dtl in ds.Tables)
{
this.填充表(dtl,"SELECT * FROM "+dtl.TableName);
}
}
#endregion
//execSql
#region dataReader to DataTable
/// <summary>
/// 执行SQL命令，返回结果集,可用于多表的SQL命令
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public static DataTable execSql(string strSql)
{
DataTable dtl = null;
OleDbDataReader rdr = null;
try
{
if(cnn==null)
cnn=new OleDbConnection();
if (cnn.ConnectionString=="")
cnn.ConnectionString=conStr;
if (cnn.State!=ConnectionState.Open)
cnn.Open();
OleDbCommand cmd=new OleDbCommand(strSql,cnn);
rdr=cmd.ExecuteReader();
dtl=new DataTable();
while(rdr.Read())
{
//只有read后才能获取FieldCount,所以先在DataTable添加列
if(!(dtl.Columns.Count>0))
{
for(int i=0;i<rdr.FieldCount;i++)
{
DataColumn col=new DataColumn(rdr.GetName(i));
col.DataType=rdr[i].GetType().ToString().IndexOf("DBNull")>=0?Type.GetType("System.String"):rdr[i].GetType();
dtl.Columns.Add(col);
}
}
DataRow dr=dtl.NewRow();
for(int i=0;i<dtl.Columns.Count;i++)
dr[i]=rdr[i];
dtl.Rows.Add(dr);
}
rdr.Close();
}
catch
{
}
finally
{
cnn.Close();
}
return dtl;
}
public DataTable 查询结果集(string strSql)
{
DataTable dtl = null;
OleDbDataReader rdr = null;
try
{
if(cnn1==null)
cnn1=new OleDbConnection();
if (cnn1.ConnectionString=="")
cnn1.ConnectionString=this._连接字符串;
if (cnn1.State!=ConnectionState.Open)
cnn1.Open();
OleDbCommand cmd1=new OleDbCommand(strSql,cnn1);
rdr=cmd.ExecuteReader();
dtl=new DataTable();
while(rdr.Read())
{
//只有read后才能获取FieldCount,所以先在DataTable添加列
if(!(dtl.Columns.Count>0))
{
for(int i=0;i<rdr.FieldCount;i++)
{
DataColumn col=new DataColumn(rdr.GetName(i));
col.DataType=rdr[i].GetType().ToString().IndexOf("DBNull")>=0?Type.GetType("System.String"):rdr[i].GetType();
dtl.Columns.Add(col);
}
}
DataRow dr=dtl.NewRow();
for(int i=0;i<dtl.Columns.Count;i++)
dr[i]=rdr[i];
dtl.Rows.Add(dr);
rdr.Close();
}
}
catch
{
dtl = null;
}
finally
{
cnn1.Close();
}
return dtl;
}
#endregion
//插入记录
#region insert(use CommandBuilder)
/// <summary>
/// 插入记录(用OleDbCommandBuilder)
/// </summary>
/// <param name="tblName">数据表</param>
/// <param name="newRow">与表中字段对应的新行</param>
/// <returns>影响的行数</returns>
public static int insert(DataTable tblName,DataRow newRow)
{
if (cnn.ConnectionString=="")
cnn.ConnectionString=conStr;
if (cnn.State!=ConnectionState.Open)
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();
}
return i;
}
public int 插入记录(DataTable tblName,DataRow newRow)
{
if (cnn1.ConnectionString=="")
cnn1.ConnectionString=this._连接字符串;
if (cnn1.State!=ConnectionState.Open)
cnn1.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,cnn1);
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 del(use CommandBuilder)
/// <summary>
/// 删除记录
/// </summary>
/// <param name="tblName">数据表</param>
/// <returns>影响的行数</returns>
public int delete(DataTable tblName)
{
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)
#region 用Command直接执行SQL命令
/// <summary>
/// 用SQL语句修改
/// </summary>
/// <param name="strUp">SQL语句</param>
/// <returns>影响的行数</returns>
public static int Exec(string strUp)
{
int i=0;
try
{
if(cnn==null)
cnn=new OleDbConnection();
if (cnn.ConnectionString=="")
cnn.ConnectionString=conStr;
if (cnn.State!=ConnectionState.Open)
cnn.Open();
OleDbCommand cmd=new OleDbCommand(strUp,cnn);
i=cmd.ExecuteNonQuery();
}
catch
{
i = -1;
}
finally
{
cnn.Close();
}
return i;
}
public int 执行SQL(string strUp)
{
int i = 0;
try
{
if(cnn1==null)
cnn1=new OleDbConnection();
if (cnn1.ConnectionString=="")
cnn1.ConnectionString=this._连接字符串;
if (cnn1.State!=ConnectionState.Open)
cnn1.Open();
cmd1=new OleDbCommand(strUp,cnn1);
i=cmd.ExecuteNonQuery();
}
catch
{
}
finally
{
cnn1.Close();
}
return i;
}
#endregion
//插入、修改、删除
#region 构造Adapter更新到数据库(DataTable)
#region updateCmd
/// <summary>
/// 构造DataAdapter的UpdateCommand对象
/// </summary>
/// <param name="dtl">DataTable参数</param>
/// <returns>OleDbCommand</returns>
private static OleDbCommand createUpdateCommand(DataTable dtl)
{
OleDbCommand upCmd=new OleDbCommand();
upCmd.Connection=cnn;
string updateSQL="UPDATE "+dtl.TableName+" SET ";
string whereSQL=" WHERE ";
for (int i=0;i<dtl.Columns.Count;i++)
{
OleDbParameter myPara = new OleDbParameter();
if(!checkColumnName(dtl.Columns[i].ColumnName))
return null;
myPara.ParameterName = "@" + dtl.Columns[i].ColumnName;
// string str=dtl.Columns[i].DataType.ToString();
// if(dtl.Columns[i].DataType.ToString().IndexOf("DateTime")>0)
// myPara.OleDbType=OleDbType.DBTimeStamp;
// else if(dtl.Columns[i].DataType.ToString().IndexOf("Byte")>=0)
// myPara.OleDbType=OleDbType.Binary;
// else
myPara.DbType = GetDbType(dtl.Columns[i].DataType);
myPara.SourceColumn = dtl.Columns[i].ColumnName;
upCmd.Parameters.Add(myPara);
updateSQL+=string.Format("{0}=?,",dtl.Columns[i].ColumnName);
whereSQL+=string.Format("(({0}=?) OR (? IS NULL AND {0} IS NULL)) AND ",dtl.Columns[i].ColumnName);
}
for (int i=0;i<dtl.Columns.Count;i++)
{
OleDbParameter myPara1 = new OleDbParameter();
myPara1.ParameterName = "or1" + dtl.Columns[i].ColumnName;
// if(dtl.Columns[i].DataType.ToString().IndexOf("DateTime")>=0)
// myPara1.OleDbType=OleDbType.DBTimeStamp;
// else if(dtl.Columns[i].DataType.ToString().IndexOf("Byte")>=0)
// myPara1.OleDbType=OleDbType.Binary;
// else
// myPara1.OleDbType = GetOleDbType(dtl.Columns[i].DataType.ToString());
myPara1.DbType = GetDbType(dtl.Columns[i].DataType);
myPara1.Direction = ParameterDirection.Input;
myPara1.SourceColumn = dtl.Columns[i].ColumnName;
myPara1.SourceVersion = DataRowVersion.Original;
upCmd.Parameters.Add(myPara1);
OleDbParameter myPara2 = new OleDbParameter();
myPara2.ParameterName = "or2" + dtl.Columns[i].ColumnName;
// string str=dtl.Columns[i].DataType.ToString();
// if(dtl.Columns[i].DataType.ToString().IndexOf("DateTime")>=0)
// myPara2.OleDbType=OleDbType.DBTimeStamp;
// else if(dtl.Columns[i].DataType.ToString().IndexOf("Byte")>=0)
// myPara1.OleDbType=OleDbType.Binary;
// else
// myPara2.DbType = GetDbType(dtl.Columns[i].DataType);
myPara2.DbType = GetDbType(dtl.Columns[i].DataType);
myPara2.Direction = ParameterDirection.Input;
myPara2.SourceColumn = dtl.Columns[i].ColumnName;
myPara2.SourceVersion = DataRowVersion.Original;
upCmd.Parameters.Add(myPara2);
}
upCmd.CommandText=updateSQL.Substring(0,updateSQL.Length-1)+whereSQL.Substring(0,whereSQL.Length-4);
int j=upCmd.Parameters.Count;
return upCmd;
}
private OleDbCommand 生成UpdateCommand(DataTable dtl)
{
OleDbCommand upCmd=new OleDbCommand();
upCmd.Connection=cnn1;
string updateSQL="UPDATE "+dtl.TableName+" SET ";
string whereSQL=" WHERE ";
for (int i=0;i<dtl.Columns.Count;i++)
{
OleDbParameter myPara = new OleDbParameter();
myPara.ParameterName = "@" + dtl.Columns[i].ColumnName;
myPara.DbType = GetDbType(dtl.Columns[i].DataType);
myPara.SourceColumn = dtl.Columns[i].ColumnName;
upCmd.Parameters.Add(myPara);
updateSQL+=string.Format("{0}=?,",dtl.Columns[i].ColumnName);
whereSQL+=string.Format("(({0}=?) OR (? IS NULL AND {0} IS NULL)) AND ",dtl.Columns[i].ColumnName);
}
for (int i=0;i<dtl.Columns.Count;i++)
{
OleDbParameter myPara1 = new OleDbParameter();
myPara1.ParameterName = "or1" + dtl.Columns[i].ColumnName;
myPara1.DbType = GetDbType(dtl.Columns[i].DataType);
myPara1.Direction = ParameterDirection.Input;
myPara1.SourceColumn = dtl.Columns[i].ColumnName;
myPara1.SourceVersion = DataRowVersion.Original;
upCmd.Parameters.Add(myPara1);
OleDbParameter myPara2 = new OleDbParameter();
myPara2.ParameterName = "or2" + dtl.Columns[i].ColumnName;
string str=dtl.Columns[i].DataType.ToString();
myPara1.DbType = GetDbType(dtl.Columns[i].DataType);
myPara2.Direction = ParameterDirection.Input;
myPara2.SourceColumn = dtl.Columns[i].ColumnName;
myPara2.SourceVersion = DataRowVersion.Original;
upCmd.Parameters.Add(myPara2);
}
upCmd.CommandText=updateSQL.Substring(0,updateSQL.Length-1)+whereSQL.Substring(0,whereSQL.Length-4);
int j=upCmd.Parameters.Count;
return upCmd;
}
#endregion
#region deleteCmd
/// <summary>
/// 构造构造DataAdapter的DeleteCommand对象
/// </summary>
/// <param name="dtl">DataTable参数</param>
/// <returns>OleDbCommand</returns>
private static OleDbCommand createDelCommand(DataTable dtl)
{
OleDbCommand delCmd=new OleDbCommand();
delCmd.Connection=cnn;
string sqlStr="delete from " + dtl.TableName.ToString() + " where ";
for (int i=0;i<dtl.Columns.Count;i++)
{
sqlStr += "([" + dtl.Columns[i].ColumnName + "] = ? OR ? IS NULL AND ["+dtl.Columns[i].ColumnName+"] IS NULL) AND";
OleDbParameter myPara = new OleDbParameter();
myPara.ParameterName = "or1_" + dtl.Columns[i].ColumnName;
myPara.DbType = GetDbType(dtl.Columns[i].DataType);
myPara.Direction = ParameterDirection.Input;
myPara.SourceColumn = dtl.Columns[i].ColumnName;
myPara.SourceVersion = DataRowVersion.Original;
delCmd.Parameters.Add(myPara);
int j=delCmd.Parameters.Count;
bool b=dtl.Columns[i].AllowDBNull;
//为何deleteCommnand对象的OleDbParameter要重复添加???,否则报错
if (b)
{
OleDbParameter myPara1 = new OleDbParameter();
myPara1.ParameterName = "or2_" + dtl.Columns[i].ColumnName;
myPara1.DbType = GetDbType(dtl.Columns[i].DataType);
myPara1.Direction = ParameterDirection.Input;
myPara1.SourceColumn = dtl.Columns[i].ColumnName;
myPara1.SourceVersion = DataRowVersion.Original;
delCmd.Parameters.Add(myPara1);
j=delCmd.Parameters.Count;
}
}
sqlStr=sqlStr.Substring(0,sqlStr.Length-3);
delCmd.CommandText = sqlStr;
return delCmd;
}
private OleDbCommand 生成DelCommand(DataTable dtl)
{
OleDbCommand delCmd=new OleDbCommand();
delCmd.Connection=cnn1;
string sqlStr="delete from " + dtl.TableName.ToString() + " where ";
for (int i=0;i<dtl.Columns.Count;i++)
{
sqlStr += "([" + dtl.Columns[i].ColumnName + "] = ? OR ? IS NULL AND ["+dtl.Columns[i].ColumnName+"] IS NULL) AND";
OleDbParameter myPara = new OleDbParameter();
myPara.ParameterName = "or1_" + dtl.Columns[i].ColumnName;
myPara.DbType = GetDbType(dtl.Columns[i].DataType);
myPara.Direction = ParameterDirection.Input;
myPara.SourceColumn = dtl.Columns[i].ColumnName;
myPara.SourceVersion = DataRowVersion.Original;
delCmd.Parameters.Add(myPara);
int j=delCmd.Parameters.Count;
bool b=dtl.Columns[i].AllowDBNull;
//为何deleteCommnand对象的OleDbParameter要重复添加???,否则报错
if (b)
{
OleDbParameter myPara1 = new OleDbParameter();
myPara1.ParameterName = "or2_" + dtl.Columns[i].ColumnName;
myPara1.DbType = GetDbType(dtl.Columns[i].DataType);
myPara1.Direction = ParameterDirection.Input;
myPara1.SourceColumn = dtl.Columns[i].ColumnName;
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
#region insertCmd
private static OleDbCommand createInsertCommand(DataTable dtl)
{
OleDbCommand insertCmd=new OleDbCommand();
insertCmd.Connection=cnn;
string sqlStr="INSERT INTO " + dtl.TableName.ToString() + "(";
//access数据库
if(isAccessDB)
{
for(int i=0;i<dtl.Columns.Count;i++)
{
sqlStr=sqlStr+dtl.Columns[i].ColumnName+",";
OleDbParameter myPara = new OleDbParameter();
myPara.ParameterName = dtl.Columns[i].ColumnName;
myPara.DbType = GetDbType(dtl.Columns[i].DataType);
myPara.SourceColumn = dtl.Columns[i].ToString();
insertCmd.Parameters.Add(myPara);
}
sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+") VALUES(";
for(int i=0;i<dtl.Columns.Count;i++)
{
sqlStr=sqlStr+"?,";
}
sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+")";
}
else
{
//先判断表中是否有Identity列
DataTable dtlTemp=operateDB.execSql("Select name from syscolumns Where autoval Is Not Null And id "+
"= object_id('"+dtl.TableName+"')");
for(int i=0;i<dtl.Columns.Count;i++)
{
if(!(dtlTemp.Rows.Count>0) || dtlTemp.Rows[0][0].ToString()!=dtl.Columns[i].ColumnName)
{
sqlStr=sqlStr+dtl.Columns[i].ColumnName+",";
OleDbParameter myPara = new OleDbParameter();
myPara.ParameterName = dtl.Columns[i].ColumnName;
myPara.DbType = GetDbType(dtl.Columns[i].DataType);
myPara.SourceColumn = dtl.Columns[i].ToString();
insertCmd.Parameters.Add(myPara);
}
}
sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+") VALUES(";
for(int i=0;i<dtl.Columns.Count;i++)
{
if(!(dtlTemp.Rows.Count>0) || dtlTemp.Rows[0][0].ToString()!=dtl.Columns[i].ColumnName)
sqlStr=sqlStr+"?,";
}
sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+")";
insertCmd.CommandText = sqlStr;
}
insertCmd.CommandText = sqlStr;
return insertCmd;
}
private OleDbCommand 生成InsertCommand(DataTable dtl)
{
OleDbCommand insertCmd=new OleDbCommand();
insertCmd.Connection=cnn1;
string sqlStr="INSERT INTO " + dtl.TableName.ToString() + "(";
//access数据库
if(isAccessDB)
{
for(int i=0;i<dtl.Columns.Count;i++)
{
sqlStr=sqlStr+dtl.Columns[i].ColumnName+",";
OleDbParameter myPara = new OleDbParameter();
myPara.ParameterName = dtl.Columns[i].ColumnName;
myPara.DbType = GetDbType(dtl.Columns[i].DataType);
myPara.SourceColumn = dtl.Columns[i].ToString();
insertCmd.Parameters.Add(myPara);
}
sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+") VALUES(";
for(int i=0;i<dtl.Columns.Count;i++)
{
sqlStr=sqlStr+"?,";
}
sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+")";
}
else
{
//先判断表中是否有Identity列
DataTable dtlTemp=operateDB.execSql("Select name from syscolumns Where autoval Is Not Null And id "+
"= object_id('"+dtl.TableName+"')");
for(int i=0;i<dtl.Columns.Count;i++)
{
if(!(dtlTemp.Rows.Count>0) || dtlTemp.Rows[0][0].ToString()!=dtl.Columns[i].ColumnName)
{
sqlStr=sqlStr+dtl.Columns[i].ColumnName+",";
OleDbParameter myPara = new OleDbParameter();
myPara.ParameterName = dtl.Columns[i].ColumnName;
myPara.DbType = GetDbType(dtl.Columns[i].DataType);
myPara.SourceColumn = dtl.Columns[i].ToString();
insertCmd.Parameters.Add(myPara);
}
}
sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+") VALUES(";
for(int i=0;i<dtl.Columns.Count;i++)
{
if(!(dtlTemp.Rows.Count>0) || dtlTemp.Rows[0][0].ToString()!=dtl.Columns[i].ColumnName)
sqlStr=sqlStr+"?,";
}
sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+")";
insertCmd.CommandText = sqlStr;
}
insertCmd.CommandText = sqlStr;
return insertCmd;
}
#endregion
/// <summary>
/// 插入、修改、删除
/// </summary>
/// <param name="tblName">要更新的DataTable</param>
/// <returns>影响的行数</returns>
public static int update(DataTable tblName)
{
int i=0;
OleDbDataAdapter daUp=new OleDbDataAdapter();
try
{
daUp.UpdateCommand=createUpdateCommand(tblName);
daUp.DeleteCommand=createDelCommand(tblName);
daUp.InsertCommand=createInsertCommand(tblName);
i=daUp.Update(tblName);
}
catch
{
i=-1;
}
return i;
}
public int 更新到数据库(DataTable tblName)
{
int i=0;
OleDbDataAdapter daUp=new OleDbDataAdapter();
try
{
daUp.UpdateCommand=this.生成UpdateCommand(tblName);
daUp.DeleteCommand=this.生成DelCommand(tblName);
daUp.InsertCommand=this.生成InsertCommand(tblName);
i=daUp.Update(tblName);
}
catch
{
i=-1;
}
return i;
}
#endregion
#region 构造Adapter更新到数据库(DataSet)
public static int update(DataSet dsName)
{
int i=0;
OleDbDataAdapter[] daUp=new OleDbDataAdapter[dsName.Tables.Count];
for(int j=0;j<daUp.Length;j++)
{
daUp[j]=new OleDbDataAdapter();
daUp[j].UpdateCommand=createUpdateCommand(dsName.Tables[j]);
daUp[j].DeleteCommand=createDelCommand(dsName.Tables[j]);
daUp[j].InsertCommand=createInsertCommand(dsName.Tables[j]);
i+=daUp[j].Update(dsName.Tables[j]);
}
return i;
}
public int 更新到数据库(DataSet dsName)
{
int i=0;
OleDbDataAdapter[] daUp=new OleDbDataAdapter[dsName.Tables.Count];
for(int j=0;j<daUp.Length;j++)
{
daUp[j]=new OleDbDataAdapter();
daUp[j].UpdateCommand=this.生成UpdateCommand(dsName.Tables[j]);
daUp[j].DeleteCommand=this.生成DelCommand(dsName.Tables[j]);
daUp[j].InsertCommand=this.生成InsertCommand(dsName.Tables[j]);
i+=daUp[j].Update(dsName.Tables[j]);
}
return i;
}
#endregion
#region 构造Adapter更新到数据库(事务处理)
#region selectCmd
/// <summary>
/// 构造selectCommand对象
/// </summary>
/// <param name="strSql">查询的SQL命令</param>
/// <returns>OleDbCommand对象</returns>
public OleDbCommand createSelectCmd(string strSql)
{
OleDbCommand selectCmd=new OleDbCommand();
selectCmd.Connection=cnn;
selectCmd.CommandText=strSql;
return selectCmd;
}
#endregion
//事物处理时identity列不能通过SQL语句动态判断，所以重新构造insertCmd
#region insertCmd(事物处理)
/// <summary>
/// 生成用于事务处理中的InsertCommand
/// </summary>
/// <param name="dtl">DataTable</param>
/// <param name="identityColumnName">identity列名</param>
/// <returns>OleDbCommand</returns>
private static OleDbCommand createInsertCommand(DataTable dtl,string identityColumnName)
{
OleDbCommand insertCmd=new OleDbCommand();
insertCmd.Connection=cnn;
string sqlStr="INSERT INTO " + dtl.TableName.ToString() + "(";
for(int i=0;i<dtl.Columns.Count;i++)
{
if(dtl.Columns[i].ColumnName!=identityColumnName)
{
sqlStr=sqlStr+dtl.Columns[i].ColumnName+",";
OleDbParameter myPara = new OleDbParameter();
myPara.ParameterName = dtl.Columns[i].ColumnName;
myPara.DbType = GetDbType(dtl.Columns[i].DataType);
myPara.SourceColumn = dtl.Columns[i].ToString();
insertCmd.Parameters.Add(myPara);
}
}
sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+") VALUES(";
for(int i=0;i<dtl.Columns.Count;i++)
{
if(dtl.Columns[i].ColumnName!=identityColumnName)
sqlStr=sqlStr+"?,";
}
sqlStr=sqlStr.Substring(0,sqlStr.Length-1)+")";
insertCmd.CommandText = sqlStr;
return insertCmd;
}
#endregion
#region CREATEADAPTER
/// <summary>
/// 生成Adapter的查询、插入、修改、删除command对象
/// </summary>
/// <param name="tblName">DataTable</param>
/// <param name="trs">事务处理的OleDbTransaction对象</param>
/// <param name="selectSql">查询表的SQL命令</param>
/// <param name="IdenColumnName">表中identity列的名字</param>
/// <returns>OleDbDataAdapter对象</returns>
public OleDbDataAdapter createAdapter(DataTable tblName,OleDbTransaction trs,string selectSql,string IdenColumnName)
{
OleDbDataAdapter daUp=new OleDbDataAdapter();
try
{
daUp.SelectCommand=createSelectCmd(selectSql);
daUp.SelectCommand.Transaction=trs;
daUp.Fill(tblName);
daUp.UpdateCommand=createUpdateCommand(tblName);
daUp.UpdateCommand.Transaction=trs;
daUp.DeleteCommand=createDelCommand(tblName);
daUp.DeleteCommand.Transaction=trs;
daUp.InsertCommand=createInsertCommand(tblName,IdenColumnName);
daUp.InsertCommand.Transaction=trs;
}
catch
{
}
return daUp;
}
#endregion
#endregion
//插入记录
#region insert(use InsideTransaction,DataTable[])
/// <summary>
/// 同时更新多表
/// </summary>
/// <param name="tbls">数据表集</param>
/// <param name="newRows">插入行集</param>
/// <returns></returns>
public static 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=createInsertCommand(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 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>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程名字</param>
/// <param name="ParaValue">参数的值</param>
/// <param name="ParaName">参数名字</param>
/// <param name="ParaType">参数的类型</param>
/// <returns></returns>
public string ExecProc(string procName,string[] ParaValue,string[] ParaName,string[] ParaType)
{
OleDbCommand cmdp=new OleDbCommand();
cmdp.Connection=cnn;
cmdp.CommandType=CommandType.StoredProcedure;
cmdp.CommandText=procName;
string strOut="";
for (int i=0;i<ParaValue.Length;i++)
{
OleDbParameter pt=new OleDbParameter();
ParaName[i]="@"+ParaName[i];
//参数名字
//pt.ParameterName=ParaName[i];
pt.SourceColumn=ParaName[i];
pt.DbType=GetDbType(ParaType[i].GetType());
pt.Value=ParaValue[i];
pt.Direction=ParameterDirection.Input;
cmdp.Parameters.Add(pt);
}
if((ParaName.Length-ParaValue.Length)==1 && ParaType.Length==ParaName.Length)
{
OleDbParameter pt=new OleDbParameter();
ParaName[ParaName.Length-1]="@"+ParaName[ParaName.Length-1];
//参数名字
//pt.ParameterName=ParaName[ParaName.Length-1];
pt.SourceColumn=ParaName[ParaName.Length-1];
pt.DbType=GetDbType(ParaType[ParaName.Length-1].GetType());
pt.Direction=ParameterDirection.Output;
cmdp.Parameters.Add(pt);
}
cnn.Open();
DataTable dtl=new DataTable();
da.SelectCommand=cmdp;
da.RowUpdated+=new OleDbRowUpdatedEventHandler(da_RowUpdated);
da.Fill(dtl);
da.Update(dtl);
if((ParaName.Length-ParaValue.Length)==1 && ParaType.Length==ParaName.Length)
strOut=cmdp.Parameters[ParaName.Length-1].Value.ToString();
cnn.Close();
return strOut;
}
private static System.Data.DbType GetDbType(Type type)
{
DbType result = DbType.String;
if( type.Equals(typeof(int)) || type.IsEnum)
result = DbType.Int32;
else if( type.Equals(typeof(long)))
result = DbType.Int32;
else if( type.Equals(typeof(double)) || type.Equals( typeof(Double)))
result = DbType.Decimal;
else if( type.Equals(typeof(DateTime)))
result = DbType.DateTime;
else if( type.Equals(typeof(bool)))
result = DbType.Boolean;
else if( type.Equals(typeof(string) ) )
result = DbType.String;
else if( type.Equals(typeof(decimal)))
result = DbType.Decimal;
else if( type.Equals(typeof(byte[])))
result = DbType.Binary;
else if( type.Equals(typeof(Guid)))
result = DbType.Guid;
return result;
}
private static bool checkColumnName(string colName)
{
if(colName.IndexOfAny(new char[] {'(',')','\'','\"'})>0)
{
// MessageBox.Show("列名包含非法字符！");
return false;
}
return true;
}
private void da_RowUpdated(object sender, OleDbRowUpdatedEventArgs e)
{
if((e.Status==UpdateStatus.Continue) && (e.StatementType==StatementType.Insert))
{
e.Row["编号"]=(int)cmdGetIdentity.ExecuteScalar();
e.Row.AcceptChanges();
}
}
#endregion execProc(return dataTable)
//修改数据库结构
#region amendDataBase
/// <summary>
/// 修改数据库表的结构
/// </summary>
/// <param name="strSql">SQL命令</param>
public void alterTable(string strSql) //修改表的结构,更新到数据库
{
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);
cmd.CommandText=strSql;
cmd.ExecuteNonQuery();
}
#endregion amendDataBase
//操作数据库对象
#region operateDataBase(DDL)
/// <summary>
/// 附加数据库
/// </summary>
/// <param name="strCon">连接字符串</param>
/// <param name="DBName">要生成的数据库名</param>
/// <param name="strMdf">mdf文件位置</param>
/// <param name="strLdf">ldf文件位置</param>
/// <returns></returns>
public bool attachDB(string strCon,string DBName,string strMdf,string strLdf)
{
bool bl=true;
try
{
cnn.ConnectionString=strCon;
if (cnn.State!=ConnectionState.Open)
cnn.Open();
cmd.Connection=cnn;
cmd.CommandText=@"sp_attach_db";
cmd.Parameters.Add(new OleDbParameter("@dbname",OleDbType.VarChar));
cmd.Parameters["@dbname"].Value=DBName;
cmd.Parameters.Add(new OleDbParameter("@filename1",OleDbType.VarChar));
cmd.Parameters["@filename1"].Value=strMdf;
cmd.Parameters.Add(new OleDbParameter("@filename2",OleDbType.VarChar));
cmd.Parameters["@filename2"].Value=strLdf;
cmd.CommandType=CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
catch
{
// MessageBox.Show(e.Message.ToString());
bl=false;
}
finally
{
cnn.Close();
}
return bl;
}
/// <summary>
/// 分离数据库
/// </summary>
/// <param name="strCon">连接字符串</param>
/// <param name="DBName">数据库名</param>
/// <returns></returns>
public bool detachDB(string strCon,string DBName)
{
bool bl=true;
try
{
cnn.ConnectionString=strCon;
if (cnn.State!=ConnectionState.Open)
cnn.Open();
cmd.Connection=cnn;
cmd.CommandText=@"sp_detach_db";
cmd.Parameters.Add(new OleDbParameter("@dbname",OleDbType.VarChar));
cmd.Parameters["@dbname"].Value=DBName;
cmd.CommandType=CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
catch
{
bl=false;
}
finally
{
cnn.Close();
}
return bl;
}
/// <summary>
/// 备份数据库
/// </summary>
/// <param name="strCon">连接字符串</param>
/// <param name="DBName">数据库名</param>
/// <param name="backupName">备份名</param>
/// <param name="backupFile">备份文件位置</param>
/// <returns></returns>
public bool BackUpDB(string strCon,string DBName,string backupName,string backupFile)
{
bool bl=true;
try
{
cnn.ConnectionString=strCon;
if (cnn.State!=ConnectionState.Open)
cnn.Open();
cmd.Connection=cnn;
cmd.CommandText=@"BACKUP DATABASE "+DBName+" to disk='"+backupFile+"'"+
"WITH NOINIT , NOUNLOAD , NAME = N'"+backupName+"', NOSKIP , STATS = 10, NOFORMAT ";
cmd.CommandType=CommandType.Text;
cmd.ExecuteNonQuery();
}
catch
{
bl=false;
}
finally
{
cnn.Close();
}
return bl;
}
/// <summary>
/// 还原数据库
/// </summary>
/// <param name="strCon">连接字符串</param>
/// <param name="DBName">数据库名</param>
/// <param name="backupFile">备份文件位置</param>
/// <returns></returns>
public bool restoreDB(string strCon,string DBName,string backupFile)
{
bool bl=true;
try
{
cnn.ConnectionString=strCon;
if (cnn.State!=ConnectionState.Open)
cnn.Open();
cmd.Connection=cnn;
cmd.CommandText=@"RESTORE FILELISTONLY from disk='"+backupFile+"'";
cmd.CommandType=CommandType.Text;
cmd.ExecuteNonQuery();
}
catch
{
bl=false;
}
finally
{
cnn.Close();
}
return bl;
}
public static void DbBackup(string strHost,string strUserName,string strUserPwd,string strDBName,string strDBBakName)
{
SQLDMO.Backup oBackup = new SQLDMO.BackupClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(strHost,strUserName,strUserPwd);
oBackup.Action = SQLDMO.SQLDMO_BACKUP_TYPE.SQLDMOBackup_Database;
oBackup.Database = strDBName;
oBackup.Files = strDBBakName;
oBackup.BackupSetName = strDBName;
oBackup.BackupSetDescription = "数据库备份";
oBackup.Initialize = true;
oBackup.SQLBackup(oSQLServer);
}
catch
{
throw;
}
finally
{
oSQLServer.DisConnect();
}
}
///
/// 数据库恢复
///
public static void DbRestore(string strHost,string strUserName,string strUserPwd,string strDBName,string strDBBakName)
{
SQLDMO.Restore oRestore = new SQLDMO.RestoreClass();
SQLDMO.SQLServer oSQLServer = new SQLDMO.SQLServerClass();
try
{
oSQLServer.LoginSecure = false;
oSQLServer.Connect(strHost, strUserName,strUserPwd);
oRestore.Action = SQLDMO.SQLDMO_RESTORE_TYPE.SQLDMORestore_Database;
oRestore.Database = strDBName;
oRestore.Files = strDBBakName;
oRestore.FileNumber = 1;
oRestore.ReplaceDatabase = true;
oRestore.SQLRestore(oSQLServer);
}
catch
{
throw;
}
finally
{
oSQLServer.DisConnect();
}
}
#endregion
}
}
