public static DapperResult QueryNormal(string sql, bool npre = true)
{
DapperResult dar = new DapperResult();
using (IDbConnection dbConnection = new SqlConnection(_conn))
{
try
{
var sql1 = ControlNPrefix(sql);
if (npre == false)
{
sql1 = sql;
}
IEnumerable <dynamic> obj = dbConnection.Query <dynamic>(sql1);
dar.rows = returnValue(obj);
dar.success = 1;
dar.msg = "searchsuccessful";
dar.total = obj.Count();
}
catch (Exception e)
{
dar.rows = null;
dar.success = 0;
dar.msg = e.Message;
dar.total = 0;
}
}
return(dar);
}
public static DapperResult CommonSqlPage(string sqlid, IIdentity _identity, int startpage, int limit, string orderby)
{
var result = string.Empty;
var dar = new DapperResult();
var _sql = $@"
SELECT sqlcontent
FROM TBL_T_DataSql a
LEFT JOIN tb_SQL b ON a.sqlid = b.NewSqlID
WHERE
CAST(a.sqlid AS NVARCHAR(50)) = '{sqlid}'
OR b.sqlid = '{sqlid}'";
List <Dictionary <string, object> > sqls = QueryNormal(_sql, false).rows;
if (sqls.Count == 0)
{
dar.rows = null;
dar.total = 0;
dar.msg = "Querynodata";
dar.success = 0;
return(dar);
}
string sql = sqls[0]["sqlcontent"].ToString();
sql = sql.Replace("@RenYuanId", CharacterUtil.SQLEncode(ExtendIdentity.GetUserId(_identity))).Replace("@CompanyCode", CharacterUtil.SQLEncode(ExtendIdentity.GetOrganizationId(_identity)));
return(QueryPage(sql, startpage, limit, orderby));
}
public static DapperResult QueryPageToContract(string sql, int offset, int limit, string orderby = "")
{
int startpage = offset;
string pagetype = ConfigurationManager.AppSettings["PageType"];
if (pagetype.Equals("1"))
{
startpage = offset / limit;
}
DapperResult dar = new DapperResult();
//var end = startpage * limit;
//var start = (startpage - 1) * limit + 1;
///以下是从0 开始
var end = (startpage + 1) * limit;
var start = (startpage) * limit + 1;
if (string.IsNullOrEmpty(orderby))
{
int pos = sql.ToLower().LastIndexOf("order by");
orderby = sql.Substring(pos + 8);
sql = sql.Substring(0, pos);
}
var newsql = string.Format(@"select * from (
select ROW_NUMBER() over(order by {1}) as num, m.* from (
select TOP 100 PERCENT * from ({0}) a order BY {1}
) m where 1=1
) as t
where num between {2} and {3} ", sql, orderby, start, end);
using (IDbConnection dbConnection = new SqlConnection(_conn))
{
try
{
//int obj = dbConnection.ExecuteScalar<int>(ControlNPrefix("SELECT COUNT(1) from(" + sql + ") a"));
//var obj1 = dbConnection.Query<dynamic>(ControlNPrefix(newsql));
var obj3 = dbConnection.Query <dynamic>(ControlNPrefix(sql));
var obj = obj3.Count();
var obj1 = obj3.Skip(limit * startpage).Take(limit).ToList();//进行分页
dar.rows = returnValue(obj1);
dar.success = 1;
dar.msg = "searchsuccessful";
dar.total = obj;
}
catch (Exception e)
{
dar.rows = null;
dar.success = 0;
dar.msg = e.Message;
dar.total = 0;
}
}
return(dar);
}
public static DapperResult QueryPageToContract(IIdentity _identity, string xml, string sql)
{
Hashtable ht = new Hashtable();
ht = Utility.HtFromPage(_identity);
//ht.Add("CompanyCode", CharacterUtil.SQLEncode(orgId));
var esql = SQLLoaderComponent.GetSQLQuery(xml, sql, ht);
var dar = new DapperResult();
if (ht["offset"] == null)
{
dar = DapperContext.QueryNormal(esql);
}
else
{
dar = DapperContext.QueryPageToContract(esql, int.Parse(ht["offset"].ToString().Replace("'", "")), int.Parse(ht["limit"].ToString().Replace("'", "")));
}
return(dar);
}
public static DapperResult PageResult(string aa, string xml, string sql)
{
//Hashtable ht = Utility.HtFromPage(_identity);
Hashtable ht = new Hashtable();
var esql = SQLLoaderComponent.GetSQLQuery(xml, sql, ht);
var dar = new DapperResult();
if (ht["offset"] == null)
{
dar = DapperContext.QueryNormal(esql);
}
else
{
dar = DapperContext.QueryPage(esql, int.Parse(ht["offset"].ToString().Replace("'", "")), int.Parse(ht["limit"].ToString().Replace("'", "")));
}
dar.msg = esql;
return(dar);
}
public static DapperResult CommonSql(string sqlid, IIdentity _identity)
{
var result = string.Empty;
var dar = new DapperResult();
var _sql = string.Format(@"
SELECT sqlcontent
FROM TBL_T_DataSql a
LEFT JOIN tb_SQL b ON a.sqlid = b.NewSqlID
WHERE
CAST(a.sqlid AS NVARCHAR(50)) = '{0}'
OR b.sqlid = '{0}'", sqlid);
var dr = QueryNormal(_sql, false);
if (dr == null || dr.success == 0 || dr.rows == null)
{
dar.rows = null;
dar.total = 0;
dar.msg = "Querynodata";
dar.success = 0;
return(dar);
}
List <Dictionary <string, object> > sqls = dr.rows;
if (sqls.Count == 0)
{
dar.rows = null;
dar.total = 0;
dar.msg = "Querynodata";
dar.success = 0;
return(dar);
}
string sql = sqls[0]["sqlcontent"].ToString();
sql = sql.Replace("@RenYuanId", CharacterUtil.SQLEncode(ExtendIdentity.GetUserId(_identity))).Replace("@CompanyCode", CharacterUtil.SQLEncode(ExtendIdentity.GetOrganizationId(_identity)));
// sql = ControlNPrefix(sql);
return(QueryNormal(sql, false));
}
public static DapperResult Execute(string sql)
{
DapperResult dar = new DapperResult();
using (IDbConnection dbConnection = new SqlConnection(_conn))
{
lock (_lockService)
{
try
{
int obj = dbConnection.Execute(ControlNPrefix(sql));
if (obj > 0)
{
dar.rows = null;
dar.success = 1;
dar.msg = "插入/更新/删除条目为" + obj;
dar.total = 0;
}
else
{
dar.rows = null;
dar.success = 0;
dar.msg = "插入/更新/删除条目为0";
dar.total = 0;
}
}
catch (Exception e)
{
dar.rows = null;
dar.success = 0;
dar.msg = e.Message;
dar.total = 0;
}
}
}
return(dar);
}
public static async Task <int> ExecuteAsync(string sql)
{
DapperResult dar = new DapperResult();
using (IDbConnection dbConnection = new SqlConnection(_conn))
{
try
{
Task <int> obj = dbConnection.ExecuteAsync(ControlNPrefix(sql));
obj.Start();
int result = obj.Result;
if (result > 0)
{
dar.rows = null;
dar.success = 1;
dar.msg = "插入/更新/删除条目为" + obj;
dar.total = 0;
}
else
{
dar.rows = null;
dar.success = 0;
dar.msg = "插入/更新/删除条目为0";
dar.total = 0;
}
}
catch (Exception e)
{
dar.rows = null;
dar.success = 0;
dar.msg = e.Message;
dar.total = 0;
}
}
return(dar.success);
}
public static DapperResult ExecuteTrans(List <string> sql)
{
DapperResult dar = new DapperResult();
using (IDbConnection dbConnection = new SqlConnection(_conn))
{
lock (_lockService)
{
dbConnection.Open();
IDbTransaction transaction = dbConnection.BeginTransaction();
try
{
foreach (string a in sql)
{
dbConnection.Execute(ControlNPrefix(a), null, transaction);
}
transaction.Commit();
dar.rows = null;
dar.success = 1;
dar.msg = "插入/更新/删除成功";
dar.total = 0;
}
catch (Exception e)
{
dar.rows = null;
dar.success = 0;
dar.msg = e.Message;
dar.total = 0;
transaction.Rollback();
}
}
}
return(dar);
}
