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

///

1<summary>   
2/// 获取连接字符串   
3/// </summary>

public string MyConStr
{
get {return conStr;}
set {conStr = value;}
}

#endregion get&setConnectionString

//获得表的名称
#region acquireTableNames

///

1<summary>   
2/// 获取数据库中的表名集合   
3/// </summary>

///

1<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

///

1<summary>   
2/// 填充dataTable的查询   
3/// </summary>

///

1<param name="tblName"/>

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

1<param name="sqlStr"/>

SQL语句
///

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

1<summary>   
2/// 插入记录(用OleDbCommandBuilder)   
3/// </summary>

///

1<param name="tblName"/>

数据表
///

1<param name="newRow"/>

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

1<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

  1<tbls.length;i++) "+sum.tostring()+"="" #endregion="" #region="" (judge)="" (num[i]="0)" ;="" <summary="" catch="" cnn.close();="" da.insertcommand='insertCmd(tbls[i],"操作编号");' da.insertcommand.transaction="tran;" else="" if="" insert(use="" insidetransaction,datatable[])="" judge="true;" num[i]="da.Update(tbls[i]);" outsidetransaction)="" return="" str="更新成功" str+",影响了="" sum="0;" sum+="num[i];" tbls[i].rows.add(newrows[i]);="" this.select(tbls[i],"1='1",tran);' tran.commit();="" tran.rollback();="" try="" {="" }="" 插入记录="" 条记录";="">   
  2/// 填充DataTable(用于事务处理)   
  3///    
  4/// <param name="tblName"/>表   
  5/// <param name="sqlStr"/>SQL语句   
  6/// <param name="trs"/>Transaction对象   
  7/// <returns>行数</returns>   
  8public int select(DataTable tblName,string sqlStr,OleDbTransaction trs)   
  9{   
 10int i=0; 
 11
 12// try   
 13// {   
 14//   
 15tblName.Clear();   
 16da.Dispose();   
 17  
 18if (cnn.ConnectionString=="")   
 19cnn.ConnectionString=conStr;   
 20if (cnn.State!=ConnectionState.Open)   
 21cnn.Open();   
 22// OleDbCommand cmd=new OleDbCommand("select * from "+tblName.TableName+" where "+sqlStr,cnn);   
 23cmd.Connection=cnn;   
 24cmd.CommandType=CommandType.Text;   
 25cmd.CommandText="select * from "+tblName.TableName+" where "+sqlStr;   
 26da.SelectCommand=cmd; 
 27
 28cmd.Transaction=trs;   
 29i=da.Fill(tblName);   
 30  
 31return i;   
 32} 
 33
 34/// <summary>   
 35/// 插入记录(用OleDbDataAdapter.Update方法及OleDbTransaction)   
 36/// </summary>   
 37/// <param name="tblName"/>数据表   
 38/// <param name="newRow"/>新行   
 39/// <param name="trs"/>事务对象   
 40/// <returns></returns>   
 41public int insert(DataTable tblName,DataRow newRow,OleDbTransaction trs)   
 42{ 
 43
 44da.InsertCommand=insertCmd(tblName,"noo");   
 45  
 46int num=0; 
 47
 48try   
 49{   
 50tblName.Rows.Add(newRow); 
 51
 52da.InsertCommand.Transaction=trs; 
 53
 54num=da.Update(tblName);   
 55}   
 56catch   
 57{ 
 58
 59}   
 60  
 61return num;   
 62  
 63} 
 64
 65  
 66#endregion insert(use OutsideTransaction) 
 67
 68//构造插入的Command   
 69#region insertCommand   
 70/// <summary>   
 71/// 构造insertCommand   
 72/// </summary>   
 73/// <param name="dtl"/>数据表   
 74/// <param name="identityCol"/>identity列的名称   
 75/// <returns></returns>   
 76private static OleDbCommand insertCmd(DataTable dtl,string identityCol)   
 77{   
 78OleDbCommand inCmd=new OleDbCommand();   
 79inCmd.Connection=cnn; 
 80
 81string sqlStr="";   
 82string strValue=""; 
 83
 84sqlStr = "INSERT " + dtl.TableName.ToString() + "(";   
 85strValue = ") Values (";   
 86  
 87for (int i=0;i<dtl.columns.count;i++) !="identityCol)" ")";="" ";="" "],="" #endregion="" #region="" (dtl.columns[i].tostring()="" +="" ;="" <summary="" dtl.columns[i].tostring()="" dtl.columns[i].tostring();="" if="" incmd.commandtext="sqlStr;" incmd.parameters.add(mypara);="" incmd;="" insertcommand="" mypara="new" mypara.direction="ParameterDirection.Input;" mypara.oledbtype="GetOleDbType(dtl.Columns[i].DataType.ToString());" mypara.parametername="@" mypara.sourcecolumn="dtl.Columns[i].ToString();" mypara.sourceversion="DataRowVersion.Current;" oledbparameter="" oledbparameter();="" return="" sqlstr="" strvalue="strValue.Substring(0,strValue.Length-1);" update="" {="" }="" 修改="" 对于identity列无需赋值="">   
 88/// 修改记录   
 89///    
 90/// <param name="tblName"/>数据表   
 91/// <param name="strUp"/>SQL语句   
 92/// <returns>影响的行数</returns>   
 93public int update(DataTable tblName,string strUp)   
 94{   
 95cnn.Close();   
 96return i;   
 97}   
 98#endregion update 
 99
100//删除   
101#region del(use CommandBuilder)   
102/// <summary>   
103/// 删除记录   
104/// </summary>   
105/// <param name="tblName"/>数据表   
106/// <param name="strDel"/>SQL语句   
107/// <returns>影响的行数</returns>   
108public int delete(DataTable tblName,string strDel) //strDel是删除条件   
109{   
110int rows=0; 
111
112//用OleDbDataAdapter.Update方法自动更新必须在where中存在主键或唯一值   
113// try   
114// {   
115//   
116cnn.Open();   
117rows=tblName.Rows.Count;   
118  
119for (int i=0;i&lt; tblName.Rows.Count;i++)   
120{   
121tblName.Rows[i].Delete();   
122} 
123
124//注意，如在da.Update前面用了下面的AcceptChanges方法，因为记录被删除--更新到数据库失败   
125//tblName.AcceptChanges();   
126da.Update(tblName);   
127//   
128  
129// }   
130// catch(Exception ce)   
131// {   
132// Console.WriteLine("产生错误:\n{0}",ce.Message);   
133// }   
134// finally   
135// {   
136cnn.Close();   
137// }   
138///   
139//用OleDbCommand直接更新   
140// try   
141// {   
142// string str="delete from "+tblName.TableName+" where "+strDel;   
143// cnn.Open();   
144// OleDbCommand cmdD=new OleDbCommand(str,cnn);   
145// cmdD.CommandType=CommandType.Text;   
146// rows=cmdD.ExecuteNonQuery();   
147// }   
148//   
149// catch(Exception ce)   
150// {   
151// Console.WriteLine("产生错误:\n{0}",ce.Message);   
152// }   
153// finally   
154// {   
155// cnn.Close();   
156// }   
157return rows;   
158}   
159#endregion del(use CommandBuilder) 
160
161//构造删除的Command   
162#region delCommand(create OleDbDataAdapter.deleteCommand) 
163
164public int delete(DataTable tblName)   
165{   
166int rows=0;   
167  
168da.DeleteCommand=delCmd(tblName);   
169  
170for (int i=0;i&lt; tblName.Rows.Count;i++)   
171{   
172tblName.Rows[i].Delete();   
173} 
174
175rows=da.Update(tblName); 
176
177return rows;   
178} 
179
180  
181private static OleDbCommand delCmd(DataTable dtl)   
182{   
183OleDbCommand delCmd=new OleDbCommand();   
184delCmd.Connection=cnn; 
185
186string sqlStr="";   
187  
188sqlStr = "delete from " + dtl.TableName.ToString() + " where ";   
189  
190for (int i=0;i<dtl.columns.count;i++) "+tblname.tablename,cnn);="" "]="?" #endregion="" #region="" (b)="" *="" +="" <summary="" ?="" ["+dtl.columns[i].tostring()+"]="" addcolumn(datatable="" amenddatabase="" and="" and";="" b="dtl.Columns[i].AllowDBNull;" bool="" cb="new" cmds="new" cnn.open();="" colitem="new" da.fill(tblname);="" da.selectcommand="cmdS;" da.update(tblname);="" datacolumn="" datacolumn(strup,type.gettype("system.string"));="" datatable)="" delcmd.commandtext="sqlStr;" delcmd.parameters.add(mypara);="" delcmd.parameters.add(mypara1);="" delcmd;="" delcommand(create="" dtl.columns[i].tostring()="" dtl.columns[i].tostring();="" execproc(return="" from="" if="" int="" is="" j="delCmd.Parameters.Count;" mypara="new" mypara.direction="ParameterDirection.Input;" mypara.oledbtype="GetOleDbType(dtl.Columns[i].DataType.ToString());" mypara.parametername="or1_" mypara.sourcecolumn="dtl.Columns[i].ToString();" mypara.sourceversion="DataRowVersion.Original;" mypara1="new" mypara1.direction="ParameterDirection.Input;" mypara1.oledbtype="GetOleDbType(dtl.Columns[i].DataType.ToString());" mypara1.parametername="or2_" mypara1.sourcecolumn="dtl.Columns[i].ToString();" mypara1.sourceversion="DataRowVersion.Original;" null="" null)="" oledbcommand="" oledbcommand("select="" oledbcommandbuilder="" oledbcommandbuilder(da);="" oledbdataadapter.deletecommand)="" oledbparameter="" oledbparameter();="" or="" public="" return="" sqlstr="sqlStr.Substring(0,sqlStr.Length-3);" strup)="" tblname,string="" tblname.columns.add(colitem);="" void="" {="" }="" 为什么上面的方法不行，只能直接用sql语句吗?="" 修改表的结构,更新到数据库="" 调用存储过程="">   
191/// 执行存储过程   
192///    
193/// <param name="procName"/>存储过程名字   
194/// <param name="ParaValue"/>参数的值   
195/// <param name="ParaName"/>参数名字   
196/// <param name="ParaType"/>参数的类型   
197/// <returns></returns>   
198public DataTable ExecProc(string procName,string[] ParaValue,string[] ParaName,string[] ParaType)   
199{   
200OleDbCommand cmdp=new OleDbCommand();   
201cmdp.Connection=cnn;   
202cmdp.CommandType=CommandType.StoredProcedure;   
203cmdp.CommandText=procName;   
204  
205for (int i=0;i<paraname.length;i++) +paraname[i];="" <summary="" cmdp.parameters.add(pt);="" cnn.close();="" cnn.open();="" da.fill(dtl);="" da.selectcommand="cmdp;" datatable="" datatable();="" dtl="new" dtl;="" oledbparameter="" oledbparameter();="" paraname[i]="@" pt="new" pt.oledbtype="GetOleDbType(ParaType[i]);" pt.parametername="ParaName[i];" pt.sourcecolumn="ParaName[i];" pt.value="ParaValue[i];" return="" {="" }="" 参数名字="">   
206/// 设置OleDbParameter对象的DbType（把字符串变为相应的OleDbType类型）   
207///    
208/// <param name="type"/>传入参数的字符串   
209/// <returns></returns>   
210private static OleDbType GetOleDbType(string type)   
211{   
212// try   
213// {   
214// return (OleDbType)Enum.Parse(typeof(OleDbType), type, true);   
215// }   
216// catch   
217// {   
218// return OleDbType.Varchar;   
219// } 
220
221switch (type)   
222{   
223case "date":   
224return OleDbType.DBDate;   
225break;   
226case "num":   
227return OleDbType.Integer;   
228break;   
229default:   
230return OleDbType.VarChar;   
231}   
232}   
233#endregion execProc(return dataTable)   
234}</paraname.length;i++)></dtl.columns.count;i++)></dtl.columns.count;i++)></tbls.length;i++)>