/// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(DataRow dr, SqlTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO hrCompanyShopInfo(");
            strSql.Append("MainID,sShopID,sShopCName,sShopEName,sRemark,sUserID)");
            strSql.Append(" VALUES (");
            strSql.Append("@MainID,@sShopID,@sShopCName,@sShopEName,@sRemark,@sUserID)");
            strSql.Append(";SELECT @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@MainID", SqlDbType.Int,4),
                    new SqlParameter("@sShopID", SqlDbType.VarChar,30),
                    new SqlParameter("@sShopCName", SqlDbType.VarChar,50),
                    new SqlParameter("@sShopEName", SqlDbType.VarChar,50),
                    new SqlParameter("@sRemark", SqlDbType.VarChar,200),
                    new SqlParameter("@sUserID", SqlDbType.VarChar,30)};
            parameters[0].Value = dr["MainID"];
            parameters[1].Value = dr["sShopID"];
            parameters[2].Value = dr["sShopCName"];
            parameters[3].Value = dr["sShopEName"];
            parameters[4].Value = dr["sRemark"];
            parameters[5].Value = dr["sUserID"];

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), trans, parameters);
            if (obj == null)
            {
                return -1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

 /// <summary>
 /// Delete values from TitleLanguage by primary key(s).
 /// </summary>
 /// <param name="sqlConnection">Sql connection or null.</param>
 /// <param name="sqlTransaction">Sql transaction or null.</param>
 /// <param name="titleLanguageID"></param>
 /// <returns>true if successful otherwise false.</returns>
 public bool TitleLanguageDeleteAuto(
     SqlConnection sqlConnection,
     SqlTransaction sqlTransaction,
     int titleLanguageID)
 {
     return TitleLanguageDeleteAuto( sqlConnection, sqlTransaction, "BHL", titleLanguageID );
 }

 /// <summary>
 /// Delete values from Item by primary key(s).
 /// </summary>
 /// <param name="sqlConnection">Sql connection or null.</param>
 /// <param name="sqlTransaction">Sql transaction or null.</param>
 /// <param name="itemID"></param>
 /// <returns>true if successful otherwise false.</returns>
 public bool ItemDeleteAuto(
     SqlConnection sqlConnection,
     SqlTransaction sqlTransaction,
     int itemID)
 {
     return ItemDeleteAuto( sqlConnection, sqlTransaction, "BHL", itemID );
 }

 public void Add(SqlTransaction transaction)
 {
     _orderInsertDataParameters = new OrderInsertDataParameters(Orders);
     DataBaseHelper dbHelper = new DataBaseHelper(StoredProcedureName);
     object id = dbHelper.RunScalar(transaction, _orderInsertDataParameters.Parameters);
     Orders.OrderId = int.Parse(id.ToString());
 }

 /// <summary>
 /// Get insert cmd of GENERAL_AUT_USERROLE table
 /// </summary>
 /// <param name="con"></param>
 /// <param name="trans"></param>
 /// <returns></returns>
 /// <remarks>
 /// Author:			PhatLT. FPTSS.
 /// Created date:	14/02/2011
 /// </remarks>
 public SqlCommand GetInsertCommand(SqlConnection con, SqlTransaction trans)
 {
     SqlCommand cmd = new SqlCommand("INSERT INTO GENERAL_AUT_USERROLE(UROLE_ID, ROLE_NAME) VALUES(@UROLE_ID, @ROLE_NAME)", con, trans);
     cmd.Parameters.Add("@UROLE_ID", SqlDbType.VarChar, 14, "UROLE_ID");
     cmd.Parameters.Add("@ROLE_NAME", SqlDbType.VarChar, 255, "ROLE_NAME");
     return cmd;
 }

 /// <summary>
 /// Delete values from PDFStatus by primary key(s).
 /// </summary>
 /// <param name="sqlConnection">Sql connection or null.</param>
 /// <param name="sqlTransaction">Sql transaction or null.</param>
 /// <param name="pdfStatusID"></param>
 /// <returns>true if successful otherwise false.</returns>
 public bool PDFStatusDeleteAuto(
     SqlConnection sqlConnection,
     SqlTransaction sqlTransaction,
     int pdfStatusID)
 {
     return PDFStatusDeleteAuto( sqlConnection, sqlTransaction, "BHL", pdfStatusID );
 }

        public bool Update_ControlPresupuestal(ControlPresupuestal_VO pControlPresupuestal, SqlTransaction TransControlPresupuestal)
        {
            bool b_ControlPresupuestal;
            try
            {

                SqlCommand cmdComand = new SqlCommand("USP_U_SICOP_CONTROLPRESUPUESTAL");
                cmdComand.CommandType = System.Data.CommandType.StoredProcedure;
                cmdComand.Connection = TransControlPresupuestal.Connection;
                cmdComand.Transaction = TransControlPresupuestal;
                cmdComand.Parameters.Clear();
                cmdComand.Parameters.AddWithValue("@CPRE_INT_IDCONTROLPRESUPUESTAL", pControlPresupuestal.CPRE_INT_IDCONTROLPRESUPUESTAL);
                cmdComand.Parameters.AddWithValue("@EGAS_VCH_IDESPECIFICADEGASTO", pControlPresupuestal.EGAS_VCH_IDESPECIFICADEGASTO);
                cmdComand.Parameters.AddWithValue("@DOCU_INT_IDDOCUMENTO", pControlPresupuestal.DOCU_INT_IDDOCUMENTO);
                cmdComand.Parameters.AddWithValue("@CPRE_VCH_NRODOCUMENTO", pControlPresupuestal.CPRE_VCH_NRODOCUMENTO);
                cmdComand.Parameters.AddWithValue("@CPRE_DAT_FECHAINGRESO", pControlPresupuestal.CPRE_DAT_FECHAINGRESO);
                cmdComand.Parameters.AddWithValue("@CPRE_VCH_NROCOMPROBANTEPAGO", pControlPresupuestal.CPRE_VCH_NROCOMPROBANTEPAGO);
                cmdComand.Parameters.AddWithValue("@CPRE_VCH_NROSIAF", pControlPresupuestal.CPRE_VCH_NROSIAF);
                cmdComand.Parameters.AddWithValue("@CPRE_DAT_FECHAGIRO", pControlPresupuestal.CPRE_DAT_FECHAGIRO);
                cmdComand.Parameters.AddWithValue("@CPRE_DAT_FECHAPAGO", pControlPresupuestal.CPRE_DAT_FECHAPAGO);
                cmdComand.Parameters.AddWithValue("@CPRE_VCH_CONCEPTOESPECIFICADEGASTO", pControlPresupuestal.CPRE_VCH_CONCEPTOESPECIFICADEGASTO);
                cmdComand.Parameters.AddWithValue("@CPRE_VCH_DETALLEESPECIFICADEGASTO", pControlPresupuestal.CPRE_VCH_DETALLEESPECIFICADEGASTO);
                cmdComand.Parameters.AddWithValue("@CPRE_VCH_PARTIDACONTABLE", pControlPresupuestal.CPRE_VCH_PARTIDACONTABLE);
                b_ControlPresupuestal = cmdComand.ExecuteNonQuery() > 0;

            }
            catch (Exception e)
            {
                if (e.Source != null)
                    Console.WriteLine("IOException source: {0}", e.Source);
                throw;
            }
            return b_ControlPresupuestal;
        }

        public static DataTable ExcuteDataTableByTextWithTrans(SqlConnection connection, SqlTransaction tran, string sqlText, IDataParameter[] paramers)
        {
            DataTable dt = new DataTable("");
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            SqlCommand cmd = new SqlCommand(sqlText, connection, tran);
            cmd.CommandTimeout = 300;
            cmd.CommandType = CommandType.Text;
            if (null != paramers)
            {
                foreach (SqlParameter param in paramers)
                {
                    cmd.Parameters.Add(param);
                }
            }

            ////Add parameter to receive return value of stored procedure
            //SqlParameter pr = new SqlParameter("ReturnValue", SqlDbType.Int);
            //pr.Direction = ParameterDirection.ReturnValue;
            //cmd.Parameters.Add(pr);

            SqlDataAdapter ada = new SqlDataAdapter(cmd);
            ada.Fill(dt);

            return dt;
            //return pr.Value;
        }

        public static bool Insert_Callstack(SqlConnection conn, ref SqlTransaction tran, int project_uid, byte[] signature, out int callstack_uid)
        {
            callstack_uid = 0;

            try
            {
                SqlCommand cmd1 = new SqlCommand("usp_insert_callstack", conn, tran);
                cmd1.CommandType = CommandType.StoredProcedure;

                SqlParameter returned = cmd1.CreateParameter();
                returned.Direction = ParameterDirection.ReturnValue;

                cmd1.Parameters.Add(returned);
                cmd1.Parameters.AddWithValue("@project_uid", project_uid);
                cmd1.Parameters.AddWithValue("@signature", signature);

                cmd1.ExecuteNonQuery();

                callstack_uid = (int)returned.Value;
            }
            catch (System.Exception e)
            {
                Console.WriteLine(e.Message);
                return false;
            }

            return true;
        }

        public SqlCommand CreateAddProductCommand(SqlConnection conn, SqlTransaction transaction)
        {
            var cmd = new SqlCommand(_sql, conn, transaction);
            CreateParameters(cmd);

            return cmd;
        }

 internal static object ExecuteScalarProcedure(SqlConnection connection, SqlTransaction transaction, string procName, Dictionary<string, object> parameters)
 {
     using (var command = PrepareCommandForProcedure(connection, transaction, procName, parameters))
     {
         return command.ExecuteScalar();
     }
 }

 internal static void ExecuteNonQueryProcedure(SqlConnection connection, SqlTransaction transaction, string procName, Dictionary<string, object> parameters)
 {
     using (var command = PrepareCommandForProcedure(connection, transaction, procName, parameters))
     {
         command.ExecuteNonQuery();
     }
 }

    //-------------------------------------------------------------------------------------------
    public void DeleteCheckedItems(SqlTransaction transaction, Control parent)
    {
        //foreach (Control control in parent.Controls)
          //{
          //     if (control.GetType() == typeof(CheckBox))
          //     {
          //          CheckBox item = (CheckBox)control;
          //          if (item.Checked)
          //          {
          //               Guid itemId = new Guid(item.Attributes["datakey"]);
          //               DataRowLookup reverseLookup = DatabaseHelper.Session.Get<DataRowLookup>(itemId);
          //               if (reverseLookup != null)
          //               {
          //                    SqlCommand command = new SqlCommand("delete from " + reverseLookup.TableName + " where [email protected]", wvvrdb.MSSQLDB);
          //                    command.Parameters.AddWithValue("Id", reverseLookup.RowId);
          //                    command.Transaction = transaction;
          //                    command.ExecuteNonQuery();
          //               }

          //               string sql = "delete from system_datalinks where ([email protected] and [email protected]) ";
          //               sql += "or ([email protected] and [email protected])";
          //               SqlCommand dataLinks = new SqlCommand(sql, wvvrdb.MSSQLDB);
          //               dataLinks.Parameters.AddWithValue("Object1", itemId);
          //               dataLinks.Parameters.AddWithValue("Object2", new Guid(Request["id"]));
          //               dataLinks.Transaction = transaction;
          //               dataLinks.ExecuteNonQuery();
          //          }
          //     }
          //     if (control.HasControls())
          //     {
          //          DeleteCheckedItems(transaction, control);
          //     }
          //}
    }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(DataRow dr, SqlTransaction trans)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO sysMenuParam(");
            strSql.Append("MenuID,sParamName,sParamValue,sUserID)");
            strSql.Append(" VALUES (");
            strSql.Append("@MenuID,@sParamName,@sParamValue,@sUserID)");
            strSql.Append(";SELECT @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@MenuID", SqlDbType.Int,4),
                    new SqlParameter("@sParamName", SqlDbType.VarChar,50),
                    new SqlParameter("@sParamValue", SqlDbType.VarChar,50),
                    new SqlParameter("@sUserID", SqlDbType.VarChar,30)};
            parameters[0].Value = dr["MenuID"];
            parameters[1].Value = dr["sParamName"];
            parameters[2].Value = dr["sParamValue"];
            parameters[3].Value = dr["sUserID"];

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), trans, parameters);
            if (obj == null)
            {
                return -1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

 /// <summary>
 /// Delete values from MarcControl by primary key(s).
 /// </summary>
 /// <param name="sqlConnection">Sql connection or null.</param>
 /// <param name="sqlTransaction">Sql transaction or null.</param>
 /// <param name="marcControlID"></param>
 /// <returns>true if successful otherwise false.</returns>
 public bool MarcControlDeleteAuto(
     SqlConnection sqlConnection,
     SqlTransaction sqlTransaction,
     int marcControlID)
 {
     return MarcControlDeleteAuto( sqlConnection, sqlTransaction, "BHL", marcControlID );
 }

 /// <summary>
 /// Delete values from PDFPage by primary key(s).
 /// </summary>
 /// <param name="sqlConnection">Sql connection or null.</param>
 /// <param name="sqlTransaction">Sql transaction or null.</param>
 /// <param name="pdfPageID"></param>
 /// <returns>true if successful otherwise false.</returns>
 public bool PDFPageDeleteAuto(
     SqlConnection sqlConnection,
     SqlTransaction sqlTransaction,
     int pdfPageID)
 {
     return PDFPageDeleteAuto( sqlConnection, sqlTransaction, "BHL", pdfPageID );
 }

 /// <summary>
 /// Executes the script.
 /// </summary>
 /// <remarks>
 /// Use script.Execute(transaction) to do the work. We will also pull the
 /// status of our script exection from here.
 /// </remarks>
 /// <param name="scriptName">Name of the script.</param>
 /// <param name="transaction">The current transaction.</param>
 /// <param name="dbUserName">Name of the DB owner.</param>
 public static void ExecuteScript(string scriptName, SqlTransaction transaction, string dbUserName)
 {
     SqlScriptRunner scriptRunner = new SqlScriptRunner(UnpackEmbeddedScript(scriptName), Encoding.UTF8);
     if(!string.IsNullOrEmpty(dbUserName))
         scriptRunner.TemplateParameters.SetValue("dbUser", dbUserName);
     scriptRunner.Execute(transaction);
 }

 /// <summary>
 /// 批量增加数据
 /// </summary>
 public void BatchDelete(List<int> list, SqlTransaction trans)
 {
     foreach (int i in list.ToArray())
     {
         Delete(i, trans);
     }
 }

 /// <summary>
 /// 查找不存在的文件并删除已删除的附件及数据
 /// </summary>
 public void DeleteList(SqlConnection conn, SqlTransaction trans, List<Model.download_attach> models, int article_id)
 {
     StringBuilder idList = new StringBuilder();
     if (models != null)
     {
         foreach (Model.download_attach modelt in models)
         {
             if (modelt.id > 0)
             {
                 idList.Append(modelt.id + ",");
             }
         }
     }
     string id_list = Utils.DelLastChar(idList.ToString(), ",");
     StringBuilder strSql = new StringBuilder();
     strSql.Append("select id,file_path from dt_download_attach where article_id=" + article_id);
     if (!string.IsNullOrEmpty(id_list))
     {
         strSql.Append(" and id not in(" + id_list + ")");
     }
     DataSet ds = DbHelperSQL.Query(conn, trans, strSql.ToString());
     foreach (DataRow dr in ds.Tables[0].Rows)
     {
         int rows = DbHelperSQL.ExecuteSql(conn, trans, "delete from dt_download_attach where id=" + dr["id"].ToString()); //删除数据库
         if (rows > 0)
         {
             Utils.DeleteFile(dr["file_path"].ToString()); //删除文件
         }
     }
 }

 /// <summary>
 /// ²éÕÒ²»´æÔÚµÄͼƬ²¢É¾³ýÒÑɾ³ýµÄͼƬ¼°Êý¾Ý
 /// </summary>
 public void DeleteList(SqlConnection conn, SqlTransaction trans, List<Model.article_albums> models, int article_id)
 {
     StringBuilder idList = new StringBuilder();
     if (models != null)
     {
         foreach (Model.article_albums modelt in models)
         {
             if (modelt.id > 0)
             {
                 idList.Append(modelt.id + ",");
             }
         }
     }
     string id_list = Utils.DelLastChar(idList.ToString(), ",");
     StringBuilder strSql = new StringBuilder();
     strSql.Append("select id,thumb_path,original_path from " + databaseprefix + "article_albums where article_id=" + article_id);
     if (!string.IsNullOrEmpty(id_list))
     {
         strSql.Append(" and id not in(" + id_list + ")");
     }
     DataSet ds = DbHelperSQL.Query(conn, trans, strSql.ToString());
     foreach (DataRow dr in ds.Tables[0].Rows)
     {
         int rows = DbHelperSQL.ExecuteSql(conn, trans, "delete from " + databaseprefix + "article_albums where id=" + dr["id"].ToString()); //ɾ³ýÊý¾Ý¿â
         if (rows > 0)
         {
             Utils.DeleteFile(dr["thumb_path"].ToString()); //ɾ³ýËõÂÔͼ
             Utils.DeleteFile(dr["original_path"].ToString()); //ɾ³ýԭͼ
         }
     }
 }