C# 数据表查询语句 更新语句代码

分页

1.var pageList = service.GetPagedObjects(new WechatUser() { Status = 1 }, pageIndex, pageSize);

2.var list = service.GetPagedObjects<DocInvitationS>(sql, search.PageIndex, search.PageSize, totalUserCount);

3.var list = service.GetPagedObjects<Article>(new ArticleSearch { Title = title, PageIndex = pageIndex, PageSize = pageSize });

4.var lists = service.GetPagedObjects<Hospital>(search);

 

列表

1.var dbMeeting = this.GetObject<Meeting>(meeting.ID);

2.MSpeaker = this.GetObject(new MeetingSpeaker { MeetingSn = MeetingSn, Openid = user.OpenID });

3.var sublist = base.GetObjects(new Models.W_Library() { Parentid = t.ID }, "", "sortid asc");

4.dbMSpeakers = this.GetObjects(new MeetingSpeaker { MeetingSn = meeting.Sn }, " and Type in ('-1','0','1','2','3','5')");

5.var list = this.GetObjectsAll(new OperateLog(), search.GeneralWhere(), search.DbParameters, "ID");

 

 执行sql语句

DBContext db = new DBContext();

var sql = string.Empty;

sql = $"UPDATE dbo.MeetingSpeaker SET Modified=GETDATE(), Openid='{user.OpenID}' WHERE IsDeleted=0 AND (Openid='' OR Openid IS NULL) AND MeetingSn='{MeetingSn}' AND DoctorSn='{item.Sn}';";

if (!string.IsNullOrWhiteSpace(sql))
　　{
　　　　this.db.ExecuteNonQuerySql(sql);
　　}

 

获取sql语句第一个第一个的值

var dt = this.db.ExecuteDataTableSql("select [UserType] from WechatUser where OpenID=@OpenID AND IsDeleted=0 ",
new List<System.Data.Common.DbParameter>()
{
new System.Data.SqlClient.SqlParameter("@OpenID",OpenID){SqlDbType=System.Data.SqlDbType.NVarChar},
});
if (dt.Rows.Count > 0)
{
UserType = int.Parse(dt.Rows[0][0].ToString());
}

更新语句：只更新列IsOnline 

 service.Update(new Article { ID = ID, IsOnline = !article.IsOnline });

防注入

var sql = "select count(1) from UserPost_Relation where IsDeleted=0 and Relation_PostNum=@Relation_PostNum and Relation_RoleType=@RoleType and PostNum!=@PostNum";
var paramList = new List<System.Data.Common.DbParameter>();
paramList.Add(new System.Data.SqlClient.SqlParameter("@RoleType", RoleType) { SqlDbType = System.Data.SqlDbType.Int });
paramList.Add(new System.Data.SqlClient.SqlParameter("@PostNum", PostNum ) { SqlDbType = System.Data.SqlDbType.NVarChar });
paramList.Add(new System.Data.SqlClient.SqlParameter("@Relation_PostNum", Relation_PostNum) { SqlDbType = System.Data.SqlDbType.NVarChar });
var dt = this.db.ExecuteDataTableSql(sql, paramList);
return int.Parse(dt.Rows[0][0].ToString()) > 0;