private void InitData(SqlSugarClient db)
{
db.ExecuteCommand("truncate table Subject");
db.ExecuteCommand("truncate table student");
db.ExecuteCommand("truncate table school");
db.ExecuteCommand("truncate table area");
db.SqlBulkCopy(SchoolList);
db.SqlBulkCopy(StudentList);
db.SqlBulkCopy(SubjectList);
db.InsertRange(AreaList);
}
private void InitData(SqlSugarClient db)
{
db.ExecuteCommand("delete from Subject");
db.ExecuteCommand("delete from student");
db.ExecuteCommand("delete from school");
db.ExecuteCommand("delete from area");
db.SqlBulkCopy(SchoolList);
db.SqlBulkCopy(StudentList);
db.SqlBulkCopy(SubjectList);
db.InsertRange(AreaList);
}
/// <summary>
/// 新增菜单
/// </summary>
/// <param name="parentTreeId"></param>
/// <param name="treeId"></param>
/// <param name="menuName"></param>
/// <param name="sort"></param>
/// <param name="founder"></param>
/// <returns></returns>
public TreeViewModel InsertMenu(Guid parentTreeId, Guid treeId, string menuName, string icon, bool isInnerPage, int sort, string founder)
{
_db.ExecuteCommand("exec SP_SysBest_AddMenuTree @parentTreeId, @treeId,@menuName,@icon,@isDeleted,@isInnerPage,@sort,@founder,@createTime,@MenuUrl", new
{
parentTreeId = parentTreeId,
treeId = treeId,
menuName = menuName,
icon = icon,
isDeleted = false,
isInnerPage = isInnerPage,
sort = sort,
founder = founder,
createTime = DateTime.Now,
MenuUrl = RequestInfo.QueryString("MenuUrl")
});
var reval = _db.Queryable <SysBest_Menu>().Where(it => it.TreeId == treeId).ToList().Select(it => new TreeViewModel()
{
id = it.Id.TryToString().Replace("/", "_"),
parentid = it.ParentId.TryToString().Replace("/", "_"),
value = it.TreeId,
text = it.MenuName,
expanded = true,
label = $"{it.Id.TryToString()} {it.MenuName}"
}).Single();
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()))
{
//ef
using (WebTest.TestLib.SqlSugarTestEntities ef = new TestLib.SqlSugarTestEntities())
{
pt.Execute(i =>
{
var datas = ef.InsertTests.Where(c => c.id == 1 || c.id > 1000);
foreach (var r in datas)
{
r.v1 = "new11";
r.v2 = "newv22";
r.v3 = "new33";
}
ef.SaveChanges();
}, m => { }, "EF4.0+sql05 Linq语法");
}
//ado.net
pt.Execute(i =>
{
db.ExecuteCommand(@" UPDATE InsertTest SET v1 =@v1 , v2 =@v2 , v3 =@v3 WHERE 1=1 and ((id =@id1) Or (id >@id1000))", new SqlParameter[] {
new SqlParameter("@v1", "new11"),
new SqlParameter("@v2", "new22"),
new SqlParameter("@v3", "new33"),
new SqlParameter("@id1", "1"),
new SqlParameter("@id1000", "1000")
});
}, m => { }, "ado.net");
//dapper
var sqlConn = db.GetConnection();
pt.Execute(i =>
{
sqlConn.Execute(@" UPDATE InsertTest SET v1 =@v1 , v2 =@v2 , v3 =@v3 WHERE 1=1 and ((id ='1') Or (id >'1000'))",
new { v1 = "newv11", v2 = "newv22", v3 = "newv33", id1 = 1, id1000 = 1000 });
}, m => { }, "dapper");
//sqlSugar
pt.Execute(i =>
{
//更新10000次
db.Update <Models.InsertTest>(new { v1 = "newv11", v2 = "newv22", v3 = "newv33" }, it => it.id == 1 || it.id > 1000);
}, m => { }, "sqlSugar");
}
//输出测试页面
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
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");
}
}
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 (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");
}
}
public int Delete(int id, bool isSoftDeleted = true)
{
string sql = "";
if (isSoftDeleted)
{
sql = "update Job_Config set Status=3 where id=" + id;
}
else
{
sql = "delete from Job_Config where id=" + id;
}
return(_sqlClient.ExecuteCommand(sql));
}
protected void Page_Load(object sender, EventArgs e)
{
PerformanceTest pt = new PerformanceTest();
pt.SetCount(1100);//设置循环次数
using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString()))
{
TuncateTable(db);
AddTest(pt, db);
//sqlSugar
pt.Execute(i =>
{
db.Delete<Models.InsertTest>(it=>it.id==i);
}, m => { }, "sqlSugar");
TuncateTable(db);
AddTest(pt, db);
//ado.net
pt.Execute(i =>
{
db.ExecuteCommand("delete InsertTest where id=@id", new SqlParameter("@id", i));
}, m => { }, "ado.net");
TuncateTable(db);
AddTest(pt, db);
//dapper
pt.Execute(i =>
{
db.GetConnection().Execute("delete InsertTest where id=@id", new { id=i});
}, m => { }, "dapper");
}
//输出测试页面
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
protected void Page_Load(object sender, EventArgs e)
{
PerformanceTest pt = new PerformanceTest();
pt.SetCount(1100);//设置循环次数
using (SqlSugarClient db = new SqlSugarClient(System.Configuration.ConfigurationManager.ConnectionStrings["sqlConn"].ToString()))
{
TuncateTable(db);
AddTest(pt, db);
//sqlSugar
pt.Execute(i =>
{
db.Delete <Models.InsertTest>(it => it.id == i);
}, m => { }, "sqlSugar");
TuncateTable(db);
AddTest(pt, db);
//ado.net
pt.Execute(i =>
{
db.ExecuteCommand("delete InsertTest where id=@id", new SqlParameter("@id", i));
}, m => { }, "ado.net");
TuncateTable(db);
AddTest(pt, db);
//dapper
pt.Execute(i =>
{
db.GetConnection().Execute("delete InsertTest where id=@id", new { id = i });
}, m => { }, "dapper");
}
//输出测试页面
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
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>
/// 创建多语言视图，带有LanguageId=1的所有有视图1替换成languageValue 并且新创视图 名称为 原有视图名+_$_+suffix
/// </summary>
/// <returns></returns>
public static void UpdateView(PubModel.Language lan, SqlSugarClient db)
{
if (lan == null)
{
return;
}
if (lan.Suffix.IsNullOrEmpty())
{
Check.Exception(true, "LanguageHelper.lan.Suffix is Null Or Empty");
}
if (PreSuffix.IsNullOrEmpty())
{
Check.Exception(true, "LanguageHelper.PreSuffix is Null Or Empty");
}
if (!lan.Suffix.StartsWith(PreSuffix))
{
lan.Suffix = PreSuffix + lan.Suffix;
}
string sql = @"
--验证参数传递规则
if LEFT(ltrim(@Suffix),3)<>'" + PreSuffix + @"'
begin
raiserror('参数传递格式不规范',16,1)
return;
end
else
if(ISNULL(" + lan.LanguageValue + @",'')='')
begin
raiserror('参数传递格式不规范',16,1)
return;
end
declare
@name varchar(100), --视图名称
@definition varchar(max) --视图脚本
--删除数据库里面所有带传递参数几号的视图
declare my_cursor cursor for
select a.name,b.[definition] from sys.objects a
JOIN sys.sql_modules b on a.[object_id]=b.[object_id]
where [type]='v'
and b.[definition] like '%" + lan.ReplaceViewStringKey + @"%'
and a.name not like '%" + PreSuffix + @"%'
--打开处理器
open my_cursor
fetch next from my_cursor into @name,@definition
while @@FETCH_STATUS=0
begin
--脚本查询语言ID更改,并且更改新脚本语言的对象名称
set @definition=REPLACE(
REPLACE(
@definition,
'" + lan.ReplaceViewStringKey + @"',
'" + string.Format(lan.ReplaceViewStringValue, lan.LanguageValue) + @"'
),
@name,
@name+@Suffix
)
--判断新脚本语言的对象名称是否存在,存在删除
exec(
'
if object_id('''+@name+@Suffix+''',''v'') is not null
begin
drop view '+@name+@Suffix+'
end
'
)
exec(@definition)
fetch next from my_cursor into @name,@definition
end
close my_cursor
deallocate my_cursor
";
db.ExecuteCommand(sql, new { Suffix = lan.Suffix });
}
//清空数据表
private static void TuncateTable(SqlSugarClient db)
{
db.ExecuteCommand("truncate table InsertTest");
System.Threading.Thread.Sleep(1000);
}
private void InitData(SqlSugarClient db)
{
try
{
db.ExecuteCommand(" DROP sequence SEQ");
}
catch (Exception)
{
}
try
{
db.ExecuteCommand(" DROP sequence SEQ2");
}
catch (Exception)
{
}
try
{
db.ExecuteCommand(" DROP sequence SEQ3");
}
catch (Exception)
{
}
try
{
db.ExecuteCommand(" DROP sequence SEQ4");
}
catch (Exception)
{
}
db.ExecuteCommand(" create sequence SEQ");
db.ExecuteCommand(" create sequence SEQ2");
db.ExecuteCommand(" create sequence SEQ3");
db.ExecuteCommand(" create sequence SEQ4");
db.ExecuteCommand("truncate table Subject");
db.ExecuteCommand("truncate table student");
db.ExecuteCommand("truncate table school");
db.ExecuteCommand("truncate table area");
db.SqlBulkCopy(StudentList);
db.SqlBulkCopy(SchoolList);
db.SqlBulkCopy(SubjectList);
db.InsertRange(AreaList);
}
public CheckOutResultDTO ReverseOrder(ReverseOrderDTO req)
{
using (var db = new SqlSugarClient(Connection))
{
CheckOutResultDTO res = null;
try
{
var dateItem = _extendItemRepository.GetModelList(req.CompanyId, 10003).FirstOrDefault();
if (dateItem == null)
{
throw new Exception("餐饮账务日期尚未初始化，请联系管理员");
}
DateTime accDate = DateTime.Today;
if (!DateTime.TryParse(dateItem.ItemValue, out accDate))
{
throw new Exception("餐饮账务日期设置错误，请联系管理员");
}
var tables = db.Queryable <R_Table>().JoinTable <R_OrderTable>
((s1, s2) => s1.Id == s2.R_Table_Id).Where <R_OrderTable>((s1, s2) =>
s2.R_OrderMainPay_Id == req.MainPayId).Select("s1.*").ToList();
var tableIds = tables.Select(p => p.Id).ToArray();
var tableOrderIngs = tables.Where(p => p.CythStatus == CythStatus.在用).ToList();
if (tableOrderIngs.Any())
{
var tableNames = string.Join(",", tableOrderIngs.Select(p => p.Name));
throw new Exception(string.Format("({0}) 正在开台状态,请结账后再执行该账单的反结操作", tableNames));
}
var reverTableNames = string.Join(",", tables.Select(p => p.Name));
var mainPayModel = db.Queryable <R_OrderMainPay>().First(p => p.Id == req.MainPayId);
if (mainPayModel.BillDate.Date != accDate.Date)
{
throw new Exception("该账单账务日期和系统当前账务日期不一致,不允许反结");
}
var orderModel = db.Queryable <R_Order>().First(p => p.Id == mainPayModel.R_Order_Id);
var orderTables = db.Queryable <R_OrderTable>()
.Where(p => p.R_OrderMainPay_Id == mainPayModel.Id).ToList();
var orderTableIds = orderTables.Select(p => p.Id).ToArray();
var orderDetails = db.Queryable <R_OrderDetail>()
.Where(p => orderTableIds.Contains(p.R_OrderTable_Id)).ToList();
var orderPayRecords = db.Queryable <R_OrderPayRecord>()
.Where(p => p.R_OrderMainPay_Id == mainPayModel.Id).ToList();
if (orderPayRecords.Any(p => p.CyddJzStatus == CyddJzStatus.已结 && p.CyddJzType != CyddJzType.定金))
{
throw new Exception(string.Format("该主结单已经做过反结操作，不能重复反结"));
}
db.BeginTran();
//db.Delete<R_OrderPayRecord>(p => p.R_OrderMainPay_Id == mainPayModel.Id);
//db.Delete<R_OrderMainPay>(p => p.Id == mainPayModel.Id);
//db.Update<R_OrderPayRecord>(new { CyddJzStatus = (int)CyddJzStatus.已付 }, p => p.R_Order_Id == orderModel.Id);
//db.Delete<R_OrderPayRecord>(p=>p.R_Order_Id == orderModel.Id && p.PayAmount<=0 && p.CyddJzType==CyddJzType.定金);
var depositReverAll = db.Queryable <R_OrderPayRecord>().Where(p => p.R_Order_Id == orderModel.Id && p.R_OrderMainPay_Id == 0 &&
p.CyddJzType == CyddJzType.定金).ToList();
var depositRePids = depositReverAll.Where(p => p.CyddJzStatus == CyddJzStatus.已退).Select(p => p.PId).ToList();
var depositReverIds = depositReverAll.Where(p => !depositRePids.Contains(p.Id) && p.CyddJzStatus == CyddJzStatus.已结)
.Select(p => p.Id).ToList();
db.Update <R_OrderPayRecord>(new { CyddJzStatus = (int)CyddJzStatus.已付 }, p => depositReverIds.Contains(p.Id));
db.Update <R_OrderPayRecord>(new { CyddJzStatus = (int)CyddJzStatus.已结 }, p => p.R_OrderMainPay_Id == mainPayModel.Id);
db.Delete <R_OrderPayRecord>(p => p.R_Order_Id == orderModel.Id && p.CyddJzType == CyddJzType.定金 &&
p.CyddJzStatus == CyddJzStatus.已结 && p.R_OrderMainPay_Id > 0);
db.Update <R_OrderDetail>(new { PayableTotalPrice = 0, DiscountRate = 1 }, p => orderTableIds.Contains(p.R_OrderTable_Id));
db.Update <R_OrderTable>(new { IsCheckOut = 0, IsOpen = 1, R_OrderMainPay_Id = 0 }, p => orderTableIds.Contains(p.Id));
db.Update <R_Order>(new { RealAmount = 0, CyddStatus = (int)CyddStatus.反结, OriginalAmount = 0, ConAmount = 0, DiscountRate = 0, DiscountAmount = 0, GiveAmount = 0, ClearAmount = 0 }, p => p.Id == orderModel.Id);
db.Update <R_Table>(new { CythStatus = (int)CythStatus.在用 }, p => tableIds.Contains(p.Id));
db.Insert <R_OrderRecord>(new R_OrderRecord()
{
CreateDate = DateTime.Now,
CreateUser = req.UserId,
CyddCzjlStatus = CyddStatus.反结,
CyddCzjlUserType = CyddCzjlUserType.员工,
Remark = string.Format("执行了反结操作,台号:({0})", reverTableNames),
R_OrderTable_Id = 0,
R_Order_Id = orderModel.Id
});
List <R_OrderPayRecord> reverseRecords = new List <R_OrderPayRecord>();
#region 反写应收账
foreach (var item in orderPayRecords)
{
if (item.CyddPayType == (int)CyddPayType.挂账 || item.CyddPayType == (int)CyddPayType.转客房 ||
item.CyddPayType == (int)CyddPayType.会员卡)
{
if (item.CyddPayType == (int)CyddPayType.会员卡)
{
if (EnabelGroupFlag)
{
try
{
var dto = AutoMapper.Mapper.Map <OrderPayRecordDTO>(item);
dto.Remark = string.Format("{0} {1}", "反结会员卡", item.Remark);
List <OrderPayRecordDTO> dtoList = new List <OrderPayRecordDTO>
{
dto
};
SaveMemberConsumeInfo(dtoList, req.UserCode, false, orderModel.R_Restaurant_Id);
//ApplyChangeMemberToDb(item.SourceId, item.SourceName, req.UserCode,
// -item.PayAmount, string.Format("{0} {1}", "反结会员卡", item.Remark), false, new SqlSugarClient(ConnentionGroup));
}
catch (Exception ex)
{
throw new Exception("反结集团库记录会员卡消费信息操作失败：" + ex.Message);
}
}
else
{
try
{
ApplyChangeMemberToDb(item.SourceId, item.SourceName, req.UserCode,
-item.PayAmount, string.Format("{0} {1}", "反结会员卡", item.Remark), false, db, orderModel.R_Restaurant_Id);
}
catch (Exception ex)
{
throw new Exception("反结本地库记录会员卡消费信息操作失败：" + ex.Message);
}
}
//if (!EnabelGroupFlag)
//{
// //若已启用集团会员库，里面则不再执行，本地库会员消费记录已在插入集团库时一并插入到本地库
//}
}
else if (item.CyddPayType == (int)CyddPayType.挂账)
{
var verifyInfo = new VerifySourceInfoDTO();
verifyInfo.SourceId = item.SourceId;
verifyInfo.SourceName = item.SourceName;
verifyInfo.RestaruantId = orderModel.R_Restaurant_Id;
verifyInfo.PayMethod = (int)CyddPayType.挂账;
verifyInfo.OperateValue = item.PayAmount;
string remark = string.Format("反结挂账客户【{0}】- 代码：({1})", item.SourceName, item.SourceId);
List <string> resultList = SearchVerifyOutsideInfo(verifyInfo, db);
try
{
var paras = SqlSugarTool.GetParameters(new
{
xh = orderModel.Id, //餐饮单序号
dh = orderModel.R_Restaurant_Id + "." + orderModel.Id, //餐厅代码+'.'+餐饮单单号
lx = resultList[0].Trim(), //协议单位代码(lxdmdm00)
je = -item.PayAmount, //金额
cz = req.UserCode, //操作员代码
ctmc = orderModel.R_Restaurant_Id, //餐厅名称
fsmc = "", //分市名称
th = orderModel.Id,
rs = orderModel.PersonNum,
bz = remark,
mz = "",
atr = 0
});
db.CommandType = System.Data.CommandType.StoredProcedure; //指定为存储过程可比上面少写EXEC和参数
db.ExecuteCommand("p_po_toys_newCY", paras);
db.CommandType = System.Data.CommandType.Text; //还原回默认
}
catch (Exception ex)
{
throw new Exception("反结挂账操作失败：" + ex.Message);
}
}
else if (item.CyddPayType == (int)CyddPayType.转客房)
{
#region 转客房处理
var verifyInfo = new VerifySourceInfoDTO();
verifyInfo.SourceId = item.SourceId;
verifyInfo.SourceName = item.SourceName;
verifyInfo.RestaruantId = orderModel.R_Restaurant_Id;
verifyInfo.PayMethod = (int)CyddPayType.转客房;
verifyInfo.OperateValue = item.PayAmount;
List <string> resultList = SearchVerifyOutsideInfo(verifyInfo, db);
try
{
var paras = SqlSugarTool.GetParameters(new
{
zh00 = Convert.ToInt32(resultList[1]), //客人账号(krzlzh00)
zwdm = resultList[0], //账项代码
hsje = -item.PayAmount, //金额
ckhm = item.SourceName, //房号(krzlfh00)
czdm = req.UserCode, //操作员代码
xfje = 1,
bz00 = "反结餐厅转客房",
bc00 = "",
});
db.CommandType = System.Data.CommandType.StoredProcedure; //指定为存储过程可比上面少写EXEC和参数
db.ExecuteCommand("p_zw_addx", paras);
db.CommandType = System.Data.CommandType.Text; //还原回默认
}
catch (Exception ex)
{
throw new Exception("反结转客房操作失败：" + ex.Message);
}
#endregion
}
}
if (item.PayAmount != 0 && (item.CyddJzStatus != CyddJzStatus.已退 || item.CyddJzStatus != CyddJzStatus.已结) && item.CyddJzType != CyddJzType.定金)
{
item.PayAmount = -item.PayAmount;
item.CreateDate = DateTime.Now;
item.BillDate = accDate;
item.R_Market_Id = req.CurrentMarketId;
item.CreateUser = req.UserId;
item.CyddJzStatus = CyddJzStatus.已结;
if (item.CyddJzType == CyddJzType.找零)
{
item.Remark = string.Format("反结找零纪录 {0}", item.SourceName);
}
else
{
item.Remark = string.Format("反结付款纪录 {0}", item.SourceName);
}
reverseRecords.Add(item);
}
//if (item.CyddJzType==CyddJzType.转结 && item.CyddJzStatus==CyddJzStatus.已结)
//{
// reverseRecords.Add(new R_OrderPayRecord()
// {
// PayAmount = -item.PayAmount,
// CreateDate = DateTime.Now,
// BillDate = accDate,
// R_Market_Id = req.CurrentMarketId,
// CreateUser = req.UserId,
// CyddJzStatus = CyddJzStatus.已付,
// CyddJzType = CyddJzType.定金,
// CyddPayType=item.CyddPayType,
// SourceId=0,
// R_OrderMainPay_Id= mainPayModel.Id,
// Remark = string.Format("反结重置定金纪录")
// });
//}
}
db.InsertRange <R_OrderPayRecord>(reverseRecords);
#endregion
db.CommitTran();
res = new CheckOutResultDTO()
{
OrderId = orderModel.Id,
OrderMainPayId = mainPayModel.Id,
OrderTables = orderTableIds.ToList(),
ReverTableNames = reverTableNames
};
}
catch (Exception ex)
{
db.RollbackTran();
throw ex;
}
return(res);
}
}
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()))
{
//ef
using (WebTest.TestLib.SqlSugarTestEntities ef = new TestLib.SqlSugarTestEntities())
{
pt.Execute(i =>
{
var datas = ef.InsertTests.Where(c => c.id == 1 || c.id > 1000);
foreach (var r in datas)
{
r.v1 = "new11";
r.v2 = "newv22";
r.v3 = "new33";
}
ef.SaveChanges();
}, m => { }, "EF4.0+sql05 Linq语法");
}
//ado.net
pt.Execute(i =>
{
db.ExecuteCommand(@" UPDATE InsertTest SET v1 =@v1 , v2 =@v2 , v3 =@v3 WHERE 1=1 and ((id =@id1) Or (id >@id1000))",new SqlParameter[]{
new SqlParameter("@v1","new11"),
new SqlParameter("@v2","new22"),
new SqlParameter("@v3","new33"),
new SqlParameter("@id1","1"),
new SqlParameter("@id1000","1000")
});
}, m => { },"ado.net");
//dapper
var sqlConn=db.GetConnection();
pt.Execute(i =>
{
sqlConn.Execute(@" UPDATE InsertTest SET v1 =@v1 , v2 =@v2 , v3 =@v3 WHERE 1=1 and ((id ='1') Or (id >'1000'))",
new { v1 = "newv11", v2 = "newv22", v3 = "newv33",id1=1,id1000=1000 } );
}, m => { }, "dapper");
//sqlSugar
pt.Execute(i =>
{
//更新10000次
db.Update<Models.InsertTest>(new { v1 = "newv11", v2 = "newv22", v3 = "newv33" }, it => it.id == 1 || it.id > 1000);
}, m => { }, "sqlSugar");
}
//输出测试页面
GridView gv = new GridView();
gv.DataSource = pt.GetChartSource();
gv.DataBind();
Form.Controls.Add(gv);
}
private MemberInfoDTO ApplyChangeMemberToDb(int memberId, string pwd, string userCode, decimal amount, string remark,
bool isGroup, SqlSugarClient db, int restaurantId)
{
SqlSugarClient localDb = null;
if (isGroup)//是否集团库连接
{
localDb = new SqlSugarClient(Connection);
}
string xtdmSql = "SELECT xtdmbz00 FROM xtdm WHERE xtdmlx00='YKBZ' AND xtdmdm00='YKBZ' AND xtdmbzs0='Y'";
List <string> list = null; //始终查本地库的会员卡配置的系统代码标识
if (isGroup)
{
list = localDb.SqlQuery <string>(xtdmSql);
}
else
{
list = db.SqlQuery <string>(xtdmSql);
}
if (list == null || list.Count == 0)
{
if (isGroup && localDb != null)
{
localDb.Dispose();
}
throw new Exception("会员卡消费挂账关联的系统代码配置不正确，请联系管理员！");
}
SqlParameter[] paras;
var member = VerifyMemberInfo(memberId, amount, pwd, db);
Guid guid = Guid.NewGuid();
paras = SqlSugarTool.GetParameters(new
{
Zs = member.Id, //客人历史序号Id
KH = member.Id, //客人历史序号Id
Lx = "B",
dd = restaurantId.ToString(),
bz = remark, //备注
je = amount, //金额
cz = userCode, //操作员代码
fs = "01", //
GPID = list[0], //协议单位代码(lxdmdm00)
GUID = guid
});
db.CommandType = System.Data.CommandType.StoredProcedure; //指定为存储过程可比上面少写EXEC和参数
db.ExecuteCommand("p_zw_gbxf", paras);
db.CommandType = System.Data.CommandType.Text; //还原回默认
if (isGroup)
{
string krlsSql = string.Format("SELECT krlsxh00 FROM krls WHERE krlsGUID = '{0}'", member.MemberGUID);
var memberList = localDb.SqlQuery <int>(krlsSql);//查本地库的会员
if (memberList == null || memberList.Count == 0)
{
string insertSql = string.Format(
"INSERT krls(krlszwxm, krlszt00, krlsvpkh, krlsxb00, krlsGPID, krlsGUID, krlsdh00, krlszjhm) " +
"VALUES('{0}', 'Y', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}') ",
member.MemberName, member.MemberCardNo, member.MemberGender,
member.MemberGPID, member.MemberGUID, member.MemberPhoneNo, member.MemberIdentityNo);
localDb.CommandType = System.Data.CommandType.Text;
localDb.ExecuteCommand(insertSql, paras);
memberList = localDb.SqlQuery <int>(krlsSql);//查本地库的会员
}
paras = SqlSugarTool.GetParameters(new
{
Zs = memberList[0], //本地库客人历史序号Id
KH = memberList[0], //本地库客人历史序号Id
Lx = "B",
dd = restaurantId.ToString(),
bz = remark, //备注
je = amount, //金额
cz = userCode, //操作员代码
fs = "01", //
GPID = list[0], //协议单位代码(lxdmdm00)
GUID = guid
});
localDb.CommandType = System.Data.CommandType.StoredProcedure; //指定为存储过程可比上面少写EXEC和参数
localDb.ExecuteCommand("p_zw_gbxf", paras);
localDb.CommandType = System.Data.CommandType.Text; //还原回默认
localDb.Dispose(); //销毁本地连接
}
return(member);
}
private void InitData(SqlSugarClient db)
{
db.ExecuteCommand("truncate table Subject");
db.ExecuteCommand("truncate table student");
db.ExecuteCommand("truncate table school");
db.ExecuteCommand("truncate table area");
db.SqlBulkCopy(SchoolList);
db.SqlBulkCopy(StudentList);
db.SqlBulkCopy(SubjectList);
db.InsertRange(AreaList);
}
/// <summary>
/// 创建多语言视图，带有LanguageId=1的所有有视图1替换成languageValue 并且新创视图 名称为 原有视图名+_$_+suffix
/// </summary>
/// <returns></returns>
public static void UpdateView(PubModel.Language lan, SqlSugarClient db)
{
if (lan == null) return;
if (lan.Suffix.IsNullOrEmpty())
{
Check.Exception(true, "LanguageHelper.lan.Suffix is Null Or Empty");
}
if (PreSuffix.IsNullOrEmpty())
{
Check.Exception(true, "LanguageHelper.PreSuffix is Null Or Empty");
}
if (!lan.Suffix.StartsWith(PreSuffix))
{
lan.Suffix = PreSuffix + lan.Suffix;
}
string sql = @"
--验证参数传递规则
if LEFT(ltrim(@Suffix),3)<>'" + PreSuffix + @"'
begin
raiserror('参数传递格式不规范',16,1)
return;
end
else
if(ISNULL("+lan.LanguageValue+@",'')='')
begin
raiserror('参数传递格式不规范',16,1)
return;
end
declare
@name varchar(100), --视图名称
@definition varchar(max) --视图脚本
--删除数据库里面所有带传递参数几号的视图
declare my_cursor cursor for
select a.name,b.[definition] from sys.objects a
JOIN sys.sql_modules b on a.[object_id]=b.[object_id]
where [type]='v'
and b.[definition] like '%" + lan.ReplaceViewStringKey + @"%'
and a.name not like '%"+PreSuffix+@"%'
--打开处理器
open my_cursor
fetch next from my_cursor into @name,@definition
while @@FETCH_STATUS=0
begin
--脚本查询语言ID更改,并且更改新脚本语言的对象名称
set @definition=REPLACE(
REPLACE(
@definition,
'" + lan.ReplaceViewStringKey + @"',
'" + string.Format(lan.ReplaceViewStringValue, lan.LanguageValue) + @"'
),
@name,
@name+@Suffix
)
--判断新脚本语言的对象名称是否存在,存在删除
exec(
'
if object_id('''+@name+@Suffix+''',''v'') is not null
begin
drop view '+@name+@Suffix+'
end
'
)
exec(@definition)
fetch next from my_cursor into @name,@definition
end
close my_cursor
deallocate my_cursor
";
db.ExecuteCommand(sql, new { Suffix = lan.Suffix });
}
public int ExecuteCommand(string sql, object pars)
{
return(_db.ExecuteCommand(sql, pars));
}
//清空数据表
private static void TuncateTable(SqlSugarClient db)
{
db.ExecuteCommand("truncate table InsertTest");
System.Threading.Thread.Sleep(1000);
}
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;
}
}//关闭数据库连接
}