SqlSugar简单使用封装

一、SqlSugar简单使用封装

 1.  封装读取数据库的方法：

　　

public  static SqlSugarClient GetInstance()
        {
            //SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { ConnectionString = "server=1231236;user id=123123;password=123123;persistsecurityinfo=True;database=pingjiae;", DbType = DbType.MySql, IsAutoCloseConnection = true });
            SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { ConnectionString = ConfigurationManager.ConnectionStrings["LeaRunFramework_MySqL"].ConnectionString, DbType = DbType.MySql, IsAutoCloseConnection = true });
            db.Aop.OnLogExecuting = (sql, pars) =>
            {
                //Console.WriteLine(sql + "\r\n" + db.Utilities.SerializeObject(pars.ToDictionary(it=>it.ParameterName,it=>it.Value)));
                //Console.WriteLine();
            };
            return db;
        }

二、 调用上面的方法：

 private readonly SqlSugarClient Db = null;
        public LuckyDrawBll()
        {
            Db = DemoBase.GetInstance();
        }

三、  SqlSugar常用的方法总结：

　　1. 第一种读取数据的方法。

string year = DateTime.Now.Year.ToString();
                string month = DateTime.Now.Month.ToString();
                string day = DateTime.Now.Day.ToString();
                string leaderboardluckylog = string.Format(" select * from fksd_leaderboardluckylog where UserId=@UserId and SiteId = @SiteId and prizeType=1 and year(CreateTime)=@year and month(CreateTime)=@month and day(CreateTime)=@day");
                List<SugarParameter> sugarParameters = new List<SugarParameter>();
                sugarParameters.Add(new SugarParameter("UserId", UserId));
                sugarParameters.Add(new SugarParameter("SiteId", SiteId));
                sugarParameters.Add(new SugarParameter("year", year));
                sugarParameters.Add(new SugarParameter("month", month));
                sugarParameters.Add(new SugarParameter("day", day));

                //查看抽奖日志
                List<fksd_leaderboardluckylog> log = Db.Ado.SqlQuery<fksd_leaderboardluckylog>(leaderboardluckylog, sugarParameters);

 　　2. 第二种读取数据的方法。

　　

string configSql = "select * from fksd_leaderboardlucky where UserId=@userId and SiteId=@siteId";
            fksd_leaderboardlucky configRusult = Db.Ado.SqlQuery<fksd_leaderboardlucky>(configSql, new { userId = userId, siteId = siteId }).FirstOrDefault();

 　　3. 第三种读取数据的方法。

string userPrizeList = "select b.UserName,c.PrizeType,c.PrizeName,a.CreateTime from fksd_leaderboardluckylog a left join fksdtb_user b on a.UserId=b.Id left join fksd_prize c on a.PrizeId=c.Id  where a.UserId=@userId and a.SiteId=@siteId and TO_DAYS(now()) - TO_DAYS(a.CreateTime) <=7 ";
            List<Getprizes> userList = Db.Ado.SqlQuery<Getprizes>(userPrizeList, new { userId, siteId });

　　4. 第四种读取数据的方法。

string configSql = "select * from task_config where RewardKey=@RewardKey";
            task_config configRusult = Db.Ado.SqlQuery<task_config>(configSql, new { RewardKey = RewardKey }).FirstOrDefault();

　　5. 第五种读取数据的方法

var leaderboard = Db.Queryable<fksd_leaderboardlucky>().First(w => w.SiteId == entity.SiteId && entity.UserId == w.UserId);

　　6. 执行插入的方法

Db.Insertable<fksd_leaderboardlucky>(entity).ExecuteCommand();

　　7. 执行更新的方法

Db.Updateable<fksd_leaderboardlucky>(leaderboard).ExecuteCommand();