sqlsugar 链接mysql_SqlSugar简单使用封装

一、SqlSugar简单使用封装

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

public staticSqlSugarClient 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();

};returndb;

}

二、 调用上面的方法：

private readonly SqlSugarClient Db = null;publicLuckyDrawBll()

{

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 sugarParameters = new List();

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 log = Db.Ado.SqlQuery(leaderboardluckylog, sugarParameters);

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

string configSql = "select * from fksd_leaderboardlucky where UserId=@userId and SiteId=@siteId";

fksd_leaderboardlucky configRusult= Db.Ado.SqlQuery(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 userList = Db.Ado.SqlQuery(userPrizeList, new { userId, siteId });

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

string configSql = "select * from task_config where RewardKey=@RewardKey";

task_config configRusult= Db.Ado.SqlQuery(configSql, new { RewardKey = RewardKey }).FirstOrDefault();

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

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

6. 执行插入的方法

Db.Insertable(entity).ExecuteCommand();

7. 执行更新的方法

Db.Updateable(leaderboard).ExecuteCommand();