Oracle,SQL Server,Access万能数据库通用类！

view source

print?

0001	using System;

0002	using System.Collections;

0003	using System.Collections.Specialized;

0004	using System.Data;

0005	using System.Data.SqlClient;

0006	using System.Data.OleDb;

0007	using System.Data.OracleClient;

0008	using System.Configuration;

0009	using System.Reflection;

0010

0011	namespace SystemFramework.DAL

0012

{

0013	/**//// <summary>

0014	/// All rights reserved

0015	/// 数据访问基础类

0016	/// 用户可以修改满足自己项目的需要。

0017	/// </summary>

0018	public class DataBaseLayer

0019

{

0020	//数据库连接字符串(web.config来配置)

0021	//<add key="ConnectionString" value="server=127.0.0.1;database=DATABASE;uid=sa;pwd=" />

0022	private string connectionString;

0023	public string ConntionString

0024

{

0025

get

0026

{

0027	return connectionString ;

0028

}

0029

set

0030

{

0031	connectionString = value;

0032

}

0033

}

0034

0035

0036	public DataBaseLayer(string strConnect,string dataType)

0037

{

0038	this.ConntionString = strConnect;

0039	this.DbType = dataType;

0040

}

0041

0042

0043	public DataBaseLayer()

0044

{

0045	this.connectionString = ConfigurationSettings.AppSettings["ConnectionString"] ;

0046	this.dbType = ConfigurationSettings.AppSettings["DataType"] ;

0047

}

0048

0049	/**//// <summary>

0050

/// 数据库类型

0051	/// </summary>

0052	private string dbType;

0053	public string DbType

0054

{

0055

get

0056

{

0057	if ( dbType == string.Empty || dbType == null )

0058

{

0059	return "Access";

0060

}

0061

else

0062

{

0063	return dbType;

0064

}

0065

}

0066

set

0067

{

0068	if ( value != string.Empty  &&  value != null )

0069

{

0070	dbType = value;

0071

}

0072	if (dbType ==string.Empty || dbType == null)

0073

{

0074	dbType = ConfigurationSettings.AppSettings["DataType"];

0075

}

0076	if ( dbType == string.Empty || dbType == null )

0077

{

0078	dbType = "Access";

0079

}

0080

}

0081

}

0082

0083

0084

0085

0086	转换参数#region 转换参数

0087	private System.Data.IDbDataParameter iDbPara(string ParaName,string DataType)

0088

{

0089	switch(this.DbType)

0090

{

0091	case "SqlServer":

0092	return GetSqlPara(ParaName,DataType);

0093

0094	case "Oracle":

0095	return GetOleDbPara(ParaName,DataType);

0096

0097	case "Access":

0098	return GetOleDbPara(ParaName,DataType);

0099

0100

default :

0101	return GetSqlPara(ParaName,DataType);

0102

0103

}

0104

}

0105

0106	private System.Data.SqlClient.SqlParameter  GetSqlPara( string ParaName , string DataType)

0107

{

0108	switch(DataType)

0109

{

0110	case "Decimal":

0111	return  new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.Decimal );

0112	case "Varchar":

0113	return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.VarChar );

0114	case "DateTime":

0115	return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.DateTime );

0116	case "Iamge":

0117	return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.Image );

0118	case "Int":

0119	return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.Int );

0120	case "Text":

0121	return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.NText );

0122

default :

0123	return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.VarChar );

0124

}

0125

}

0126

0127	private System.Data.OracleClient.OracleParameter  GetOraclePara( string ParaName , string DataType)

0128

{

0129	switch(DataType)

0130

{

0131	case "Decimal":

0132	return  new System.Data.OracleClient.OracleParameter( ParaName, System.Data.OracleClient.OracleType.Double);

0133

0134	case "Varchar":

0135	return  new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.VarChar );

0136

0137	case "DateTime":

0138	return  new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.DateTime );

0139

0140	case "Iamge":

0141	return  new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.BFile );

0142

0143	case "Int":

0144	return  new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.Int32 );

0145

0146	case "Text":

0147	return  new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.LongVarChar );

0148

0149

default:

0150	return  new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.VarChar );

0151

0152

}

0153

}

0154

0155	private System.Data.OleDb.OleDbParameter  GetOleDbPara( string ParaName , string DataType)

0156

{

0157	switch(DataType)

0158

{

0159	case "Decimal":

0160	return  new System.Data.OleDb.OleDbParameter( ParaName, System.Data.DbType.Decimal);

0161

0162	case "Varchar":

0163	return  new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.String );

0164

0165	case "DateTime":

0166	return  new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.DateTime );

0167

0168	case "Iamge":

0169	return   new System.Data.OleDb.OleDbParameter( ParaName, System.Data.DbType.Binary );

0170

0171	case "Int":

0172	return  new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.Int32 );

0173

0174	case "Text":

0175	return  new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.String );

0176

0177

default:

0178	return  new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.String );

0179

0180

}

0181

}

0182

0183	#endregion

0184

0185	创建 Connection 和 Command#region 创建 Connection 和 Command

0186

0187	private IDbConnection GetConnection()

0188

{

0189	switch(this.DbType)

0190

{

0191	case "SqlServer":

0192	return new System.Data.SqlClient.SqlConnection(this.ConntionString);

0193

0194	case "Oracle":

0195	return new System.Data.OracleClient.OracleConnection(this.ConntionString);

0196

0197	case "Access":

0198	return new System.Data.OleDb.OleDbConnection(this.ConntionString);

0199

default:

0200	return new System.Data.SqlClient.SqlConnection(this.ConntionString);

0201

}

0202

}

0203

0204

0205	private IDbCommand GetCommand(string Sql,IDbConnection iConn)

0206

{

0207	switch(this.DbType)

0208

{

0209	case "SqlServer":

0210	return new System.Data.SqlClient.SqlCommand(Sql,(SqlConnection)iConn);

0211

0212	case "Oracle":

0213	return new System.Data.OracleClient.OracleCommand(Sql,(OracleConnection)iConn);

0214

0215	case "Access":

0216	return new System.Data.OleDb.OleDbCommand(Sql,(OleDbConnection)iConn);

0217

default:

0218	return new System.Data.SqlClient.SqlCommand(Sql,(SqlConnection)iConn);

0219

}

0220

}

0221

0222	private IDbCommand GetCommand()

0223

{

0224	switch(this.DbType)

0225

{

0226	case "SqlServer":

0227	return new System.Data.SqlClient.SqlCommand();

0228

0229	case "Oracle":

0230	return new System.Data.OracleClient.OracleCommand();

0231

0232	case "Access":

0233	return new System.Data.OleDb.OleDbCommand();

0234

default:

0235	return new System.Data.SqlClient.SqlCommand();

0236

}

0237

}

0238

0239	private IDataAdapter GetAdapater(string Sql,IDbConnection iConn)

0240

{

0241	switch(this.DbType)

0242

{

0243	case "SqlServer":

0244	return new System.Data.SqlClient.SqlDataAdapter(Sql,(SqlConnection)iConn);

0245

0246	case "Oracle":

0247	return new System.Data.OracleClient.OracleDataAdapter(Sql,(OracleConnection)iConn);

0248

0249	case "Access":

0250	return new System.Data.OleDb.OleDbDataAdapter(Sql,(OleDbConnection)iConn);

0251

0252

default:

0253	return new System.Data.SqlClient.SqlDataAdapter(Sql,(SqlConnection)iConn);;

0254

}

0255

0256

}

0257

0258	private IDataAdapter GetAdapater()

0259

{

0260	switch(this.DbType)

0261

{

0262	case "SqlServer":

0263	return new System.Data.SqlClient.SqlDataAdapter();

0264

0265	case "Oracle":

0266	return new System.Data.OracleClient.OracleDataAdapter();

0267

0268	case "Access":

0269	return new System.Data.OleDb.OleDbDataAdapter();

0270

0271

default:

0272	return new System.Data.SqlClient.SqlDataAdapter();

0273

}

0274

}

0275

0276	private IDataAdapter GetAdapater(IDbCommand iCmd)

0277

{

0278	switch(this.DbType)

0279

{

0280	case "SqlServer":

0281	return new System.Data.SqlClient.SqlDataAdapter((SqlCommand)iCmd);

0282

0283	case "Oracle":

0284	return new System.Data.OracleClient.OracleDataAdapter((OracleCommand)iCmd);

0285

0286	case "Access":

0287	return new System.Data.OleDb.OleDbDataAdapter((OleDbCommand)iCmd);

0288

0289

default:

0290	return new System.Data.SqlClient.SqlDataAdapter((SqlCommand)iCmd);

0291

}

0292

}

0293	#endregion

0294

0295	执行简单SQL语句#region  执行简单SQL语句

0296	/**//// <summary>

0297	/// 执行SQL语句，返回影响的记录数

0298	/// </summary>

0299	/// <param name="SQLString">SQL语句</param>

0300	/// <returns>影响的记录数</returns>

0301	public int ExecuteSql(string SqlString)

0302

{

0303	using (System.Data.IDbConnection  iConn = this.GetConnection())

0304

{

0305	using (System.Data.IDbCommand iCmd  = GetCommand(SqlString,iConn))

0306

{

0307	iConn.Open();

0308

try

0309

{

0310

0311	int rows=iCmd.ExecuteNonQuery();

0312	return rows;

0313

}

0314	catch(System.Exception E)

0315

{

0316	throw new Exception(E.Message);

0317

}

0318

finally

0319

{

0320	if(iConn.State != ConnectionState.Closed)

0321

{

0322	iConn.Close();

0323

}

0324

}

0325

}

0326

}

0327

}

0328

0329	/**//// <summary>

0330	/// 执行多条SQL语句，实现数据库事务。

0331	/// </summary>

0332	/// <param name="SQLStringList">多条SQL语句</param>

0333	public  void ExecuteSqlTran(ArrayList SQLStringList)

0334

{

0335	using (System.Data.IDbConnection  iConn = this.GetConnection())

0336

{

0337	iConn.Open();

0338	using(System.Data.IDbCommand iCmd  =  GetCommand())

0339

{

0340	iCmd.Connection=iConn;

0341	using(System.Data.IDbTransaction iDbTran = iConn.BeginTransaction())

0342

{

0343	iCmd.Transaction=iDbTran;

0344

try

0345

{

0346	for(int n=0;n<SQLStringList.Count;n++)

0347

{

0348	string strsql = SQLStringList[n].ToString();

0349	if ( strsql.Trim().Length>1)

0350

{

0351	iCmd.CommandText = strsql;

0352	iCmd.ExecuteNonQuery();

0353

}

0354

}

0355	iDbTran.Commit();

0356

}

0357	catch(System.Exception E)

0358

{

0359	iDbTran.Rollback();

0360	throw new Exception(E.Message);

0361

}

0362

finally

0363

{

0364	if(iConn.State != ConnectionState.Closed)

0365

{

0366	iConn.Close();

0367

}

0368

}

0369

}

0370

0371

}

0372

0373

}

0374

}

0375	/**//// <summary>

0376	/// 执行带一个存储过程参数的的SQL语句。

0377	/// </summary>

0378	/// <param name="SQLString">SQL语句</param>

0379	/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>

0380	/// <returns>影响的记录数</returns>

0381	public int ExecuteSql(string SqlString,string content)

0382

{

0383	using (System.Data.IDbConnection  iConn = this.GetConnection())

0384

{

0385	using(System.Data.IDbCommand iCmd  = GetCommand(SqlString,iConn))

0386

{

0387	System.Data.IDataParameter  myParameter = this.iDbPara( "@content", "Text");

0388	myParameter.Value = content ;

0389	iCmd.Parameters.Add(myParameter);

0390	iConn.Open();

0391

try

0392

{

0393

0394	int rows = iCmd.ExecuteNonQuery();

0395	return rows;

0396

}

0397	catch( System.Exception e )

0398

{

0399	throw new Exception(e.Message);

0400

}

0401

finally

0402

{

0403	if(iConn.State != ConnectionState.Closed)

0404

{

0405	iConn.Close();

0406

}

0407

}

0408

}

0409

}

0410

}

0411

0412

0413	/**//// <summary>

0414	/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)

0415	/// </summary>

0416	/// <param name="strSQL">SQL语句</param>

0417	/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>

0418	/// <returns>影响的记录数</returns>

0419	public int ExecuteSqlInsertImg(string SqlString,byte[] fs)

0420

{

0421	using (System.Data.IDbConnection  iConn = this.GetConnection())

0422

{

0423	using(System.Data.IDbCommand iCmd  = GetCommand(SqlString,iConn))

0424

{

0425	System.Data.IDataParameter  myParameter = this.iDbPara( "@content", "Image");

0426	myParameter.Value = fs ;

0427	iCmd.Parameters.Add(myParameter);

0428	iConn.Open();

0429

try

0430

{

0431	int rows = iCmd.ExecuteNonQuery();

0432	return rows;

0433

}

0434	catch( System.Exception e )

0435

{

0436	throw new Exception(e.Message);

0437

}

0438

finally

0439

{

0440	if(iConn.State != ConnectionState.Closed)

0441

{

0442	iConn.Close();

0443

}

0444

}

0445

}

0446

}

0447

}

0448

0449	/**//// <summary>

0450	/// 执行一条计算查询结果语句，返回查询结果（object）。

0451	/// </summary>

0452	/// <param name="SQLString">计算查询结果语句</param>

0453	/// <returns>查询结果（object）</returns>

0454	public  object GetSingle(string SqlString)

0455

{

0456	using (System.Data.IDbConnection  iConn = GetConnection())

0457

{

0458	using (System.Data.IDbCommand iCmd  =  GetCommand(SqlString,iConn))

0459

{

0460	iConn.Open();

0461

try

0462

{

0463	object obj = iCmd.ExecuteScalar();

0464	if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))

0465

{

0466	return null;

0467

}

0468

else

0469

{

0470	return obj;

0471

}

0472

}

0473	catch(System.Exception e)

0474

{

0475	throw new Exception(e.Message);

0476

}

0477

finally

0478

{

0479	if(iConn.State != ConnectionState.Closed)

0480

{

0481	iConn.Close();

0482

}

0483

}

0484

}

0485

}

0486

}

0487	/**//// <summary>

0488	/// 执行查询语句，返回IDataAdapter

0489	/// </summary>

0490	/// <param name="strSQL">查询语句</param>

0491	/// <returns>IDataAdapter</returns>

0492	public IDataAdapter ExecuteReader(string strSQL)

0493

{

0494	using (System.Data.IDbConnection  iConn = this.GetConnection())

0495

{

0496	iConn.Open();

0497

try

0498

{

0499	System.Data.IDataAdapter iAdapter = this.GetAdapater(strSQL,iConn);

0500	return iAdapter;

0501

}

0502	catch(System.Exception e)

0503

{

0504	throw new Exception(e.Message);

0505

}

0506

finally

0507

{

0508	if(iConn.State != ConnectionState.Closed)

0509

{

0510	iConn.Close();

0511

}

0512

}

0513

}

0514

}

0515	/**//// <summary>

0516	/// 执行查询语句，返回DataSet

0517	/// </summary>

0518	/// <param name="SQLString">查询语句</param>

0519	/// <returns>DataSet</returns>

0520	public DataSet Query(string sqlString)

0521

{

0522	using (System.Data.IDbConnection  iConn = this.GetConnection())

0523

{

0524	using(System.Data.IDbCommand iCmd  =  GetCommand(sqlString,iConn))

0525

{

0526	DataSet ds = new DataSet();

0527	iConn.Open();

0528

try

0529

{

0530	System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString,iConn);

0531	iAdapter.Fill(ds);

0532	return ds;

0533

}

0534	catch(System.Exception ex)

0535

{

0536	throw new Exception(ex.Message);

0537

}

0538

finally

0539

{

0540	if(iConn.State != ConnectionState.Closed)

0541

{

0542	iConn.Close();

0543

}

0544

}

0545

}

0546

}

0547

}

0548

0549	/**//// <summary>

0550	/// 执行查询语句，返回DataSet

0551	/// </summary>

0552	/// <param name="sqlString">查询语句</param>

0553	/// <param name="dataSet">要填充的DataSet</param>

0554	/// <param name="tableName">要填充的表名</param>

0555	/// <returns>DataSet</returns>

0556	public DataSet Query(string sqlString,DataSet dataSet,string tableName)

0557

{

0558	using (System.Data.IDbConnection  iConn = this.GetConnection())

0559

{

0560	using(System.Data.IDbCommand iCmd  =  GetCommand(sqlString,iConn))

0561

{

0562	iConn.Open();

0563

try

0564

{

0565	System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString,iConn);

0566	((OleDbDataAdapter)iAdapter).Fill(dataSet,tableName);

0567	return dataSet;

0568

}

0569	catch(System.Exception ex)

0570

{

0571	throw new Exception(ex.Message);

0572

}

0573

finally

0574

{

0575	if(iConn.State != ConnectionState.Closed)

0576

{

0577	iConn.Close();

0578

}

0579

}

0580

}

0581

}

0582

}

0583

0584

0585	/**//// <summary>

0586	/// 执行SQL语句 返回存储过程

0587	/// </summary>

0588	/// <param name="sqlString">Sql语句</param>

0589	/// <param name="dataSet">要填充的DataSet</param>

0590	/// <param name="startIndex">开始记录</param>

0591	/// <param name="pageSize">页面记录大小</param>

0592	/// <param name="tableName">表名称</param>

0593	/// <returns>DataSet</returns>

0594	public DataSet Query(string sqlString , DataSet dataSet ,int  startIndex ,int pageSize, string tableName )

0595

{

0596	using (System.Data.IDbConnection iConn = this.GetConnection())

0597

{

0598	iConn.Open();

0599

try

0600

{

0601	System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString,iConn);

0602

0603	((OleDbDataAdapter)iAdapter).Fill(dataSet,startIndex,pageSize,tableName);

0604

0605	return dataSet;

0606

}

0607	catch(Exception ex)

0608

{

0609	throw new Exception(ex.Message);

0610

}

0611

finally

0612

{

0613	if(iConn.State != ConnectionState.Closed)

0614

{

0615	iConn.Close();

0616

}

0617

}

0618

}

0619

}

0620

0621

0622	/**//// <summary>

0623	/// 执行查询语句，向XML文件写入数据

0624	/// </summary>

0625	/// <param name="sqlString">查询语句</param>

0626	/// <param name="xmlPath">XML文件路径</param>

0627	public void WriteToXml(string sqlString,string xmlPath)

0628

{

0629	Query(sqlString).WriteXml(xmlPath);

0630

}

0631

0632	/**//// <summary>

0633	/// 执行查询语句

0634	/// </summary>

0635	/// <param name="SqlString">查询语句</param>

0636	/// <returns>DataTable </returns>

0637	public DataTable ExecuteQuery(string sqlString)

0638

{

0639	using (System.Data.IDbConnection  iConn = this.GetConnection())

0640

{

0641	//System.Data.IDbCommand iCmd  =  GetCommand(sqlString,iConn);

0642	DataSet ds = new DataSet();

0643

try

0644

{

0645	System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString,iConn);

0646	iAdapter.Fill(ds);

0647

}

0648	catch(System.Exception e)

0649

{

0650	throw new Exception(e.Message);

0651

}

0652

finally

0653

{

0654	if(iConn.State != ConnectionState.Closed)

0655

{

0656	iConn.Close();

0657

}

0658

}

0659	return ds.Tables[0];

0660

}

0661

}

0662

0663	/**//// <summary>

0664	/// 执行查询语句

0665	/// </summary>

0666	/// <param name="SqlString">查询语句</param>

0667	/// <returns>DataTable </returns>

0668	public DataTable ExecuteQuery(string SqlString,string Proc)

0669

{

0670	using (System.Data.IDbConnection  iConn = this.GetConnection())

0671

{

0672	using(System.Data.IDbCommand iCmd  =  GetCommand(SqlString,iConn))

0673

{

0674	iCmd.CommandType = CommandType.StoredProcedure;

0675	DataSet ds = new DataSet();

0676

try

0677

{

0678	System.Data.IDataAdapter iDataAdapter = this.GetAdapater(SqlString,iConn);

0679	iDataAdapter.Fill(ds);

0680

}

0681	catch(System.Exception e)

0682

{

0683	throw new Exception(e.Message);

0684

}

0685

finally

0686

{

0687	if(iConn.State != ConnectionState.Closed)

0688

{

0689	iConn.Close();

0690

}

0691

}

0692	return ds.Tables[0];

0693

}

0694

0695

0696

}

0697

}

0698

0699	/**//// <summary>

0700

///

0701	/// </summary>

0702	/// <param name="Sql"></param>

0703	/// <returns></returns>

0704	public DataView ExeceuteDataView(string Sql)

0705

{

0706	using (System.Data.IDbConnection  iConn = this.GetConnection())

0707

{

0708	using(System.Data.IDbCommand iCmd  =  GetCommand(Sql,iConn))

0709

{

0710	DataSet ds = new DataSet();

0711

try

0712

{

0713	System.Data.IDataAdapter iDataAdapter = this.GetAdapater(Sql,iConn);

0714	iDataAdapter.Fill(ds);

0715	return ds.Tables[0].DefaultView;

0716

}

0717	catch(System.Exception e)

0718

{

0719	throw new Exception(e.Message);

0720

}

0721

finally

0722

{

0723	if(iConn.State != ConnectionState.Closed)

0724

{

0725	iConn.Close();

0726

}

0727

}

0728

}

0729

}

0730

}

0731

0732	#endregion

0733

0734	执行带参数的SQL语句#region 执行带参数的SQL语句

0735	/**//// <summary>

0736	/// 执行SQL语句，返回影响的记录数

0737	/// </summary>

0738	/// <param name="SQLString">SQL语句</param>

0739	/// <returns>影响的记录数</returns>

0740	public  int ExecuteSql(string SQLString,params IDataParameter[] iParms)

0741

{

0742	using (System.Data.IDbConnection  iConn = this.GetConnection())

0743

{

0744	System.Data.IDbCommand iCmd  =  GetCommand();

0745

{

0746

try

0747

{

0748	PrepareCommand(out iCmd, iConn, null , SQLString, iParms );

0749	int rows=iCmd.ExecuteNonQuery();

0750	iCmd.Parameters.Clear();

0751	return rows;

0752

}

0753	catch(System.Exception E)

0754

{

0755	throw new Exception( E.Message );

0756

}

0757

finally

0758

{

0759	iCmd.Dispose();

0760	if(iConn.State != ConnectionState.Closed)

0761

{

0762	iConn.Close();

0763

}

0764

}

0765

}

0766

}

0767

}

0768

0769

0770	/**//// <summary>

0771	/// 执行多条SQL语句，实现数据库事务。

0772	/// </summary>

0773	/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>

0774	public void ExecuteSqlTran(Hashtable SQLStringList)

0775

{

0776	using (System.Data.IDbConnection  iConn = this.GetConnection())

0777

{

0778	iConn.Open();

0779	using (IDbTransaction  iTrans = iConn.BeginTransaction())

0780

{

0781	System.Data.IDbCommand iCmd  =  GetCommand();

0782

try

0783

{

0784

//循环

0785	foreach ( DictionaryEntry myDE in SQLStringList)

0786

{

0787	string     cmdText = myDE.Key.ToString();

0788	IDataParameter[] iParms=( IDataParameter[] ) myDE.Value;

0789	PrepareCommand( out iCmd , iConn , iTrans , cmdText , iParms );

0790	int val = iCmd.ExecuteNonQuery();

0791	iCmd.Parameters.Clear();

0792

}

0793	iTrans.Commit();

0794

}

0795

catch

0796

{

0797	iTrans.Rollback();

0798

throw;

0799

}

0800

finally

0801

{

0802	iCmd.Dispose();

0803	if(iConn.State != ConnectionState.Closed)

0804

{

0805	iConn.Close();

0806

}

0807

}

0808

0809

}

0810

}

0811

}

0812

0813

0814	/**//// <summary>

0815	/// 执行一条计算查询结果语句，返回查询结果（object）。

0816	/// </summary>

0817	/// <param name="SQLString">计算查询结果语句</param>

0818	/// <returns>查询结果（object）</returns>

0819	public object GetSingle(string SQLString,params IDataParameter[] iParms)

0820

{

0821	using (System.Data.IDbConnection  iConn = this.GetConnection())

0822

{

0823	System.Data.IDbCommand iCmd  =  GetCommand();

0824

{

0825

try

0826

{

0827	PrepareCommand( out iCmd, iConn, null , SQLString, iParms );

0828	object obj = iCmd.ExecuteScalar();

0829	iCmd.Parameters.Clear();

0830	if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))

0831

{

0832	return null;

0833

}

0834

else

0835

{

0836	return obj;

0837

}

0838

}

0839	catch(System.Exception e)

0840

{

0841	throw new Exception(e.Message);

0842

}

0843

finally

0844

{

0845	iCmd.Dispose();

0846	if(iConn.State != ConnectionState.Closed)

0847

{

0848	iConn.Close();

0849

}

0850

}

0851

}

0852

}

0853

}

0854

0855	/**//// <summary>

0856	/// 执行查询语句，返回IDataReader

0857	/// </summary>

0858	/// <param name="strSQL">查询语句</param>

0859	/// <returns> IDataReader </returns>

0860	public IDataReader ExecuteReader(string SQLString,params IDataParameter[] iParms)

0861

{

0862	System.Data.IDbConnection  iConn = this.GetConnection();

0863

{

0864	System.Data.IDbCommand iCmd  =  GetCommand();

0865

{

0866

try

0867

{

0868	PrepareCommand(out iCmd, iConn , null , SQLString , iParms);

0869	System.Data.IDataReader iReader = iCmd.ExecuteReader();

0870	iCmd.Parameters.Clear();

0871	return iReader;

0872

}

0873	catch(System.Exception e)

0874

{

0875	throw new Exception(e.Message);

0876

}

0877

finally

0878

{

0879	iCmd.Dispose();

0880	if(iConn.State != ConnectionState.Closed)

0881

{

0882	iConn.Close();

0883

}

0884

}

0885

}

0886

}

0887

}

0888

0889	/**//// <summary>

0890	/// 执行查询语句，返回DataSet

0891	/// </summary>

0892	/// <param name="SQLString">查询语句</param>

0893	/// <returns>DataSet</returns>

0894	public DataSet Query(string sqlString,params IDataParameter[] iParms)

0895

{

0896	using (System.Data.IDbConnection  iConn = this.GetConnection())

0897

{

0898	IDbCommand iCmd = GetCommand();

0899

{

0900	PrepareCommand(out iCmd , iConn , null , sqlString , iParms );

0901

try

0902

{

0903	IDataAdapter iAdapter =  this.GetAdapater(sqlString,iConn);

0904	DataSet ds = new DataSet();

0905	iAdapter.Fill(ds);

0906	iCmd.Parameters.Clear();

0907	return ds;

0908

}

0909	catch(System.Exception ex)

0910

{

0911	throw new Exception(ex.Message);

0912

}

0913

finally

0914

{

0915	iCmd.Dispose();

0916	if(iConn.State != ConnectionState.Closed)

0917

{

0918	iConn.Close();

0919

}

0920

}

0921

}

0922

}

0923

}

0924

0925

0926	/**//// <summary>

0927	/// 初始化Command

0928	/// </summary>

0929	/// <param name="iCmd"></param>

0930	/// <param name="iConn"></param>

0931	/// <param name="iTrans"></param>

0932	/// <param name="cmdText"></param>

0933	/// <param name="iParms"></param>

0934	private void PrepareCommand(out IDbCommand iCmd,IDbConnection iConn,System.Data.IDbTransaction iTrans, string cmdText, IDataParameter[] iParms)

0935

{

0936	if (iConn.State != ConnectionState.Open)

0937	iConn.Open();

0938	iCmd = this.GetCommand();

0939	iCmd.Connection =  iConn;

0940	iCmd.CommandText = cmdText;

0941	if (iTrans != null)

0942	iCmd.Transaction = iTrans;

0943	iCmd.CommandType = CommandType.Text;//cmdType;

0944	if (iParms != null)

0945

{

0946	foreach (IDataParameter parm in iParms)

0947	iCmd.Parameters.Add(parm);

0948

}

0949

}

0950

0951	#endregion

0952

0953	存储过程操作#region 存储过程操作

0954

0955	/**//// <summary>

0956	/// 执行存储过程

0957	/// </summary>

0958	/// <param name="storedProcName">存储过程名</param>

0959	/// <param name="parameters">存储过程参数</param>

0960	/// <returns>SqlDataReader</returns>

0961	public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )

0962

{

0963	System.Data.IDbConnection  iConn = this.GetConnection();

0964

{

0965	iConn.Open();

0966

0967	using(SqlCommand sqlCmd = BuildQueryCommand(iConn,storedProcName, parameters))

0968

{

0969	return  sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);

0970

}

0971

}

0972

}

0973

0974	/**//// <summary>

0975	/// 执行存储过程

0976	/// </summary>

0977	/// <param name="storedProcName">存储过程名</param>

0978	/// <param name="parameters">存储过程参数</param>

0979	/// <param name="tableName">DataSet结果中的表名</param>

0980	/// <returns>DataSet</returns>

0981	public  DataSet RunProcedure(string storedProcName, IDataParameter[] parameters ,string tableName)

0982

{

0983

0984	using (System.Data.IDbConnection   iConn = this.GetConnection())

0985

{

0986	DataSet dataSet = new DataSet();

0987	iConn.Open();

0988	System.Data.IDataAdapter iDA = this.GetAdapater();

0989	iDA = this.GetAdapater( BuildQueryCommand(iConn, storedProcName, parameters ) );

0990

0991	((SqlDataAdapter)iDA).Fill( dataSet,tableName);

0992	if(iConn.State != ConnectionState.Closed)

0993

{

0994	iConn.Close();

0995

}

0996	return dataSet;

0997

}

0998

}

0999

1000

1001

1002	/**//// <summary>

1003	/// 执行存储过程

1004	/// </summary>

1005	/// <param name="storedProcName">存储过程名</param>

1006	/// <param name="parameters">存储过程参数</param>

1007	/// <param name="tableName">DataSet结果中的表名</param>

1008	/// <param name="startIndex">开始记录索引</param>

1009	/// <param name="pageSize">页面记录大小</param>

1010	/// <returns>DataSet</returns>

1011	public  DataSet RunProcedure(string storedProcName, IDataParameter[] parameters ,int startIndex,int pageSize,string tableName)

1012

{

1013

1014	using (System.Data.IDbConnection   iConn = this.GetConnection())

1015

{

1016	DataSet dataSet = new DataSet();

1017	iConn.Open();

1018	System.Data.IDataAdapter iDA = this.GetAdapater();

1019	iDA = this.GetAdapater( BuildQueryCommand(iConn, storedProcName, parameters ) );

1020

1021	((SqlDataAdapter)iDA).Fill( dataSet,startIndex,pageSize,tableName);

1022	if(iConn.State != ConnectionState.Closed)

1023

{

1024	iConn.Close();

1025

}

1026	return dataSet;

1027

}

1028

}

1029

1030	/**//// <summary>

1031	/// 执行存储过程 填充已经存在的DataSet数据集

1032	/// </summary>

1033	/// <param name="storeProcName">存储过程名称</param>

1034	/// <param name="parameters">存储过程参数</param>

1035	/// <param name="dataSet">要填充的数据集</param>

1036	/// <param name="tablename">要填充的表名</param>

1037	/// <returns></returns>

1038	public DataSet RunProcedure(string storeProcName,IDataParameter[] parameters,DataSet dataSet,string tableName)

1039

{

1040	using (System.Data.IDbConnection iConn = this.GetConnection())

1041

{

1042	iConn.Open();

1043	System.Data.IDataAdapter iDA = this.GetAdapater();

1044	iDA = this.GetAdapater(BuildQueryCommand(iConn,storeProcName,parameters));

1045

1046	((SqlDataAdapter)iDA).Fill(dataSet,tableName);

1047

1048	if(iConn.State != ConnectionState.Closed)

1049

{

1050	iConn.Close();

1051

}

1052

1053	return dataSet;

1054

}

1055

}

1056

1057	/**//// <summary>

1058	/// 执行存储过程并返回受影响的行数

1059	/// </summary>

1060	/// <param name="storedProcName"></param>

1061	/// <param name="parameters"></param>

1062	/// <returns></returns>

1063	public int RunProcedureNoQuery(string storedProcName, IDataParameter[] parameters )

1064

{

1065

1066	int result = 0;

1067	using (System.Data.IDbConnection  iConn = this.GetConnection())

1068

{

1069	iConn.Open();

1070	using(SqlCommand scmd = BuildQueryCommand(iConn,storedProcName,parameters))

1071

{

1072	result = scmd.ExecuteNonQuery();

1073

}

1074

1075	if(iConn.State != ConnectionState.Closed)

1076

{

1077	iConn.Close();

1078

}

1079

}

1080

1081	return result ;

1082

}

1083

1084	public string RunProcedureExecuteScalar(string storeProcName,IDataParameter[] parameters)

1085

{

1086	string result = string.Empty;

1087	using (System.Data.IDbConnection iConn = this.GetConnection())

1088

{

1089

1090	iConn.Open();

1091	using(SqlCommand scmd = BuildQueryCommand(iConn,storeProcName,parameters))

1092

{

1093	object obj = scmd.ExecuteScalar();

1094	if(obj == null)

1095	result = null;

1096

else

1097	result = obj.ToString();

1098

}

1099

1100	if(iConn.State != ConnectionState.Closed)

1101

{

1102	iConn.Close();

1103

}

1104

1105

}

1106

1107	return result;

1108

}

1109

1110	/**//// <summary>

1111	/// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)

1112	/// </summary>

1113	/// <param name="connection">数据库连接</param>

1114	/// <param name="storedProcName">存储过程名</param>

1115	/// <param name="parameters">存储过程参数</param>

1116	/// <returns>SqlCommand</returns>

1117	private SqlCommand BuildQueryCommand(IDbConnection iConn,string storedProcName, IDataParameter[] parameters)

1118

{

1119

1120	IDbCommand iCmd = GetCommand(storedProcName,iConn);

1121	iCmd.CommandType = CommandType.StoredProcedure;

1122	if (parameters == null)

1123

{

1124	return (SqlCommand)iCmd;

1125

}

1126	foreach (IDataParameter parameter in parameters)

1127

{

1128	iCmd.Parameters.Add( parameter );

1129

}

1130	return (SqlCommand)iCmd;

1131

}

1132

1133	/**//// <summary>

1134	/// 执行存储过程，返回影响的行数

1135	/// </summary>

1136	/// <param name="storedProcName">存储过程名</param>

1137	/// <param name="parameters">存储过程参数</param>

1138	/// <param name="rowsAffected">影响的行数</param>

1139	/// <returns></returns>

1140	public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )

1141

{

1142	using (System.Data.IDbConnection  iConn = this.GetConnection())

1143

{

1144	int result;

1145	iConn.Open();

1146	using(SqlCommand sqlCmd = BuildIntCommand(iConn,storedProcName, parameters ))

1147

{

1148	rowsAffected = sqlCmd.ExecuteNonQuery();

1149	result = (int)sqlCmd.Parameters["ReturnValue"].Value;

1150

1151	if(iConn.State != ConnectionState.Closed)

1152

{

1153	iConn.Close();

1154

}

1155	return result;

1156

}

1157

}

1158

}

1159

1160	/**//// <summary>

1161	/// 创建 SqlCommand 对象实例(用来返回一个整数值)

1162	/// </summary>

1163	/// <param name="storedProcName">存储过程名</param>

1164	/// <param name="parameters">存储过程参数</param>

1165	/// <returns>SqlCommand 对象实例</returns>

1166	private SqlCommand BuildIntCommand(IDbConnection iConn,string storedProcName, IDataParameter[] parameters)

1167

{

1168	SqlCommand sqlCmd = BuildQueryCommand(iConn,storedProcName, parameters );

1169	sqlCmd.Parameters.Add( new SqlParameter ( "ReturnValue",

1170	SqlDbType.Int,4,ParameterDirection.ReturnValue,

1171	false,0,0,string.Empty,DataRowVersion.Default,null ));

1172	return sqlCmd;

1173

}

1174	#endregion

1175

1176

1177

}

1178

}