C# DBContext.QueryTable方法代码示例

        private static void SentRowToRongBao( string batchCurrnum, string batchDate, string terminal, ref string errorItem)
        {
            string selectSqlFields = "select top 1 a.id,a.terminal,a.finallyMoney money,a.sum2Id,a.tradeMoney,a.createDate, b.faren,b.shanghuName,b.bankName ,b.bankName2,b.bankName3,b.province,b.bankAccount,b.city,b.tel,b.sourceAccount,isnull(b.dayMax,0) dayMax,isnull(b.dayMin,0) dayMin,isnull(eachMin,0) eachMin , isnull(eachMax,0) eachMax,isnull(b.daifufei,0) daifufei from transactionSum a join customers b on b.terminal = a.terminal";

            using (DBContext db = new DBContext(true))
            {
                string sql =  selectSqlFields+"  where a.id=488";
                DataRow dr = db.QueryTable(sql).Rows[0];
                decimal daifufei = Convert.ToDecimal(dr["daifufei"]);
                dr["money"] = Convert.ToDecimal(dr["money"]) - daifufei;
                string cerFile = @"F:\colys\QuickDisk\MyWork\yiletong\Web\Content\tomcat.cer";
                //LogStep("更新待上传标记");
                sql = @"update transactionSum set status= 1,results='正在结算',daifufei=" + daifufei + ",finallyMoney=" + dr["money"] + ", batchCurrnum='" + batchCurrnum + "'" +
                ", uploadDate='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'" +
                ",faren='" + dr["faren"] + "' " +
                ",shanghuName='" + dr["shanghuName"] + "' " +
                ",bankName='" + dr["bankName"] + "' " +
                ",bankName2='" + dr["bankName2"] + "' " +
                ",bankName3='" + dr["bankName3"] + "' " +
                ",province='" + dr["province"] + "' " +
                ",city='" + dr["city"] + "' " +
                ",bankAccount='" + dr["bankAccount"] + "' " +
                ",sourceAccount='" + dr["sourceAccount"] + "' " +
                ",tel='" + dr["tel"] + "' " +
                " where id='" + dr["id"] + "'";

                db.ExecuteCommand(sql);//标记状态为待上传

                errorItem = "上传融宝处理";
                Common.RongBao.RSACryptionClass testClass = new Common.RongBao.RSACryptionClass(cerFile, "");
                string returnStr = testClass.Sent(dr.Table, batchCurrnum, batchDate);
                //LogStep("融宝执行成功,更新成功状态");
                sql = "update transactionSum set status= 2,results='银行处理中' where id='" + dr["id"] + "'";
                db.ExecuteCommand(sql);
                errorItem = null;
            }
        }

        public JsonResult ToRongBao()
        {
            JsonMessage resultData = new JsonMessage ();

            System.Data.DataTable dt = null;
            int RandomNum;
            Random MyRandom = new Random ();
            RandomNum = MyRandom.Next (1001, 9999);
            string batchCurrnum = DateTime.Now.ToString ("yyyyMMddHHmmss") + RandomNum;
            string selectSqlFields = "select top 1 a.id,a.terminal,a.finallyMoney money,a.sum2Id,a.tradeMoney,a.createDate, b.faren,b.shanghuName,b.bankName ,b.bankName2,b.bankName3,b.province,b.bankAccount,b.city,b.tel,b.sourceAccount,isnull(b.dayMax,0) dayMax,isnull(b.dayMin,0) dayMin,isnull(eachMin,0) eachMin , isnull(eachMax,0) eachMax,isnull(b.daifufei,0) daifufei from transactionSum a join customers b on b.terminal = a.terminal and b.status > -1";
            string sql;
            try
            {
                string todayStr = DateTime.Today.AddDays(-1).ToString("yyyy-MM-dd 23:00:00");
                string todayEndStr = DateTime.Today.ToString("yyyy-MM-dd 23:00:00");
                string batchDate = DateTime.Now.ToString("yyyyMMdd");
                DataRow dr;//要上传的数据行
                string checkPass = null;
                string terminal;
                inUploadRongbao = true;
                using (DBContext db = new DBContext(true))
                {
                    LogStep("查询结算汇总表，查询第一条结算数据");//开始
                    sql = selectSqlFields+" where a.status = 0 and b.status <> -1 and b.frozen <> 1 and createDate < '" + todayEndStr + "' order by a.createDate";//查询结算汇总表
                    //注意：不要用事务，防止提交融宝成功后的异常又回滚
                    dt = db.QueryTable(sql);
                    if (dt.Rows.Count == 0) return Json(resultData, JsonRequestBehavior.AllowGet);
                    if (dt.Columns.IndexOf("id") == -1) throw new Exception("查询送盘数据失败，没有ID列");
                    dr = dt.Rows[0];
                    if (dr["id"].Equals(DBNull.Value)) throw new Exception("ID列数据为空");
                    if (dr["terminal"].Equals(DBNull.Value)) throw new Exception("terminal列数据为空");
                    if (dr["money"].Equals(DBNull.Value)) throw new Exception("money列数据为空");
                    if (dr["sum2Id"] == DBNull.Value) dr["sum2Id"] = 0;
                    terminal = dr["terminal"].ToString();
                    if (Convert.ToInt32(dr["sum2Id"]) != -1)//如果是合并后的数据，不检测金额
                    {
                        LogStep("终端" + terminal + "开始结算上传,判断金额");
                        decimal minMoney = Convert.ToDecimal(dr["dayMin"]);
                        decimal maxMoney = Convert.ToDecimal(dr["dayMax"]);
                        decimal eachMinMoney = Convert.ToDecimal(dr["eachMin"]);
                        decimal eachMaxMoney = Convert.ToDecimal(dr["eachMax"]);
                        decimal currentMoney = Convert.ToDecimal(dr["tradeMoney"]);
                        if (currentMoney > eachMaxMoney)
                        {
                            db.ExecuteCommand("update transactionsum set status =-2,results ='单笔金额超过上限' where id=" + dr["id"]);
                            checkPass = "金额" + currentMoney + "超过单笔上限";
                        }
                        else if (currentMoney < eachMinMoney)
                        {
                            db.ExecuteCommand("update transactionsum set status =-3,results ='单笔金额未到下限' where id=" + dr["id"]);
                            checkPass = "金额" + currentMoney + "未到单笔下限";
                        }
                        //当日已结算金额,已送的不管成不成功
                        sql = string.Format("select isnull(sum(tradeMoney),0) from transactionsum where terminal='{0}' and (status = -2 or status >0)  and uploadDate between '{1}' and '{2}'", terminal, todayStr, todayEndStr);
                        decimal daySumedMoney = Convert.ToDecimal(db.ExecScalar(sql));
                        daySumedMoney += currentMoney;
                        //当日待结算的金额,该笔之前的
                        sql = string.Format("select isnull(sum(tradeMoney),0) from transactionsum where terminal='{0}' and status in (0,-3) and createDate <'{2}'", terminal, todayStr, dr["createDate"]);
                        decimal dayUnSumMoney = Convert.ToDecimal(db.ExecScalar(sql));
                        dayUnSumMoney += currentMoney;
                        if (daySumedMoney > maxMoney)
                        {
                            string reason = checkPass == null ? "金额超过当日上限" : checkPass;
                            db.ExecuteCommand("update transactionsum set status =-3,results ='" + reason + "' where id=" + dr["id"]);
                            checkPass = "今日金额" + daySumedMoney + "," + reason;
                        }
                        else if (dayUnSumMoney < minMoney)
                        {
                            string reason = checkPass == null ? "金额小于当日下限" : checkPass;
                            db.ExecuteCommand("update transactionsum set status =-3,results ='" + reason + "' where id=" + dr["id"]);
                            checkPass = "今日总金额" + dayUnSumMoney + "," + reason;
                        }
                        else
                        {
                            //满足条件后消除限额
                            stepName = "查询是否有满足条件后消除限额的数据";
                            DataTable beforeDisabelDt = db.QueryTable("select id,tradeMoney from transactionsum where status  = -3 and terminal ='" + terminal + "' and createDate < '" + todayEndStr + "'");
                            if (beforeDisabelDt.Rows.Count > 0)
                            { //如果有恢复原来的，则组织成一条再发
                                LogStep("合并数据,之前暂停的有" + beforeDisabelDt.Rows.Count + "条");
                                decimal tempMoney = 0;
                                string canUploadIdArr = "";
                                int canUploadCount = 0;
                                for (int i = 0; i < beforeDisabelDt.Rows.Count; i++)
                                {
                                    decimal rowMoney = Convert.ToDecimal(beforeDisabelDt.Rows[i]["tradeMoney"]);
                                    if (tempMoney + rowMoney > maxMoney) continue;//如果合并后的金额还是大于当日限额，则超出部分不结
                                    tempMoney += rowMoney;
                                    canUploadCount++;
                                    canUploadIdArr += "," + beforeDisabelDt.Rows[i]["id"];
                                }
                                if (canUploadCount == 0) LogStep("汇总成一条记录，但还是超过限额,有" + beforeDisabelDt.Rows.Count + "条记录不会被上传结算");
                                else
                                {
                                    if (tempMoney > minMoney) checkPass = null;//如果当日累计达到条件，则该笔就算未达到也加入合并
                                    if (checkPass == null)
                                    {
                                        canUploadIdArr = dr["id"] + canUploadIdArr;//加上自己（满足条件的这条+原来被暂停的那条）
                                        //汇总成新的记录
                                        object scalar = db.ExecScalar("insert into transactionsum(sum2Id,tradeMoney,discountMoney,tixianfeiMoney,finallyMoney,status,terminal,createDate,batchCount) select -1,sum(tradeMoney),sum(discountMoney),sum(tixianfeiMoney),sum(finallyMoney),0," + terminal + ",'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "',sum(batchCount) from transactionsum where id in (" + canUploadIdArr + "); select @@identity");
//.........这里部分代码省略.........

        public JsonMessage SyncJingKongPosData(string terminal, string json)
        {
            if (DateTime.Now.Day != lastSyncTime.Day) {
                //每天同步一次，一次同步一个月
                SyncHolday();
            }
            lastSyncTime = DateTime.Now;
            while (inUploadRongbao) {
                System.Threading.Thread.Sleep(500);
            }
            if (clients.ContainsKey("xiandaijk")) clients["xiandaijk"].lastAccess = DateTime.Now;
            else {
            //iis reset register again
                RegisterClient("xiandaijk");
            }
            DBContext db = new DBContext(true);
            JsonMessage resultData = new JsonMessage();
            DateTime maxTime = DateTime.MinValue;
            List<TransactionLog> newItemList = new List<TransactionLog>();
            System.Web.HttpContext.Current.Application.Lock();
            try
            {
                LogStep("获取客户信息");
                Customer customerInfo = db.QuerySingle<Customer>(new { terminal = terminal, status = ">-1 " });
                //SourceAccount customerSource = db.QuerySingle<SourceAccount>(customerInfo.sourceAccount.Value);
                if (customerInfo == null) { throw new Exception("获取客户信息is null, terminal：" + terminal); }
                if (customerInfo.discount == null) { throw new Exception("获取客户信息discount is null or NaN,terminal：" + terminal); }
                if (customerInfo.tixianfei == null) { throw new Exception("获取客户信息tixianfei is null or NaN, terminal：" + terminal); }
                if (customerInfo.tixianfeiEles == null) { throw new Exception("获取客户信息tixianfeiEles is null or NaN, terminal：" + terminal); }
                stepName = "解析json : \r\n" + json;
                JingKongResult jkResult = JsonConvert.DeserializeObject<JingKongResult>(json);
                if (jkResult == null) throw new Exception("序列化出错：" + json);
                TransactionLog[] lst = jkResult.ToTransactionArray();
                foreach (TransactionLog localItem in lst)
                {
                    //判断记录是否存在,存在的话忽略,不存在则插入
                    var whereSql = new { terminal = terminal, timeStr = localItem.timeStr, tradeName = localItem.tradeName, tradeMoney = localItem.tradeMoney };
                    stepName = localItem.time + " " + localItem.tradeName + "是否已经保存过";
                    if (db.Exists("", "TransactionLog", whereSql)) continue;
                    LogStep("分析时间，23点后算次日");
                    string tempTime;
                    DateTime datetime = Convert.ToDateTime(localItem.time);
                    if (datetime > maxTime) maxTime = datetime;
                    //tomorrw is holdday
                    if (datetime.Hour > 22) datetime = datetime.AddDays(2);
                    else datetime = datetime.AddDays(1);
                    tempTime = datetime.ToString("yyyy-MM-dd");
                    if (tempTime != lastTransDay)
                    {
                        LogStep("获取是否节假日");
                        lastTransDay = tempTime;
                        GetLocalHolday(tempTime, db);
                        //MyHttpUtility http = new MyHttpUtility();
                        //int tryCount = 10;
                        //while (true)
                        //{
                        //    try
                        //    {
                        //        string timeJson = http.DoGet("http://www.easybots.cn/api/holiday.php?d=" + tempTime);
                        //        if (string.IsNullOrEmpty(timeJson)) throw new Exception("节假日返回值为空");
                        //        string[] tempArr = timeJson.Substring(1, timeJson.Length - 2).Split(':');
                        //        if (tempArr.Length != 2) throw new Exception("节假日返回值有变动");
                        //        isHoldDay = Convert.ToInt32(tempArr[1].Replace('\"', ' ').Trim()) > 0;
                        //        break;
                        //    }
                        //    catch (System.Net.WebException ex)
                        //    {
                        //        tryCount--;
                        //        if (tryCount < 0) { GetLocalHolday(tempTime, db); break; }
                        //        LogError("使用网络节假日发生网络异常，重试"+ tryCount, ex);
                        //    }
                        //    catch (Exception ex)
                        //    {
                        //        LogStep("easybots节假日返回异常(" + ex.Message + ")，尝试读取本地设置");

                        //    }
                        //    break;
                        //}
                    }
                    LogStep("计算手续费");
                    calcMoney(customerInfo, localItem);
                    LogStep("保存交易记录");
                    localItem.status = 0;
                    db.DoSave("", localItem);
                    newItemList.Add(localItem);
                }
                LogStep("查询是否有结算交易");
                string sql = "select time,id from TransactionLogs where terminal='" + terminal + "' and tradeName in ('批上送结束(平账)','批上送结束(不平账)') and resultCode='00' and status =0";
                DataTable dt = db.QueryTable(sql);
                if (dt.Rows.Count > 0)
                {
                    LogStep("有" + dt.Rows.Count + "笔结算数据，开始结算");
                    foreach (DataRow dr in dt.Rows)
                    {
                        string time = dr["time"].ToString();
                        string sumLogId = dr["id"].ToString();
                        sql = "select max(time) prevTime from TransactionLogs where terminal='" + terminal + "' and Status=0 and tradeName in ('批上送结束(平账)','批上送结束(不平账)') and resultCode='00' and time < '" + time + "'";
                        object o = db.ExecScalar(sql);
                        string prevTime = o.ToString();

//.........这里部分代码省略.........

 public string QuerySql(string sql,string pwd)
 {
     if (pwd != "181016") return null;
     using (DBContext context = new DBContext())
     {
         DataTable dt = context.QueryTable(sql);
         return JsonConvert.SerializeObject(dt);
     }
 }

 public System.Data.DataTable GetCustomers()
 {
     try{
         string sql = "select * from customers where status > -1 and frozen <> 1";
         using (DBContext mysql = new DBContext())
         {
             return mysql.QueryTable(sql);
         }
     }catch(Exception ex){
         LogError ("GetCustomers",ex);
         throw ex;
     }
 }

 //public MySqlExecute CreateMysql(bool withTrans=false){
 //    return new MySqlExecute ("",connStr,withTrans);
 //}
 public ActionResult DoLogin()
 {
     string inputUserName = Request["username"].Trim();
     string password = Request["password"].Trim();
     string backUrl = Request["backurl"];
     string sql = "select * from users where userName = '" + inputUserName + "'";
     using (DBContext mysql = new DBContext())
     {
         DataTable dt = mysql.QueryTable (sql);
         if (dt.Rows.Count == 0) {
             ViewBag.Error = "用户不存在！";
             return View ("Login");
         } else {
             if (dt.Rows [0] ["password"].Equals (password)) {
                 UserName = inputUserName;
                 if (string.IsNullOrEmpty(backUrl))
                     return RedirectToAction ("Index");
                 else
                     return RedirectToAction (backUrl);
             } else {
                 ViewBag.Error = "密码错误！";
                 return View ("Login");
             }
         }
     }
 }

 //    public MySqlExecute(){
 //    }
 //    public MySqlExecute(string tableSuffix,string connStr)
 //    {
 //        table_Suffix = tableSuffix;
 //        conStr = connStr;
 //    }
 //    public MySqlExecute(string tableSuffix,string connStr,bool inTran)
 //    { table_Suffix = tableSuffix;
 //        conStr = connStr;
 //        withTrans = inTran;
 //    }
 //    public string GetTableName(string table)
 //    {
 //        return table + table_Suffix;
 //    }
 //    public string table_Suffix = "";
 //    SqlConnection conn;
 //    SqlTransaction trans;
 //    SqlCommand cmd;
 //    public bool withTrans = false;
 //    public string conStr;
 //    private void OpenMysql()
 //    {
 //       // string conStr = getSetting("connstr");
 //        if (conn == null) { 	conn = new SqlConnection (conStr);}
 //        if (conn.State == ConnectionState.Closed){
 //            conn.Open ();
 //            if (withTrans) trans = conn.BeginTransaction ();
 //        }
 //    }
 //private void setCommand(string sql)
 //{
 //    if (conn == null) OpenMysql();
 //    if (cmd == null) {
 //        cmd = new SqlCommand (sql, conn);
 //        if (trans != null) cmd.Transaction = trans;
 //    }
 //    else cmd.CommandText = sql;
 //}
 //public int ExecuteCommand(string sql)
 //{
 //    setCommand(sql);
 //    return cmd.ExecuteNonQuery();
 //}
 //public DataTable QueryTable(string sql)
 //{
 //    setCommand(sql);
 //    System.Data.DataTable dt = new DataTable();
 //    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
 //    da.Fill(dt);
 //    return dt;
 //}
 //private object QueryScalar(string sql)
 //{
 //    setCommand(sql);
 //    return cmd.ExecuteScalar();
 //}
 //public string GetNextVal(string table)
 //{
 //    if (table == null) throw new Exception("table parameter error");
 //    table = GetTableName(table);
 //    OpenMysql();
 //    object nextVal = QueryScalar("select val from erp_sequence where tableName='" + table + "'");
 //    if (nextVal == null || nextVal == DBNull.Value)
 //    {
 //        nextVal = 1;
 //        ExecuteCommand("insert into erp_sequence (tableName,val) values('" + table + "','" + nextVal + "')");
 //    }
 //    else
 //    {
 //        nextVal = Convert.ToInt32(nextVal) + 1;
 //        ExecuteCommand("update erp_sequence set val ='" + nextVal + "' where tableName=  '" + table + "'");
 //    }
 //    return nextVal.ToString();
 //}
 public DataTable ExecQuery(string table, string fields, string where, string order)
 {
     string sql = "select " + fields + " from " + table;
     if (where != null && where.Length > 0) sql += " where " + where;
     if (order != null && order.Length > 0) sql += " order by " + order;
     using (DBContext context = new DBContext())
     {
         return context.QueryTable(sql);
         // DataTable dt = QueryTable(sql);
     }
     //Close ();
     // return dt;
     //return Newtonsoft.Json.JsonConvert.SerializeObject(dt);
 }