C# DAL.RunSqlGetID方法代码示例

        /// <summary>
        /// 获取最近一小时站点信息
        /// </summary>
        /// <returns></returns>
        public static DataTable getOnetimegas()
        {
            DAL dal = new DAL();
            string gas = "";
            DataTable dt = new DataTable();
            dt.Columns.Add("站点");
            dt.Columns.Add("销售气量");
            dt.Columns.Add("当班班组");

            int n = 0;
            for (int i = 0; i < 2; i++)//循环3次,统计3个站点数据
            {
                DataRow row = dt.NewRow();
                n = i + 2;
                if (n == 2) { row["站点"] = "南高"; }
                if (n == 3) { row["站点"] = "西路"; }
                if (n == 4) { row["站点"] = "麻柳沱"; }
                gas = dal.RunSqlGetID("SELECT  sum([NowGas]) as 气量 FROM [CSERVER].[dbo].[tempGAS] where StationNO =" + n.ToString() + " and TradeDate > dateadd(hh,-1,getdate()) ");
                if (gas == null) { gas = "0"; }
                row["销售气量"] = gas;
                row["当班班组"] = dal.RunSqlGetID("SELECT top 1 [BZNO] FROM [CSERVER].[dbo].[DateSum] where StationNO=" + n.ToString() + " ORDER BY SxBTime DESC");
                dt.Rows.Add(row);
            }
            return dt;
        }

        /// <summary>
        /// 上一笔定购单
        /// </summary>
        /// <param name="context"></param>
        public void CG_last(HttpContext context)
        {
            string retext = "";
            string code = context.Request["code"];
            string SQL = "SELECT top 1 [单号]  FROM [CNGCL].[dbo].[CL_采购订单] order by 单号";
            DAL dal = new DAL();
            string min = dal.RunSqlGetID(SQL);

            if (code == min)
            {
                retext = "{\"err\":{\"ISok\":false,\"msg\":\"已经是第一笔订单!\"},\"data\":[],\"minxi\":{\"total\":0,\"rows\":[] }}";
                context.Response.Write(retext);
                return;
            }
            SQL = "SELECT top 1 [单号]  FROM [CNGCL].[dbo].[CL_采购订单] where 单号<'" + code + "' order by 单号 desc";
            code = dal.RunSqlGetID(SQL);
            retext = CG_json(code);
            context.Response.Write(retext);
        }

 protected void Page_Load(object sender, EventArgs e)
 {
     DAL dal = new DAL();
     string SQL = "SELECT TOP 1 convert(varchar(20),[时间],120) as 时间 FROM [CNGTZ].[dbo].[TZ_人员_考勤处理时间] ORDER BY 时间 DESC";
     string val = dal.RunSqlGetID(SQL);
     if (val == null)
     {
         val = "";
     }
     cxdate = val;
 }

 //通过 日期 找订单数据
 public void CG_date(HttpContext context)
 {
     string retext = "{\"err\":{\"ISok\":true,\"msg\":\"未找到订单数据!\"},\"data\":订单数据对象,\"minxi\":明细对象}";
     string date = context.Request["date"];
     string SQL = "SELECT TOP 1 [单号] FROM [CNGCL].[dbo].[CL_采购订单] where 制单日期>='" + date + "' ORDER BY 单号";
     DAL dal = new DAL();
     string code = dal.RunSqlGetID(SQL);
     if (code == null)
     {
         retext = "{\"err\":{\"ISok\":false,\"msg\":\"未找到订单数据!\"},\"data\":[],\"minxi\":{\"total\":0,\"rows\":[] }}";
         context.Response.Write(retext);
         return;
     }
     retext = CG_json(code);
     context.Response.Write(retext);
 }

        /// <summary>
        /// 验证用户签名
        /// </summary>
        /// <param name="user">用户名</param>
        /// <param name="pw">签名密码</param>
        /// <returns></returns>
        public static bool UserQianMing(string user, string pw)
        {
            //用参数传入方式,可以防SQL注入
            string sql = "SELECT [权限] FROM [WebApp].[dbo].[DL_用户名] where 用户名[email protected]参数一 and 签名密码[email protected]参数二";
            SqlParameter par1 = new SqlParameter("@参数一", user.Trim());
            SqlParameter par2 = new SqlParameter("@参数二", Sys.getMd5(pw.Trim()));

            SqlParameter[] par = { par1, par2 };
            DAL dal = new DAL();
               string qx = dal.RunSqlGetID(sql, par);
            if (qx == null)
            {
                qx = "";
                return (false);
            }
            else
            {
                return (true);
            }
        }

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";
            context.Response.Charset = "utf-8";

            var file = context.Request.Files["Filedata"];//文件 对象
            string userID = context.Request["userID"];//上传用户
            string user = context.Request["user"];//上传用户
            string imgname = context.Request["imgname"];//图片名
            string imgID = context.Request["imgID"];//相片ID 如果为0 表示 没有上传记录
            string savePaht = context.Request.PhysicalApplicationPath + "CNGTZ\\userimg\\" + imgname + ".jpg"; //略缩图储存路径
            string sourcePaht = context.Request.PhysicalApplicationPath + "CNGTZ\\userimg\\source\\" + imgname + ".jpg"; //原图储存路径
            //string xnpath =context.Request.Path;

            DAL dal = new DAL();
            if (file != null)
            {
                file.SaveAs(sourcePaht);
                ImageClass.MakeThumbnail(sourcePaht, savePaht, 100, 130, "HW");//生成略缩图

                FileItem fi = FileManager.GetItemInfo(savePaht);

                if (fi.Name.Length > 0)//判断略缩图是否创建完成
                {
                    string SQL1 = "", SQL2 = "", SQL3 = "";
                    if (imgID != "0")
                    {
                        SQL1 = "UPDATE [CNGTZ].[dbo].[TZ_图片]SET [类型] [email protected]类型,[图片] [email protected]图片,[备注] [email protected]备注,[上传用户] [email protected]上传用户,[所属用户] [email protected]所属用户,[更新时间] = getdate() WHERE ID=" + imgID;
                    }
                    else
                    {
                        SQL1 = @"INSERT INTO [CNGTZ].[dbo].[TZ_图片]([类型],[图片],[备注],[上传用户],[所属用户],[更新时间])VALUES(@类型,@图片,@备注,@上传用户,@所属用户,getdate())";
                        SQL2 = @"SELECT [ID] FROM [CNGTZ].[dbo].[TZ_图片]where 类型='人员' AND [所属用户]='" + imgname + "'";//查询是否已经上传过相片
                        SQL3 = @"UPDATE [CNGTZ].[dbo].[TZ_人员_基本信息] SET [相片ID] ={0}, 更新时间 = getdate() WHERE ID=" + userID;
                    }

                    try
                    {
                        dal.TranBegin();//开始事务
                        SqlParameter par1 = new SqlParameter("@类型", "人员");
                        SqlParameter par2 = new SqlParameter("@图片", savePaht);
                        SqlParameter par3 = new SqlParameter("@备注", "");
                        SqlParameter par4 = new SqlParameter("@上传用户", user);
                        SqlParameter par5 = new SqlParameter("@所属用户", imgname);

                        dal.ExeSql(SQL1, new SqlParameter[] { par1, par2, par3, par4, par5 });//执行语句

                        if (imgID == "0")
                        {
                            string ID = dal.RunSqlGetID(SQL2);
                            SQL3 = string.Format(SQL3, ID);
                            dal.ExeSql(SQL3);
                        }
                        dal.TranCommit();//提交事务
                        Sys.addSteps(user, "上传员工相片:[" + fi.Name + "]", "人员台账");
                        //下面这句代码缺少的话，上传成功后上传队列的显示不会自动消失
                        context.Response.Write("1");
                    }
                    catch (Exception)
                    {
                        dal.TranRollBack();//回滚事务
                        context.Response.Write("0");//数据库操作失败
                    }
                }
                else
                {
                    context.Response.Write("0"); //如果创建文件失败
                }
            }
            else
            {
                context.Response.Write("0");//上传文件失败
            }
        }

        /// <summary>
        /// 源图片获取
        /// </summary>
        /// <param name="context"></param>
        public void Img_getsource(HttpContext context)
        {
            string ID = context.Request["ID"];//图片ID

            DAL dal = new DAL();
            string fiPaht = dal.RunSqlGetID("SELECT [图片] FROM [CNGTZ].[dbo].[TZ_图片] where ID=" + ID);

            if (fiPaht == null)
            {
                context.Response.Write("对不起,服务器,没有找到此文件!");
                return;
            }
            fiPaht = fiPaht.Replace("userimg", "userimg\\source");
            if (System.IO.File.Exists(fiPaht) == false)
            {
                context.Response.Write("对不起,服务器,没有找到此文件!");
                return;
            }
            //string filename = "XX.jpg";
            Byte[] imgbyte = System.IO.File.ReadAllBytes(fiPaht);//这里只要 得到图片的字节流就可以，如：FileStream f=new FileStream(path);这方法也可以实现
            context.Response.ContentType = "image/jpeg";
            context.Response.BinaryWrite(imgbyte);
        }

        /// <summary>
        /// 新增员工
        /// </summary>
        /// <param name="context"></param>
        public void user_new(HttpContext context)
        {
            string name = context.Request["name"];
            name = name.Trim();
            string py = PY.GetTop(name);
            string SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员]([姓名],[拼音],[是否在职],[所属部门],[更新时间]) VALUES(@name,@拼音,1,'03',getdate())";
            DAL dal = new DAL();
            SqlParameter par1 = new SqlParameter("@name", name);
            SqlParameter par2 = new SqlParameter("@拼音", py);
            dal.RunSql(SQL, new SqlParameter[] { par1, par2 });

            if (dal.ExecuteRowCount == 1)
            {
                SQL = "SELECT [ID] FROM [CNGTZ].[dbo].[TZ_人员] where 姓名='" + name + "'";
                string ID = dal.RunSqlGetID(SQL);

                SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员_基本信息] ([ID],[更新时间])VALUES(" + ID + ",getdate())";
                dal.RunSql(SQL);

                SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员_档案1] ([ID],[更新时间])VALUES(" + ID + ",getdate())";
                dal.RunSql(SQL);

                SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员_档案2] ([ID],[更新时间])VALUES(" + ID + ",getdate())";
                dal.RunSql(SQL);

                context.Response.Write("true");
            }
            else
            {
                context.Response.Write("false");
            }
        }

        private string Chart_站点年度生产(string year ,string zd)
        {
            string retext = "{ \"ok\":true,\"msg\":\"\",\"sc1\":@替换对象[email protected],\"sc2\":@替换对象[email protected],\"sum\":@替换对象[email protected]  }";

            string SQL = @"SELECT sum(生产气量) as 生产气量,
                                            '输差率'=
                                            CASE  sum([购进数]+[修正值]) when 0 then 0
                                            else round((sum([购进数])+sum([修正值])-sum([生产气量])-sum([库存数]))/sum([购进数]+[修正值])*100,2) end FROM [CNGQT].[dbo].[CW_月度统计] where 年[email protected]年度@ and 站点[email protected]站点@  GROUP BY 月 order by 月";
            SQL = SQL.Replace("@年度@", year);
            SQL = SQL.Replace("@站点@", zd);

            DAL dal = new DAL();
            DataTable dt = dal.RunSqlDataTable(SQL);
            if (dt == null)
            {
                retext = retext.Replace(":true", ":false");

                return retext;
            }
            double[] sc1 = new double[12];
            double[] sc2 = new double[12];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sc1[i] = double.Parse(dt.Rows[i]["生产气量"].ToString());
                sc2[i] = double.Parse(dt.Rows[i]["输差率"].ToString());
            }
            SQL = "SELECT sum(生产气量) as 生产气量 FROM [CNGQT].[dbo].[CW_月度统计] where 年[email protected]年度@ and 站点[email protected]站点@";
            SQL = SQL.Replace("@年度@", year);
            SQL = SQL.Replace("@站点@", zd);
            string yearSum = dal.RunSqlGetID(SQL);

            string sc1Json = Json.Json_转换到json<double[]>(sc1);
            string sc2Json = Json.Json_转换到json<double[]>(sc2);
            retext = retext.Replace("@替换对象[email protected]", sc1Json);
            retext = retext.Replace("@替换对象[email protected]", sc2Json);
            retext = retext.Replace("@替换对象[email protected]", yearSum);

            return retext;
        }

        /// <summary>
        /// 根据 单号 查找出库数据 返回json对象
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        private string CK_json(string code)
        {
            string retext = "{\"err\":{\"ISok\":true,\"msg\":\"未找到单据数据!\"},\"data\":订单数据对象,\"minxi\":明细对象}";
            string SQL = "SELECT [ID],[单号],[仓库],[来往单位],[制单日期],[经手人],[总金额],[备注],[领料人],[审核人]FROM [CNGCL].[dbo].[CL_仓库单据] where 进出='出库' and 单据类型 ='物资出库' and 单号='" + code.Trim().ToUpper() + "'";
            DAL dal = new DAL();
            DataTable dt = dal.RunSqlDataTable(SQL);
            string temp = "";
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                string weixiu = dr["来往单位"].ToString();
                string data = "{{\"单号\":\"{0}\",\"仓库\":\"{1}\",\"出库分类\":\"{2}\",\"制单日期\":\"{3}\",\"经手人\":\"{4}\",\"总金额\":\"{5}\",\"备注\":\"{6}\",\"领料人\":\"{7}\",\"审核人\":\"{8}\",\"维修编号\":\"@维修编号@\" }}";
                temp = string.Format(data, dr["单号"].ToString(), dr["仓库"].ToString(), dr["来往单位"].ToString(), DateTime.Parse(dr["制单日期"].ToString()).ToString("yyyy-MM-dd"), dr["经手人"].ToString(), dr["总金额"].ToString(), dr["备注"].ToString().Replace("\r\n", "\\r\\n"), dr["领料人"].ToString(), dr["审核人"].ToString());
                retext = retext.Replace("订单数据对象", temp);

                if (weixiu == "维修维护")
                {
                    SQL = "SELECT [维修编号] FROM [CNGCL].[dbo].[CL_维修出库] where 出库单号='" + code.Trim().ToUpper() + "'";
                    weixiu = dal.RunSqlGetID(SQL);
                    retext = retext.Replace("@维修编号@", weixiu);
                }
                else
                {
                    retext = retext.Replace("@维修编号@", "");
                }

                SQL = "SELECT [ID],[物品名称],[物品编号],[分类名称],[规格] ,[单位],[数量],[单价],[小计],[批号],[备注] FROM [CNGCL].[dbo].[CL_仓库明细] where 单号='" + code.Trim().ToUpper() + "'";
                dt = dal.RunSqlDataTable(SQL);
                temp = "{\"total\":100,\"rows\":[] }";
                if (dt.Rows.Count > 0)
                {
                    temp = temp.Replace("[]", Json.TableToJson(dt));
                }
                retext = retext.Replace("明细对象", temp);
                return retext;
            }
            else
            {
                retext = "{\"err\":{\"ISok\":false,\"msg\":\"未找到单据数据!\"},\"data\":[],\"minxi\":{\"total\":0,\"rows\":[] }}";
                return retext;
            }
        }

        /// <summary>
        /// 检查数据是否上传
        /// </summary>
        /// <param name="context"></param>
        public void scDate(HttpContext context)
        {
            string t1 = context.Request["time"];
            string zd = context.Request["zd"];
            DAL dal = new DAL();
            string SQL = "SELECT top 1  convert(varchar(10), [日期], 120) as 日期  FROM [CNGQT].[dbo].[SC_班销售统计]where 站点=" + zd + " order by 日期 desc";
            string t2 = dal.RunSqlGetID(SQL);

            if (DateTime.Parse(t1) > DateTime.Parse(t2))
            {
                SQL = "最后上报数据日期为：【" + t2 + "】，统计数据可能不完整！<br>请确认是否要继续查询？";
            }
            else
            {
                SQL = "yes";
            }
            context.Response.Write(SQL);
        }

        /// <summary>
        /// 取站点区间数据
        /// </summary>
        /// <param name="context"></param>
        public void getZDreg(HttpContext context)
        {
            string temp = "";
            string text = "{\"err\":{\"ok\":false,\"mes\":\"\"}}";
            string retext = "{\"err\":{\"ok\":true,\"mes\":\"\"},\"CanShu\":返回参数对象,\"title\":\"头对象\",\"top1\":流量计对象,\"TingYe\":停业对象,\"gasFL\":销售分类对象,\"YaShoJi\":压缩机对象,\"YouLei\":油类对象,\"ShuiLei\":水类对象,\"DianLei\":电类对象,\"jizhang\":记账单位对象}";
            DAL dal = new DAL();
            DateTime dtstart = DateTime.Parse(context.Request["datestart"]); //起始日期
            DateTime dtend = DateTime.Parse(context.Request["dateend"]); //结束日期
            int day = (dtend - dtstart).Days + 1;

            int zd = Convert.ToInt32(context.Request["zd"].ToString());//站点
            string title = dtstart.ToString("yyyy年MM月dd日") + " 至 " + dtend.ToString("yyyy年MM月dd日") + " 共:" + day.ToString() + "天";
            if (dtstart > dtend)
            {
                text = "{\"err\":{\"ok\":false,\"mes\":\"起始日期在结束日期之后!\"}}";
                context.Response.Write(text);
                return;
            }
            temp = "{\"zd\":" + zd.ToString() + ",\"time1\":\"" + dtstart.ToShortDateString() + "\",\"time2\":\"" + dtend.ToShortDateString() + "\"}";
            retext = retext.Replace("返回参数对象", temp);

            dtstart = dtstart.AddDays(-1);//起始班 的上一天

            int bz_str = 0, bz_end = 0;
            string ID_str = "0", ID_end = "0", SQL = "";
            temp = "";
            CW_每班统计 modstart; //起始数据 起始班 的上一个夜班
            CW_每班统计 modend; //结束数据
            CW_每班统计 modsum; //累计数据
            xzz zdxz;//区间修正值累计

            if (zd == 4)
            {
                bz_str = ZDGAS.getBZno(dtstart);
                bz_end = ZDGAS.getBZno(dtend);
                title = "区间统计：麻柳沱CNG站   " + title;
            }
            else
            {
                bz_str = ZDGAS.getBZno(dtstart, zd, true);
                bz_end = ZDGAS.getBZno(dtend, zd, true);
                if (zd == 2) { title = "区间统计：南高CNG站   " + title; }
                if (zd == 3) { title = "区间统计：江阳西路CNG站   " + title; }
            }
            retext = retext.Replace("头对象", title);

            SQL = "SELECT ID  FROM [CNGQT].[dbo].[CW_每班统计]  where 站点[email protected]站点 AND 日期[email protected]日期 AND 班组[email protected]班组";
            SqlParameter par1 = new SqlParameter("@站点", zd);
            SqlParameter par2 = new SqlParameter("@日期", dtstart);
            SqlParameter par3 = new SqlParameter("@班组", bz_str);
            ID_str = dal.RunSqlGetID(SQL, new SqlParameter[] { par1, par2, par3 });

            par1 = new SqlParameter("@站点", zd);
            par2 = new SqlParameter("@日期", dtend);
            par3 = new SqlParameter("@班组", bz_end);
            ID_end = dal.RunSqlGetID(SQL, new SqlParameter[] { par1, par2, par3 });
            if (ID_str == null)
            {
                text = "{\"err\":{\"ok\":false,\"mes\":\"没有找到起始日期数据!\"}}";
                context.Response.Write(text);
                return;
            }
            if (ID_end == null)
            {
                text = "{\"err\":{\"ok\":false,\"mes\":\"没有找到结束日期数据!\"}}";
                context.Response.Write(text);
                return;
            }

            modstart = ZDGAS.GetModel_CW_每班统计(int.Parse(ID_str));
            modend = ZDGAS.GetModel_CW_每班统计(int.Parse(ID_end));
            modsum = getZDregsum(modstart, modend);
            zdxz = getZDregXzz(modstart, modend);

            temp = jsonLiuLianJi(modstart, modend, modsum, zdxz);
            retext = retext.Replace("流量计对象", temp);

            temp = jsonGasFenLei(modsum, day);
            retext = retext.Replace("销售分类对象", temp);

            temp = jsonYouLei(modstart, modend, modsum);
            retext = retext.Replace("油类对象", temp);

            temp = jsonShuiLei(modstart, modend, modsum, day, zdxz);
            retext = retext.Replace("水类对象", temp);

            temp = jsonDianLei(modstart, modend, modsum, zdxz);
            retext = retext.Replace("电类对象", temp);

            temp = jsonYaShoJi(modstart, modend, modsum);
            retext = retext.Replace("压缩机对象", temp);

            temp = jsonJiZhang(modstart, modend);
            retext = retext.Replace("记账单位对象", temp);

            temp = jsonTingYe(modstart, modend);
//.........这里部分代码省略.........

 /// <summary>
 /// 补签考勤操作 取签到时间
 /// </summary>
 /// <param name="context"></param>
 public void KQ_XG_BuQiang(HttpContext context)
 {
     string index = context.Request["index"];
     string ID = context.Request["ID"];
     string SQL = "SELECT  convert(varchar(20), @替换@,120) as 时间 FROM [CNGTZ].[dbo].[TZ_人员_考勤表] where ID =";
     if (index == "4")
     {
         SQL = SQL.Replace("@替换@", "上班时间") + ID;
     }
     if (index == "5")
     {
         SQL = SQL.Replace("@替换@", "下班时间") + ID;
     }
     DAL dal = new DAL();
     string retext = dal.RunSqlGetID(SQL);
     context.Response.Write(retext);
 }

 //返回 最后处理考勤的时间
 public void KQ_time(HttpContext context)
 {
     DAL dal = new DAL();
     string SQL = "SELECT TOP 1 convert(varchar(20),[时间],120) as 时间 FROM [CNGTZ].[dbo].[TZ_人员_考勤处理时间] ORDER BY 时间 DESC";
     string val = dal.RunSqlGetID(SQL);
     if (val==null)
     {
         val = "";
     }
     context.Response.Write(val);
 }

 /// <summary>
 /// 删除版本控制记录
 /// </summary>
 /// <param name="context"></param>
 public void VerDel(HttpContext context)
 {
     string SQL = "DELETE FROM [WebApp].[dbo].[WEB_版本控制] WHERE  版本号[email protected]版本号";
     string ver = context.Request["ver"];
     string conf = context.Server.MapPath("~/Public/CNGconfig.xml");
     string oldver = Sys.xmlRead(conf, "缓存配置", "全局版本", "1.00");//读取版本号
     string retext = "{{ \"ok\":{0},\"msg\":\"{1}\",\"data\":@替换对象@ }}";
     DAL dal = new DAL();
     SqlParameter par1 = new SqlParameter("版本号", ver);
     dal.RunSql(SQL, new SqlParameter[] { par1 });
     if (dal.ExecuteRowCount == 1)
     {
         if (double.Parse(ver) >= double.Parse(oldver))
         {
             SQL = "SELECT TOP 1 [版本号] FROM [WebApp].[dbo].[WEB_版本控制] ORDER BY 版本号 DESC";
             oldver = dal.RunSqlGetID(SQL) ?? "1.00";
             Sys.xmlAdd(conf, "缓存配置", "全局版本", oldver);
         }
         retext = string.Format(retext, "true", "更改数据成功，当前版本号为：" + oldver);
         retext = retext.Replace("@替换对象@", "0");
     }
     else
     {
         retext = string.Format(retext, "false", "错误信息：" + dal.ErrorMsg);
         retext = retext.Replace("@替换对象@", "0");
     }
     context.Response.Write(retext);
 }