最近在整理以前的资料时，看到了以前我们在项目中经常用的一个数据库访问类，虽然现在已经可以用代码生成工具生成比较完整的数据库访问类，但是这个类在我们以前的项目中久经考验，所以我觉得还是比较好用，废话不多说了，上代码：
//======================================================================
// // filename : DataBaseAccess.cs // // description: 1. data base access operation class DataBaseAccess. // 2. data base access operation help class SQLHelper. // // // //====================================================================== using System; using System.Data; using System.Data.Sql; using System.Data.SqlClient; using System.Configuration; using System.IO; using System.Collections; using System.Collections.Generic; using System.Reflection; using System.Xml; namespace DAL { #region Database Access /// <summary> /// DataBase Operate Class DataBaseAccess /// </summary> public class DataBaseAccess { /// <summary> /// DataBase Connection /// </summary> private SqlConnection conn = new SqlConnection(SQLHelper.StrConn); /// <summary> /// DataBase Connection /// </summary> public SqlConnection Conn { get { return conn; } } /// <summary> /// Construct /// </summary> public DataBaseAccess() { } /// <summary> /// Destruct /// </summary> ~DataBaseAccess() { CloseDB(); } #region "***** Debug Configuration *****" /// <summary> ///Judge whether the state is Debug /// </summary> /// <returns>if the state is Debug return true,else false</returns> private bool JudgeDebug() { bool bIsDebug = false; #if DEBUG string strIsDebug = ConfigurationManager.AppSettings["IsDebug"]; bIsDebug = ((bIsDebug || (strIsDebug != null && strIsDebug.Equals("true"))) ? true : false); #endif return bIsDebug; } /// <summary> /// Output the Debug Information /// </summary> /// <param name="objDebugInfo">Debug Information</param> private void debug(object objDebugInfo) { #if DEBUG //if open debug,output the debug information into the file(the Directory in which Current programe run and the file name is DebugInfo\[日期].ini) if (JudgeDebug()) { string strPath = System.Environment.CurrentDirectory + "\\DebugInfo\\"; if (!Directory.Exists(strPath)) { Directory.CreateDirectory(strPath); } try { StreamWriter swDebugOutput = new StreamWriter(strPath + DateTime.Now.ToLongDateString() + ".ini", true, System.Text.Encoding.Unicode); swDebugOutput.Write("time：" + DateTime.Now.ToString() + "\r\n" + objDebugInfo + "\r\n\r\n"); swDebugOutput.Close(); swDebugOutput.Dispose(); } catch (Exception ex) { throw ex; } } #endif } #endregion #region "***** Database Basic Operation *****" #region ExecuteSql /// <summary> /// Execute SQL(insert,delete,update)command,return the number of the rows which are affected /// </summary> /// <param name="sqlcmd">SQL Command which will be Executed</param> /// <returns>return the number of the rows which are affected</returns> public int ExecuteSql(SqlCommand sqlcmd) { debug("Now Execute DataBaseAccess's Method:ExecuteSql(SqlCommand)，Return Type：int "); this.conn.Open(); sqlcmd.Connection = this.conn; SqlTransaction trans = this.conn.BeginTransaction(); sqlcmd.Transaction = trans; try { debug("Execute SQL Command：" + sqlcmd.CommandText); int iReturnValue = sqlcmd.ExecuteNonQuery(); trans.Commit(); return iReturnValue; } catch (SqlException ex) { debug("Exception Information：" + ex.ToString()); trans.Rollback(); throw ex; } finally { sqlcmd.Dispose(); this.conn.Close(); } } /// <summary> /// Execute SQL(insert,delete,update)command,return the number of the rows which are affected /// </summary> /// <param name="sqlcmd">SQL Command which will be Executed</param> /// <returns>return the number of the rows which are affected</returns> public int ExecuteSql(string strSql) { debug("Now Execute DataBaseAccess's Method:ExecuteSql(string)，Return Type：int "); return ExecuteSql(new SqlCommand(strSql,this.conn)); } /// <summary> /// Execute SQL(insert,delete,update)command,return the number of the rows which are affected. /// </summary> /// <param name="strSql">SQL Command which will be Executed</param> /// <param name="sqlParameters">SQL Parameter</param> /// <returns>return the number of the rows which are affected</returns> public int ExecuteSql(string strSql, SqlParameter[] sqlParameters) { debug("Now Execute DataBaseAccess's Method:ExecuteSql(string, SqlParameter[])，Return Type：int "); return ExecuteSql(SQLHelper.CreateCommand(strSql, sqlParameters, this.conn)); } #endregion #region ExecuteSqlDic /// <summary> /// Execute mutil-SQL(insert,delete,update)command,keep an affair. /// </summary> /// <param name="sqlcmd">SQL Command collection which will be Executed</param> /// <param name="bNotAffectRowRollback">if true,once one SQL Execute,the result of the execution is invalid,if false,ignore the result and rollback.</param> /// <returns>return the list number of the rows which are affected</returns> public List<int> ExecuteSqlDic(Dictionary<string, SqlParameter[]> dic, bool bNotAffectRowRollback) { debug("Now Execute DataBaseAccess's Method:ExecuteSqlDic(Dictionary<string, SqlParameter[]>, bool)，Return Type：List<int> "); List<int> iReturnValueList = new List<int>(); this.conn.Open(); SqlTransaction trans = this.conn.BeginTransaction(); try { foreach (KeyValuePair<string, SqlParameter[]> kvp in dic) { SqlCommand sqlcmd = SQLHelper.CreateCommand(kvp.Key, kvp.Value, this.conn); sqlcmd.Transaction = trans; debug("Execute SQL Command：" + sqlcmd.CommandText); int iAffectRow=sqlcmd.ExecuteNonQuery(); iReturnValueList.Add(iAffectRow); if (bNotAffectRowRollback && iAffectRow == 0) { trans.Rollback(); iReturnValueList.Clear(); return iReturnValueList; } } trans.Commit(); } catch (SqlException ex) { debug("Exception Information：" + ex.ToString()); trans.Rollback(); throw ex; } finally { this.conn.Close(); } return iReturnValueList; } /// <summary> /// Execute mutil-SQL(insert,delete,update)command,keep an affair. /// </summary> /// <param name="sqlcmd">SQL Command collection which will be Executed</param> /// <returns>return the list number of the rows which are affected</returns> public List<int> ExecuteSqlDic(Dictionary<string, SqlParameter[]> dic) { debug("Now Execute DataBaseAccess's Method:ExecuteSqlDic(Dictionary<string, SqlParameter[]>)，Return Type：List<int> "); return ExecuteSqlDic(dic, false); } #endregion #region /// <summary> /// Execute SQL Command,Return single Result. /// </summary> /// <param name="sqlcmd">SQL Command collection which will be Executed</param> /// <returns>return single Result</returns> public object ExecScalar(SqlCommand sqlcmd) { debug("Now Execute DataBaseAccess's Method:ExecScalar(SqlCommand)，Return Type：object "); sqlcmd.Connection = this.conn; try { debug("Execute SQL Command：" + sqlcmd.CommandText); this.conn.Open(); object r = sqlcmd.ExecuteScalar(); //if (Object.Equals(r, null)) //{ // throw new Exception("object is null!"); //} //else //{ // return r; //} return r; } catch (SqlException ex) { debug("Exception Information：" + ex.ToString()); throw ex; } finally { sqlcmd.Dispose(); this.conn.Close(); } } /// <summary> /// Execute SQL Command,Return single Result. /// </summary> /// <param name="sqlcmd">SQL Command collection which will be Executed</param> /// <returns>return single Result</returns> public object ExecScalar(string strSql) { debug("Now Execute DataBaseAccess's Method:ExecScalar(string)，Return Type：object "); return ExecScalar(new SqlCommand(strSql,this.conn)); } /// <summary> /// Execute SQL Command,Return single Result. /// </summary> /// <param name="sqlcmd">SQL Command collection which will be Executed</param> /// <param name="sqlParameters">SQL Parameters Collection</param> /// <returns>return single Result</returns> public object ExecScalar(string strSql, SqlParameter[] sqlParameters) { debug("Now Execute DataBaseAccess's Method:ExecScalar(string,SqlParameter[])，Return Type：object "); return ExecScalar(SQLHelper.CreateCommand(strSql, sqlParameters, this.conn)); } #endregion #region ExecScalarEx /// <summary> /// Execute SQL command,if result set has note return 1,if result set is null return 0 /// </summary> /// <param name="sqlcmd">SQL Command which will be Executed</param> /// <returns>Execute SQL command,if result set has note return 1,if result set is null return 0</returns> public int ExecScalarEx(SqlCommand sqlcmd) { debug("Now Execute DataBaseAccess's Method:ExecScalarEx(SqlCommand)，Return Type：int "); sqlcmd.Connection = this.conn; try { debug("Execute SQL Command：" + sqlcmd.CommandText); this.conn.Open(); SqlDataReader myDr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection); if (myDr.Read()) { return 1; } else { return 0; } } catch (SqlException ex) { debug("Exception Information：" + ex.ToString()); throw ex; } finally { sqlcmd.Dispose(); this.conn.Close(); } } /// <summary> /// Execute SQL command,if result set has note return 1,if result set is null return 0 /// </summary> /// <param name="strSql">SQL Command which will be Executed</param> /// <returns>Execute SQL command,if result set has note return 1,if result set is null return 0</returns> public int ExecScalarEx(string strSql) { debug("Now Execute DataBaseAccess's Method:ExecScalarEx(strSql)，Return Type：int "); return ExecScalarEx(new SqlCommand(strSql, this.conn)); } /// <summary> /// Execute SQL command,if result set has note return 1,if result set is null return 0 /// </summary> /// <param name="strSql">SQL Command which will be Executed</param> /// <param name="sqlParameters">SQL Command Collection</param> /// <returns>Execute SQL command,if result set has note return 1,if result set is null return 0</returns> public int ExecScalarEx(string strSql, SqlParameter[] sqlParameters) { debug("Now Execute DataBaseAccess's Method:ExecScalarEx(string,SqlParameter[])，Return Type：int "); return ExecScalarEx(SQLHelper.CreateCommand(strSql, sqlParameters, this.conn)); } #endregion #region ExecuteSqlDs /// <summary> /// Execute SQL Command,return DataSet. /// </summary> /// <param name="sqlcmd">SQL Command which will be Executed</param> /// <param name="strTableName">table name</param> /// <returns>return DataSet.</returns> public DataSet ExecuteSqlDs(SqlCommand sqlcmd, string strTableName) { debug("Now Execute DataBaseAccess's Method:ExecuteSqlDs(SqlCommand,string)，Return Type：DataSet "); sqlcmd.Connection = this.conn; SqlDataAdapter sqlda = new SqlDataAdapter(sqlcmd); DataSet dsReturn = new DataSet(); try { debug("Execute SQL Command：" + sqlcmd.CommandText); this.conn.Open(); sqlda.Fill(dsReturn, strTableName); return dsReturn; } catch (SqlException ex) { debug("Exception information：" + ex.ToString()); throw ex; } finally { sqlcmd.Dispose(); sqlda.Dispose(); this.conn.Close(); } } /// <summary> /// Execute SQL Command,return DataSet. /// </summary> /// <param name="strSql">SQL Command which will be Executed</param> /// <param name="strTableName">table name</param> /// <returns>return dataset.</returns> public DataSet ExecuteSqlDs(string strSql, string strTableName) { debug("Now Execute DataBaseAccess's Method:ExecuteSqlDs(string,string)，Return Type：DataSet "); return ExecuteSqlDs(new SqlCommand(strSql, this.conn), strTableName); } /// <summary> /// Execute SQL Command,return DataSet. /// </summary> /// <param name="strSql">SQL Command which will be Executed</param> /// <param name="sqlParameters">SQL Parameter Collection</param> /// <param name="strTableName">table name</param> /// <returns>return DataSet.</returns> public DataSet ExecuteSqlDs(string strSql, SqlParameter[] sqlParameters, string strTableName) { debug("Now Execute DataBaseAccess's Method:ExecuteSqlDs(string,SqlParameter[],string)，Return Type：DataSet "); return ExecuteSqlDs(SQLHelper.CreateCommand(strSql, sqlParameters, this.conn), strTableName); } #endregion #region ExecuteSqlFillDs /// <summary> /// Execute SQL Command,add new resultset into current ref DataSet. /// </summary> /// <param name="sqlcmd">SQL Command which will be Executed</param> /// <param name="strTableName">table name</param> /// <param name="dsRef">current Dataset</param> public void ExecuteSqlFillDs(SqlCommand sqlcmd, string strTableName, ref DataSet dsRef) { debug("Now Execute DataBaseAccess's Method:ExecuteSqlFillDs(SqlCommand,string,ref DataSet)"); sqlcmd.Connection = this.conn; SqlDataAdapter sqlda = new SqlDataAdapter(sqlcmd); try { debug("Execute SQL Command：" + sqlcmd.CommandText); this.conn.Open(); sqlda.Fill(dsRef, strTableName); } catch (SqlException ex) { debug("Exception information：" + ex.ToString()); throw ex; } finally { sqlcmd.Dispose(); sqlda.Dispose(); this.conn.Close(); } } /// <summary> /// Execute SQL Command,add new resultset into current ref DataSet. /// </summary> /// <param name="strSql">SQL Command which will be Executed</param> /// <param name="strTableName">table name</param> /// <param name="dsRef">current Dataset</param> public void ExecuteSqlFillDs(string strSql, string strTableName, ref DataSet dsRef) { debug("Now Execute DataBaseAccess's Method:ExecuteSqlFillDs(string,string,ref DataSet)"); ExecuteSqlFillDs(new SqlCommand(strSql),strTableName, ref dsRef); } /// <summary> /// Execute SQL Command,add new resultset into current ref DataSet. /// </summary> /// <param name="strSql">SQL Command which will be Executed</param> /// <param name="sqlParameters">SQL Parameters Collection</param> /// <param name="strTableName">table name</param> /// <param name="dsRef">Current Dataset</param> public void ExecuteSqlFillDs(string strSql, SqlParameter[] sqlParameters, string strTableName, ref DataSet dsRef) { debug("Now Execute DataBaseAccess's Method:ExecuteSqlFillDs(string strSql, SqlParameter[], string, ref DataSet)"); ExecuteSqlFillDs(SQLHelper.CreateCommand(strSql, sqlParameters, this.conn), strTableName, ref dsRef); } #endregion #region ExecuteSqlDsEx /// <summary> /// Define pagination(Execute SQL Command,return DataSet). /// </summary> /// <param name="sqlcmd">SQL Command which will be Executed</param> /// <param name="iStartRecord">index of StartRecord</param> /// <param name="iMaxRecord">number of Records</param> /// <param name="strTableName">table name</param> /// <returns>return DataSet</returns> public DataSet ExecuteSqlDsEx(SqlCommand sqlcmd, int iStartRecord, int iMaxRecord, string strTableName) { debug("Now Execute DataBaseAccess's Method:ExecuteSqlDsEx(SqlCommand,int,int,string),Return Type：DataSet "); sqlcmd.Connection = this.conn; SqlDataAdapter sqlda = new SqlDataAdapter(sqlcmd); DataSet dsReapter = new DataSet(); try { debug("Execute SQL Command：" + sqlcmd.CommandText); this.conn.Open(); if (iStartRecord < 0) iStartRecord = 0; sqlda.Fill(dsReapter, iStartRecord, iMaxRecord, strTableName); return dsReapter; } catch (SqlException ex) { debug("Exception information：" + ex.ToString()); throw ex; } finally { sqlcmd.Dispose(); sqlda.Dispose(); this.conn.Close(); } } /// <summary> /// Define pagination(Execute SQL Command,return DataSet). /// </summary> /// <param name="strSql">SQL Command which will be Executed</param> /// <param name="iStartRecord">index of StartRecord</param> /// <param name="iMaxRecord">number of Records</param> /// <param name="strTableName">table name</param> /// <returns>return DataSet.</returns> public DataSet ExecuteSqlDsEx(string strSql, int iStartRecord, int iMaxRecord, string strTableName) { debug("Now Execute DataBaseAccess's Method:ExecuteSqlDsEx(string,int,int,string),Return Type：DataSet "); return ExecuteSqlDsEx(new SqlCommand(strSql), iStartRecord, iMaxRecord, strTableName); } /// <summary> /// Define pagination(Execute SQL Command,return DataSet). /// </summary> /// <param name="strSql">SQL Command which will be Executed</param> /// <param name="sqlParameters">SQL Parameters Collection</param> /// <param name="iStartRecord">index of StartRecord</param> /// <param name="iMaxRecord">number of Records</param> /// <param name="strTableName">table name</param> /// <returns>return DataSet.</returns> public DataSet ExecuteSqlDsEx(string strSql, SqlParameter[] sqlParameters, int iStartRecord, int iMaxRecord, string strTableName) { debug("Now Execute DataBaseAccess's Method:ExecuteSqlDsEx(string, SqlParameter[], int, int, string),Return Type：DataSet "); return ExecuteSqlDsEx(SQLHelper.CreateCommand(strSql, sqlParameters, this.conn), iStartRecord, iMaxRecord, strTableName); } #endregion #region ExecuteSqlDr /// <summary> /// Execute SQL Command,return SqlDataReader. /// </summary> /// <param name="sqlcmd">SQL Command which will be Executed</param> /// <returns>Return SqlDataReader</returns> public SqlDataReader ExecuteSqlDr(SqlCommand sqlcmd) { debug("Now Execute DataBaseAccess's Method:ExecuteSqlDr(SqlCommand),Return Type：SqlDataReader "); sqlcmd.Connection = this.conn; SqlDataReader sqldr; try { debug("Execute SQL Command：" + sqlcmd.CommandText.ToString()); this.conn.Open(); sqldr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection); return sqldr; } catch (SqlException ex) { debug("Exception information：" + ex.ToString()); throw ex; } finally { sqlcmd.Dispose(); } } /// <summary> /// Execute SQL Command,return SqlDataReader. /// </summary> /// <param name="strSql">SQL Command which will be Executed</param> /// <returns>Return SqlDataReader</returns> public SqlDataReader ExecuteSqlDr(string strSql) { debug("Now Execute DataBaseAccess's Method:ExecuteSqlDr(string),Return Type：SqlDataReader "); return ExecuteSqlDr(new SqlCommand(strSql)); } /// <summary> /// Execute SQL Command,return SqlDataReader. /// </summary> /// <param name="strSql">SQL Command which will be Executed</param> /// <param name="sqlParameters">SQL Parameters Collection</param> /// <returns>Return SqlDataReader</returns> public SqlDataReader ExecuteSqlDr(string strSql, SqlParameter[] sqlParameters) { debug("Now Execute DataBaseAccess's Method:ExecuteSqlDr(string, SqlParameter[]),Return Type：SqlDataReader "); return ExecuteSqlDr(SQLHelper.CreateCommand(strSql, sqlParameters, this.conn)); } #endregion #region ExecuteSqlTarn /// <summary> /// Execute SQL Command,Keep affair. /// </summary> /// <param name="strSql">SQL Command which will be Executed</param> /// <param name="sqlParameters">SQL Parameters Collection</param> public void ExecuteSqlTran(string strSql, SqlParameter[] sqlParameters) { conn.Open(); SqlCommand sqlcmd = SQLHelper.CreateCommand(strSql, sqlParameters, this.conn); using(SqlTransaction sqltrans = this.conn.BeginTransaction()) { sqlcmd.Transaction = sqltrans; try { sqlcmd.ExecuteNonQuery(); sqltrans.Commit(); } catch (System.Data.SqlClient.SqlException E) { sqltrans.Rollback(); throw E; } finally { sqlcmd.Dispose(); this.conn.Close(); } } } /// <summary> /// execute SQL script,Keep SqlTransaction 。 /// </summary> /// <param name="objSqlList">save sql command and sql parameter</param> public void ExecuteSqlTran(Dictionary<string ,SqlParameter []> objSqlList) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { SqlCommand sqlcmd = new SqlCommand(); //circulation foreach (KeyValuePair<string, SqlParameter[]> kvp in objSqlList) { //the key value is by|Division ，Serial number|sql script string[] tmp = kvp.Key.ToString().Split(new char[] { '|' }); string cmdText = tmp[1]; //get SqlParameter value SqlParameter[] sqlParms = kvp.Value; if (sqlParms!=null) sqlcmd = SQLHelper.CreateCommand(cmdText, sqlParms, this.conn); sqlcmd.Transaction = trans; int val = sqlcmd.ExecuteNonQuery(); //clear SqlParameter sqlcmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } finally { this.conn.Close(); } } } #endregion #endregion #region other /// <summary> ///Close DataBase Connection. /// </summary> public void CloseDB() { if (this.conn != null) { if (this.conn.State != ConnectionState.Closed) this.conn.Close(); } } /// <summary> /// Dispose Resource /// </summary> public void Dispose() { if (this.conn != null) { if (this.conn.State != ConnectionState.Closed) this.conn.Close(); this.conn.Dispose(); } } #endregion } #endregion #region DataBase Operate assistant class SQLHelper /// <summary> /// SQLHelper. /// </summary> public abstract class SQLHelper { /// <summary> /// DataBase ConnectionString /// </summary> public static string StrConn = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString; //------------------------------------------------------------------------------------------------------------ /// <summary> /// Create SqlParameter. /// </summary> /// <param name="parameterName">The name of the parameter to map.</param> /// <param name="DbType">One of the System.Data.SqlDbType values.</param> /// <param name="value">The length of the parameter.</param> /// <returns>Return new SqlParameter.</returns> public static SqlParameter CreateSqlParameter(string parameterName, SqlDbType DbType, object value) { SqlParameter sqlpara = new SqlParameter(parameterName, DbType); sqlpara.Value = value; return sqlpara; } /// <summary> /// Create SqlParameter. /// </summary> /// <param name="parameterName">The name of the parameter to map.</param> /// <param name="DbType">One of the System.Data.SqlDbType values.</param> /// <returns>Return new SqlParameter.</returns> public static SqlParameter CreateSqlParameter(string parameterName, SqlDbType DbType, int size, object value) { SqlParameter sqlpara = new SqlParameter(parameterName, DbType, size); sqlpara.Value = value; return sqlpara; } //------------------------------------------------------------------------------------------------------------ /// <summary> /// Param amortize Hashtable /// </summary> private static Hashtable htParamCache = Hashtable.Synchronized(new Hashtable()); /// <summary> /// Save Parameters in Cache /// </summary> /// <param name="strCacheKey"></param> /// <param name="sqlParameters"></param> public static void CacheParameters(string strCacheKey, params SqlParameter[] sqlParameters) { SQLHelper.htParamCache[strCacheKey] = sqlParameters; } /// <summary> /// Get Parameters from Cache /// </summary> /// <param name="strCacheKey"></param> /// <returns></returns> public static SqlParameter[] GetCachedParameters(string strCacheKey) { SqlParameter[] sqlParameters = (SqlParameter[])SQLHelper.htParamCache[strCacheKey]; if (sqlParameters == null) { return null; } SqlParameter[] clonedParms = new SqlParameter[sqlParameters.Length]; for (int i = 0, j = sqlParameters.Length; i < j; i++) { clonedParms[i] = (SqlParameter)((ICloneable)sqlParameters[i]).Clone(); } return clonedParms; } //---------------------------------------------------------------------------------------------------------------- /// <summary> /// Create new SqlComand /// </summary> /// <param name="strSql"></param> public static SqlCommand CreateCommand(string strSql) { SqlCommand sqlcmd = new SqlCommand(strSql); return sqlcmd; } /// <summary> /// Create new SqlComand,Set DataBase Connection /// </summary> /// <param name="strSql"></param> /// <param name="sqlconn"></param> /// <returns></returns> public static SqlCommand CreateCommand(string strSql, SqlConnection sqlconn) { SqlCommand sqlcmd = new SqlCommand(strSql, sqlconn); return sqlcmd; } /// <summary> /// Create new SqlComand which has Parameters /// </summary> /// <param name="strSql"></param> /// <param name="sqlParameters"></param> public static SqlCommand CreateCommand(string strSql, SqlParameter[] sqlParameters) { SqlCommand sqlcmd = new SqlCommand(strSql); foreach (SqlParameter param in sqlParameters) { sqlcmd.Parameters.Add(param); } return sqlcmd; } /// <summary> /// Create new SqlComand which has Parameters,Set DataBase Connection /// </summary> /// <param name="strSql"></param> /// <param name="conn"></param> /// <param name="sqlParameters"></param> public static SqlCommand CreateCommand(string strSql, SqlParameter[] sqlParameters, SqlConnection sqlconn) { SqlCommand sqlcmd = new SqlCommand(strSql, sqlconn); foreach (SqlParameter param in sqlParameters) { sqlcmd.Parameters.Add(param); } return sqlcmd; } /// <summary> /// Create new SqlComand which has Parameters,Set Stored Procedure Flag /// </summary> /// <param name="strSql"></param> /// <param name="sqlParameters"></param> /// <param name="bIsStoredProcedure"></param> /// <returns></returns> public static SqlCommand CreateCommand(string strSql, SqlParameter[] sqlParameters, bool bIsStoredProcedure) { SqlCommand sqlcmd = new SqlCommand(strSql); if (bIsStoredProcedure) sqlcmd.CommandType = CommandType.StoredProcedure; else sqlcmd.CommandType = CommandType.Text; foreach (SqlParameter param in sqlParameters) { sqlcmd.Parameters.Add(param); } return sqlcmd; } /// <summary> /// Create new SqlComand which has Parameters,Set Stored Procedure Flag and DataBase Connection /// </summary> /// <param name="strSql"></param> /// <param name="sqlParameters"></param> /// <param name="bIsStoredProcedure"></param> /// <returns></returns> public static SqlCommand CreateCommand(string strSql, SqlParameter[] sqlParameters, bool bIsStoredProcedure, SqlConnection sqlconn) { SqlCommand sqlcmd = new SqlCommand(strSql, sqlconn); if (bIsStoredProcedure) sqlcmd.CommandType = CommandType.StoredProcedure; else sqlcmd.CommandType = CommandType.Text; foreach (SqlParameter param in sqlParameters) { sqlcmd.Parameters.Add(param); } return sqlcmd; } /// <summary> /// Create new SqlDataAdapter which has Parameters and set DataBase Connection /// </summary> /// <param name="sqlda"></param> /// <param name="mySqlParamter"></param> public static SqlDataAdapter CreateDataAdapter(string strSql, SqlParameter[] sqlParameters, SqlConnection sqlconn) { SqlDataAdapter sqlda = new SqlDataAdapter(strSql, sqlconn); foreach (SqlParameter param in sqlParameters) { sqlda.SelectCommand.Parameters.Add(param); } return sqlda; } /// <summary> /// Create new SqlDataAdapter which has Parameters,Set Stored Procedure Flag and DataBase Connection /// </summary> /// <param name="strSql"></param> /// <param name="mySqlParamter"></param> /// <param name="bIsStoredProcedure"></param> /// <returns></returns> public static SqlDataAdapter CreateDataAdapter(string strSql, SqlParameter[] sqlParameters, bool bIsStoredProcedure, SqlConnection sqlconn) { SqlDataAdapter sqlda = new SqlDataAdapter(strSql, sqlconn); if (bIsStoredProcedure) sqlda.SelectCommand.CommandType = CommandType.StoredProcedure; else sqlda.SelectCommand.CommandType = CommandType.Text; foreach (SqlParameter param in sqlParameters) { sqlda.SelectCommand.Parameters.Add(param); } return sqlda; } /// <summary> /// Create SqlParameter[] /// </summary> /// <param name="sqlParameterArr"></param> /// <returns></returns> public static SqlParameter[] CreateSqlParameters(object[,] sqlParameterArr) { SqlParameter[] sqlParameters = new SqlParameter[sqlParameterArr.GetLength(0)]; int i = 0; foreach (SqlParameter param in sqlParameters) { sqlParameters[i] = new SqlParameter(Convert.ToString(sqlParameterArr[i, 0]), sqlParameterArr[i, 1]); i++; } return sqlParameters; } /// <summary> /// add Parameters for Command /// </summary> /// <param name="sqlcmd"></param> /// <param name="mySqlParamter"></param> public static void AddCommandParams(ref SqlCommand sqlcmd, SqlParameter[] sqlParameters) { foreach (SqlParameter param in sqlParameters) { sqlcmd.Parameters.Add(param); } } /// <summary> /// add Parameters for DataAdapter /// </summary> /// <param name="sqlda"></param> /// <param name="mySqlParamter"></param> public static void AddDataAdapterParam(ref SqlDataAdapter sqlda, SqlParameter[] sqlParameters) { foreach (SqlParameter param in sqlParameters) { sqlda.SelectCommand.Parameters.Add(param); } } /// <summary> /// Get SQLScript /// </summary> /// <param name="strFilepath"></param> /// <param name="strNodePath"></param> public static string GetSQLScript(string strFilepath,string strNodePath) { string strSql; XmlDocument xmldoc = new XmlDocument(); xmldoc.Load(strFilepath); XmlNode node = xmldoc.SelectSingleNode(strNodePath); strSql = node.ChildNodes[0].InnerText; return strSql; } } #endregion }