C# SqlHelper.ExecuteNonQuery方法代码示例

 public void Import()
 {
     var mySqlHelper = new MySqlHelper(MySql);
     var sqlHelper = new SqlHelper(SqlServer);
     var mysql = string.Format("select * from qdt_user");
     var dt = mySqlHelper.ExecuteDataTable(mysql);
     var sql = "";
     var n = 0;
     for (var i = 0; i < dt.Rows.Count; i++)
     {
         sql += "INSERT INTO qdt_user (id,user_name,user_pwd,sex,create_time,update_time,login_time,login_ip,group_id,is_effect," +
                "is_delete,email,email_passed,real_name,idno,idcard_passed,mobile,mobile_passed,pay_pwd,pay_pwd_passed," +
                "is_lender,is_borrower,money,lock_money,point,quota,pid) " +
                "VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}');\r\n"
                .FormatWith(dt.Rows[i]["id"], dt.Rows[i]["user_name"], dt.Rows[i]["user_pwd"].ToString().ToUpper(), dt.Rows[i]["sex"], GetTime(dt.Rows[i]["create_time"]), GetTime(dt.Rows[i]["update_time"]), GetTime(dt.Rows[i]["login_time"]), dt.Rows[i]["login_ip"], dt.Rows[i]["group_id"], dt.Rows[i]["is_effect"],
                dt.Rows[i]["is_delete"], dt.Rows[i]["email"], true, dt.Rows[i]["real_name"], dt.Rows[i]["idno"], dt.Rows[i]["idcardpassed"], dt.Rows[i]["mobile"], dt.Rows[i]["mobilepassed"], dt.Rows[i]["pay_pwd"].ToString().ToUpper(), !string.IsNullOrWhiteSpace(dt.Rows[i]["pay_pwd"].ToString()),
                !dt.Rows[i]["group_id"].Equals(2), dt.Rows[i]["group_id"].Equals(2), dt.Rows[i]["money"], dt.Rows[i]["lock_money"], dt.Rows[i]["point"], dt.Rows[i]["quota"], dt.Rows[i]["pid"]).Replace("'NULL'", "NULL");
         if ((i + 1) % 30 != 0) continue;
         n += sqlHelper.ExecuteNonQuery(sql);
         sql = "";
     }
     if (!string.IsNullOrWhiteSpace(sql))
     n += sqlHelper.ExecuteNonQuery(sql);
     Console.WriteLine("成功导入user表：" + n + "条数据！");
 }

 public void Import()
 {
     var mySqlHelper = new MySqlHelper(MySql);
     var sqlHelper = new SqlHelper(SqlServer);
     var mysql = string.Format("select * from qdt_deal");
     var dt = mySqlHelper.ExecuteDataTable(mysql);
     var sql = "";
     var n = 0;
     for (var i = 0; i < dt.Rows.Count; i++)
     {
         sql += "INSERT INTO qdt_deal (id,name,sub_name,cate_id,user_id,description,is_effect,is_delete,borrow_amount,min_loan_money," +
                "repay_time,rate,create_time,update_time,is_recommend,buy_count,load_money,repay_money,start_time,success_time," +
                "repay_start_time,last_repay_time,next_repay_time,bad_time,deal_status,enddate,services_fee,is_send_bad_msg,bad_msg,loantype," +
                "warrant,titlecolor,is_send_contract,max_loan_money,icon,repay_time_type) " +
                "VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}','{33}','{34}','{35}');\r\n"
                .FormatWith(dt.Rows[i]["id"], dt.Rows[i]["name"], dt.Rows[i]["sub_name"], dt.Rows[i]["cate_id"], dt.Rows[i]["user_id"], dt.Rows[i]["description"].ToString().Replace('\'', '\"').Replace("./help/id-", "/Help/Index/").Replace("?", ""), dt.Rows[i]["is_effect"], dt.Rows[i]["is_delete"], dt.Rows[i]["borrow_amount"], dt.Rows[i]["min_loan_money"],
                dt.Rows[i]["repay_time"], dt.Rows[i]["rate"], GetTime(dt.Rows[i]["create_time"]), GetTime(dt.Rows[i]["update_time"]), dt.Rows[i]["is_recommend"], dt.Rows[i]["buy_count"], dt.Rows[i]["load_money"], dt.Rows[i]["repay_money"], GetTime(dt.Rows[i]["start_time"]), GetTime(dt.Rows[i]["success_time"]),
                GetTime(dt.Rows[i]["repay_start_time"]), GetTime(dt.Rows[i]["last_repay_time"]), GetTime(dt.Rows[i]["next_repay_time"]), GetTime(dt.Rows[i]["bad_time"]), dt.Rows[i]["deal_status"], dt.Rows[i]["enddate"], dt.Rows[i]["services_fee"], dt.Rows[i]["is_send_bad_msg"], dt.Rows[i]["bad_msg"], dt.Rows[i]["loantype"],
                dt.Rows[i]["warrant"], dt.Rows[i]["titlecolor"], dt.Rows[i]["is_send_contract"], dt.Rows[i]["max_loan_money"], dt.Rows[i]["icon"], dt.Rows[i]["repay_time_type"]).Replace("'NULL'","NULL");
         if ((i + 1) % 10 != 0) continue;
         n += sqlHelper.ExecuteNonQuery(sql);
         sql = "";
     }
     if (!string.IsNullOrWhiteSpace(sql))
     n += sqlHelper.ExecuteNonQuery(sql);
     Console.WriteLine("成功导入deal表：" + n + "条数据！");
 }

 public void Run()
 {
     var sqlHelper = new SqlHelper(SqlServer);
     var money = sqlHelper.ExecuteScalar<decimal>("select SUM(ac.Money) from qdt_user as us, qdt_user_account as ac where us.UserAccountID=ac.Id and us.group_id='3';");
     var lockMoney = sqlHelper.ExecuteScalar<decimal>("select SUM(ac.LockMoney) from qdt_user as us, qdt_user_account as ac where us.UserAccountID=ac.Id and us.group_id='3';");
     var chargeMoney =
         sqlHelper.ExecuteScalar<decimal>(
             "select SUM(od.pay_amount) from qdt_user as us, qdt_charge_order as od where us.id=od.user_id and us.group_id='3' and od.update_time>='{0}' and od.update_time<'{1}';"
                 .FormatWith(StartTime, EndTime));
     var carryMoney =
         sqlHelper.ExecuteScalar<decimal>(
             "select SUM(ca.Money) from qdt_user as us, qdt_user_carry as ca where us.id=ca.user_id and us.group_id='3' and ca.Status='1' and ca.update_time>='{0}' and ca.update_time<'{1}';"
                 .FormatWith(StartTime, EndTime));
     var carryLock =
         sqlHelper.ExecuteScalar<decimal>(
             "select SUM(ca.Money) from qdt_user as us, qdt_user_carry as ca where us.id=ca.user_id and us.group_id='3' and ca.Status='0' and ca.Create_Time>='{0}' and ca.Create_Time<'{1}';"
                 .FormatWith(StartTime, EndTime));
     var investLock =
         sqlHelper.ExecuteScalar<decimal>(
             "select SUM(cr.Money) from qdt_user as us, qdt_credit as cr where us.id=cr.UserID and us.group_id='3' and AssignmentID='0' and (cr.Status='1' or cr.Status='2') and cr.CreateTime>='{0}' and cr.CreateTime<'{1}';"
                 .FormatWith(StartTime, EndTime));
     var repayMoney = sqlHelper.ExecuteScalar<decimal>(
         "SELECT SUM(re.RepayMoney) FROM qdt_credit_repayment as re, qdt_credit as cr, qdt_user as us where re.TrueRepayTime>='{0}' AND re.TrueRepayTime<'{1}' AND re.CreditId=cr.Id AND cr.UserID=us.id AND us.group_id='3';"
         .FormatWith(StartTime, EndTime));
     var insertSql =
         "insert into qdt_user_money (money,chargemoney,carrymoney,lockmoney,carrylock,investlock,repaymoney) values ('{0}','{1}','{2}','{3}','{4}','{5}',{6});"
             .FormatWith(money, chargeMoney, carryMoney, lockMoney, carryLock, investLock,repayMoney);
     sqlHelper.ExecuteNonQuery(insertSql);
 }

 public int DeleteDownClass(DownClassInfo downClass)
 {
     SqlHelper objSqlHelper = new SqlHelper();
     SqlParameter[] objParams = new SqlParameter[1];
     objParams[0] = new SqlParameter("@catID", downClass.ID);
     return objSqlHelper.ExecuteNonQuery("je_DC_DeleteDownClass", objParams);
 }

 public static void setActiveStatus(string userLogin, bool isActive)
 {
     var sql = new SqlHelper("update table_user set status = {0} where login_name = {1}");
     sql.Parameters.Add("isActive", isActive ? 1 : 0);
     sql.Parameters.Add("login", userLogin);
     sql.ExecuteNonQuery();
 }

 public static void createImpersonationFor(string impersonatingUserLogin, string userLoginBeingImpersonated)
 {
     var sql = new SqlHelper("update table_user set user2proxy_user = (select objid from table_user where login_name = {0}) where login_name = {1}");
     sql.Parameters.Add("login", userLoginBeingImpersonated);
     sql.Parameters.Add("proxylogin", impersonatingUserLogin);
     sql.ExecuteNonQuery();
 }

 public void InsertDownClass(DownClassInfo downClass)
 {
     SqlHelper objSqlHelper = new SqlHelper();
     SqlParameter[] objParams = new SqlParameter[1];
     objParams[0] = new SqlParameter("@catName", downClass.ClassName);
     objSqlHelper.ExecuteNonQuery("je_DC_InsertDownClass", objParams);
 }

 public int DeleteTitleCss(int titleCssID)
 {
     SqlHelper objSqlHelper = new SqlHelper();
     SqlParameter[] objParams = new SqlParameter[1];
     objParams[0] = new SqlParameter("@cssID", titleCssID);
     return objSqlHelper.ExecuteNonQuery("je_Tc_DeleteTitleCss", objParams);
 }

 public int DeleteHelp(int helpID)
 {
     SqlHelper objSqlHelper = new SqlHelper();
     SqlParameter[] objParams = new SqlParameter[1];
     objParams[0] = new SqlParameter("@helpID", helpID);
   return objSqlHelper.ExecuteNonQuery("je_Help_DeleteHelp", objParams);
 }

 public int DeleteCatena(int catenaID)
  {
      SqlHelper objSqlHelper = new SqlHelper();
      SqlParameter[] objParams = new SqlParameter[1];
      objParams[0] = new SqlParameter("@catenaID", catenaID);
      return objSqlHelper.ExecuteNonQuery("je_Ct_DeleteCatena", objParams);
  }

 public int DeleteFavBookByID(int bookID)
 {
     SqlHelper objSqlHelper = new SqlHelper();
     SqlParameter[] objParams = new SqlParameter[1];
     objParams[0] = new SqlParameter("@bookID", bookID);
     return objSqlHelper.ExecuteNonQuery("je_FBook_DeleteFavBooksByID", objParams);
 }

 public int DeleteGuestBookByID(int gbID)
 {
     SqlHelper objSqlHelper = new SqlHelper();
     SqlParameter[] objParams = new SqlParameter[1];
     objParams[0] = new SqlParameter("@gbID", gbID);
     return objSqlHelper.ExecuteNonQuery("je_Gb_DeleteGuestBookByID", objParams);
 }

 public static void setAllowProxy(string userLogin, bool canProxy)
 {
     var sql = new SqlHelper("update table_employee set allow_proxy = {0} where objid = (select objid from table_user where login_name = {1})");
     sql.Parameters.Add("canProxy", canProxy ? 1 : 0);
     sql.Parameters.Add("login", userLogin);
     sql.ExecuteNonQuery();
 }

 public int DeleteSend(int SendID)
 {
     SqlHelper objSqlHelper = new SqlHelper();
     SqlParameter[] objParams = new SqlParameter[1];
     objParams[0] = new SqlParameter("@sendID", SendID);
     return objSqlHelper.ExecuteNonQuery("je_Send_DeleteSend", objParams);
 }

 public int UpdateDownClass(DownClassInfo downClass)
 {
     SqlHelper objSqlHelper = new SqlHelper();
     SqlParameter[] objParams = new SqlParameter[2];
     objParams[0] = new SqlParameter("@catName", downClass.ClassName);
     objParams[1] = new SqlParameter("@catID", downClass.ID);
    return objSqlHelper.ExecuteNonQuery("je_DC_UpdateDownClass", objParams);
 }