C#access数据库连接代码.docx
《C#access数据库连接代码.docx》由会员分享,可在线阅读,更多相关《C#access数据库连接代码.docx(15页珍藏版)》请在冰豆网上搜索。
C#access数据库连接代码
usingSystem;
usingSystem.Collections;
usingSystem.Collections.Specialized;
usingSystem.Data;
usingSystem.Data.OleDb;
usingSystem.Configuration;
namespaceMaticsoft.DBUtility
{
///
///Copyright(C)Maticsoft
///数据访问基础类(基于OleDb)
///可以用户可以修改满足自己项目的需要。
publicabstractclassDbHelperOleDb
//数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库.
//publicstaticstringconnectionString="|DataDirectory|"+"pp.asp";
publicstaticstringconnectionString="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+System.Web.HttpContext.Current.Server.MapPath(@"/db/dbtest.mdb");
publicDbHelperOleDb()
}
#region公用方法
publicstaticintGetMaxID(stringFieldName,stringTableName)
stringstrsql="selectmax("+FieldName+")+1from"+TableName;
objectobj=DbHelperSQL.GetSingle(strsql);
if(obj==null)
return1;
else
returnint.Parse(obj.ToString());
publicstaticboolExists(stringstrSql)
objectobj=DbHelperSQL.GetSingle(strSql);
intcmdresult;
if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
cmdresult=0;
cmdresult=int.Parse(obj.ToString());
if(cmdresult==0)
returnfalse;
returntrue;
publicstaticboolExists(stringstrSql,paramsOleDbParameter[]cmdParms)
objectobj=GetSingle(strSql,cmdParms);
#endregion
#region执行简单SQL语句
///执行SQL语句,返回影响的记录数
///SQL语句
///影响的记录数
publicstaticintExecuteSql(stringSQLString)
using(OleDbConnectionconnection=newOleDbConnection(connectionString))
using(OleDbCommandcmd=newOleDbCommand(SQLString,connection))
try
connection.Open();
introws=cmd.ExecuteNonQuery();
returnrows;
catch(System.Data.OleDb.OleDbExceptionE)
connection.Close();
thrownewException(E.Message);
///执行多条SQL语句,实现数据库事务。
///多条SQL语句
publicstaticvoidExecuteSqlTran(ArrayListSQLStringList)
using(OleDbConnectionconn=newOleDbConnection(connectionString))
conn.Open();
OleDbCommandcmd=newOleDbCommand();
cmd.Connection=conn;
OleDbTransactiontx=conn.BeginTransaction();
cmd.Transaction=tx;
for(intn=0;n{stringstrsql=SQLStringList[n].ToString();if(strsql.Trim().Length>1){cmd.CommandText=strsql;cmd.ExecuteNonQuery();}}tx.Commit();}catch(System.Data.OleDb.OleDbExceptionE){tx.Rollback();thrownewException(E.Message);}}}//////执行带一个存储过程参数的的SQL语句。//////SQL语句///参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加///影响的记录数publicstaticintExecuteSql(stringSQLString,stringcontent){using(OleDbConnectionconnection=newOleDbConnection(connectionString)){OleDbCommandcmd=newOleDbCommand(SQLString,connection);System.Data.OleDb.OleDbParametermyParameter=newSystem.Data.OleDb.OleDbParameter("@content",OleDbType.VarChar);myParameter.Value=content;cmd.Parameters.Add(myParameter);try{connection.Open();introws=cmd.ExecuteNonQuery();returnrows;}catch(System.Data.OleDb.OleDbExceptionE){thrownewException(E.Message);}finally{cmd.Dispose();connection.Close();}}}//////向数据库里插入图像格式的字段(和上面情况类似的另一种实例)//////SQL语句///图像字节,数据库的字段类型为image的情况///影响的记录数publicstaticintExecuteSqlInsertImg(stringstrSQL,byte[]fs){using(OleDbConnectionconnection=newOleDbConnection(connectionString)){OleDbCommandcmd=newOleDbCommand(strSQL,connection);System.Data.OleDb.OleDbParametermyParameter=newSystem.Data.OleDb.OleDbParameter("@fs",OleDbType.Binary);myParameter.Value=fs;cmd.Parameters.Add(myParameter);try{connection.Open();introws=cmd.ExecuteNonQuery();returnrows;}catch(System.Data.OleDb.OleDbExceptionE){thrownewException(E.Message);}finally{cmd.Dispose();connection.Close();}}}//////执行一条计算查询结果语句,返回查询结果(object)。//////计算查询结果语句///查询结果(object)publicstaticobjectGetSingle(stringSQLString){using(OleDbConnectionconnection=newOleDbConnection(connectionString)){using(OleDbCommandcmd=newOleDbCommand(SQLString,connection)){try{connection.Open();objectobj=cmd.ExecuteScalar();if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value))){returnnull;}else{returnobj;}}catch(System.Data.OleDb.OleDbExceptione){connection.Close();thrownewException(e.Message);}}}}//////执行查询语句,返回OleDbDataReader//////查询语句///OleDbDataReaderpublicstaticOleDbDataReaderExecuteReader(stringstrSQL){OleDbConnectionconnection=newOleDbConnection(connectionString);OleDbCommandcmd=newOleDbCommand(strSQL,connection);try{connection.Open();OleDbDataReadermyReader=cmd.ExecuteReader();returnmyReader;}catch(System.Data.OleDb.OleDbExceptione){thrownewException(e.Message);}}//////执行查询语句,返回DataSet//////查询语句///DataSetpublicstaticDataSetQuery(stringSQLString){using(OleDbConnectionconnection=newOleDbConnection(connectionString)){DataSetds=newDataSet();try{connection.Open();OleDbDataAdaptercommand=newOleDbDataAdapter(SQLString,connection);command.Fill(ds,"ds");}catch(System.Data.OleDb.OleDbExceptionex){thrownewException(ex.Message);}returnds;}}#endregion#region执行带参数的SQL语句//////执行SQL语句,返回影响的记录数//////SQL语句///影响的记录数publicstaticintExecuteSql(stringSQLString,paramsOleDbParameter[]cmdParms){using(OleDbConnectionconnection=newOleDbConnection(connectionString)){using(OleDbCommandcmd=newOleDbCommand()){try{PrepareCommand(cmd,connection,null,SQLString,cmdParms);introws=cmd.ExecuteNonQuery();cmd.Parameters.Clear();returnrows;}catch(System.Data.OleDb.OleDbExceptionE){thrownewException(E.Message);}}}}//////执行多条SQL语句,实现数据库事务。//////SQL语句的哈希表(key为sql语句,value是该语句的OleDbParameter[])publicstaticvoidExecuteSqlTran(HashtableSQLStringList){using(OleDbConnectionconn=newOleDbConnection(connectionString)){conn.Open();using(OleDbTransactiontrans=conn.BeginTransaction()){OleDbCommandcmd=newOleDbCommand();try{//循环foreach(DictionaryEntrymyDEinSQLStringList){stringcmdText=myDE.Key.ToString();OleDbParameter[]cmdParms=(OleDbParameter[])myDE.Value;PrepareCommand(cmd,conn,trans,cmdText,cmdParms);intval=cmd.ExecuteNonQuery();cmd.Parameters.Clear();trans.Commit();}}catch{trans.Rollback();throw;}}}}//////执行一条计算查询结果语句,返回查询结果(object)。//////计算查询结果语句///查询结果(object)publicstaticobjectGetSingle(stringSQLString,paramsOleDbParameter[]cmdParms){using(OleDbConnectionconnection=newOleDbConnection(connectionString)){using(OleDbCommandcmd=newOleDbCommand()){try{PrepareCommand(cmd,connection,null,SQLString,cmdParms);objectobj=cmd.ExecuteScalar();cmd.Parameters.Clear();if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value))){returnnull;}else{returnobj;}}catch(System.Data.OleDb.OleDbExceptione){thrownewException(e.Message);}}}}//////执行查询语句,返回OleDbDataReader//////查询语句///OleDbDataReaderpublicstaticOleDbDataReaderExecuteReader(stringSQLString,paramsOleDbParameter[]cmdParms){OleDbConnectionconnection=newOleDbConnection(connectionString);OleDbCommandcmd=newOleDbCommand();try{PrepareCommand(cmd,connection,null,SQLString,cmdParms);OleDbDataReadermyReader=cmd.ExecuteReader();cmd.Parameters.Clear();returnmyReader;}catch(System.Data.OleDb.OleDbExceptione){thrownewException(e.Message);}}//////执行查询语句,返回DataSet//////查询语句///DataSetpublicstaticDataSetQuery(stringSQLString,paramsOleDbParameter[]cmdParms){using(OleDbConnectionconnection=newOleDbConnection(connectionString)){OleDbCommandcmd=newOleDbCommand();PrepareCommand(cmd,connection,null,SQLString,cmdParms);using(OleDbDataAdapterda=newOleDbDataAdapter(cmd)){DataSetds=newDataSet();try{da.Fill(ds,"ds");cmd.Parameters.Clear();}catch(System.Data.OleDb.OleDbExceptionex){thrownewException(ex.Message);}returnds;}}}privatestaticvoidPrepareCommand(OleDbCommandcmd,OleDbConnectionconn,OleDbTransactiontran
stringstrsql=SQLStringList[n].ToString();
if(strsql.Trim().Length>1)
cmd.CommandText=strsql;
cmd.ExecuteNonQuery();
tx.Commit();
tx.Rollback();
///执行带一个存储过程参数的的SQL语句。
///参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加
publicstaticintExecuteSql(stringSQLString,stringcontent)
OleDbCommandcmd=newOleDbCommand(SQLString,connection);
System.Data.OleDb.OleDbParametermyParameter=newSystem.Data.OleDb.OleDbParameter("@content",OleDbType.VarChar);
myParameter.Value=content;
cmd.Parameters.Add(myParameter);
finally
cmd.Dispose();
///向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
///图像字节,数据库的字段类型为image的情况
publicstaticintExecuteSqlInsertImg(stringstrSQL,byte[]fs)
OleDbCommandcmd=newOleDbCommand(strSQL,connection);
System.Data.OleDb.OleDbParametermyParameter=newSystem.Data.OleDb.OleDbParameter("@fs",OleDbType.Binary);
myParameter.Value=fs;
///执行一条计算查询结果语句,返回查询结果(object)。
///计算查询结果语句
///查询结果(object)
publicstaticobjectGetSingle(stringSQLString)
objectobj=cmd.ExecuteScalar();
returnnull;
returnobj;
catch(System.Data.OleDb.OleDbExceptione)
thrownewException(e.Message);
///执行查询语句,返回OleDbDataReader
///查询语句
///OleDbDataReader
publicstaticOleDbDataReaderExecuteReader(stringstrSQL)
OleDbConnectionconnection=newOleDbConnection(connectionString);
OleDbDataReadermyReader=cmd.ExecuteReader();
returnmyReader;
///执行查询语句,返回DataSet
///DataSet
publicstaticDataSetQuery(stringSQLString)
DataSetds=newDataSet();
OleDbDataAdaptercommand=newOleDbDataAdapter(SQLString,connection);
command.Fill(ds,"ds");
catch(System.Data.OleDb.OleDbExceptionex)
thrownewException(ex.Message);
returnds;
#region执行带参数的SQL语句
publicstaticintExecuteSql(stringSQLString,paramsOleDbParameter[]cmdParms)
using(OleDbCommandcmd=newOleDbCommand())
PrepareCommand(cmd,connection,null,SQLString,cmdParms);
cmd.Parameters.Clear();
///SQL语句的哈希表(key为sql语句,value是该语句的OleDbParameter[])
publicstaticvoidExecuteSqlTran(HashtableSQLStringList)
using(OleDbTransactiontrans=conn.BeginTransaction())
//循环
foreach(DictionaryEntrymyDEinSQLStringList)
stringcmdText=myDE.Key.ToString();
OleDbParameter[]cmdParms=(OleDbParameter[])myDE.Value;
PrepareCommand(cmd,conn,trans,cmdText,cmdParms);
intval=cmd.ExecuteNonQuery();
trans.Commit();
catch
trans.Rollback();
throw;
publicstaticobjectGetSingle(stringSQLString,paramsOleDbParameter[]cmdParms)
publicstaticOleDbDataReaderExecuteReader(stringSQLString,paramsOleDbParameter[]cmdParms)
publicstaticDataSetQuery(stringSQLString,paramsOleDbParameter[]cmdParms)
using(OleDbDataAdapterda=newOleDbDataAdapter(cmd))
da.Fill(ds,"ds");
privatestaticvoidPrepareCommand(OleDbCommandcmd,OleDbConnectionconn,OleDbTransactiontran
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1