コード例 #1
0
        public List<CountyModel> Get(CountyParameterModel parameter)
        {
            using (IDbConnection conn = new SqlConnection(this.databaseConstants.CommonConnection))
            {
                var sql = new StringBuilder();

                sql.AppendLine(@"SELECT
                                            CityCode,
                                            CityName,
                                            CitySName,
                                            CityAName,
                                            CityEName,
                                            CityHallLng,
                                            CityHallLat,
                                            GPSResult,
                                            GPSDatetime,
                                            Geographys,
                                            Geometrys,
                                            MinLng,
                                            MinLat,
                                            MaxLng,
                                            MaxLat,
                                            CLng,
                                            CLat,
                                            GeoString,
                                            UseFlag,
                                            UpdatDate
                                        FROM ADDRCity WITH (NOLOCK)
                                        WHERE UseFlag = 'Y'");

                if (parameter != null)
                {
                    var whereSql = new StringBuilder();

                    if (!string.IsNullOrWhiteSpace(parameter.CityCode))
                    {
                        whereSql.AppendLine(@" AND CityCode = @CityCode");
                    }

                    if (!string.IsNullOrWhiteSpace(parameter.CityName))
                    {
                        whereSql.AppendLine(@" AND CityName = @CityName");
                    }

                    if (whereSql.Length > 0)
                    {
                        sql.AppendLine(whereSql.ToString());
                    }
                }

                IDapperHelper dapper = new DapperHelper("API", "API.CountyDistractRoad");

                var counties = dapper.Query<CountyModel>(
                      dbConnection: conn,
                      sql: sql.ToString(),
                      param: parameter).ToList();

                return counties;
            }
        }
コード例 #2
0
        public List<DistrictModel> Get(DistrictParameterModel parameter)
        {
            using (IDbConnection conn = new SqlConnection(this.databaseConstants.CommonConnection))
            {
                var sql = new StringBuilder();
                sql.AppendLine(@"SELECT
                                                        ZipID,
                                                        CityCode,
                                                        ZipCode,
                                                        ZipName,
                                                        ZipEName,
                                                        UseFlag,
                                                        GPSResult,
                                                        MinLng,
                                                        MinLat,
                                                        MaxLng,
                                                        MaxLat,
                                                        CLng,
                                                        CLat,
                                                        TownHallLng,
                                                        TownHallLat,
                                                        GeoString
                                                    FROM ADDRDistrict WITH (NOLOCK)
                                                    WHERE UseFlag = 'Y'
                                                        AND CityCode = @CityCode");

                var whereSql = new StringBuilder();

                if (parameter.ZipID != null)
                {
                    whereSql.AppendLine(@" AND ZipID = @ZipID");
                }

                if (!string.IsNullOrWhiteSpace(parameter.ZipName))
                {
                    whereSql.AppendLine(@" AND ZipName = @ZipName");
                }

                if (whereSql.Length > 0)
                {
                    sql.AppendLine(whereSql.ToString());
                }

                IDapperHelper dapper = new DapperHelper("API", "API.CountyDistractRoad");

                var districts = dapper.Query<DistrictModel>(
                      dbConnection: conn,
                      sql: sql.ToString(),
                      param: parameter).ToList();

                return districts;
            }
        }
コード例 #3
0
        public List<RoadModel> Get(RoadParameterModel parameter)
        {
            using (IDbConnection conn = new SqlConnection(this.databaseConstants.CommonConnection))
            {
                var sql = new StringBuilder();
                sql.AppendLine(@"SELECT
                                                        CityCode,
                                                        ZipCode,
                                                        ZipID,
                                                        RoadCode,
                                                        RoadName,
                                                        RoadEName,
                                                        Lng,
                                                        Lat,
                                                        UseFlag
                                                    FROM ADDRRoad WITH (NOLOCK)
                                                    WHERE UseFlag = 'Y'
                                                        AND ZipID = @ZipID");

                var whereSql = new StringBuilder();

                if (!string.IsNullOrWhiteSpace(parameter.RoadCode))
                {
                    whereSql.AppendLine(@" AND RoadCode = @RoadCode");
                }

                if (!string.IsNullOrWhiteSpace(parameter.RoadName))
                {
                    whereSql.AppendLine(@" AND RoadName = @RoadName");
                }

                if (whereSql.Length > 0)
                {
                    sql.AppendLine(whereSql.ToString());
                }

                IDapperHelper dapper = new DapperHelper("API", "API.CountyDistractRoad");

                var roads = dapper.Query<RoadModel>(
                      dbConnection: conn,
                      sql: sql.ToString(),
                      param: parameter).ToList();

                return roads;
            }
        }
コード例 #4
0
ファイル: CompanyRepo.cs プロジェクト: YongLWei03/EM-2
        public string GetCompanyName(int id)
        {
            var name = DapperHelper.SqlQuery <string>("select CompanyName from  EM_Company where Id=@Id ", new { Id = id }).FirstOrDefault();

            return(name ?? "");
        }
コード例 #5
0
 public Repositorio(DapperHelper dapperHelper)
 {
     this.dapperHelper = dapperHelper;
 }
コード例 #6
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="dapper"></param>
 public MenuByIdQueryHandler(DapperHelper dapper)
 {
     this._dapper = dapper;
 }
コード例 #7
0
 public IEnumerable <T> QueryEnable <T>(SQLiteConnection conn, SQLiteTransaction trans)
 {
     return(DapperHelper.Query <T>(conn, $"select * from {tableName} where organSign='{CacheInfo.Drugstore.OrganSign}' and yn=1;", null, trans));
 }
コード例 #8
0
        public async Task <ActionResult> BrowseInstructorList(JObject Parameter)
        {
            try
            {
                /*Validation Required parameter */
                if (string.IsNullOrEmpty(Convert.ToString(Parameter["page_number"])))
                {
                    return(BadRequest("Bad request, page number is required"));
                }

                string            Sql = "";
                DynamicParameters DyParam_InstructorList = new DynamicParameters();

                /*Dynamic Parameter Alter*/

                if (!string.IsNullOrEmpty(Convert.ToString(Parameter["name"])))
                {
                    string Full_name  = Convert.ToString(Parameter["name"]);
                    var    Names      = Full_name.Split(' ', '.', ',');
                    string First_name = Names[0];
                    string Last_name  = Names[1];

                    Sql += "AND `first_name` = @first_name AND `last_name` = @last_name" + " ";

                    DyParam_InstructorList.Add("first_name", First_name);
                    DyParam_InstructorList.Add("last_name", Last_name);
                }

                if (!string.IsNullOrEmpty(Convert.ToString(Parameter["instructor_id"])))
                {
                    string instructor_id = Convert.ToString(Parameter["instructor_id"]);
                    Sql += "AND `instructor_id` = @instructor_id ";
                    DyParam_InstructorList.Add("instructor_id", instructor_id);
                }

                if (Sql != "")
                {
                    Sql = "WHERE 1 = 1 " + Sql;
                }

                /*calculate the total amount of records*/
                string Original_zq61H4SJdL = XMLHelper.GetSql("SQL_zq61H4SJdL");// get recod amount
                Original_zq61H4SJdL = Original_zq61H4SJdL.Replace("@dynamic_condition", Sql);
                DataTable DT_zq61H4SJdL = await DapperHelper.ExecuteSqlDataTableAsync(Original_zq61H4SJdL, DyParam_InstructorList);

                /*Get pagination */
                DyParam_InstructorList.Add("page_number", (Convert.ToInt32(Parameter["page_number"]) - 1) * 10);
                string Original_U83Eb0bl4Q = XMLHelper.GetSql("SQL_U83Eb0bl4Q");
                Original_U83Eb0bl4Q = Original_U83Eb0bl4Q.Replace("@dynamic_condition", Sql);
                DataTable DT_U83Eb0bl4Q = await DapperHelper.ExecuteSqlDataTableAsync(Original_U83Eb0bl4Q, DyParam_InstructorList);// get datas

                JArray jArray_Data = JArray.Parse(JsonConvert.SerializeObject(DT_U83Eb0bl4Q));

                JArray jArray_Columns = new JArray();

                for (int i = 0; i < DT_U83Eb0bl4Q.Columns.Count; i++)
                {
                    JObject Json = new JObject();
                    Json.Add("tittle", DT_U83Eb0bl4Q.Columns[i].ColumnName);
                    Json.Add("dataIndex", DT_U83Eb0bl4Q.Columns[i].ColumnName);

                    jArray_Columns.Add(Json);
                }

                for (int j = 0; j < jArray_Data.Count; j++)
                {
                    ((JObject)jArray_Data[j]).Add("key", j);
                }

                JObject Json_Result = new JObject();

                Json_Result.Add("pageSum", Convert.ToInt32(DT_zq61H4SJdL.Rows[0]["pageSum"]));

                Json_Result.Add("columnData", jArray_Columns);

                Json_Result.Add("rowData", jArray_Data);

                return(Ok(Json_Result));
            }
            catch (Exception ex)
            {
                return(BadRequest("Unknow Error"));
            }
        }//tested
コード例 #9
0
        public int UptRoles(Model.Roles a)
        {
            string sql = $"update  Roles  set  RoleName='{a.RoleName}' where RoleId={a.RoleId})";

            return(DapperHelper.Execute(sql));
        }
コード例 #10
0
        /// <summary>
        /// 返回月度数据(全部)
        /// </summary>
        /// <returns></returns>
        public List <MonthAnalizeData> GetMonthAnalizeDatas(int Code, DateTime time)
        {
            using (OracleConnection conn = DapperHelper.GetConnString())
            {
                //传回的是用户id
                string sql1         = string.Format("select a.region_code from Region a,\"User\" b where a.id=b.RegionId and b.id='" + Code + "'");
                string collectList1 = conn.Query <string>(sql1, null).FirstOrDefault();
                string code1        = collectList1;
                //获取到行政区的编号
                string code2 = code1;
                //三表联查
                //string sql = string.Format("select a.avgvalue,a.monitortime,c.PollutantName,d.PointName from MONTHDATA a ,MonitorPointPollutan b, Pollutant c,MonitorPoint d where a.Monitor_PollutionId=b.Id and b.PollutantId=c.id and b.pointid=d.id and d.RegionCode='" + code2 + "'");
                //var collectList = conn.Query<MonthAnalizeData>(sql, null);

                //List<MonthAnalizeData> list= collectList.ToList<MonthAnalizeData>();
                //List<MonthAnalizeData> list2= list.Where(n => n.PointName == "PM2.5").ToList();
                //List<MonthAnalizeData> list3 = list.Where(n => n.PointName == "PM10").ToList();
                //List<MonthAnalizeData> list4 = list.Where(n => n.PointName == "O3").ToList();
                //List<MonthAnalizeData> list5 = list.Where(n => n.PointName == "NO2").ToList();
                //List<MonthAnalizeData> list6 = list.Where(n => n.PointName == "CO").ToList();
                //List<MonthAnalizeData> list7 = list.Where(n => n.PointName == "SO2").ToList();
                //4表连查
                string sql = string.Format("select a.avgvalue,c.pointname,d.pollutantname from MonthData a,MonitorPointPollutan b,MonitorPoint c,Pollutant d where a.Monitor_Pollutionid=b.id and b.PointId=c.id and b.pollutantid=d.id and c.RegionCode='" + code2 + "' and a.monitortime=:time  ");
                //接收
                var collectList = conn.Query <MonthAnalizeData>(sql, new { time });
                //求上一年同月份,用于同比
                DateTime time3 = time.AddYears(-1);
                //求上个月份,用于环比
                DateTime time2 = time.AddMonths(-1);
                //获取同比数据的sql语句
                string sql12 = string.Format("select a.avgvalue, c.pointname, d.pollutantname from MonthData a, MonitorPointPollutan b, MonitorPoint c, Pollutant d where a.Monitor_Pollutionid = b.id and b.PointId = c.id and b.pollutantid = d.id and c.RegionCode = '" + code2 + "' and a.monitortime =:times");
                //用于同比的数据
                var collectList2 = conn.Query <MonthAnalizeData>(sql12, new { times = time3 });
                //获取环比数据的sql语句
                string sql13 = string.Format("select a.avgvalue, c.pointname, d.pollutantname from MonthData a, MonitorPointPollutan b, MonitorPoint c, Pollutant d where a.Monitor_Pollutionid = b.id and b.PointId = c.id and b.pollutantid = d.id and c.RegionCode = '" + code2 + "' and a.monitortime =:timess");
                //获取环比的数据
                var collectList3 = conn.Query <MonthAnalizeData>(sql13, new { timess = time2 });
                //数组接收所有的站点的名称
                List <string> pointNames = (from c in collectList select c.PointName).Distinct().OrderBy(m => m).ToList();
                //实例化一个数组用于返回
                List <MonthAnalizeData> list = new List <MonthAnalizeData>();
                //循环遍历 取出各个站点名称
                foreach (string pointName in pointNames)
                {
                    //实例化对象,用于存储数据
                    MonthAnalizeData monthAnalizeData = new MonthAnalizeData();
                    //赋值
                    monthAnalizeData.PointName = pointName;
                    //循环遍历同一站点的数据
                    foreach (MonthAnalizeData monthAnalizeData1 in collectList.Where(m => m.PointName == pointName))
                    {
                        //同比,因为上一年的数据会没有,所以要有一个非空判断
                        if (collectList2.Count() > 0)
                        {
                            //遍历同一站点的同比数据
                            foreach (MonthAnalizeData monthAnalizeData2 in collectList2.Where(m => m.PointName == pointName))
                            {
                                //遍历同一站点的环比数据
                                foreach (MonthAnalizeData monthAnalizeData3 in collectList3.Where(m => m.PointName == pointName))
                                {
                                    ///同一站点 污染物 的数据
                                    if (monthAnalizeData1.PollutantName == "PM2.5" && monthAnalizeData2.PollutantName == "PM2.5" && monthAnalizeData3.PollutantName == "PM2.5")
                                    {
                                        //赋值
                                        monthAnalizeData.PM2AVGValue = monthAnalizeData1.AVGValue;
                                        //同比赋值
                                        monthAnalizeData.TPM2AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData2.AVGValue;
                                        //环比复制
                                        monthAnalizeData.HPM2AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
                                    }
                                    ///同一站点 污染物 的数据
                                    if (monthAnalizeData1.PollutantName == "PM10" && monthAnalizeData2.PollutantName == "PM10" && monthAnalizeData3.PollutantName == "PM10")
                                    {
                                        monthAnalizeData.PM10AVGValue  = monthAnalizeData1.AVGValue;
                                        monthAnalizeData.TPM10AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData2.AVGValue;
                                        monthAnalizeData.HPM10AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
                                    }
                                    if (monthAnalizeData1.PollutantName == "CO" && monthAnalizeData2.PollutantName == "CO" && monthAnalizeData3.PollutantName == "CO")
                                    {
                                        monthAnalizeData.COAVGValue  = monthAnalizeData1.AVGValue;
                                        monthAnalizeData.TCOAVGValue = monthAnalizeData1.AVGValue / monthAnalizeData2.AVGValue;
                                        monthAnalizeData.HCOAVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
                                    }
                                    if (monthAnalizeData1.PollutantName == "NO" && monthAnalizeData2.PollutantName == "NO" && monthAnalizeData3.PollutantName == "NO")
                                    {
                                        monthAnalizeData.N02AVGValue  = monthAnalizeData1.AVGValue;
                                        monthAnalizeData.TN02AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData2.AVGValue;
                                        monthAnalizeData.HN02AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
                                    }
                                    if (monthAnalizeData1.PollutantName == "O3" && monthAnalizeData2.PollutantName == "O3" && monthAnalizeData3.PollutantName == "O3")
                                    {
                                        monthAnalizeData.O3AVGValue  = monthAnalizeData1.AVGValue;
                                        monthAnalizeData.TO3AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData2.AVGValue;
                                        monthAnalizeData.HO3AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
                                    }
                                    if (monthAnalizeData1.PollutantName == "SO2" && monthAnalizeData2.PollutantName == "SO2" && monthAnalizeData3.PollutantName == "SO2")
                                    {
                                        monthAnalizeData.SO2AVGValue  = monthAnalizeData1.AVGValue;
                                        monthAnalizeData.TSO2AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData2.AVGValue;
                                        monthAnalizeData.HSO2AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
                                    }
                                }
                            }
                        }
                        ///判断环比数据是否为空
                        else if (collectList3.Count() > 0)
                        {
                            foreach (MonthAnalizeData monthAnalizeData3 in collectList3.Where(m => m.PointName == pointName))
                            {
                                if (monthAnalizeData1.PollutantName == "PM2.5")
                                {
                                    monthAnalizeData.PM2AVGValue = monthAnalizeData1.AVGValue;

                                    monthAnalizeData.HPM2AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
                                }
                                if (monthAnalizeData1.PollutantName == "PM10")
                                {
                                    monthAnalizeData.PM10AVGValue = monthAnalizeData1.AVGValue;

                                    monthAnalizeData.HPM10AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
                                }
                                if (monthAnalizeData1.PollutantName == "CO")
                                {
                                    monthAnalizeData.COAVGValue = monthAnalizeData1.AVGValue;

                                    monthAnalizeData.HCOAVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
                                }
                                if (monthAnalizeData1.PollutantName == "NO")
                                {
                                    monthAnalizeData.N02AVGValue = monthAnalizeData1.AVGValue;

                                    monthAnalizeData.HN02AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
                                }
                                if (monthAnalizeData1.PollutantName == "O3")
                                {
                                    monthAnalizeData.O3AVGValue = monthAnalizeData1.AVGValue;

                                    monthAnalizeData.HO3AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
                                }
                                if (monthAnalizeData1.PollutantName == "SO2")
                                {
                                    monthAnalizeData.SO2AVGValue = monthAnalizeData1.AVGValue;

                                    monthAnalizeData.HSO2AVGValue = monthAnalizeData1.AVGValue / monthAnalizeData3.AVGValue;
                                }
                            }
                        }
                        else
                        {
                            if (monthAnalizeData1.PollutantName == "PM2.5")
                            {
                                monthAnalizeData.PM2AVGValue = monthAnalizeData1.AVGValue;
                            }

                            if (monthAnalizeData1.PollutantName == "PM10")
                            {
                                monthAnalizeData.PM10AVGValue = monthAnalizeData1.AVGValue;
                            }

                            if (monthAnalizeData1.PollutantName == "CO")
                            {
                                monthAnalizeData.COAVGValue = monthAnalizeData1.AVGValue;
                            }

                            if (monthAnalizeData1.PollutantName == "NO")
                            {
                                monthAnalizeData.N02AVGValue = monthAnalizeData1.AVGValue;
                            }

                            if (monthAnalizeData1.PollutantName == "O3")
                            {
                                monthAnalizeData.O3AVGValue = monthAnalizeData1.AVGValue;
                            }

                            if (monthAnalizeData1.PollutantName == "SO2")
                            {
                                monthAnalizeData.SO2AVGValue = monthAnalizeData1.AVGValue;
                            }
                        }
                    }
                    //给list赋值
                    list.Add(monthAnalizeData);
                }
                return(list);
            }
        }
コード例 #11
0
        public int Update(Model.Delivery Model)
        {
            string sql = $"UPDATE Delivery SET DeliveryName='{Model.DeliveryName}',DeliveryPath='{Model.DeliveryPath}' WHERE DeliveryId={Model.DeliveryId}";

            return(DapperHelper.Execute(sql));
        }
コード例 #12
0
        public List <Model.Delivery> Query()
        {
            string sql = "SELECT * FROM Delivery a JOIN Users b ON a.UserId=b.UserId join Colonel c ON a.ColonelID=c.ColonelID";

            return(DapperHelper.GetList <IOT.Core.Model.Delivery>(sql));
        }
コード例 #13
0
        public int Insert(Model.Delivery Model)
        {
            string sql = $"INSERT INTO Delivery VALUES(NULL,{Model.UserId},{Model.ColonelID},'{Model.DeliveryPath}','{Model.DeliveryName}')";

            return(DapperHelper.Execute(sql));
        }
コード例 #14
0
        public int Delete(string ids)
        {
            string sql = $"DELETE FROM Delivery WHERE DeliveryId IN({ids})";

            return(DapperHelper.Execute(sql));
        }
コード例 #15
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="dapper"></param>
 public RoleByIdQueryHandle(DapperHelper dapper)
 {
     _dapper = dapper;
 }
コード例 #16
0
        public int AddRoles(Model.Roles a)
        {
            string sql = $"insert into Roles values (null,'{a.RoleName}')";

            return(DapperHelper.Execute(sql));
        }
コード例 #17
0
        // 显示
        public List <Model.Warehouse> ShowWarehouse()
        {
            string sql = "select * from Warehouse";

            return(DapperHelper.GetList <Model.Warehouse>(sql));
        }
コード例 #18
0
        public List <Model.Roles> ShowRoles()
        {
            string sql = "select * from Roles";

            return(DapperHelper.GetList <Model.Roles>(sql));
        }
コード例 #19
0
        // 删除
        public int DelWarehouse(string id)
        {
            string sql = $"delete from Warehouse where WarehouseId={id}";

            return(DapperHelper.Execute(sql));
        }
コード例 #20
0
 public List <Model.ApplicationType> Query(string sql)
 {
     return(DapperHelper.GetList <Model.ApplicationType>(sql));
 }
コード例 #21
0
        public List <Users> UsersList(string name)
        {
            List <Users> TestList = DapperHelper.GetList <Users>("SELECT * From users where AccountName='" + name + "'");

            return(TestList);
        }
コード例 #22
0
        public async Task <ActionResult> BrowseCourseList(JObject Parameter)
        {
            //try
            //{

            //}
            //catch (Exception ex) { return BadRequest("Get course list failed."); }

            /*Validation Required parameter */

            if (string.IsNullOrEmpty(Convert.ToString(Parameter["page_number"])))
            {
                return(BadRequest("Bad request, page number is required"));
            }

            string            Sql = "";
            DynamicParameters DyParam_courseList = new DynamicParameters();

            /*Dynamic Parameter Alter*/

            if (!string.IsNullOrEmpty(Convert.ToString(Parameter["course_code"])))
            {
                string course_code = Convert.ToString(Parameter["course_code"]);
                Sql += "AND `course_code` = @course_code ";
                DyParam_courseList.Add("course_code", course_code);
            }


            if (!string.IsNullOrEmpty(Convert.ToString(Parameter["program"])))
            {
                string program = Convert.ToString(Parameter["program"]);
                Sql += "AND `program` = @program";
                DyParam_courseList.Add("program", program);
            }

            if (Sql != "")
            {
                Sql = "WHERE 1 = 1 " + Sql;
            }

            /*calculate the total amount of records*/
            string Original_8aN4nnYYVz = XMLHelper.GetSql("SQL_8aN4nnYYVz");// get recod amount

            Original_8aN4nnYYVz = Original_8aN4nnYYVz.Replace("@dynamic_condition", Sql);
            DataTable DT_8aN4nnYYVz = await DapperHelper.ExecuteSqlDataTableAsync(Original_8aN4nnYYVz, DyParam_courseList);

            /*Get pagination */
            DyParam_courseList.Add("page_number", (Convert.ToInt32(Parameter["page_number"]) - 1) * 10);
            string Original_yZZSnuIMA4 = XMLHelper.GetSql("SQL_yZZSnuIMA4");

            Original_yZZSnuIMA4 = Original_yZZSnuIMA4.Replace("@dynamic_condition", Sql);
            DataTable DT_yZZSnuIMA4 = await DapperHelper.ExecuteSqlDataTableAsync(Original_yZZSnuIMA4, DyParam_courseList);// get datas

            JArray jArray_Data = JArray.Parse(JsonConvert.SerializeObject(DT_yZZSnuIMA4));

            JArray jArray_Columns = new JArray();

            for (int i = 0; i < DT_yZZSnuIMA4.Columns.Count; i++)
            {
                JObject Json = new JObject();
                Json.Add("tittle", DT_yZZSnuIMA4.Columns[i].ColumnName);
                Json.Add("dataIndex", DT_yZZSnuIMA4.Columns[i].ColumnName);

                jArray_Columns.Add(Json);
            }

            for (int j = 0; j < jArray_Data.Count; j++)
            {
                ((JObject)jArray_Data[j]).Add("key", j);
            }

            JObject Json_Result = new JObject();

            Json_Result.Add("pageSum", Convert.ToInt32(DT_8aN4nnYYVz.Rows[0]["pageSum"]));

            Json_Result.Add("columnData", jArray_Columns);

            Json_Result.Add("rowData", jArray_Data);

            return(Ok(Json_Result));
        }//tested
コード例 #23
0
        public List <Model.SeckillCom> Query()
        {
            string sql = "select a.*,b.ActivityId,c.CommodityPic,b.ActivityName,b.BeginTime,b.EndTime,c.ShopPrice,NOW() times,c.CommodityName FROM SeckillCom a JOIN Activity b ON a.ActivityId=b.ActivityId JOIN Commodity c ON a.CommodityId=c.CommodityId";

            return(DapperHelper.GetList <IOT.Core.Model.SeckillCom>(sql));
        }
コード例 #24
0
 /// <summary>
 /// 小程序显示
 /// </summary>
 /// <returns></returns>
 public List<Model.MiniProgram> MiniPrograms()
 {
     string sql = $" select * from MiniProgram ";
     return DapperHelper.GetList<Model.MiniProgram>(sql);
 }
コード例 #25
0
 /// <summary>
 ///     清空当前serverid所有记录
 /// </summary>
 public void ClearTaskRunLog()
 {
     DapperHelper.Execute(@"delete from task_run_log where taskserverid=@serverid", Context,
                          new { serverid = _runnerConfig.TaskServerId });
 }
コード例 #26
0
        public int DelRoles(string id)
        {
            string sql = $"delete from Roles where RoleId={id}";

            return(DapperHelper.Execute(sql));
        }
コード例 #27
0
ファイル: SongMarkRepository.cs プロジェクト: dkest/MicSystem
 public SongMarkRepository()
 {
     helper = new DapperHelper <SqlConnection>(WebConfig.SqlConnection);
 }
コード例 #28
0
ファイル: BookController.cs プロジェクト: hwqdt/Demos
 public BookController(DapperHelper helper)
 {
     this._helper = helper;
 }
コード例 #29
0
        /// <summary>
        /// 显示
        /// </summary>
        /// <returns></returns>
        public List <Model.Activity> ShowActivity()
        {
            string sql = "select * from Activity";

            return(DapperHelper.GetList <Model.Activity>(sql));
        }
コード例 #30
0
        public void DailyMenu_Test()
        {
            IRepository repo = new Repository();

            if (!repo.Any <Menu>(x => x.MenuDate == DateTime.Today))
            {
                // Today's menu no exist
                var menuL1 = new Menu
                {
                    MenuDate   = DateTime.Today,
                    MenuType   = MenuTypeEnum.Lunch,
                    MenuFlag   = "A",
                    Meat       = "红烧大排",
                    MeatSmall  = "芙蓉鸡片",
                    Vegetable1 = "酸辣土豆丝",
                    Vegetable2 = "麻婆豆腐",
                    CreateTime = DateTime.Now,
                    CreateUser = "******",
                    IsApproved = false,
                    IsActive   = true,
                    Remark     = "TEST Lunch A"
                };

                var menuL2 = new Menu
                {
                    MenuDate   = DateTime.Today,
                    MenuType   = MenuTypeEnum.Lunch,
                    MenuFlag   = "B",
                    Meat       = "咖喱牛肉",
                    MeatSmall  = "肉饼蒸蛋",
                    Vegetable1 = "油焖笋",
                    Vegetable2 = "凉拌马兰头",
                    CreateTime = DateTime.Now,
                    CreateUser = "******",
                    IsApproved = false,
                    IsActive   = true,
                    Remark     = "TEST Lunch B"
                };

                var menuS1 = new Menu
                {
                    MenuDate   = DateTime.Today,
                    MenuType   = MenuTypeEnum.Supper,
                    MenuFlag   = "A",
                    Meat       = "啤酒鸭",
                    MeatSmall  = "番茄炒蛋",
                    Vegetable1 = "上汤菠菜",
                    Vegetable2 = "清炒冬瓜",
                    CreateTime = DateTime.Now,
                    CreateUser = "******",
                    IsApproved = false,
                    IsActive   = true,
                    Remark     = "TEST Supper A"
                };

                var menuS2 = new Menu
                {
                    MenuDate   = DateTime.Today,
                    MenuType   = MenuTypeEnum.Supper,
                    MenuFlag   = "B",
                    Meat       = "香煎带鱼",
                    MeatSmall  = "雪菜炒鸡",
                    Vegetable1 = "香菇青菜",
                    Vegetable2 = "手撕包心菜",
                    CreateTime = DateTime.Now,
                    CreateUser = "******",
                    IsApproved = false,
                    IsActive   = true,
                    Remark     = "TEST Supper B"
                };

                using (IDapperHelper dapper = DapperHelper.GetInstance())
                {
                    var trans = dapper.BeginTransaction();

                    try
                    {
                        repo.Insert(menuL1);
                        repo.Insert(menuL2);
                        repo.Insert(menuS1);
                        repo.Insert(menuS2);

                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }

            Assert.IsTrue(repo.Count <Menu>(x => x.MenuDate == DateTime.Today) == 4 ||
                          repo.Count <Menu>(x => x.MenuDate == DateTime.Today) == 2);
        }
コード例 #31
0
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelActivity(string id)
        {
            string sql = $"delete from Activity where ActivityId={id}";

            return(DapperHelper.Execute(sql));
        }
コード例 #32
0
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="a"></param>
        /// <returns></returns>
        public int AddActivity(Model.Activity a)
        {
            string sql = $"insert into Activity values (null,'{a.ActivityName}', '{a.BeginTime}', '{a.EndTime}', '{a.Slideshow}', {a.State},'{a.CreateDate}', {a.ActivityTime})";

            return(DapperHelper.Execute(sql));
        }
コード例 #33
0
 public ContentsRepository(DapperHelper dapperHelper) : base(dapperHelper)
 {
 }
コード例 #34
0
 /// <summary>
 ///
 /// </summary>
 /// <param name="dapper"></param>
 public MonthPieDataQueryHandler(DapperHelper dapper)
 {
     _dapper = dapper;
 }
コード例 #35
-11
        public List<VillageModel> Get(VillageParameterModel parameter)
        {
            using (IDbConnection conn = new SqlConnection(this.databaseConstants.CommonConnection))
            {
                var sql = @"SELECT
                                            VillageID,
                                            ZipID,
                                            CityCode,
                                            ZipCode,
                                            VillageName,
                                            VillageEName,
                                            GPSResult,
                                            MinLng,
                                            MinLat,
                                            MaxLng,
                                            MaxLat,
                                            CLng,
                                            CLat,
                                            GeoString,
                                            UseFlag
                                        FROM ADDRVillage WITH (NOLOCK)
                                        WHERE UseFlag = 'Y'
                                            AND ZipID = @ZipID";

                IDapperHelper dapper = new DapperHelper("API", "API.CountyDistractRoad");

                var villages = dapper.Query<VillageModel>(
                      dbConnection: conn,
                      sql: sql,
                      param: parameter).ToList();

                return villages;
            }
        }