c#vs2010连接数据库大全
using system;
using system.collections.generic;
using system.linq;
using system.text;
using system.configuration;
using system.data.sqlclient;
using system.data;
using system.collections;
using system.data.oracleclient;
using system.data.oledb;
namespace systemframework
{
public class databaselayer
{
private string connectionstring;
private string dbtype;
public string dbtype
{
get { return dbtype; }
set { dbtype = value; }
}
public string connectionstring
{
get
{
if (dbtype == string.empty || dbtype == null)
{
return access;
}
else
{
return dbtype;
}
}
set
{
if (value != string.empty && value != null)
{
dbtype = value;
}
if (dbtype == string.empty || dbtype == null)
{
dbtype = configurationsettings.appsettings[datatype];
}
if (dbtype == string.empty || dbtype == null)
{
dbtype = access;
}
}
}
public databaselayer(string strconnect, string datatype)
{
this.connectionstring = strconnect;
this.dbtype = datatype;
}
#region 转换参数
private system.data.idbdataparameter idbpara(string paraname, string datatype)
{
switch (this.dbtype)
{
case sqlserver:
return getsqlpara(paraname, datatype);
case oracle:
return getoledbpara(paraname, datatype);
case access:
return getoledbpara(paraname, datatype);
default:
return getsqlpara(paraname, datatype);
}
}
private system.data.sqlclient.sqlparameter getsqlpara(string paraname, string datatype)
{
switch (datatype)
{
case decimal:
return new system.data.sqlclient.sqlparameter(paraname, system.data.sqldbtype.decimal);
case varchar:
return new system.data.sqlclient.sqlparameter(paraname, system.data.sqldbtype.varchar);
case datetime:
return new system.data.sqlclient.sqlparameter(paraname, system.data.sqldbtype.datetime);
case iamge:
return new system.data.sqlclient.sqlparameter(paraname, system.data.sqldbtype.image);
case int:
return new system.data.sqlclient.sqlparameter(paraname, system.data.sqldbtype.int);
case text:
return new system.data.sqlclient.sqlparameter(paraname, system.data.sqldbtype.ntext);
default:
return new system.data.sqlclient.sqlparameter(paraname, system.data.sqldbtype.varchar);
}
}
private system.data.oracleclient.oracleparameter getoraclepara(string paraname, string datatype)
{
switch (datatype)
{
case decimal:
return new system.data.oracleclient.oracleparameter(paraname, system.data.oracleclient.oracletype.double);
case varchar:
return new system.data.oracleclient.oracleparameter(paraname, system.data.oracleclient.oracletype.varchar);
case datetime:
return new system.data.oracleclient.oracleparameter(paraname, system.data.oracleclient.oracletype.datetime);
case iamge:
return new system.data.oracleclient.oracleparameter(paraname, system.data.oracleclient.oracletype.bfile);
case int:
return new system.data.oracleclient.oracleparameter(paraname, system.data.oracleclient.oracletype.int32);
case text:
return new system.data.oracleclient.oracleparameter(paraname, system.data.oracleclient.oracletype.longvarchar);
default:
return new system.data.oracleclient.oracleparameter(paraname, system.data.oracleclient.oracletype.varchar);
}
}
private system.data.oledb.oledbparameter getoledbpara(string paraname, string datatype)
{
switch (datatype)
{
case decimal:
return new system.data.oledb.oledbparameter(paraname, system.data.dbtype.decimal);
case varchar:
return new system.data.oledb.oledbparameter(paraname, system.data.dbtype.string);
case datetime:
return new system.data.oledb.oledbparameter(paraname, system.data.dbtype.datetime);
case iamge:
return new system.data.oledb.oledbparameter(paraname, system.data.dbtype.binary);
case int:
return new system.data.oledb.oledbparameter(paraname, system.data.dbtype.int32);
case text:
return new system.data.oledb.oledbparameter(paraname, system.data.dbtype.string);
default:
return new system.data.oledb.oledbparameter(paraname, system.data.dbtype.string);
}
}
#endregion
#region 创建 connection 和 command
private idbconnection getconnection()
{
switch (this.dbtype)
{
case sqlserver:
return new system.data.sqlclient.sqlconnection(this.connectionstring);
case oracle:
return new system.data.oracleclient.oracleconnection(this.connectionstring);
case access:
return new system.data.oledb.oledbconnection(this.connectionstring);
default:
return new system.data.sqlclient.sqlconnection(this.connectionstring);
}
}
private idbcommand getcommand()
{
switch (this.dbtype)
{
case sqlserver:
return new system.data.sqlclient.sqlcommand();
case oracle:
return new system.data.oracleclient.oraclecommand();
case access:
return new system.data.oledb.oledbcommand();
default:
return new system.data.sqlclient.sqlcommand();
}
}
private idataadapter getadapater(string sql, idbconnection iconn)
{
switch (this.dbtype)
{
case sqlserver:
return new system.data.sqlclient.sqldataadapter(sql, (sqlconnection)iconn);
case oracle:
return new system.data.oracleclient.oracledataadapter(sql, (oracleconnection)iconn);
case access:
return new system.data.oledb.oledbdataadapter(sql, (oledbconnection)iconn);
default:
return new system.data.sqlclient.sqldataadapter(sql, (sqlconnection)iconn); ;
}
}
private idataadapter getadapater()
{
switch (this.dbtype)
{
case sqlserver:
return new system.data.sqlclient.sqldataadapter();
case oracle:
return new system.data.oracleclient.oracledataadapter();
case access:
return new system.data.oledb.oledbdataadapter();
default:
return new system.data.sqlclient.sqldataadapter();
}
}
private idataadapter getadapater(idbcommand icmd)
{
switch (this.dbtype)
{
case sqlserver:
return new system.data.sqlclient.sqldataadapter((sqlcommand)icmd);
case oracle:
return new system.data.oracleclient.oracledataadapter((oraclecommand)icmd);
case access:
return new system.data.oledb.oledbdataadapter((oledbcommand)icmd);
default:
return new system.data.sqlclient.sqldataadapter((sqlcommand)icmd);
}
}
#endregion
#region 执行简单sql语句
/**/
///
/// 执行sql语句，返回影响的记录数
///
/// sql语句
/// 影响的记录数
public int executesql(string sqlstring)
{
using (system.data.idbconnection iconn = this.getconnection())
{
using (system.data.idbcommand icmd = getcommand(sqlstring, iconn))
{
iconn.open();
try
{
int rows = icmd.executenonquery();
return rows;
}
catch (system.exception e)
{
throw new exception(e.message);
}
finally
{
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}
/**/
///
/// 执行多条sql语句，实现数据库事务。
///
/// 多条sql语句
public void executesqltran(arraylist sqlstringlist)
{
using (system.data.idbconnection iconn = this.getconnection())
{
iconn.open();
using (system.data.idbcommand icmd = getcommand())
{
icmd.connection = iconn;
using (system.data.idbtransaction idbtran = iconn.begintransaction())
{
icmd.transaction = idbtran;
try
{
for (int n = 0; n {
string strsql = sqlstringlist[n].tostring();
if (strsql.trim().length > 1)
{
icmd.commandtext = strsql;
icmd.executenonquery();
}
}
idbtran.commit();
}
catch (system.exception e)
{
idbtran.rollback();
throw new exception(e.message);
}
finally
{
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}
}
/**/
///
/// 执行带一个存储过程参数的的sql语句。
///
/// sql语句
/// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加
/// 影响的记录数
public int executesql(string sqlstring, string content)
{
using (system.data.idbconnection iconn = this.getconnection())
{
using (system.data.idbcommand icmd = getcommand(sqlstring, iconn))
{
system.data.idataparameter myparameter = this.idbpara(@content, text);
myparameter.value = content;
icmd.parameters.add(myparameter);
iconn.open();
try
{
int rows = icmd.executenonquery();
return rows;
}
catch (system.exception e)
{
throw new exception(e.message);
}
finally
{
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}
/**/
///
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
///
/// sql语句
/// 图像字节,数据库的字段类型为image的情况
/// 影响的记录数
public int executesqlinsertimg(string sqlstring, byte[] fs)
{
using (system.data.idbconnection iconn = this.getconnection())
{
using (system.data.idbcommand icmd = getcommand(sqlstring, iconn))
{
system.data.idataparameter myparameter = this.idbpara(@content, image);
myparameter.value = fs;
icmd.parameters.add(myparameter);
iconn.open();
try
{
int rows = icmd.executenonquery();
return rows;
}
catch (system.exception e)
{
throw new exception(e.message);
}
finally
{
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}
/**/
///
/// 执行一条计算查询结果语句，返回查询结果（object）。
///
/// 计算查询结果语句
/// 查询结果（object）
public object getsingle(string sqlstring)
{
using (system.data.idbconnection iconn = getconnection())
{
using (system.data.idbcommand icmd = getcommand(sqlstring, iconn))
{
iconn.open();
try
{
object obj = icmd.executescalar();
if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value)))
{
return null;
}
else
{
return obj;
}
}
catch (system.exception e)
{
throw new exception(e.message);
}
finally
{
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}
/**/
///
/// 执行查询语句，返回idataadapter
///
/// 查询语句
/// idataadapter
public idataadapter executereader(string strsql)
{
using (system.data.idbconnection iconn = this.getconnection())
{
iconn.open();
try
{
system.data.idataadapter iadapter = this.getadapater(strsql, iconn);
return iadapter;
}
catch (system.exception e)
{
throw new exception(e.message);
}
finally
{
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
/**/
///
/// 执行查询语句，返回dataset
///
/// 查询语句
/// dataset
public dataset query(string sqlstring)
{
using (system.data.idbconnection iconn = this.getconnection())
{
using (system.data.idbcommand icmd = getcommand(sqlstring, iconn))
{
dataset ds = new dataset();
iconn.open();
try
{
system.data.idataadapter iadapter = this.getadapater(sqlstring, iconn);
iadapter.fill(ds);
return ds;
}
catch (system.exception ex)
{
throw new exception(ex.message);
}
finally
{
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}
/**/
///
/// 执行查询语句，返回dataset
///
/// 查询语句
/// 要填充的dataset
/// 要填充的表名
/// dataset
public dataset query(string sqlstring, dataset dataset, string tablename)
{
using (system.data.idbconnection iconn = this.getconnection())
{
using (system.data.idbcommand icmd = getcommand(sqlstring, iconn))
{
iconn.open();
try
{
system.data.idataadapter iadapter = this.getadapater(sqlstring, iconn);
((oledbdataadapter)iadapter).fill(dataset, tablename);
return dataset;
}
catch (system.exception ex)
{
throw new exception(ex.message);
}
finally
{
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}
/**/
///
/// 执行sql语句 返回存储过程
///
/// sql语句
/// 要填充的dataset
/// 开始记录
/// 页面记录大小
/// 表名称
/// dataset
public dataset query(string sqlstring, dataset dataset, int startindex, int pagesize, string tablename)
{
using (system.data.idbconnection iconn = this.getconnection())
{
iconn.open();
try
{
system.data.idataadapter iadapter = this.getadapater(sqlstring, iconn);
((oledbdataadapter)iadapter).fill(dataset, startindex, pagesize, tablename);
return dataset;
}
catch (exception ex)
{
throw new exception(ex.message);
}
finally
{
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
/**/
///
/// 执行查询语句，向xml文件写入数据
///
/// 查询语句
/// xml文件路径
public void writetoxml(string sqlstring, string xmlpath)
{
query(sqlstring).writexml(xmlpath);
}
/**/
///
/// 执行查询语句
///
/// 查询语句
/// datatable
public datatable executequery(string sqlstring)
{
using (system.data.idbconnection iconn = this.getconnection())
{
//system.data.idbcommand icmd = getcommand(sqlstring,iconn);
dataset ds = new dataset();
try
{
system.data.idataadapter iadapter = this.getadapater(sqlstring, iconn);
iadapter.fill(ds);
}
catch (system.exception e)
{
throw new exception(e.message);
}
finally
{
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
return ds.tables[0];
}
}
/**/
///
/// 执行查询语句
///
/// 查询语句
/// datatable
public datatable executequery(string sqlstring, string proc)
{
using (system.data.idbconnection iconn = this.getconnection())
{
using (system.data.idbcommand icmd = getcommand(sqlstring, iconn))
{
icmd.commandtype = commandtype.storedprocedure;
dataset ds = new dataset();
try
{
system.data.idataadapter idataadapter = this.getadapater(sqlstring, iconn);
idataadapter.fill(ds);
}
catch (system.exception e)
{
throw new exception(e.message);
}
finally
{
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
return ds.tables[0];
}
}
}
/**/
///
///
///
///
///
public dataview execeutedataview(string sql)
{
using (system.data.idbconnection iconn = this.getconnection())
{
using (system.data.idbcommand icmd = getcommand(sql, iconn))
{
dataset ds = new dataset();
try
{
system.data.idataadapter idataadapter = this.getadapater(sql, iconn);
idataadapter.fill(ds);
return ds.tables[0].defaultview;
}
catch (system.exception e)
{
throw new exception(e.message);
}
finally
{
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}
#endregion
#region 执行带参数的sql语句
/**/
///
/// 执行sql语句，返回影响的记录数
///
/// sql语句
/// 影响的记录数
public int executesql(string sqlstring, params idataparameter[] iparms)
{
using (system.data.idbconnection iconn = this.getconnection())
{
system.data.idbcommand icmd = getcommand();
{
try
{
preparecommand(out icmd, iconn, null, sqlstring, iparms);
int rows = icmd.executenonquery();
icmd.parameters.clear();
return rows;
}
catch (system.exception e)
{
throw new exception(e.message);
}
finally
{
icmd.dispose();
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}
/**/
///
/// 执行多条sql语句，实现数据库事务。
///
/// sql语句的哈希表（key为sql语句，value是该语句的sqlparameter[]）
public void executesqltran(hashtable sqlstringlist)
{
using (system.data.idbconnection iconn = this.getconnection())
{
iconn.open();
using (idbtransaction itrans = iconn.begintransaction())
{
system.data.idbcommand icmd = getcommand();
try
{
//循环
foreach (dictionaryentry myde in sqlstringlist)
{
string cmdtext = myde.key.tostring();
idataparameter[] iparms = (idataparameter[])myde.value;
preparecommand(out icmd, iconn, itrans, cmdtext, iparms);
int val = icmd.executenonquery();
icmd.parameters.clear();
}
itrans.commit();
}
catch
{
itrans.rollback();
throw;
}
finally
{
icmd.dispose();
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}
/**/
///
/// 执行一条计算查询结果语句，返回查询结果（object）。
///
/// 计算查询结果语句
/// 查询结果（object）
public object getsingle(string sqlstring, params idataparameter[] iparms)
{
using (system.data.idbconnection iconn = this.getconnection())
{
system.data.idbcommand icmd = getcommand();
{
try
{
preparecommand(out icmd, iconn, null, sqlstring, iparms);
object obj = icmd.executescalar();
icmd.parameters.clear();
if ((object.equals(obj, null)) || (object.equals(obj, system.dbnull.value)))
{
return null;
}
else
{
return obj;
}
}
catch (system.exception e)
{
throw new exception(e.message);
}
finally
{
icmd.dispose();
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}
/**/
///
/// 执行查询语句，返回idatareader
///
/// 查询语句
/// idatareader
public idatareader executereader(string sqlstring, params idataparameter[] iparms)
{
system.data.idbconnection iconn = this.getconnection();
{
system.data.idbcommand icmd = getcommand();
{
try
{
preparecommand(out icmd, iconn, null, sqlstring, iparms);
system.data.idatareader ireader = icmd.executereader();
icmd.parameters.clear();
return ireader;
}
catch (system.exception e)
{
throw new exception(e.message);
}
finally
{
icmd.dispose();
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}
/**/
///
/// 执行查询语句，返回dataset
///
/// 查询语句
/// dataset
public dataset query(string sqlstring, params idataparameter[] iparms)
{
using (system.data.idbconnection iconn = this.getconnection())
{
idbcommand icmd = getcommand();
{
preparecommand(out icmd, iconn, null, sqlstring, iparms);
try
{
idataadapter iadapter = this.getadapater(sqlstring, iconn);
dataset ds = new dataset();
iadapter.fill(ds);
icmd.parameters.clear();
return ds;
}
catch (system.exception ex)
{
throw new exception(ex.message);
}
finally
{
icmd.dispose();
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
}
}
}
/**/
///
/// 初始化command
///
///
///
///
///
///
private void preparecommand(out idbcommand icmd, idbconnection iconn, system.data.idbtransaction itrans, string cmdtext, idataparameter[] iparms)
{
if (iconn.state != connectionstate.open)
iconn.open();
icmd = this.getcommand();
icmd.connection = iconn;
icmd.commandtext = cmdtext;
if (itrans != null)
icmd.transaction = itrans;
icmd.commandtype = commandtype.text;//cmdtype;
if (iparms != null)
{
foreach (idataparameter parm in iparms)
icmd.parameters.add(parm);
}
}
#endregion
#region 存储过程操作
/**/
///
/// 执行存储过程
///
/// 存储过程名
/// 存储过程参数
/// sqldatareader
public sqldatareader runprocedure(string storedprocname, idataparameter[] parameters)
{
system.data.idbconnection iconn = this.getconnection();
{
iconn.open();
using (sqlcommand sqlcmd = buildquerycommand(iconn, storedprocname, parameters))
{
return sqlcmd.executereader(commandbehavior.closeconnection);
}
}
}
/**/
///
/// 执行存储过程
///
/// 存储过程名
/// 存储过程参数
/// dataset结果中的表名
/// dataset
public dataset runprocedure(string storedprocname, idataparameter[] parameters, string tablename)
{
using (system.data.idbconnection iconn = this.getconnection())
{
dataset dataset = new dataset();
iconn.open();
system.data.idataadapter ida = this.getadapater();
ida = this.getadapater(buildquerycommand(iconn, storedprocname, parameters));
((sqldataadapter)ida).fill(dataset, tablename);
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
return dataset;
}
}
/**/
///
/// 执行存储过程
///
/// 存储过程名
/// 存储过程参数
/// dataset结果中的表名
/// 开始记录索引
/// 页面记录大小
/// dataset
public dataset runprocedure(string storedprocname, idataparameter[] parameters, int startindex, int pagesize, string tablename)
{
using (system.data.idbconnection iconn = this.getconnection())
{
dataset dataset = new dataset();
iconn.open();
system.data.idataadapter ida = this.getadapater();
ida = this.getadapater(buildquerycommand(iconn, storedprocname, parameters));
((sqldataadapter)ida).fill(dataset, startindex, pagesize, tablename);
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
return dataset;
}
}
/**/
///
/// 执行存储过程 填充已经存在的dataset数据集
///
/// 存储过程名称
/// 存储过程参数
/// 要填充的数据集
/// 要填充的表名
///
public dataset runprocedure(string storeprocname, idataparameter[] parameters, dataset dataset, string tablename)
{
using (system.data.idbconnection iconn = this.getconnection())
{
iconn.open();
system.data.idataadapter ida = this.getadapater();
ida = this.getadapater(buildquerycommand(iconn, storeprocname, parameters));
((sqldataadapter)ida).fill(dataset, tablename);
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
return dataset;
}
}
/**/
///
/// 执行存储过程并返回受影响的行数
///
///
///
///
public int runprocedurenoquery(string storedprocname, idataparameter[] parameters)
{
int result = 0;
using (system.data.idbconnection iconn = this.getconnection())
{
iconn.open();
using (sqlcommand scmd = buildquerycommand(iconn, storedprocname, parameters))
{
result = scmd.executenonquery();
}
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
return result;
}
public string runprocedureexecutescalar(string storeprocname, idataparameter[] parameters)
{
string result = string.empty;
using (system.data.idbconnection iconn = this.getconnection())
{
iconn.open();
using (sqlcommand scmd = buildquerycommand(iconn, storeprocname, parameters))
{
object obj = scmd.executescalar();
if (obj == null)
result = null;
else
result = obj.tostring();
}
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
}
return result;
}
/**/
///
/// 构建 sqlcommand 对象(用来返回一个结果集，而不是一个整数值)
///
/// 数据库连接
/// 存储过程名
/// 存储过程参数
/// sqlcommand
private sqlcommand buildquerycommand(idbconnection iconn, string storedprocname, idataparameter[] parameters)
{
idbcommand icmd = getcommand(storedprocname, iconn);
icmd.commandtype = commandtype.storedprocedure;
if (parameters == null)
{
return (sqlcommand)icmd;
}
foreach (idataparameter parameter in parameters)
{
icmd.parameters.add(parameter);
}
return (sqlcommand)icmd;
}
/**/
///
/// 执行存储过程，返回影响的行数
///
/// 存储过程名
/// 存储过程参数
/// 影响的行数
///
public int runprocedure(string storedprocname, idataparameter[] parameters, out int rowsaffected)
{
using (system.data.idbconnection iconn = this.getconnection())
{
int result;
iconn.open();
using (sqlcommand sqlcmd = buildintcommand(iconn, storedprocname, parameters))
{
rowsaffected = sqlcmd.executenonquery();
result = (int)sqlcmd.parameters[returnvalue].value;
if (iconn.state != connectionstate.closed)
{
iconn.close();
}
return result;
}
}
}
/**/
///
/// 创建 sqlcommand 对象实例(用来返回一个整数值)
///
/// 存储过程名
/// 存储过程参数
/// sqlcommand 对象实例
private sqlcommand buildintcommand(idbconnection iconn, string storedprocname, idataparameter[] parameters)
{
sqlcommand sqlcmd = buildquerycommand(iconn, storedprocname, parameters);
sqlcmd.parameters.add(new sqlparameter(returnvalue,
sqldbtype.int, 4, parameterdirection.returnvalue,
false, 0, 0, string.empty, datarowversion.default, null));
return sqlcmd;
}
private idbcommand getcommand(string sql, idbconnection iconn)
{
switch (this.dbtype)
{
case sqlserver:
return new system.data.sqlclient.sqlcommand(sql, (sqlconnection)iconn);
case oracle:
return new system.data.oracleclient.oraclecommand(sql, (oracleconnection)iconn);
case access:
return new system.data.oledb.oledbcommand(sql, (oledbconnection)iconn);
default:
return new system.data.sqlclient.sqlcommand(sql, (sqlconnection)iconn);
}
}
#endregion
}
}