var sql 的不同用法

1. 查询某列的string集合

var sql = "select distinct age from WechatUserDetail where IsDeleted=0 and LEN(ISNULL(Age,''))>0";
var table = db.ExecuteDataTableSql(sql);
return table.AsEnumerable().Select(item => item["age"].ToString()).ToList();

 

2. 参数防注入 结果转表结构

var sql = "SELECT UnionId FROM dbo.WechatUserTags WHERE IsDeleted=0 AND TagType=@tagType AND TagTypeChild=@tagTypeChild AND TagValue=tagValue;";
var data = db.ExecuteDataTableSql(sql, new List<System.Data.Common.DbParameter>()
{
new System.Data.SqlClient.SqlParameter("@tagType",item.TagType){SqlDbType=System.Data.SqlDbType.Int},
new System.Data.SqlClient.SqlParameter("@tagTypeChild",item.TagTypeChild){SqlDbType=System.Data.SqlDbType.Int},
new System.Data.SqlClient.SqlParameter("@tagValue",item.TagValue){SqlDbType=System.Data.SqlDbType.NVarChar},
});
var UnionIdArr = EntityReader.GetEntities<UnionIds>(data).ToList();

 

3. 返回首行首列

var sql = "select ID from WechatUser where isdeleted=0 and UnionId=@UnionId";
var tb = db.ExecuteDataTableSql(sql, new List<DbParameter> { new SqlParameter("@UnionId", unionid) });
if (tb != null && tb.Rows.Count > 0 && tb.Columns.Count > 0)
{
　　return Convert.ToInt32(tb.Rows[0][0]);
}