/// <summary>
/// 创建SQL实体文件
/// </summary>
public void CreateClassFiles(SqlSugarClient db, string fileDirectory, string nameSpace = null)
{
var tables = db.GetDataTable("select name from sysobjects where xtype in ('U','V') ");
if (tables != null && tables.Rows.Count > 0)
{
foreach (DataRow dr in tables.Rows)
{
string tableName = dr["name"].ToString();
var currentTable = db.GetDataTable(string.Format("select top 1 * from {0}", tableName));
var classCode = DataTableToClass(currentTable, tableName, nameSpace);
FileSugar.WriteText(fileDirectory.TrimEnd('\\') + "\\" + tableName + ".cs", classCode);
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
string connStr = "server=.;uid=sa;password=sasa;database=nfd";
using (SqlSugarClient db = new SqlSugarClient(connStr))
{
db.BeginTran();
db.Sqlable.IsNoLock = true;
try
{
var sql = db.Sqlable.MappingTable<CutBill, CutBillShipment>("t1.c_id=t2.c_id").SelectToSql("t1.*");
var dt = db.GetDataTable(sql);
var id = db.Insert(new test() { name = "哈哈" + DateTime.Now });
var del = db.Delete<test>(21);
var update = db.Update<test>(new { name = "5555" + DateTime.Now }, new { id=1 });
}
catch (Exception)
{
db.RollbackTran();
}
}
;
//var xx = SqlTool.CreateMappingTable(20);
Console.Read();
}
/// <summary>
/// 创建SQL实体文件,指定表名
/// </summary>
public void CreateClassFilesByTableNames(SqlSugarClient db, string fileDirectory, string nameSpace, params string[] tableNames)
{
var isLog = db.IsEnableLogEvent;
db.IsEnableLogEvent = false;
string sql = SqlSugarTool.GetCreateClassSql(null);
var tables = db.GetDataTable(sql);
if (!FileSugar.IsExistDirectory(fileDirectory))
{
FileSugar.CreateDirectory(fileDirectory);
}
if (tables != null && tables.Rows.Count > 0)
{
foreach (DataRow dr in tables.Rows)
{
string tableName = dr["name"].ToString();
if (tableNames.Any(it => it.ToLower() == tableName.ToLower()))
{
var currentTable = db.GetDataTable(string.Format(SqlSugarTool.GetSelectTopSql(), GetTableNameWithSchema(db,tableName).GetTranslationSqlName()));
var tableColumns = GetTableColumns(db, tableName);
string className = db.GetClassTypeByTableName(tableName);
var classCode = DataTableToClass(currentTable, className, nameSpace, tableColumns);
FileSugar.CreateFile(fileDirectory.TrimEnd('\\') + "\\" + className + ".cs", classCode, Encoding.UTF8);
}
}
}
db.IsEnableLogEvent = isLog;
}
protected void Page_Load(object sender, EventArgs e)
{
PerformanceTest pt = new PerformanceTest();
pt.SetCount(1000);
using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString()))
{
//ado.GetDataTable
pt.Execute(i =>
{
db.GetDataTable(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id");
}, m => { }, "ado.DateTable 纯SQL写法");
//dapper
var conn = db.GetConnection();
pt.Execute(i =>
{
conn.Query<Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id").ToList();
}, m => { }, "dapper 纯SQL写法");
//sqlSugar
pt.Execute(i =>
{
db.Sqlable().Form("Student", "s")
.Join("School","sc","sc.id","s.sch_id",JoinType.INNER)
.Join("subject","sb","sb.sid","s.id",JoinType.LEFT).SelectToList<Models.Student>("*");
}, m => { }, "sqlSugar SQL语法糖");
//sqlSugar
pt.Execute(i =>
{
db.SqlQuery<Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id").ToList();
}, m => { }, "sqlSugar 纯SQL写法");
}
using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities())
{
//EF
pt.Execute(i =>
{
db.ExecuteStoreQuery<WebTest.TestLib.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id");
}, m => { }, "EF4.0+sql05 纯SQL写法");
}
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
/// <summary>
/// 根据表名获取实体类的字符串
/// </summary>
/// <param name="db"></param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public string TableNameToClass(SqlSugarClient db, string tableName)
{
var dt = db.GetDataTable(string.Format(SqlSugarTool.GetSelectTopSql(), tableName.GetTranslationSqlName()));
var tableColumns = GetTableColumns(db, tableName);
var reval = DataTableToClass(dt, tableName, null, tableColumns);
return(reval);
}
protected void Page_Load(object sender, EventArgs e)
{
PerformanceTest pt = new PerformanceTest();
pt.SetCount(10000);
using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString()))
{
//ado.GetDataTable
pt.Execute(i =>
{
db.GetDataTable(@"select * from (select s.*,row_number() over (order by s.id asc,s.name desc) r from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id where s.id>@id1 and s.id>@id2) t where t.r between @b and @e", new SqlParameter("@id1", "1"), new SqlParameter("@id2", "2"), new SqlParameter("@b", "11"), new SqlParameter("@e", "20"));
}, m => { }, "ado.DateTable 纯SQL写法");
//dapper
var conn = db.GetConnection();
pt.Execute(i =>
{
conn.Query <Models.Student>(@"select * from (select s.*,row_number() over (order by s.id asc,s.name desc) r from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id where s.id>@id1 and s.id>@id2) t where t.r between @b and @e", new { id1 = 1, id2 = 2, b = 11, e = 20 }).ToList();
}, m => { }, "dapper 纯SQL写法");
//sqlSugar
pt.Execute(i =>
{
db.Sqlable().From("Student", "s")
.Join("School", "sc", "sc.id", "s.sch_id", JoinType.INNER)
.Join("subject", "sb", "sb.sid", "s.id", JoinType.LEFT).Where("s.id>@id1").Where("s.id>@id2")
.SelectToPageList <Models.Student>("s.*", "s.id asc,s.name desc", 2, 10, new { id1 = 1, id2 = 2 });
}, m => { }, "sqlSugar SQL语法糖");
}
using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities())
{
//EF
pt.Execute(i =>
{
var reval = (from s in db.Student
join sc in db.School on s.sch_id equals sc.id
join sb in db.Subject on s.id equals sb.sid
into ssb
from sb2 in ssb.DefaultIfEmpty()
select new {
s.id,
s.name,
s.sch_id,
s.sex
}).Where(c => c.id > 1).Where(c => c.id > 2).OrderBy(c => c.id).ThenByDescending(c => c.name).Skip(10).Take(10).ToList();
}, m => { }, "EF4.0+sql05 LINQ TO SQL");
}
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
public DataTable GetSelectionRatioReportDataTable(string startDate, string endDate)
{
DataTable selectionRatioReport = new DataTable();
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
selectionRatioReport = _dbMsSql.GetDataTable("exec usp_SelectionRatioReport @StartDate,@EndDate", new { StartDate = startDate, EndDate = endDate });
selectionRatioReport.TableName = "table";
}
return(selectionRatioReport);
}
public DataTable GetPersionSelectionRatioReportDataTable(string startDate, string endDate)
{
DataTable persionSelectionRatios = new DataTable();
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
persionSelectionRatios = _dbMsSql.GetDataTable("exec usp_PersionSelectionRatioReport @StartDate,@EndDate", new { StartDate = startDate, EndDate = endDate });
persionSelectionRatios.TableName = "table";
}
return(persionSelectionRatios);
}
/// <summary>
/// 创建SQL实体文件
/// </summary>
/// <param name="db"></param>
/// <param name="tableOrView">是生成视图文件还是表文件,null生成表和视图，true生成表，false生成视图(默认为：null)</param>
/// <param name="callBack">回调函数</param>
public void CreateClassFilesInterface(SqlSugarClient db, bool?tableOrView, Action <DataTable, string, string> callBack)
{
var isLog = db.IsEnableLogEvent;
db.IsEnableLogEvent = false;
string sql = SqlSugarTool.GetCreateClassSql(tableOrView);
var tables = db.GetDataTable(sql);
if (tables != null && tables.Rows.Count > 0)
{
foreach (DataRow dr in tables.Rows)
{
string tableName = dr["name"].ToString();
var currentTable = db.GetDataTable(string.Format(SqlSugarTool.GetSelectTopSql(), GetTableNameWithSchema(db, tableName).GetTranslationSqlName()));
string className = db.GetClassTypeByTableName(tableName);
callBack(tables, className, tableName);
}
}
db.IsEnableLogEvent = isLog;
}
protected void Page_Load(object sender, EventArgs e)
{
PerformanceTest pt = new PerformanceTest();
pt.SetCount(1000);
using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString()))
{
//ado.GetDataTable
pt.Execute(i =>
{
db.GetDataTable(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id");
}, m => { }, "ado.DateTable 纯SQL写法");
//dapper
var conn = db.GetConnection();
pt.Execute(i =>
{
conn.Query <Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id").ToList();
}, m => { }, "dapper 纯SQL写法");
//sqlSugar
pt.Execute(i =>
{
db.Sqlable().Form("Student", "s")
.Join("School", "sc", "sc.id", "s.sch_id", JoinType.INNER)
.Join("subject", "sb", "sb.sid", "s.id", JoinType.LEFT).SelectToList <Models.Student>("*");
}, m => { }, "sqlSugar SQL语法糖");
//sqlSugar
pt.Execute(i =>
{
db.SqlQuery <Models.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id").ToList();
}, m => { }, "sqlSugar 纯SQL写法");
}
using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities())
{
//EF
pt.Execute(i =>
{
db.ExecuteStoreQuery <WebTest.TestLib.Student>(@"select * from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id");
}, m => { }, "EF4.0+sql05 纯SQL写法");
}
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
public void Init()
{
using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接
{
var r1 = db.GetDataTable("select * from student");
var r2 = db.GetSingle <Student>("select top 1 * from student");
var r3 = db.GetScalar("select count(1) from student");
var r4 = db.GetReader("select count(1) from student");
r4.Dispose();
var r5 = db.GetString("select top 1 name from student");
var r6 = db.ExecuteCommand("select 1");
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接
{
var r1 = db.GetDataTable("select * from student");
var r2 = db.GetSingle <Student>("select * from student limit 0,1");
var r3 = db.GetScalar("select count(1) from student");
var r4 = db.GetReader("select count(1) from student");
r4.Dispose();
var r5 = db.GetString("select name from student limit 0,1");
var r6 = db.ExecuteCommand("select 1");
}
}
protected void Page_Load(object sender, EventArgs e)
{
using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接
{
var r1 = db.GetDataTable("select * from Student ");
var r2 = db.GetSingle <STUDENT>("SELECT * FROM (SELECT * FROM Student ORDER BY id DESC ) A where ROWNUM=1");
var r3 = db.GetScalar("select count(1) from Student");
var r4 = db.GetReader("select count(1) from Student");
r4.Dispose();
var r5 = db.GetString("SELECT name FROM (SELECT * FROM Student ORDER BY id DESC ) A where ROWNUM=1");
var r6 = db.ExecuteCommand("delete from Student where id=20");
}
}
/// <summary>
///根据表名获取自添列 keyTableName Value columnName
/// </summary>
/// <param name="db"></param>
/// <param name="tableName"></param>
/// <returns></returns>
internal static List <KeyValue> GetIdentitiesKeyByTableName(SqlSugarClient db, string tableName)
{
string key = "GetIdentityKeyByTableName" + tableName;
var cm = CacheManager <List <KeyValue> > .GetInstance();
List <KeyValue> identityInfo = null;
if (cm.ContainsKey(key))
{
identityInfo = cm[key];
return(identityInfo);
}
else
{
string sql = string.Format(@"
declare @Table_name varchar(60)
set @Table_name = '{0}';
Select so.name tableName, --表名字
sc.name keyName, --自增字段名字
ident_current(so.name) curr_value, --自增字段当前值
ident_incr(so.name) incr_value, --自增字段增长值
ident_seed(so.name) seed_value --自增字段种子值
from sysobjects so
Inner Join syscolumns sc
on so.id = sc.id
and columnproperty(sc.id, sc.name, 'IsIdentity') = 1
Where upper(so.name) = upper(@Table_name)
", tableName);
var isLog = db.IsEnableLogEvent;
db.IsEnableLogEvent = false;
var dt = db.GetDataTable(sql);
db.IsEnableLogEvent = isLog;
identityInfo = new List <KeyValue>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
identityInfo.Add(new KeyValue()
{
Key = dr["tableName"].ToString().ToLower(), Value = dr["keyName"].ToString()
});
}
}
cm.Add(key, identityInfo, cm.Day);
return(identityInfo);
}
}
protected void Page_Load(object sender, EventArgs e)
{
PerformanceTest pt = new PerformanceTest();
pt.SetCount(10000);
int id = 1;
using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString()))
{
//ado.GetDataTable
pt.Execute(i =>
{
db.GetDataTable("select * from Student WHERE ID>@id", new SqlParameter("@id", id));
}, m => { }, "ado.DateTable 纯SQL写法");
//dapper
var conn = db.GetConnection();
pt.Execute(i =>
{
conn.Query<Models.Student>("select * from Student where id>@id", new { id = id}).ToList();
}, m => { }, "dapper 纯SQL写法");
//sqlSugar
pt.Execute(i =>
{
db.Queryable<Models.Student>().Where(c => c.id == id).ToList();
}, m => { }, "sqlSugar 拉姆达");
}
using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities())
{
//EF
pt.Execute(i =>
{
db.Student.Where(c => c.id == id).ToList();
}, m => { }, "EF4.0+sql05 拉姆达");
}
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
/// <summary>
/// 创建实体文件
/// </summary>
/// <param name="db"></param>
/// <param name="fileDirectory"></param>
/// <param name="nameSpace">命名空间（默认：system）</param>
/// <param name="tableOrView">是生成视图文件还是表文件,null生成表和视图，true生成表，false生成视图(默认为：null)</param>
/// <param name="callBack">生成文件后的处理，参数string为实体名</param>
/// <param name="preAction">生成文件前的处理，参数string为表名</param>
public void CreateClassFiles(SqlSugarClient db, string fileDirectory, string nameSpace = null, bool?tableOrView = null, Action <string> callBack = null, Action <string> preAction = null)
{
var isLog = db.IsEnableLogEvent;
db.IsEnableLogEvent = false;
string sql = SqlSugarTool.GetCreateClassSql(tableOrView);
var tables = db.GetDataTable(sql);
if (tables != null && tables.Rows.Count > 0)
{
foreach (DataRow dr in tables.Rows)
{
string tableName = dr["name"].ToString();
if (preAction != null)
{
preAction(tableName);
}
var currentTable = db.GetDataTable(string.Format(SqlSugarTool.GetSelectTopSql(), GetTableNameWithSchema(db, tableName).GetTranslationSqlName()));
if (callBack != null)
{
var tableColumns = GetTableColumns(db, tableName);
var classCode = DataTableToClass(currentTable, tableName, nameSpace, tableColumns);
string className = db.GetClassTypeByTableName(tableName);
classCode = classCode.Replace("class " + tableName, "class " + className);
FileSugar.CreateFile(fileDirectory.TrimEnd('\\') + "\\" + className + ".cs", classCode, Encoding.UTF8);
callBack(className);
}
else
{
var tableColumns = GetTableColumns(db, tableName);
string className = db.GetClassTypeByTableName(tableName);
var classCode = DataTableToClass(currentTable, className, nameSpace, tableColumns);
FileSugar.CreateFile(fileDirectory.TrimEnd('\\') + "\\" + className + ".cs", classCode, Encoding.UTF8);
}
}
}
db.IsEnableLogEvent = isLog;
}
/// <summary>
/// 导出
/// </summary>
/// <param name="exerciseVguid">习题Vguid</param>
/// <param name="exportType">导出类型</param>
/// <param name="departmentVguid">部门vguid</param>
public void Export(string exerciseVguid, string exportType, string departmentVguid)
{
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
string sql = string.Format(@"EXEC usp_ExercisesAnswerUser @ExercisesVGUID,@Type,@DepartmentVGUID");
DataTable dt = new DataTable();
string departVguid = string.Empty;
if (CurrentUser.GetCurrentUser().LoginName == "sysAdmin")
{
departVguid = string.IsNullOrEmpty(departmentVguid) ? "" : departmentVguid;
}
else
{
departVguid = CurrentUser.GetCurrentUser().Department;
if (!string.IsNullOrEmpty(departmentVguid))
{
Guid dep = Guid.Parse(departVguid);
var listDep = _dbMsSql.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + dep + "')"); //找到该部门以及其所有子部门
Guid depVguid = Guid.Parse(departmentVguid);
departVguid = listDep.Contains(depVguid) ? departmentVguid : departVguid;
}
}
dt = _dbMsSql.GetDataTable(sql, new
{
ExercisesVGUID = exerciseVguid,
Type = exportType,
DepartmentVGUID = departVguid
});
dt.TableName = "table";
DataView dv = dt.DefaultView;
dv.Sort = "Department";
dt = dv.ToTable();
//因为导出模板不一样（exportType为3/4/5的是一个模板,其他的是一个模板）
if (exportType == "3" || exportType == "4" || exportType == "5")
{
string amountFileName = SyntacticSugar.ConfigSugar.GetAppString("ScoreTemplate");
Common.ExportExcel.ExportExcels("ScoreTemplate.xlsx", amountFileName, dt);
_logLogic.SaveLog(13, 22, Common.CurrentUser.GetCurrentUser().LoginName, "ScoreTemplate", Common.Tools.DataTableHelper.Dtb2Json(dt));
}
else
{
string amountFileName = SyntacticSugar.ConfigSugar.GetAppString("PersonReportTemplate");
Common.ExportExcel.ExportExcels("ReprotPersonTemplate.xlsx", amountFileName, dt);
_logLogic.SaveLog(13, 22, Common.CurrentUser.GetCurrentUser().LoginName, "PersonReportTemplate", Common.Tools.DataTableHelper.Dtb2Json(dt));
}
}
}
public DataTable GetExerciseTotalSource(string start, string end, string dept)
{
DataTable dt = new DataTable();
if (string.IsNullOrEmpty(dept))
{
dept = CurrentUser.GetCurrentUser().Department;
}
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
dt = _dbMsSql.GetDataTable(string.Format("exec usp_CT_Total '{0}-01','{1}-01','{2}'", start, end, dept));
dt.TableName = "table";
}
return(dt);
}
/// <summary>
/// 创建实体文件
/// </summary>
/// <param name="db"></param>
/// <param name="fileDirectory"></param>
/// <param name="nameSpace">命名空间（默认：system）</param>
/// <param name="tableOrView">是生成视图文件还是表文件,null生成表和视图，true生成表，false生成视图(默认为：null)</param>
/// <param name="callBack">生成文件后的处理，参数string为实体名</param>
/// <param name="preAction">生成文件前的处理，参数string为表名</param>
public void CreateClassFiles(SqlSugarClient db, string fileDirectory, string nameSpace = null, bool? tableOrView = null, Action<string> callBack = null, Action<string> preAction = null)
{
var isLog = db.IsEnableLogEvent;
db.IsEnableLogEvent = false;
string sql = SqlSugarTool.GetCreateClassSql(tableOrView);
var tables = db.GetDataTable(sql);
if (tables != null && tables.Rows.Count > 0)
{
foreach (DataRow dr in tables.Rows)
{
string tableName = dr["name"].ToString();
if (preAction != null)
{
preAction(tableName);
}
var currentTable = db.GetDataTable(string.Format(SqlSugarTool.GetSelectTopSql(), GetTableNameWithSchema(db,tableName).GetTranslationSqlName()));
if (callBack != null)
{
var tableColumns = GetTableColumns(db, tableName);
var classCode = DataTableToClass(currentTable, tableName, nameSpace, tableColumns);
string className = db.GetClassTypeByTableName(tableName);
classCode = classCode.Replace("class " + tableName, "class " + className);
FileSugar.CreateFile(fileDirectory.TrimEnd('\\') + "\\" + className + ".cs", classCode, Encoding.UTF8);
callBack(className);
}
else
{
var tableColumns = GetTableColumns(db, tableName);
string className = db.GetClassTypeByTableName(tableName);
var classCode = DataTableToClass(currentTable, className, nameSpace, tableColumns);
FileSugar.CreateFile(fileDirectory.TrimEnd('\\') + "\\" + className + ".cs", classCode, Encoding.UTF8);
}
}
}
db.IsEnableLogEvent = isLog;
}
/// <summary>
/// 根据表获取主键
/// </summary>
/// <param name="db"></param>
/// <param name="tableName"></param>
/// <returns></returns>
internal static string GetPrimaryKeyByTableName(SqlSugarClient db, string tableName)
{
string key = "GetPrimaryKeyByTableName" + tableName;
tableName = tableName.ToLower();
var cm = CacheManager <List <KeyValue> > .GetInstance();
List <KeyValue> primaryInfo = null;
//获取主键信息
if (cm.ContainsKey(key))
{
primaryInfo = cm[key];
}
else
{
string sql = @" SELECT a.name as keyName ,d.name as tableName
FROM syscolumns a
inner join sysobjects d on a.id=d.id
where exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
)))";
var isLog = db.IsEnableLogEvent;
db.IsEnableLogEvent = false;
var dt = db.GetDataTable(sql);
db.IsEnableLogEvent = isLog;
primaryInfo = new List <KeyValue>();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
primaryInfo.Add(new KeyValue()
{
Key = dr["tableName"].ToString().ToLower(), Value = dr["keyName"].ToString()
});
}
}
cm.Add(key, primaryInfo, cm.Day);
}
//反回主键
if (!primaryInfo.Any(it => it.Key == tableName))
{
return(null);
}
return(primaryInfo.First(it => it.Key == tableName).Value);
}
protected void Page_Load(object sender, EventArgs e)
{
PerformanceTest pt = new PerformanceTest();
pt.SetCount(10000);
using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString()))
{
//ado.GetDataTable
pt.Execute(i =>
{
db.GetDataTable("select * from(select *,row_number() over(order by id) as r from Student where id>@id) t where t.r between @b and @e", new SqlParameter("@id","0"), new SqlParameter("@b", 11), new SqlParameter("@e", 20));
}, m => { }, "ado.DateTable 纯SQL写法");
//dapper
var conn = db.GetConnection();
pt.Execute(i =>
{
conn.Query<Models.Student>("select * from(select *,row_number() over(order by id) as r from Student where id>@id) t where t.r between @b and @e", new { id = 0,b=11,e=20 }).ToList();
}, m => { }, "dapper 纯SQL写法");
//sqlSugar
pt.Execute(i =>
{
db.Queryable<Models.Student>().Where(c => c.id > 0).OrderBy("id").ToPageList(2, 10);
}, m => { }, "sqlSugar 拉姆达");
}
using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities())
{
//EF
pt.Execute(i =>
{
db.Student.Where(c => c.id == 2).OrderBy(c=>c.id).Skip(10).Take(10).ToList();
}, m => { }, "EF4.0+sql05 拉姆达");
}
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
/// <summary>
/// 获取导出类型列表
/// </summary>
/// <returns></returns>
//public List<CS_Master_2> GetExportTypeList()
//{
// using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
// {
// List<CS_Master_2> exportTypeList = new List<CS_Master_2>();
// Guid exportTypeVguid = Guid.Parse(Common.Tools.MasterVGUID.ExportType);
// exportTypeList = _dbMsSql.Queryable<CS_Master_2>().Where(i => i.VGUID == exportTypeVguid).ToList().OrderBy("MasterCode", OrderByType.Asc).ToList();
// return exportTypeList;
// }
//}
/// <summary>
/// 查询问卷报表信息列表
/// </summary>
/// <param name="searchQuestionReport"></param>
/// <returns></returns>
public U_QuestionReport GetQuestionReport(Search_QuestionReport searchQuestionReport)
{
using (SqlSugarClient _dbMsSql = SugarDao.SugarDao_MsSql.GetInstance())
{
U_QuestionReport reportEntity = new U_QuestionReport();
string sql = @"EXEC usp_ExercisesAnswerquestionsInfotmation @ExercisesName,@DepartmentVGUID";
DataTable dt = new DataTable();
dt = _dbMsSql.GetDataTable(sql, new
{
QuestionName = searchQuestionReport.QuestionName,
DepartmentVGUID = string.IsNullOrEmpty(CurrentUser.GetCurrentUser().Department) ? "" : CurrentUser.GetCurrentUser().Department
});
//reportEntity.questionMain = GetExerciseMainList(dt);
return(reportEntity);
}
}
public static void Main(string[] args)
{
using (SqlSugarClient db = new SqlSugarClient("server=.;uid=sa;pwd=sasa;database=SqlSugarTest"))
{
var dt = db.GetDataTable("select * from student where id=@id", new { id = 1 });
//设置执行的DEMO
string switch_on = "CreateClass";
IDemos demo = null;
switch (switch_on)
{
//ADO.NET基本功能
case "Ado": demo = new Ado(); break;
//查询
case "Select": demo = new Select(); break;
//插入
case "Insert": demo = new Insert(); break;
//更新
case "Update": demo = new Update(); break;
//删除
case "Delete": demo = new Delete(); break;
//事务
case "Tran": demo = new Tran(); break;
//生成实体
case "CreateClass": demo = new CreateClass(); break;
//枚举类型的支持
case "EnumType": demo = new EnumType(); break;
//除了多库并行计算外的所有功能都已经移植成功更多例子请关注我的博客
}
//执行DEMO
demo.Init();
//更多例子请查看API
//http://www.cnblogs.com/sunkaixuan/p/5654695.html
Console.WriteLine("执行成功请关闭窗口 ,更多例子请查看API:http://www.cnblogs.com/sunkaixuan/p/5654695.html");
Console.ReadKey();
}
}
/// <summary>
/// 获取推送列表
/// </summary>
/// <param name="searchParam"></param>
/// <param name="para"></param>
/// <returns></returns>
public List <U_WeChatPushRate> GetSysUserListBySearch(string pushMsgName)
{
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
List <U_WeChatPushRate> weChatPushRateList = new List <U_WeChatPushRate>();
string sql = string.Format(@"EXEC usp_WeChatPushRate @Title,@userVguid");
DataTable dt = new DataTable();
dt = _dbMsSql.GetDataTable(sql, new
{
Title = pushMsgName,
userVguid = CurrentUser.GetCurrentUser().Vguid
});
weChatPushRateList = GetPushMsgRateList(dt);
return(weChatPushRateList);
}
}
protected void Page_Load(object sender, EventArgs e)
{
PerformanceTest pt = new PerformanceTest();
pt.SetCount(10000);
int id = 1;
using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString()))
{
//ado.GetDataTable
pt.Execute(i =>
{
db.GetDataTable("select * from Student WHERE ID>@id", new SqlParameter("@id", id));
}, m => { }, "ado.DateTable 纯SQL写法");
//dapper
var conn = db.GetConnection();
pt.Execute(i =>
{
conn.Query <Models.Student>("select * from Student where id>@id", new { id = id }).ToList();
}, m => { }, "dapper 纯SQL写法");
//sqlSugar
pt.Execute(i =>
{
db.Queryable <Models.Student>().Where(c => c.id == id).ToList();
}, m => { }, "sqlSugar 拉姆达");
}
using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities())
{
//EF
pt.Execute(i =>
{
db.Student.Where(c => c.id == id).ToList();
}, m => { }, "EF4.0+sql05 拉姆达");
}
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
/// <summary>
/// 导出
/// </summary>
/// <param name="pushMsgName"></param>
public void Export(string pushMsgName)
{
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
string sql = string.Format(@"EXEC usp_WeChatPushRate @Title,@userVguid");
DataTable dt = new DataTable();
dt = _dbMsSql.GetDataTable(sql, new
{
Title = pushMsgName,
userVguid = CurrentUser.GetCurrentUser().Vguid
});
dt.TableName = "table";
string amountFileName = SyntacticSugar.ConfigSugar.GetAppString("PushTemplate");
Common.ExportExcel.ExportExcels("PushTemplate.xlsx", amountFileName, dt);
_logLogic.SaveLog(13, 23, Common.CurrentUser.GetCurrentUser().LoginName, "PushTemplate", Common.Tools.DataTableHelper.Dtb2Json(dt));
}
}
protected void Page_Load(object sender, EventArgs e)
{
PerformanceTest pt = new PerformanceTest();
pt.SetCount(10000);
using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString()))
{
//ado.GetDataTable
pt.Execute(i =>
{
db.GetDataTable("select * from(select *,row_number() over(order by id) as r from Student where id>@id) t where t.r between @b and @e", new SqlParameter("@id", "0"), new SqlParameter("@b", 11), new SqlParameter("@e", 20));
}, m => { }, "ado.DateTable 纯SQL写法");
//dapper
var conn = db.GetConnection();
pt.Execute(i =>
{
conn.Query <Models.Student>("select * from(select *,row_number() over(order by id) as r from Student where id>@id) t where t.r between @b and @e", new { id = 0, b = 11, e = 20 }).ToList();
}, m => { }, "dapper 纯SQL写法");
//sqlSugar
pt.Execute(i =>
{
db.Queryable <Models.Student>().Where(c => c.id > 0).OrderBy("id").ToPageList(2, 10);
}, m => { }, "sqlSugar 拉姆达");
}
using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities())
{
//EF
pt.Execute(i =>
{
db.Student.Where(c => c.id == 2).OrderBy(c => c.id).Skip(10).Take(10).ToList();
}, m => { }, "EF4.0+sql05 拉姆达");
}
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
/// <summary>
/// 明细
/// </summary>
/// <param name="vguid">习题vguid</param>
/// <param name="departmentVguid">部门vguid</param>
/// <returns></returns>
public List <U_QuestionDetailCollect> GetQuestionDetail(string vguid, string departmentVguid)
{
using (SqlSugarClient _dbMsSql = SugarDao_MsSql.GetInstance())
{
Guid Vguid = Guid.Parse(vguid);
List <U_QuestionDetailCollect> reportEntity = new List <U_QuestionDetailCollect>();
string sql = @"EXEC usp_QuestionSAnswerRate @ExercisesVGUID,@DepartmentVGUID";
DataTable dt = new DataTable();
string departVguid = Guid.Empty.ToString();
if (CurrentUser.GetCurrentUser().LoginName == "sysAdmin")
{
departVguid = string.IsNullOrEmpty(departmentVguid) ? "00000000-0000-0000-0000-000000000000" : departmentVguid;
}
else
{
departVguid = CurrentUser.GetCurrentUser().Department;
if (!string.IsNullOrEmpty(departmentVguid))
{
Guid dep = Guid.Parse(departVguid);
var listDep = _dbMsSql.SqlQuery <Guid>("SELECT * FROM dbo.TF_OrganizationFDetail('" + dep + "')"); //找到该部门以及其所有子部门
Guid depVguid = Guid.Parse(departmentVguid);
departVguid = listDep.Contains(depVguid) ? departmentVguid : departVguid;
}
}
dt = _dbMsSql.GetDataTable(sql, new
{
ExercisesVGUID = Vguid,
DepartmentVGUID = departVguid
});
//reportEntity.exerciseMain = GetExerciseMainList(ds.Tables[0]);
reportEntity = GetQuestionDetailList(dt);
return(reportEntity);
}
}
/// <summary>
/// 创建SQL实体文件
/// </summary>
/// <param name="db"></param>
/// <param name="tableOrView">是生成视图文件还是表文件,null生成表和视图，true生成表，false生成视图(默认为：null)</param>
/// <param name="callBack">回调函数</param>
public void CreateClassFilesInterface(SqlSugarClient db, bool? tableOrView, Action<DataTable, string, string> callBack)
{
var isLog = db.IsEnableLogEvent;
db.IsEnableLogEvent = false;
string sql = SqlSugarTool.GetCreateClassSql(tableOrView);
var tables = db.GetDataTable(sql);
if (tables != null && tables.Rows.Count > 0)
{
foreach (DataRow dr in tables.Rows)
{
string tableName = dr["name"].ToString();
var currentTable = db.GetDataTable(string.Format(SqlSugarTool.GetSelectTopSql(),GetTableNameWithSchema(db,tableName).GetTranslationSqlName()));
string className = db.GetClassTypeByTableName(tableName);
callBack(tables, className, tableName);
}
}
db.IsEnableLogEvent = isLog;
}
/// <summary>
/// 根据SQL语句获取实体类的字符串
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="className"></param>
/// <returns></returns>
public string SqlToClass(SqlSugarClient db, string sql, string className)
{
var dt = db.GetDataTable(sql);
var reval = DataTableToClass(dt, className);
return reval;
}
/// <summary>
/// 根据表名获取实体类的字符串
/// </summary>
/// <param name="db"></param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public string TableNameToClass(SqlSugarClient db, string tableName)
{
var dt = db.GetDataTable(string.Format(SqlSugarTool.GetSelectTopSql(), tableName.GetTranslationSqlName()));
var tableColumns = GetTableColumns(db, tableName);
var reval = DataTableToClass(dt, tableName, null, tableColumns);
return reval;
}
protected void Page_Load(object sender, EventArgs e)
{
//连接字符串
string connStr = @"Server=.;uid=sa;pwd=sasa;database=SqlSugarTest";
using (SqlSugarClient db = new SqlSugarClient(connStr))//开启数据库连接
{
//开启事务，可以不使用事务,也可以使用多个事务
db.BeginTran();
//db.CommitTran 提交事务会,在using结束前自动执行，可以不声名
//db.RollbackTran(); 事务回滚，catch中声名
//查询是允许脏读的，可以声名多个（默认值:不允许）
db.IsNoLock = true;
try
{
/************************************************************************************************************/
/*********************************************1、实体生成****************************************************/
/************************************************************************************************************/
//根据当前数据库生成所有表的实体类文件 （参数：SqlSugarClient ，文件目录，命名空间）
//db.ClassGenerating.CreateClassFiles(db,Server.MapPath("~/Models"),"Models");
//根据表名生成实体类文件
//db.ClassGenerating.CreateClassFilesByTableNames(db, Server.MapPath("~/Models"), "Models" , "student","school");
//根据表名生成class字符串
var str = db.ClassGenerating.TableNameToClass(db, "Student");
//根据SQL语句生成class字符串
var str2 = db.ClassGenerating.SqlToClass(db, "select top 1 * from Student", "student");
/************************************************************************************************************/
/*********************************************2、查询********************************************************/
/************************************************************************************************************/
//---------Queryable<T>,扩展函数查询---------//
//针对单表或者视图查询
//查询所有
var student = db.Queryable<Student>().ToList();
var stud = new Student() { id = 1 };
//查询单条
var single = db.Queryable<Student>().Single(c => c.id==stud.id);
//取10-20条
var page1 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(10).Take(20).ToList();
//上一句的简化写法，同样取10-20条
var page2 = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").ToPageList(2, 10);
//查询条数
var count = db.Queryable<Student>().Where(c => c.id > 10).Count();
//从第2条开始以后取所有
var skip = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Skip(2).ToList();
//取前2条
var take = db.Queryable<Student>().Where(c => c.id > 10).OrderBy("id").Take(2).ToList();
// Not like
string conval = "a";
var notLike = db.Queryable<Student>().Where(c => !c.name.Contains(conval.ToString())).ToList();
// 可以在拉姆达使用 ToString和 Convert,比EF出色的地方
var convert1 = db.Queryable<Student>().Where(c => c.name == "a".ToString()).ToList();
var convert2 = db.Queryable<Student>().Where(c => c.id == Convert.ToInt32("1")).ToList();//
var convert3 = db.Queryable<Student>().Where(c => DateTime.Now > Convert.ToDateTime("2015-1-1")).ToList();
var convert4 = db.Queryable<Student>().Where(c => DateTime.Now > DateTime.Now).ToList();
//支持字符串Where 让你解决，更复杂的查询
var student12 = db.Queryable<Student>().Where(c => "a" == "a").Where("id>100").ToList();
//存在记录反回true，则否返回false
bool isAny100 = db.Queryable<Student>().Any(c => c.id == 100);
bool isAny1 = db.Queryable<Student>().Any(c => c.id == 1);
//---------Sqlable,创建多表查询---------//
//多表查询
List<School> dataList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<@id").SelectToList<School>("st.*", new { id = 1 });
//多表分页
List<School> dataPageList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<100").SelectToPageList<School>("st.*", "s.id", 1, 10);
//---------SqlQuery,根据SQL或者存储过程---------//
//用于多用复杂语句查询
var School = db.SqlQuery<Student>("select * from Student");
//获取id
var id = db.SqlQuery<int>("select top 1 id from Student").Single();
//存储过程
//var spResult = db.SqlQuery<school>("exec sp_school @p1,@p2", new { p1=1,p2=2 });
/************************************************************************************************************/
/*************************************************3、添加****************************************************/
/************************************************************************************************************/
School s = new School()
{
name = "蓝翔"
};
//插入单条
var id2 = Convert.ToInt32(db.Insert(s));
//插入多条
List<School> sList = new List<School>();
sList.Add(s);
var ids = db.InsertRange(sList);
/************************************************************************************************************/
/*************************************************4、修改****************************************************/
/************************************************************************************************************/
//指定列更新
db.Update<School>(new { name = "蓝翔2" }, it => it.id == id);
//整个实体更新,注意主键必需为实体类的第一个属性
db.Update<School>(new School { id = id, name = "蓝翔2" }, it => it.id == id);
/************************************************************************************************************/
/*************************************************5、删除****************************************************/
/************************************************************************************************************/
db.Delete<School>(10);//注意主键必需为实体类的第一个属性
db.Delete<School>(it => it.id > 100);
db.Delete<School>(new string[] { "100", "101", "102" });
//db.FalseDelete<school>("is_del", 100);
//等同于 update school set is_del=0 where id in(100)
//db.FalseDelete<school>("is_del", it=>it.id==100);
/************************************************************************************************************/
/*************************************************6、基类****************************************************/
/************************************************************************************************************/
string sql = "select * from Student";
db.ExecuteCommand(sql);
db.GetDataTable(sql);
db.GetList<Student>(sql);
db.GetSingle<Student>(sql + " where id=1");
using (SqlDataReader read = db.GetReader(sql)) { } //事务中一定要释放DataReader
db.GetScalar(sql);
db.GetString(sql);
db.GetInt(sql);
}
catch (Exception ex)
{
//回滚事务
db.RollbackTran();
throw ex;
}
}//关闭数据库连接
}
protected void Page_Load(object sender, EventArgs e)
{
using (SqlSugarClient db = SugarDao.GetInstance())//开启数据库连接
{
string aaa = null;
var xx = db.Queryable <School>().Where(it => true).ToList();
var xx2 = db.Queryable <Student>().Where(it => it.isOk == false).ToList();
var sl2 = db.Sqlable().Form <Student>("s").SelectToList <Student>("id");
var sl = db.Sqlable().Form <Student>("s").SelectToList <Student>("*");
db.Queryable <Student>().In("id", "1", "2", "3").ToList();
db.Queryable <Student>().In("id", new string[] { "1", "2", "3" }).ToList();
db.Queryable <Student>().In("id", new List <string> {
"1", "2", "3"
}).ToList();
var array = new string[] { "1", "2", "3" };
db.Queryable <Student>().Where(it => array.Contains(it.name));
db.Delete <Student, int>(1, 2);
//开启事务，可以不使用事务,也可以使用多个事务
db.BeginTran();
//db.CommitTran 提交事务会,在using结束前自动执行，可以不声名
//db.RollbackTran(); 事务回滚，catch中声名
//查询是允许脏读的，可以声名多个（默认值:不允许）
db.IsNoLock = true;
try
{
/************************************************************************************************************/
/*********************************************1、实体生成****************************************************/
/************************************************************************************************************/
//根据当前数据库生成所有表的实体类文件 （参数：SqlSugarClient ，文件目录，命名空间）
//db.ClassGenerating.CreateClassFiles(db,Server.MapPath("~/Models"),"Models");
//根据表名生成实体类文件
//db.ClassGenerating.CreateClassFilesByTableNames(db, Server.MapPath("~/Models"), "Models" , "student","school");
//根据表名生成class字符串
var str = db.ClassGenerating.TableNameToClass(db, "Student");
//根据SQL语句生成class字符串
var str2 = db.ClassGenerating.SqlToClass(db, "select top 1 * from Student", "student");
/************************************************************************************************************/
/*********************************************2、查询********************************************************/
/************************************************************************************************************/
//---------Queryable<T>,扩展函数查询---------//
//针对单表或者视图查询
//查询所有
var student = db.Queryable <Student>().ToList();
var stud = new Student()
{
id = db.GetInt("select top 1 id from Student")
};
//查询单条
var single = db.Queryable <Student>().Single(c => c.id == stud.id);
//取10-20条
var page1 = db.Queryable <Student>().Where(c => c.id > 10).OrderBy("id").Skip(10).Take(20).ToList();
//上一句的简化写法，同样取10-20条
var page2 = db.Queryable <Student>().Where(c => c.id > 10).OrderBy("id").ToPageList(2, 10);
//查询条数
var count = db.Queryable <Student>().Where(c => c.id > 10).Count();
//从第2条开始以后取所有
var skip = db.Queryable <Student>().Where(c => c.id > 10).OrderBy("id").Skip(2).ToList();
//取前2条
var take = db.Queryable <Student>().Where(c => c.id > 10).OrderBy("id").Take(2).ToList();
// Not like
string conval = "a";
var notLike = db.Queryable <Student>().Where(c => !c.name.Contains(conval.ToString())).ToList();
// 可以在拉姆达使用 ToString和 Convert,比EF出色的地方
var convert1 = db.Queryable <Student>().Where(c => c.name == "a".ToString()).ToList();
var convert2 = db.Queryable <Student>().Where(c => c.id == Convert.ToInt32("1")).ToList();//
var convert3 = db.Queryable <Student>().Where(c => DateTime.Now > Convert.ToDateTime("2015-1-1")).ToList();
var convert4 = db.Queryable <Student>().Where(c => DateTime.Now > DateTime.Now).ToList();
//支持字符串Where 让你解决，更复杂的查询
var student12 = db.Queryable <Student>().Where(c => "a" == "a").Where("id>100").ToList();
//存在记录反回true，则否返回false
bool isAny100 = db.Queryable <Student>().Any(c => c.id == 100);
bool isAny1 = db.Queryable <Student>().Any(c => c.id == 1);
int maxId = db.Queryable <Student>().Max <Student, int>("id");
int minId = db.Queryable <Student>().Where(c => c.id > 0).Min <Student, int>("id");
//---------Sqlable,创建多表查询---------//
//多表查询
List <School> dataList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<@id").SelectToList <School>("st.*", new { id = 1 });
//多表分页
List <School> dataPageList = db.Sqlable()
.Form("school", "s")
.Join("student", "st", "st.id", "s.id", JoinType.INNER)
.Join("student", "st2", "st2.id", "st.id", JoinType.LEFT).Where("s.id>100 and s.id<100").SelectToPageList <School>("st.*", "s.id", 1, 10);
//---------SqlQuery,根据SQL或者存储过程---------//
//用于多用复杂语句查询
var School = db.SqlQuery <Student>("select * from Student");
//获取id
var id = db.SqlQuery <int>("select top 1 id from Student").Single();
//存储过程
//var spResult = db.SqlQuery<school>("exec sp_school @p1,@p2", new { p1=1,p2=2 });
/************************************************************************************************************/
/*************************************************3、添加****************************************************/
/************************************************************************************************************/
School s = new School()
{
name = "蓝翔"
};
//插入单条
var id2 = Convert.ToInt32(db.Insert(s));
//插入多条
List <School> sList = new List <School>();
sList.Add(s);
var ids = db.InsertRange(sList);
/************************************************************************************************************/
/*************************************************4、修改****************************************************/
/************************************************************************************************************/
//指定列更新
db.Update <School>(new { name = "蓝翔2" }, it => it.id == id);
db.Update <School, int>(new { name = "蓝翔2" }, 1, 3, 12);
db.Update <School, string>(new { name = "蓝翔2" }, new string[] { "1", "2" });
//整个实体更新,注意主键必需为实体类的第一个属性
db.Update(new School {
id = id, name = "蓝翔2"
});
db.Update <School>(new School {
id = id, name = "蓝翔2"
}, it => it.id == id);
/************************************************************************************************************/
/*************************************************5、删除****************************************************/
/************************************************************************************************************/
db.Delete <School, int>(10);//注意主键必需为实体类的第一个属性
db.Delete <School>(it => it.id > 100);
db.Delete <School, string>(new string[] { "100", "101", "102" });
//db.FalseDelete<school>("is_del", 100);
//等同于 update school set is_del=0 where id in(100)
//db.FalseDelete<school>("is_del", it=>it.id==100);
/************************************************************************************************************/
/*************************************************6、基类****************************************************/
/************************************************************************************************************/
string sql = "select * from Student";
db.ExecuteCommand(sql);
db.GetDataTable(sql);
db.GetList <Student>(sql);
db.GetSingle <Student>(sql + " where id=" + stud.id);
using (SqlDataReader read = db.GetReader(sql)) { } //事务中一定要释放DataReader
db.GetScalar(sql);
db.GetString(sql);
db.GetInt(sql);
}
catch (Exception ex)
{
//回滚事务
db.RollbackTran();
throw ex;
}
}//关闭数据库连接
}
/// <summary>
/// 根据表名获取实体类的字符串
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="className"></param>
/// <returns></returns>
public string TableNameToClass(SqlSugarClient db, string tableName)
{
var dt = db.GetDataTable(string.Format("select top 1 * from {0}", tableName));
var reval = DataTableToClass(dt, tableName);
return reval;
}
public DataTable GetDataTable(string sql, object pars)
{
return(_db.GetDataTable(sql, pars));
}
protected void Page_Load(object sender, EventArgs e)
{
PerformanceTest pt = new PerformanceTest();
pt.SetCount(10000);
using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString()))
{
//ado.GetDataTable
pt.Execute(i =>
{
db.GetDataTable(@"select * from (select s.*,row_number() over (order by s.id asc,s.name desc) r from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id where s.id>@id1 and s.id>@id2) t where t.r between @b and @e", new SqlParameter("@id1", "1"), new SqlParameter("@id2", "2"), new SqlParameter("@b", "11"), new SqlParameter("@e", "20"));
}, m => { }, "ado.DateTable 纯SQL写法");
//dapper
var conn = db.GetConnection();
pt.Execute(i =>
{
conn.Query<Models.Student>(@"select * from (select s.*,row_number() over (order by s.id asc,s.name desc) r from dbo.Student s inner join dbo.School sc on sc.id=s.sch_id
left join dbo.Subject sb on sb.sid=s.id where s.id>@id1 and s.id>@id2) t where t.r between @b and @e", new { id1 = 1, id2 = 2, b = 11, e = 20 }).ToList();
}, m => { }, "dapper 纯SQL写法");
//sqlSugar
pt.Execute(i =>
{
db.Sqlable().Form("Student", "s")
.Join("School", "sc", "sc.id", "s.sch_id", JoinType.INNER)
.Join("subject", "sb", "sb.sid", "s.id", JoinType.LEFT).Where("s.id>@id1").Where("s.id>@id2")
.SelectToPageList<Models.Student>("s.*", "s.id asc,s.name desc", 2, 10, new { id1=1,id2=2 });
}, m => { }, "sqlSugar SQL语法糖");
}
using (WebTest.TestLib.SqlSugarTestEntities db = new TestLib.SqlSugarTestEntities())
{
//EF
pt.Execute(i =>
{
var reval = (from s in db.Student
join sc in db.School on s.sch_id equals sc.id
join sb in db.Subject on s.id equals sb.sid
into ssb
from sb2 in ssb.DefaultIfEmpty()
select new {
s.id,
s.name,
s.sch_id,
s.sex
}).Where(c=>c.id>1).Where(c=>c.id>2).OrderBy(c=>c.id).ThenByDescending(c=>c.name).Skip(10).Take(10).ToList();
}, m => { }, "EF4.0+sql05 LINQ TO SQL");
}
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
