EF中执行sql语句，以及事务

EF to sql 
string sql = "select T_Task.BSID,T_Task.CloseDate,T_Task.CompleteDate,T_Task.CloseUser,T_Task.TaskID,T_BaseStation.Name from T_Task  " +
                "inner join T_BaseStation on T_Task.BSID=T_BaseStation.BSID"+
                " where " + sqltaskid;
 ObjectResult<Sys> q = db.ExecuteStoreQuery<Sys>(sql);
 List<Sys> qList = q.ToList();


EF中执行sql语句，以及事务
 db.Connection.Open();
            var trans = db.Connection.BeginTransaction();
            JsonResult json = new JsonResult();
            json.Data = true;
            try
            {
                t_device.IsAssets = 1;
                db.T_Device.AddObject(t_device);
                db.SaveChanges();

                var q = db.T_DeviceType.Single(p => p.DeviceTypeID == t_device.DeviceTypeID);
                if (!string.IsNullOrEmpty(q.InfoTableName))
                {
                    //根据不同的类型新建一个设备详细信息
                    string sql = "Insert into T_" + q.InfoTableName + "(DeviceID,BSID) values(" + t_device.DeviceID + "," + t_device.BSID + ")";

                    if (db.ExecuteStoreCommand(sql) != 1)
                    {
                        throw new Exception("创建详细信息失败，请检查该设备类型的表名是否正确");
                    }
                }
                trans.Commit();

            }
            catch (Exception ee)
            {
                trans.Rollback();
                json.Data = ee.Message;
            }

            return json;




        //基站过滤，调用存储过程
        public JsonResult AutoSrc(string q)
        {
            int userid = Utils.GetCurrentLoginUserID(this.Session);
            //var code = db.T_DepartmentInfo.Single(t => t.DepartmentID == (db.T_User.FirstOrDefault(p => p.UserID == userid).DepartmentID)).Code;
            //  var r = from e in db.T_BaseStation
            //        join d in db.T_DepartmentInfo on e.DepartmentID equals d.DepartmentID
            //        where d.Code != null && d.Code.Substring(0, code.Length).Contains(code)&&(e.Name.Contains(q)||e.PinYin.Contains(q))
            //        select new
            //        {
            //            BSID = e.BSID,
            //            Name = e.Name,
            //            PinYin = e.PinYin,
            //            parentBSID = e.parentBSID
            //        };

            return proc(userid, q);
        }
        public JsonResult proc(int code, string name)
        {
            string cmdText = "P_inspectBaseList";
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@userId",code),
                new SqlParameter("@searchName",name)
            };
            DataTable dt = new DataTable();
            dt = ExecuteQuery(cmdText, paras, CommandType.StoredProcedure);

            return Json(dt, JsonRequestBehavior.AllowGet);
        }
        public DataTable ExecuteQuery(string cmdText, SqlParameter[] paras, CommandType ct)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(@"Data Source=ip\SQL2008,1848;Initial Catalog=111;Persist Security Info=True;User ID=sa;Password=111");
            conn.Open();
            cmd = new SqlCommand(cmdText, conn);
            cmd.CommandType = ct;
            cmd.Parameters.AddRange(paras);

            using (SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }



    //接受Json数据
    [HttpPost]
        [OutputCache(Location = OutputCacheLocation.None)]
        public JsonResult DataContractJsonDeserialize(T_MapOffset[] json)
        {
            var sqlparam = "";
            int i = 0;

            if (json != null && json.Length > 0)
            {

                foreach (var mapOffset in json)
                {
                    if (i == 0)
                        sqlparam += "( lat=" + mapOffset.lat + " and lng=" + mapOffset.lng + ") ";
                    else
                    {
                        sqlparam += "or ( lat=" + mapOffset.lat + " and lng=" + mapOffset.lng + ") ";
                    }
                    i++;
                }
                JsonResult result = new JsonResult();
                try
                {
                    SqlConnection conn = new SqlConnection(@"Data Source=ip\SQL2008,1848;Initial Catalog=111;Persist Security Info=True;User ID=sa;Password=111");
                    string strSql = "select * from T_MapOffset where " + sqlparam;
                    List<T_MapOffset> map = new List<T_MapOffset>();
                    SqlCommand cmd = new SqlCommand(strSql, conn);
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        T_MapOffset mapOffset = new T_MapOffset();
                        mapOffset.lat = Convert.ToInt32(dr["lat"]);
                        mapOffset.lng = Convert.ToInt32(dr["lng"]);
                        mapOffset.latoffset = Convert.ToInt32(dr["latoffset"]);
                        mapOffset.lngoffset = Convert.ToInt32(dr["lngoffset"]);
                        map.Add(mapOffset);
                    }
                    conn.Close();//关闭数据库连接
                    return Json(map);
                }
                catch (Exception)
                {
                    result.Data = null;
                }
                return result;
            }
            else
            {             
                return null;
            }
        

            
        }



//接受泛型集合
 public JsonResult GetListMapOffset(List<string> lnglat)
        {
            JsonResult result = new JsonResult();
            result.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            List<object> list = new List<object>();
            try
            {
                foreach (var temp in lnglat)
                {
                    string[] t = temp.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                    if (t.Length != 2)
                        continue;

                    int lng = Int32.Parse(t[0]);
                    int lat = Int32.Parse(t[1]);

                    var q = from m in db.T_MapOffset.Where(p => p.lng == lng && p.lat == lat) select new { m.lat, m.lng, m.latoffset, m.lngoffset };
                    list.AddRange(q);
                }


                return Json(list);
            }
            catch
            {
                result.Data = null;
            }

            return result;

        }






//接受string字符串
 public JsonResult GetListForPlan(string bsids)
        {
            string[] list = bsids.Split(new[] {','}, StringSplitOptions.RemoveEmptyEntries);
            if(list.Length<=0)
            {
                return null;
            }
            int[] ids=new int[list.Length];
            int i = 0;
            foreach (string s in list)
            {
                ids[i] = int.Parse(s);
                i++;
            }

            var q = from e in db.T_BaseStation
                    join a in ids on e.BSID equals a
                    select new
                    {
                        BSID = e.BSID,
                        Name = e.Name,
                        Code = e.Code,
                        RoomID = e.RoomID,
                        Building = e.Building,
                        Type = e.Type,
                        Address = e.Address,
                        LocationInBuilding = e.LocationInBuilding,
                        Longitude = e.Longitude,
                        Latitude = e.Latitude,
                        LonLatUpdateDate = e.LonLatUpdateDate,
                        VIP = e.VIP,
                        InspectorArrivalTime = e.InspectorArrivalTime,
                        PowerGenerationArrivalTime = e.PowerGenerationArrivalTime,
                        LastInspectTime = e.LastInspectTime,
                        BestInspectCircle = e.BestInspectCircle,
                        AddUser = e.AddUser,
                        AddTime = e.AddTime,
                        MonitoringInstalled = e.MonitoringInstalled,
                        ResponsibleUser = e.ResponsibleUser,
                        DepartmentID = e.DepartmentID,
                        Network = e.Network
                    };


            return Json(q, JsonRequestBehavior.AllowGet);
        }








select ManufacturersName,COUNT(*) from T_DeviceType group by ManufacturersName,model,TypeName having COUNT(*) >1
Fiddler4BetaSetup.exe

 

转载于:https://www.cnblogs.com/yangwujun/p/3405990.html