C# SqlHelper.ExecuteScalar方法代码示例

 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);
 }

        /// <summary>
        /// 列表数据获取
        /// </summary>
        /// <param name="page"></param>
        /// <param name="sid"></param>
        /// <returns></returns>
        public List<ProInvoiceModel> DataGet(int page,QueryModel queryModel)
        {
            //分页助手
            PageHelper parameter = GetPageParameterInfo();
            //ViewBag.SearchModel = model;
            ViewBag.PageParameter = parameter;
            string sid = queryModel.Sid;
            DataTable dt = new DataTable();
            List<ProInvoiceModel> list = new List<ProInvoiceModel>();
            using (SqlHelper sqlhelper = new SqlHelper())
            {
                string sql = "select * from proinvoice where 1=1 ";
                if (!string.IsNullOrWhiteSpace(sid)) sql += " and sid like '" + sid + "%'";
                if (!string.IsNullOrWhiteSpace(queryModel.Randnum)) sql += " and randnum like '" + queryModel.Randnum + "%'";
                parameter.SqlScript =sql;
                parameter.OrderBy = string.IsNullOrWhiteSpace(parameter.OrderBy) ? " AddTime desc " : parameter.OrderBy;
                parameter.RecordCount =
                    TypeParse.StrToInt(
                        sqlhelper.ExecuteScalar(string.Format("select count(1) from {0}", parameter.SqlScript)), 0);
                dt = sqlhelper.ExecuteSql(parameter.ToString());
                if (dt == null) return null;
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        ProInvoiceModel model = new ProInvoiceModel();
                        model.SID = (int) dt.Rows[i]["sid"];
                        model.ReceiveMan = dt.Rows[i]["receiveman"].ToString();
                        model.AddTime = (DateTime) dt.Rows[i]["addtime"];
                        model.BusinessId = (int) dt.Rows[i]["BusinessId"];
                        model.InvoiceTypeId = (int) dt.Rows[i]["InvoiceTypeId"];
                        model.Randnum = dt.Rows[i]["Randnum"].ToString();
                        model.Randint = (int) dt.Rows[i]["Randint"];
                        list.Add(model);
                    }
                }

            }
            return list;
        }

 /// <summary>
 /// 列表数据获取
 /// </summary>
 /// <param name="page"></param>
 /// <param name="sid"></param>
 /// <returns></returns>
 public List<ViewModel> DataGet(int page)
 {
     //分页助手
     PageHelper parameter = GetPageParameterInfo();
     ViewBag.PageParameter = parameter;
     DataTable dt = new DataTable();
     List<ViewModel> list = new List<ViewModel>();
     using (SqlHelper sqlhelper = new SqlHelper())
     {
         string sql = "select * from blogs";
         parameter.SqlScript = sql;
         parameter.OrderBy = " createtime desc ";
         parameter.RecordCount =
             TypeParse.StrToInt(
                 sqlhelper.ExecuteScalar(string.Format("select count(1) from {0}", parameter.SqlScript)), 0);
         dt = sqlhelper.ExecuteSql(parameter.ToString());
         if (dt == null) return null;
         if (dt.Rows.Count > 0)
         {
             for (int i = 0; i < dt.Rows.Count; i++)
             {
                 ViewModel model = new ViewModel();
                 model.title = dt.Rows[i]["title"].ToString();
                 model.content = dt.Rows[i]["briefcontent"].ToString();
                 model.url = dt.Rows[i]["titleurl"].ToString();
                 model.CreateTime = (DateTime)dt.Rows[i]["createtime"];
                 model.name = dt.Rows[i]["name"].ToString();
                 model.bloggerurl = dt.Rows[i]["bloggerurl"].ToString();
                 model.pinglun_url = dt.Rows[i]["pinglun_url"].ToString();
                 int pinglunNum, readernum;
                 int.TryParse(dt.Rows[i]["pinglunNum"].ToString(), out pinglunNum);
                 model.pinglunNum = pinglunNum;
                 int.TryParse(dt.Rows[i]["readerCount"].ToString(), out readernum);
                 model.readerCount = readernum;
                 list.Add(model);
             }
         }
     }
     return list;
 }

 /// <summary>
 /// 分页
 /// </summary>
 /// <param name="page"></param>
 public void PageGet(int page)
 {
     //分页助手
     PageHelper parameter = GetPageParameterInfo();
     using (SqlHelper sqlhelper = new SqlHelper())
     {
         string sql = "select * from blogs ";
         parameter.SqlScript = sql;
         parameter.RecordCount =TypeParse.StrToInt(sqlhelper.ExecuteScalar(string.Format("select count(1) from {0}", parameter.SqlScript)), 0);
     }
     ViewBag.PageParameter = parameter;
 }

 public bool GetBookByID(int bookID, int status)
 {
     SqlHelper objSqlHelper = new SqlHelper();
     SqlParameter[] objParams = new SqlParameter[2];
     objParams[0] = new SqlParameter("@bookID", SqlDbType.Int);
     objParams[0].Value = bookID;
     objParams[1] = new SqlParameter("@status", SqlDbType.Int);
     objParams[1].Value = status;
     int bookCount = (int)objSqlHelper.ExecuteScalar("je_Order_GetBookByID", objParams);
     
     bool isHave=true;
     if(bookCount==0)
     {
         isHave = false;
     }
     return isHave;
 }

        public string GetImpersonatedLoginFor(string login)
        {
            if (!_settings.IsImpersonationEnabled)
            {
                return null;
            }

            var sql = new SqlHelper("SELECT p.login_name FROM table_user u, table_user p WHERE u.login_name = {0} AND p.objid = u.user2proxy_user");
            sql.Parameters.Add("login", login);
            var result = sql.ExecuteScalar();

            if (result == null || result == DBNull.Value) return null;

            return Convert.ToString(result);
        }

 /// <summary>
 /// 分页数据获取
 /// </summary>
 /// <param name="page"></param>
 /// <param name="sid"></param>
 public void PageGet(int page,QueryModel model)
 {
     //分页助手
     PageHelper parameter = GetPageParameterInfo();
     ViewBag.PageParameter = parameter;
     string sid = model.Sid;
     DataTable dt = new DataTable();
     List<ProInvoiceModel> list = new List<ProInvoiceModel>();
     using (SqlHelper sqlhelper = new SqlHelper())
     {
         string sql = "select * from proinvoice";
         if (!string.IsNullOrWhiteSpace(sid)) sql += " where sid like '" + sid + "%'";
         if (!string.IsNullOrWhiteSpace(model.Randnum)) sql += " and randnum like '" + model.Randnum + "%'";
         parameter.SqlScript = sql;
         parameter.OrderBy = string.IsNullOrWhiteSpace(parameter.OrderBy) ? " AddTime desc " : parameter.OrderBy;
         parameter.RecordCount =
             TypeParse.StrToInt(
                 sqlhelper.ExecuteScalar(string.Format("select count(1) from {0}", parameter.SqlScript)), 0);
     }
 }