database.cs

工作流引擎

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace WorkFlow.util {
	public abstract class Database {

		public static int ExecuteNonQuery(SqlConnection  conn,string sql, params SqlParameter[] parms) 
		{
			using(SqlCommand cmd = new SqlCommand())
			{
				try
				{
					PrepareCommand(cmd,conn,null,CommandType.Text,sql,parms);
					int val = cmd.ExecuteNonQuery();
					cmd.Parameters.Clear();
					return val;
				}
				catch
				{
					throw;
				}
			}
		}
		
		public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
		{
			using(SqlCommand cmd = new SqlCommand())
			{
				try
				{
					PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
					int val = cmd.ExecuteNonQuery();
					cmd.Parameters.Clear();
					return val;
				}
				catch
				{
					throw;
				}
			}
		}

		public static bool ExecuteNonQuery(SqlConnection  conn, string[] sqls,int[] rtnNumbers) 
		{
			using(SqlCommand cmd =  conn.CreateCommand())
			{
				using(SqlTransaction trans = conn.BeginTransaction())
				{
					cmd.Transaction = trans;
					bool onError = false;
					for(int i=0;i<sqls.Length;i++)
					{
						cmd.CommandText = sqls[i];
						try
						{
							int result = cmd.ExecuteNonQuery();
							if(result<rtnNumbers[i])
							{
								onError = true;
							}
						}
						catch(Exception)
						{
							onError = true;
						}
					}
					if(onError)
					{
						trans.Rollback();
						return false;
					}
					else
					{
						trans.Commit();
						return true;
					}
				}

			}
		}
	

		public static bool ExecuteNonQuery(SqlConnection  conn, string[] sqls) 
		{
			using(SqlCommand cmd =  conn.CreateCommand())
			{
				using(SqlTransaction trans = conn.BeginTransaction())
				{
					cmd.Transaction = trans;
					bool onError = false;
					for(int i=0;i<sqls.Length;i++)
					{
						cmd.CommandText = sqls[i];
						try
						{
							int result = cmd.ExecuteNonQuery();
							if(result<=0)
							{
								onError = true;
							}
						}
						catch(Exception)
						{
							onError = true;
						}
					}
					if(onError)
					{
						trans.Rollback();
						return false;
					}
					else
					{
						trans.Commit();
						return true;
					}
				}

			}
		}
	

		public static bool ExecuteNonQuery(SqlConnection  conn, ArrayList list) 
		{
			string[] sqls = new string[list.Count];
			for(int i=0;i<list.Count;i++)
			{
				sqls[i] = list[i].ToString();
			}

			using(SqlCommand cmd =  conn.CreateCommand())
			{
				using(SqlTransaction trans = conn.BeginTransaction())
				{
					cmd.Transaction = trans;
					bool onError = false;
					for(int i=0;i<sqls.Length;i++)
					{
						cmd.CommandText = sqls[i];
						try
						{
							int result = cmd.ExecuteNonQuery();
							if(result<=0)
							{
								onError = true;
							}
						}
						catch(Exception se)
						{
							onError = true;
							Console.WriteLine(se.ToString());
						}
					}
					if(onError)
					{
						trans.Rollback();
						return false;
					}
					else
					{
						trans.Commit();
						return true;
					}
				}

			}
		}


		public static int ExecuteNonQuery(SqlConnection  conn, string sql) 
		{
			using(SqlCommand cmd = new SqlCommand())
			{
				try
				{
					PrepareCommand(cmd,conn,null,CommandType.Text, sql, null);
					int val = cmd.ExecuteNonQuery();
					cmd.Parameters.Clear();
					return val;
				}
				catch
				{
					throw;
				}
			}
		}

		public static SqlDataReader ExecuteReader(SqlConnection  conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
			using(SqlCommand cmd = new SqlCommand())
			{
				try 
				{
					PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
					SqlDataReader odr = cmd.ExecuteReader();
					cmd.Parameters.Clear();
					return odr;
				}
				catch 
				{
					throw;
				}
			}
		}
		
		public static object ExecuteScalar(SqlConnection  conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) {
			using(SqlCommand cmd = new SqlCommand())
			 {
				try
				{
					PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
					object val = cmd.ExecuteScalar();
					cmd.Parameters.Clear();
					return val;
				}
				catch
				{
					throw;
				}
			}
		}

		public static object ExecuteScalar(SqlConnection  conn, string cmdText) 
		{
			using(SqlCommand cmd = new SqlCommand())
			{
				try
				{
					PrepareCommand(cmd, conn, null,CommandType.Text, cmdText,null);
					object val = cmd.ExecuteScalar();
					cmd.Parameters.Clear();
					return val;
				}
				catch
				{
					throw;
				}
			}
		}

		public static object ExecuteScalar(SqlTransaction trans, string cmdText) 
		{
			using(SqlCommand cmd = new SqlCommand())
			{
				try
				{
					PrepareCommand(cmd,trans.Connection,trans,CommandType.Text,cmdText,null);
					object val = cmd.ExecuteScalar();
					cmd.Parameters.Clear();
					return val;
				}
				catch
				{
					throw;
				}
			}
		}

		public static int Fill(System.Data.DataSet dataSet,string srcTable,SqlConnection  conn,string cmdText)
		{
			try
			{
				return Fill(dataSet,srcTable,conn,CommandType.Text,cmdText,null);
			}
			catch
			{
				throw;
			}
		}

		public static int Fill(System.Data.DataSet dataSet,string srcTable,SqlConnection  conn,CommandType cmdType,string cmdText,params SqlParameter[] cmdParms)
		{
			try
			{
				using(SqlCommand cmd = new SqlCommand())
				{
					PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms);
					using(SqlDataAdapter odap = new SqlDataAdapter(cmd))
					{
						int val = odap.Fill(dataSet,srcTable);
						cmd.Parameters.Clear();
						return val;
					}
				}
			}
			catch
			{
				throw;
			}
		}		
		
		public static int Fill(System.Data.DataTable dataTable,SqlConnection  conn,string cmdText)
		{
			try
			{
				return Fill(dataTable,conn,CommandType.Text,cmdText,null);
			}
			catch
			{
				throw;
			}
		}

		public static int Fill(System.Data.DataTable dataTable,SqlConnection  conn,CommandType cmdType,string cmdText,params SqlParameter[] cmdParms)
		{
			try
			{
				using(SqlCommand cmd = new SqlCommand())
				{
					PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms);
					using(SqlDataAdapter odap = new SqlDataAdapter(cmd))
					{
						int val = odap.Fill(dataTable);
						cmd.Parameters.Clear();
						return val;
					}
				}
			}
			catch
			{
				throw;
			}
		}

		private static void PrepareCommand(SqlCommand cmd, SqlConnection  conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) {
			try
			{
				if (conn.State != ConnectionState.Open)
				{
					conn.Open();
				}

				cmd.Connection = conn;
				cmd.CommandText = cmdText;

				if (trans != null)
				{
					cmd.Transaction = trans;
				}
			
				cmd.CommandType = cmdType;

				if (cmdParms != null) 
				{
					foreach (SqlParameter parm in cmdParms)
					{
						cmd.Parameters.Add(parm);
					}
				}
			}
			catch
			{
				throw;
			}
		}
	}
}