DBHelper

using System;

using System.Collections;

using System.Collections.Specialized;

0006	using System.Data;

0007	using System.Data.SqlClient;

0008	using System.Configuration;

0009	using System.Data.Common;

0010	using System.Collections.Generic;

0011	namespace Legalsoft.Wizard.DBUtility

0012

{

0013	public enum EffentNextType

0014

{

0015	/// <summary>

0016	/// 对其他语句无任何影响

0017	/// </summary>

0018

None,

0019	/// <summary>

0020	/// 当前语句必须为"SELECT COUNT(1) FROM .."格式，如果存在则继续执行，不存在回滚事务

0021	/// </summary>

0022	WhenHaveContine,

0023	/// <summary>

0024	/// 当前语句必须为"SELECT COUNT(1) FROM .."格式，如果不存在则继续执行，存在回滚事务

0025	/// </summary>

0026	WhenNoHaveContine,

0027	/// <summary>

0028	/// 当前语句影响到的行数必须大于0，否则回滚事务

0029	/// </summary>

0030	ExcuteEffectRows,

0031	/// <summary>

0032	/// 引发事件-当前语句必须为"SELECT COUNT(1) FROM .."格式，如果不存在则继续执行，存在回滚事务

0033	/// </summary>

0034	SolicitationEvent

0035

}

0036	public class CommandInfo

0037

{

0038	public object ShareObject = null;

0039	public object OriginalData = null;

0040	event EventHandler _solicitationEvent;

0041	public event EventHandler SolicitationEvent

0042

{

0043

add

0044

{

0045	_solicitationEvent += value;

0046

}

0047

remove

0048

{

0049	_solicitationEvent -= value;

0050

}

0051

}

0052	public void OnSolicitationEvent()

0053

{

0054	if (_solicitationEvent != null)

0055

{

0056	_solicitationEvent(this,new EventArgs());

0057

}

0058

}

0059	public string CommandText;

0060	public System.Data.Common.DbParameter[] Parameters;

0061	public EffentNextType EffentNextType = EffentNextType.None;

0062	public CommandInfo()

0063

{

0064

}

0065	public CommandInfo(string sqlText, SqlParameter[] para)

0066

{

0067	this.CommandText = sqlText;

0068	this.Parameters = para;

0069

}

0070	public CommandInfo(string sqlText, SqlParameter[] para, EffentNextType type)

0071

{

0072	this.CommandText = sqlText;

0073	this.Parameters = para;

0074	this.EffentNextType = type;

0075

}

0076

}

0077	/// <summary>

0078	/// 数据访问抽象基础类

0079	/// </summary>

0080	public abstract class SQLHelper

0081

{

0082	//数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.

0083	public static string connectionString = "";

0084	public SQLHelper()

0085

{

0086	connectionString = @"DATA SOURCE=(local);UID=sa;PWD=111111;DATABASE=LegalSoft";

0087

}

0088	#region 公用方法

0089	/// <summary>

0090	/// 读取指定图片的二进制信息

0091	/// </summary>

0092	/// <param name="id"></param>

0093	/// <returns></returns>

0094	public object LoadImage(int id)

0095

{

0096	SqlConnection myConnection = new SqlConnection(connectionString);

0097	SqlCommand myCommand = new SqlCommand("SELECT [bImage] FROM [tblCAIPIAO_NEWS_IMG] WHERE <a href="mailto:id=@id" target="_blank">id=@id</a>", myConnection);

0098	myCommand.CommandType = CommandType.Text;

0099	myCommand.Parameters.Add(new SqlParameter("@id", id));

0100	myConnection.Open();

0101	object result = myCommand.ExecuteScalar();

0102

try

0103

{

0104	return new System.IO.MemoryStream((byte[])result);

0105

}

0106	catch (ArgumentNullException e)

0107

{

0108	return null;

0109

}

0110

finally

0111

{

0112	myConnection.Close();

0113

}

0114

}

0115	/// <summary>

0116	/// 判断是否存在某表的某个字段

0117	/// </summary>

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

0119	/// <param name="columnName">列名称</param>

0120	/// <returns>是否存在</returns>

0121	public static bool ColumnExists(string tableName, string columnName)

0122

{

0123	string sql = "select count(1) from syscolumns where [id]=object_id("" + tableName + "") and [name]="" + columnName + """;

0124	object res = GetSingle(sql);

0125	if (res == null)

0126

{

0127	return false;

0128

}

0129	return Convert.ToInt32(res) > 0;

0130

}

0131	public static int GetMaxID(string FieldName, string TableName)

0132

{

0133	string strsql = "select max(" + FieldName + ")+1 from " + TableName;

0134	object obj = SQLHelper.GetSingle(strsql);

0135	if (obj == null)

0136

{

0137

return 1;

0138

}

0139

else

0140

{

0141	return int.Parse(obj.ToString());

0142

}

0143

}

0144	public static bool Exists(string strSql)

0145

{

0146	object obj = SQLHelper.GetSingle(strSql);

0147	int cmdresult;

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

0149

{

0150	cmdresult = 0;

0151

}

0152

else

0153

{

0154	cmdresult = int.Parse(obj.ToString());

0155

}

0156	if (cmdresult == 0)

0157

{

0158	return false;

0159

}

0160

else

0161

{

0162	return true;

0163

}

0164

}

0165	/// <summary>

0166

/// 表是否存在

0167	/// </summary>

0168	/// <param name="TableName"></param>

0169	/// <returns></returns>

0170	public static bool TabExists(string TableName)

0171

{

0172	string strsql = "select count(*) from sysobjects where id = object_id(N"[" + TableName + "]") and OBJECTPROPERTY(id, N"IsUserTable") = 1";

0173	//string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N"[dbo].[" + TableName + "]") AND type in (N"U")";

0174	object obj = SQLHelper.GetSingle(strsql);

0175	int cmdresult;

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

0177

{

0178	cmdresult = 0;

0179

}

0180

else

0181

{

0182	cmdresult = int.Parse(obj.ToString());

0183

}

0184	if (cmdresult == 0)

0185

{

0186	return false;

0187

}

0188

else

0189

{

0190	return true;

0191

}

0192

}

0193	public static bool Exists(string strSql, params SqlParameter[] cmdParms)

0194

{

0195	object obj = SQLHelper.GetSingle(strSql, cmdParms);

0196	int cmdresult;

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

0198

{

0199	cmdresult = 0;

0200

}

0201

else

0202

{

0203	cmdresult = int.Parse(obj.ToString());

0204

}

0205	if (cmdresult == 0)

0206

{

0207	return false;

0208

}

0209

else

0210

{

0211	return true;

0212

}

0213

}

0214	#endregion

0215	#region 执行简单SQL语句

0216	/// <summary>

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

0218	/// </summary>

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

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

0221	public static int ExecuteSql(string SQLString)

0222

{

0223	using (SqlConnection connection = new SqlConnection(connectionString))

0224

{

0225	using (SqlCommand cmd = new SqlCommand(SQLString, connection))

0226

{

0227

try

0228

{

0229	connection.Open();

0230	int rows = cmd.ExecuteNonQuery();

0231	return rows;

0232

}

0233	catch (System.Data.SqlClient.SqlException e)

0234

{

0235	connection.Close();

0236

throw e;

0237

}

0238

}

0239

}

0240

}

0241	public static int ExecuteSqlByTime(string SQLString, int Times)

0242

{

0243	using (SqlConnection connection = new SqlConnection(connectionString))

0244

{

0245	using (SqlCommand cmd = new SqlCommand(SQLString, connection))

0246

{

0247

try

0248

{

0249	connection.Open();

0250	cmd.CommandTimeout = Times;

0251	int rows = cmd.ExecuteNonQuery();

0252	return rows;

0253

}

0254	catch (System.Data.SqlClient.SqlException e)

0255

{

0256	connection.Close();

0257

throw e;

0258

}

0259

}

0260

}

0261

}

0262	/// <summary>

0263	/// 执行Sql和Oracle滴混合事务

0264	/// </summary>

0265	/// <param name="list">SQL命令行列表</param>

0266	/// <param name="oracleCmdSqlList">Oracle命令行列表</param>

0267	/// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>

0268	public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList)

0269

{

0270	using (SqlConnection conn = new SqlConnection(connectionString))

0271

{

0272	conn.Open();

0273	SqlCommand cmd = new SqlCommand();

0274	cmd.Connection = conn;

0275	SqlTransaction tx = conn.BeginTransaction();

0276	cmd.Transaction = tx;

0277

try

0278

{

0279	foreach (CommandInfo myDE in list)

0280

{

0281	string cmdText = myDE.CommandText;

0282	SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;

0283	PrepareCommand(cmd, conn, tx, cmdText, cmdParms);

0284	if (myDE.EffentNextType == EffentNextType.SolicitationEvent)

0285

{

0286	if (myDE.CommandText.ToLower().IndexOf("count(") == -1)

0287

{

0288	tx.Rollback();

0289	throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式");

0290	//return 0;

0291

}

0292	object obj = cmd.ExecuteScalar();

0293	bool isHave = false;

0294	if (obj == null && obj == DBNull.Value)

0295

{

0296	isHave = false;

0297

}

0298	isHave = Convert.ToInt32(obj) > 0;

0299	if (isHave)

0300

{

0301

//引发事件

0302	myDE.OnSolicitationEvent();

0303

}

0304

}

0305	if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)

0306

{

0307	if (myDE.CommandText.ToLower().IndexOf("count(") == -1)

0308

{

0309	tx.Rollback();

0310	throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");

0311	//return 0;

0312

}

0313	object obj = cmd.ExecuteScalar();

0314	bool isHave = false;

0315	if (obj == null && obj == DBNull.Value)

0316

{

0317	isHave = false;

0318

}

0319	isHave = Convert.ToInt32(obj) > 0;

0320	if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)

0321

{

0322	tx.Rollback();

0323	throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");

0324	//return 0;

0325

}

0326	if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)

0327

{

0328	tx.Rollback();

0329	throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");

0330	//return 0;

0331

}

0332

continue;

0333

}

0334	int val = cmd.ExecuteNonQuery();

0335	if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)

0336

{

0337	tx.Rollback();

0338	throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");

0339	//return 0;

0340

}

0341	cmd.Parameters.Clear();

0342

}

0343	//string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");

0344	//bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);

0345	//if (!res)

0346

//{

0347	// tx.Rollback();

0348	// throw new Exception("Oracle执行失败");

0349	// return -1;

0350

//}

0351	tx.Commit();

0352

return 1;

0353

}

0354	catch (System.Data.SqlClient.SqlException e)

0355

{

0356	tx.Rollback();

0357

throw e;

0358

}

0359	catch (Exception e)

0360

{

0361	tx.Rollback();

0362

throw e;

0363

}

0364

}

0365

}

0366	/// <summary>

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

0368	/// </summary>

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

0370	public static int ExecuteSqlTran(List<String> SQLStringList)

0371

{

0372	using (SqlConnection conn = new SqlConnection(connectionString))

0373

{

0374	conn.Open();

0375	SqlCommand cmd = new SqlCommand();

0376	cmd.Connection = conn;

0377	SqlTransaction tx = conn.BeginTransaction();

0378	cmd.Transaction = tx;

0379

try

0380

{

0381	int count = 0;

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

0383

{

0384	string strsql = SQLStringList[n];

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

0386

{

0387	cmd.CommandText = strsql;

0388	count += cmd.ExecuteNonQuery();

0389

}

0390

}

0391	tx.Commit();

0392	return count;

0393

}

0394

catch

0395

{

0396	tx.Rollback();

0397

return 0;

0398

}

0399

}

0400

}

0401	/// <summary>

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

0403	/// </summary>

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

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

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

0407	public static int ExecuteSql(string SQLString, string content)

0408

{

0409	using (SqlConnection connection = new SqlConnection(connectionString))

0410

{

0411	SqlCommand cmd = new SqlCommand(SQLString, connection);

0412	System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);

0413	myParameter.Value = content;

0414	cmd.Parameters.Add(myParameter);

0415

try

0416

{

0417	connection.Open();

0418	int rows = cmd.ExecuteNonQuery();

0419	return rows;

0420

}

0421	catch (System.Data.SqlClient.SqlException e)

0422

{

0423

throw e;

0424

}

0425

finally

0426

{

0427	cmd.Dispose();

0428	connection.Close();

0429

}

0430

}

0431

}

0432	/// <summary>

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

0434	/// </summary>

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

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

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

0438	public static object ExecuteSqlGet(string SQLString, string content)

0439

{

0440	using (SqlConnection connection = new SqlConnection(connectionString))

0441

{

0442	SqlCommand cmd = new SqlCommand(SQLString, connection);

0443	System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);

0444	myParameter.Value = content;

0445	cmd.Parameters.Add(myParameter);

0446

try

0447

{

0448	connection.Open();

0449	object obj = cmd.ExecuteScalar();

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

0451

{

0452	return null;

0453

}

0454

else

0455

{

0456	return obj;

0457

}

0458

}

0459	catch (System.Data.SqlClient.SqlException e)

0460

{

0461

throw e;

0462

}

0463

finally

0464

{

0465	cmd.Dispose();

0466	connection.Close();

0467

}

0468

}

0469

}

0470	/// <summary>

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

0472	/// </summary>

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

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

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

0476	public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)

0477

{

0478	using (SqlConnection connection = new SqlConnection(connectionString))

0479

{

0480	SqlCommand cmd = new SqlCommand(strSQL, connection);

0481	System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);

0482	myParameter.Value = fs;

0483	cmd.Parameters.Add(myParameter);

0484

try

0485

{

0486	connection.Open();

0487	int rows = cmd.ExecuteNonQuery();

0488	return rows;

0489

}

0490	catch (System.Data.SqlClient.SqlException e)

0491

{

0492

throw e;

0493

}

0494

finally

0495

{

0496	cmd.Dispose();

0497	connection.Close();

0498

}

0499

}

0500

}

0501	/// <summary>

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

0503	/// </summary>

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

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

0506	public static object GetSingle(string SQLString)

0507

{

0508	using (SqlConnection connection = new SqlConnection(connectionString))

0509

{

0510	using (SqlCommand cmd = new SqlCommand(SQLString, connection))

0511

{

0512

try

0513

{

0514	connection.Open();

0515	object obj = cmd.ExecuteScalar();

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

0517

{

0518	return null;

0519

}

0520

else

0521

{

0522	return obj;

0523

}

0524

}

0525	catch (System.Data.SqlClient.SqlException e)

0526

{

0527	connection.Close();

0528

throw e;

0529

}

0530

}

0531

}

0532

}

0533	public static object GetSingle(string SQLString, int Times)

0534

{

0535	using (SqlConnection connection = new SqlConnection(connectionString))

0536

{

0537	using (SqlCommand cmd = new SqlCommand(SQLString, connection))

0538

{

0539

try

0540

{

0541	connection.Open();

0542	cmd.CommandTimeout = Times;

0543	object obj = cmd.ExecuteScalar();

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

0545

{

0546	return null;

0547

}

0548

else

0549

{

0550	return obj;

0551

}

0552

}

0553	catch (System.Data.SqlClient.SqlException e)

0554

{

0555	connection.Close();

0556

throw e;

0557

}

0558

}

0559

}

0560

}

0561	/// <summary>

0562	/// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )

0563	/// </summary>

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

0565	/// <returns>SqlDataReader</returns>

0566	public static SqlDataReader ExecuteReader(string strSQL)

0567

{

0568	SqlConnection connection = new SqlConnection(connectionString);

0569	SqlCommand cmd = new SqlCommand(strSQL, connection);

0570

try

0571

{

0572	connection.Open();

0573	SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

0574	return myReader;

0575

}

0576	catch (System.Data.SqlClient.SqlException e)

0577

{

0578

throw e;

0579

}

0580

}

0581	/// <summary>

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

0583	/// </summary>

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

0585	/// <returns>DataSet</returns>

0586	public static DataSet Query(string SQLString)

0587

{

0588	using (SqlConnection connection = new SqlConnection(connectionString))

0589

{

0590	DataSet ds = new DataSet();

0591

try

0592

{

0593	connection.Open();

0594	SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);

0595	command.Fill(ds, "ds");

0596

}

0597	catch (System.Data.SqlClient.SqlException ex)

0598

{

0599	throw new Exception(ex.Message);

0600

}

0601	return ds;

0602

}

0603

}

0604	/// <summary>

0605	/// 查询并得到数据集DataSet

0606	/// </summary>

0607	/// <param name="SQLString"></param>

0608	/// <param name="Times"></param>

0609	/// <returns></returns>

0610	public static DataSet Query(string SQLString, int Times)

0611

{

0612	using (SqlConnection connection = new SqlConnection(connectionString))

0613

{

0614	DataSet ds = new DataSet();

0615

try

0616

{

0617	connection.Open();

0618	SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);

0619	command.SelectCommand.CommandTimeout = Times;

0620	command.Fill(ds, "ds");

0621

}

0622	catch (System.Data.SqlClient.SqlException ex)

0623

{

0624	throw new Exception(ex.Message);

0625

}

0626	return ds;

0627

}

0628

}

0629	#endregion

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

0631	/// <summary>

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

0633	/// </summary>

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

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

0636	public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)

0637

{

0638	using (SqlConnection connection = new SqlConnection(connectionString))

0639

{

0640	using (SqlCommand cmd = new SqlCommand())

0641

{

0642

try

0643

{

0644	PrepareCommand(cmd, connection, null, SQLString, cmdParms);

0645	int rows = cmd.ExecuteNonQuery();

0646	cmd.Parameters.Clear();

0647	return rows;

0648

}

0649	catch (System.Data.SqlClient.SqlException e)

0650

{

0651

throw e;

0652

}

0653

}

0654

}

0655

}

0656	/// <summary>

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

0658	/// </summary>

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

0660	public static void ExecuteSqlTran(Hashtable SQLStringList)

0661

{

0662	using (SqlConnection conn = new SqlConnection(connectionString))

0663

{

0664	conn.Open();

0665	using (SqlTransaction trans = conn.BeginTransaction())

0666

{

0667	SqlCommand cmd = new SqlCommand();

0668

try

0669

{

0670

//循环

0671	foreach (DictionaryEntry myDE in SQLStringList)

0672

{

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

0674	SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;

0675	PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

0676	int val = cmd.ExecuteNonQuery();

0677	cmd.Parameters.Clear();

0678

}

0679	trans.Commit();

0680

}

0681

catch

0682

{

0683	trans.Rollback();

0684

throw;

0685

}

0686

}

0687

}

0688

}

0689	/// <summary>

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

0691	/// </summary>

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

0693	public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)

0694

{

0695	using (SqlConnection conn = new SqlConnection(connectionString))

0696

{

0697	conn.Open();

0698	using (SqlTransaction trans = conn.BeginTransaction())

0699

{

0700	SqlCommand cmd = new SqlCommand();

0701

try

0702

{

0703	int count = 0;

0704

//循环

0705	foreach (CommandInfo myDE in cmdList)

0706

{

0707	string cmdText = myDE.CommandText;

0708	SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;

0709	PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

0710	if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)

0711

{

0712	if (myDE.CommandText.ToLower().IndexOf("count(") == -1)

0713

{

0714	trans.Rollback();

0715

return 0;

0716

}

0717	object obj = cmd.ExecuteScalar();

0718	bool isHave = false;

0719	if (obj == null && obj == DBNull.Value)

0720

{

0721	isHave = false;

0722

}

0723	isHave = Convert.ToInt32(obj) > 0;

0724	if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)

0725

{

0726	trans.Rollback();

0727

return 0;

0728

}

0729	if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)

0730

{

0731	trans.Rollback();

0732

return 0;

0733

}

0734

continue;

0735

}

0736	int val = cmd.ExecuteNonQuery();

0737	count += val;

0738	if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)

0739

{

0740	trans.Rollback();

0741

return 0;

0742

}

0743	cmd.Parameters.Clear();

0744

}

0745	trans.Commit();

0746	return count;

0747

}

0748

catch

0749

{

0750	trans.Rollback();

0751

throw;

0752

}

0753

}

0754

}

0755

}

0756	/// <summary>

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

0758	/// </summary>

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

0760	public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList)

0761

{

0762	using (SqlConnection conn = new SqlConnection(connectionString))

0763

{

0764	conn.Open();

0765	using (SqlTransaction trans = conn.BeginTransaction())

0766

{

0767	SqlCommand cmd = new SqlCommand();

0768

try

0769

{

0770	int indentity = 0;

0771

//循环

0772	foreach (CommandInfo myDE in SQLStringList)

0773

{

0774	string cmdText = myDE.CommandText;

0775	SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;

0776	foreach (SqlParameter q in cmdParms)

0777

{

0778	if (q.Direction == ParameterDirection.InputOutput)

0779

{

0780	q.Value = indentity;

0781

}

0782

}

0783	PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

0784	int val = cmd.ExecuteNonQuery();

0785	foreach (SqlParameter q in cmdParms)

0786

{

0787	if (q.Direction == ParameterDirection.Output)

0788

{

0789	indentity = Convert.ToInt32(q.Value);

0790

}

0791

}

0792	cmd.Parameters.Clear();

0793

}

0794	trans.Commit();

0795

}

0796

catch

0797

{

0798	trans.Rollback();

0799

throw;

0800

}

0801

}

0802

}

0803

}

0804	/// <summary>

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

0806	/// </summary>

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

0808	public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)

0809

{

0810	using (SqlConnection conn = new SqlConnection(connectionString))

0811

{

0812	conn.Open();

0813	using (SqlTransaction trans = conn.BeginTransaction())

0814

{

0815	SqlCommand cmd = new SqlCommand();

0816

try

0817

{

0818	int indentity = 0;

0819

//循环

0820	foreach (DictionaryEntry myDE in SQLStringList)

0821

{

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

0823	SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;

0824	foreach (SqlParameter q in cmdParms)

0825

{

0826	if (q.Direction == ParameterDirection.InputOutput)

0827

{

0828	q.Value = indentity;

0829

}

0830

}

0831	PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

0832	int val = cmd.ExecuteNonQuery();

0833	foreach (SqlParameter q in cmdParms)

0834

{

0835	if (q.Direction == ParameterDirection.Output)

0836

{

0837	indentity = Convert.ToInt32(q.Value);

0838

}

0839

}

0840	cmd.Parameters.Clear();

0841

}

0842	trans.Commit();

0843

}

0844

catch

0845

{

0846	trans.Rollback();

0847

throw;

0848

}

0849

}

0850

}

0851

}

0852	/// <summary>

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

0854	/// </summary>

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

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

0857	public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)

0858

{

0859	using (SqlConnection connection = new SqlConnection(connectionString))

0860

{

0861	using (SqlCommand cmd = new SqlCommand())

0862

{

0863

try

0864

{

0865	PrepareCommand(cmd, connection, null, SQLString, cmdParms);

0866	object obj = cmd.ExecuteScalar();

0867	cmd.Parameters.Clear();

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

0869

{

0870	return null;

0871

}

0872

else

0873

{

0874	return obj;

0875

}

0876

}

0877	catch (System.Data.SqlClient.SqlException e)

0878

{

0879

throw e;

0880

}

0881

}

0882

}

0883

}

0884	/// <summary>

0885	/// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )

0886	/// </summary>

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

0888	/// <returns>SqlDataReader</returns>

0889	public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)

0890

{

0891	SqlConnection connection = new SqlConnection(connectionString);

0892	SqlCommand cmd = new SqlCommand();

0893

try

0894

{

0895	PrepareCommand(cmd, connection, null, SQLString, cmdParms);

0896	SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

0897	cmd.Parameters.Clear();

0898	return myReader;

0899

}

0900	catch (System.Data.SqlClient.SqlException e)

0901

{

0902

throw e;

0903

}

0904

//finally

0905

//{

0906	//cmd.Dispose();

0907	//connection.Close();

0908

//}

0909

}

0910	/// <summary>

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

0912	/// </summary>

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

0914	/// <returns>DataSet</returns>

0915	public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)

0916

{

0917	using (SqlConnection connection = new SqlConnection(connectionString))

0918

{

0919	SqlCommand cmd = new SqlCommand();

0920	PrepareCommand(cmd, connection, null, SQLString, cmdParms);

0921	using (SqlDataAdapter da = new SqlDataAdapter(cmd))

0922

{

0923	DataSet ds = new DataSet();

0924

try

0925

{

0926	da.Fill(ds, "ds");

0927	cmd.Parameters.Clear();

0928

}

0929	catch (System.Data.SqlClient.SqlException ex)

0930

{

0931	throw new Exception(ex.Message);

0932

}

0933	return ds;

0934

}

0935

}

0936

}

0937	private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)

0938

{

0939	if (conn.State != ConnectionState.Open)

0940	conn.Open();

0941	cmd.Connection = conn;

0942	cmd.CommandText = cmdText;

0943	if (trans != null)

0944	cmd.Transaction = trans;

0945	cmd.CommandType = CommandType.Text;//cmdType;

0946	if (cmdParms != null)

0947

{

0948	foreach (SqlParameter parameter in cmdParms)

0949

{

0950	if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&

0951	(parameter.Value == null))

0952

{

0953	parameter.Value = DBNull.Value;

0954

}

0955	cmd.Parameters.Add(parameter);

0956

}

0957

}

0958

}

0959	#endregion

0960	#region 存储过程操作

0961	/// <summary>

0962	/// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )

0963	/// </summary>

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

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

0966	/// <returns>SqlDataReader</returns>

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

0968

{

0969	SqlConnection connection = new SqlConnection(connectionString);

0970	SqlDataReader returnReader;

0971	connection.Open();

0972	SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);

0973	command.CommandType = CommandType.StoredProcedure;

0974	returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);

0975	return returnReader;

0976

}

0977	/// <summary>

0978	/// 执行存储过程

0979	/// </summary>

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

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

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

0983	/// <returns>DataSet</returns>

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

0985

{

0986	using (SqlConnection connection = new SqlConnection(connectionString))

0987

{

0988	DataSet dataSet = new DataSet();

0989	connection.Open();

0990	SqlDataAdapter sqlDA = new SqlDataAdapter();

0991	sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);

0992	sqlDA.Fill(dataSet, tableName);

0993	connection.Close();

0994	return dataSet;

0995

}

0996

}

0997	public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)

0998

{

0999	using (SqlConnection connection = new SqlConnection(connectionString))

1000

{

1001	DataSet dataSet = new DataSet();

1002	connection.Open();

1003	SqlDataAdapter sqlDA = new SqlDataAdapter();

1004	sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);

1005	sqlDA.SelectCommand.CommandTimeout = Times;

1006	sqlDA.Fill(dataSet, tableName);

1007	connection.Close();

1008	return dataSet;

1009

}

1010

}

1011	/// <summary>

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

1013	/// </summary>

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

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

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

1017	/// <returns>SqlCommand</returns>

1018	private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)

1019

{

1020	SqlCommand command = new SqlCommand(storedProcName, connection);

1021	command.CommandType = CommandType.StoredProcedure;

1022	foreach (SqlParameter parameter in parameters)

1023

{

1024	if (parameter != null)

1025

{

1026	// 检查未分配值的输出参数,将其分配以DBNull.Value.

1027	if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&

1028	(parameter.Value == null))

1029

{

1030	parameter.Value = DBNull.Value;

1031

}

1032	command.Parameters.Add(parameter);

1033

}

1034

}

1035	return command;

1036

}

1037	/// <summary>

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

1039	/// </summary>

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

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

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

1043	/// <returns></returns>

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

1045

{

1046	using (SqlConnection connection = new SqlConnection(connectionString))

1047

{

1048	int result;

1049	connection.Open();

1050	SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);

1051	rowsAffected = command.ExecuteNonQuery();

1052	result = (int)command.Parameters["ReturnValue"].Value;

1053	//Connection.Close();

1054	return result;

1055

}

1056

}

1057	/// <summary>

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

1059	/// </summary>

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

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

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

1063	private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)

1064

{

1065	SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);

1066	command.Parameters.Add(new SqlParameter("ReturnValue",

1067	SqlDbType.Int, 4, ParameterDirection.ReturnValue,

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

1069	return command;

1070

}

1071	#endregion

1072

}

1073