public int Delete <T>(Expression <Func <T, bool> > condition) where T : class, new()
{
using (IExpressionCondition expression = db.GetExpressionCondition(condition))
{
string sql = $"DELETE FROM [{typeof(T).GetTableName()}] { expression.ToCondition(out DynamicParameters parameters) }";
return(db.ExecuteNonQuery(CommandType.Text, sql, parameters));
}
}
/// <summary>
/// 新建词条
/// </summary>
public bool AddKeyword(string keyword, string channelName)
{
if (string.IsNullOrEmpty(channelName))
{
return(this.FaildMessage("频道为空"));
}
string keyId = keyword.GetKey();
Regex regex = new Regex(@"^~(?<Content>.+)~$");
if (regex.IsMatch(keyword))
{
keyword = regex.Match(keyword).Groups["Content"].Value;
}
using (DbExecutor db = NewExecutor())
{
db.ExecuteNonQuery(CommandType.StoredProcedure, "tran_AddKeyword", new
{
KeyID = keyId,
Content = keyword,
Channel = channelName
});
MemoryUtils.Remove(channelName);
}
return(true);
}
/// <summary>
/// 执行存储过程，返回受影响的行数
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="obj"></param>
/// <returns></returns>
public static int ExecuteNonQuery <T>(this DbExecutor db, T obj) where T : IProcedureModel
{
int rows = db.ExecuteNonQuery(CommandType.StoredProcedure, typeof(T).Name,
obj.ToParameters());
obj.Fill();
return(rows);
}
/// <summary>
/// 压缩数据库
/// </summary>
/// <param name="dataType">数据库类型</param>
/// <param name="connetionString">连接字符串</param>
public static void Compression(string connetionString, DataBaseType dataType = DataBaseType.SqlServer)
{
var db = new DbExecutor(connetionString, dataType, 30);
switch (dataType)
{
case DataBaseType.SQLite: db.ExecuteNonQuery(CommandType.Text, "VACUUM", null); break;
default: throw new NotImplementedException("该数据库不支持该方法！");
}
}
/// <summary>
/// 创建表
/// </summary>
/// <param name="lstCreateField">创建的字段列表</param>
public virtual bool CreateTable(List <string> lstCreateField)
{
var sql = new StringBuilder();
sql.AppendFormat("CREATE TABLE {0} (", dbProvider.CreateTableAegis(TableName));
sql.Append(lstCreateField.ToString(","));
sql.Append(");");
dbExecutor.ExecuteNonQuery(CommandType.Text, sql.ToString(), null);
return(IsExistTable());
}
/// <summary>
/// 保存内容
/// </summary>
/// <param name="keyId"></param>
/// <param name="language"></param>
/// <param name="content"></param>
public void SaveContent(string keyId, Language language, string content, string channelName)
{
using (DbExecutor db = NewExecutor())
{
db.ExecuteNonQuery(CommandType.StoredProcedure, "tran_SaveContent", new
{
KeyID = keyId,
Language = (byte)language,
Content = content,
Token = channelName
});
MemoryUtils.Remove(channelName);
}
}
/// <summary>
/// DbExecutor插入
/// </summary>
/// <returns></returns>
SpeedTest.SpeedResult DbExecutor_Insert(int count)
{
using (var sp = new SpeedTest().Begin())
{
// 循环10万次
for (int i = 0; i < count; i++)
{
using (var db = new DbExecutor(DataBaseType.SqlServer, connString, 60))
{
db.ExecuteNonQuery(CommandType.Text, "INSERT INTO [Speed] ([UserName],[PassWord],[GenderType],[LoginCount],[LoginIP],[RoleID]) VALUES ('xxxx','yyyy',0,1,'127.0.0.1',3);");
}
}
return(sp.Result);
}
}
/// <summary>
/// DbExecutor删除
/// </summary>
/// <returns></returns>
SpeedTest.SpeedResult DbExecutor_Delete(int count)
{
using (var sp = new SpeedTest().Begin())
{
// 循环10万次
for (int i = 0; i < count; i++)
{
using (var db = new DbExecutor(DataBaseType.SqlServer, connString, 60))
{
db.ExecuteNonQuery(CommandType.Text, "Delete from [Speed] Where ID > 1;");
}
}
return(sp.Result);
}
}
/// <summary>
/// DbExecutor修改
/// </summary>
/// <returns></returns>
SpeedTest.SpeedResult DbExecutor_Update(int count)
{
using (var sp = new SpeedTest().Begin())
{
// 循环10万次
for (int i = 0; i < count; i++)
{
using (var db = new DbExecutor(DataBaseType.SqlServer, connString, 60))
{
db.ExecuteNonQuery(CommandType.Text, "update [Speed] set [UserName] = 'xxxx',[PassWord] = 'yyyy',[GenderType] = 0,[LoginCount] = 1,[LoginIP] = '127.0.0.1',[RoleID] = 3");
}
}
return(sp.Result);
}
}
public void TestDbExecutor()
{
CodeTimer.Time("DbExecutor", 1, () =>
{
using (DbExecutor db = new DbExecutor(new SqlConnection(connString)))
{
//var lst = db.Select<Customer>("select * from Customers ");
//Console.WriteLine(lst.Count());
string sql =
string.Format(
"insert into Customers Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')",
Guid.NewGuid(), "sds", "sf", "sf", "sf", "sf", 212, true);
db.ExecuteNonQuery(sql);
}
});
}
public void ExecuteNonQuery()
{
using (var exec = new DbExecutor(connectionFactory(), IsolationLevel.ReadCommitted))
{
var affected = exec.ExecuteNonQuery(
"insert into Types(Name) values(@Name)",
new { Name = "NewTypeEXECUTE" });
affected.Is(1);
var f = exec.Select <Type>("select top 1 * from Types order by TypeId desc").First();
f.Is(t => t.Name == "NewTypeEXECUTE");
// Transaction Uncommit
}
// Transaction Rollback test.
var xs = DbExecutor.Select <Type>(connectionFactory(), "select * from Types where TypeId = 5").ToArray();
xs.Count().Is(0);
}
public void ExecuteNonQuery()
{
using (var exec = new DbExecutor(connectionFactory(), IsolationLevel.ReadCommitted))
{
var affected = exec.ExecuteNonQuery(
"insert into Departments(dept_no, dept_name) values(@dept_no, @dept_name)",
new { dept_no = "d1", dept_name = "dept_name" });
affected.Is(1);
var f = exec.Select <Departments>("select * from Departments order by dept_no desc").First();
f.Is(t => t.dept_name == "dept_name");
// Transaction Uncommit
}
// Transaction Rollback test.
var xs = DbExecutor.Select <Departments>(connectionFactory(), "select * from Departments where dept_no = 'd1'").ToArray();
xs.Count().Is(0);
}
public static void Init(TestContext tc)
{
Contract.ContractFailed += (sender, e) =>
{
e.SetUnwind();
Assert.Fail(e.FailureKind.ToString() + ":" + e.Message);
};
if (System.IO.File.Exists(SQLConnectionFactory.DB_NAME))
{
System.IO.File.Delete(SQLConnectionFactory.DB_NAME);
}
using (var exec = new DbExecutor(new SQLiteConnection(new SQLConnectionFactory().GetConnection())))
{
var sb = new StringBuilder();
sb.AppendLine("CREATE TABLE employees (");
sb.AppendLine(" emp_no INT NOT NULL,");
sb.AppendLine(" birth_date DATE NOT NULL,");
sb.AppendLine(" first_name VARCHAR(14) NOT NULL,");
sb.AppendLine(" last_name VARCHAR(16) NOT NULL,");
sb.AppendLine(" gender VARCHAR(1) NOT NULL,");
sb.AppendLine(" hire_date DATE NOT NULL,");
sb.AppendLine(" PRIMARY KEY (emp_no)");
sb.AppendLine(");");
exec.ExecuteNonQuery(sb.ToString());
sb.Clear();
sb.AppendLine("CREATE TABLE departments (");
sb.AppendLine(" dept_no CHAR(4) NOT NULL,");
sb.AppendLine(" dept_name VARCHAR(40) NOT NULL,");
sb.AppendLine(" PRIMARY KEY (dept_no)");
sb.AppendLine(");");
exec.ExecuteNonQuery(sb.ToString());
sb.Clear();
sb.AppendLine("CREATE TABLE dept_emp (");
sb.AppendLine(" emp_no INT NOT NULL,");
sb.AppendLine(" dept_no CHAR(4) NOT NULL,");
sb.AppendLine(" from_date DATE NOT NULL,");
sb.AppendLine(" to_date DATE NOT NULL,");
sb.AppendLine(" PRIMARY KEY (emp_no, dept_no)");
sb.AppendLine(");");
exec.ExecuteNonQuery(sb.ToString());
sb.Clear();
sb.AppendLine("CREATE TABLE dept_manager (");
sb.AppendLine(" dept_no CHAR(4) NOT NULL,");
sb.AppendLine(" emp_no INT NOT NULL,");
sb.AppendLine(" from_date DATE NOT NULL,");
sb.AppendLine(" to_date DATE NOT NULL,");
sb.AppendLine(" PRIMARY KEY (emp_no, dept_no)");
sb.AppendLine(");");
exec.ExecuteNonQuery(sb.ToString());
sb.Clear();
sb.AppendLine("CREATE TABLE titles (");
sb.AppendLine(" emp_no INT NOT NULL,");
sb.AppendLine(" title VARCHAR(50) NOT NULL,");
sb.AppendLine(" from_date DATE NOT NULL,");
sb.AppendLine(" to_date DATE,");
sb.AppendLine(" PRIMARY KEY (emp_no, title, from_date)");
sb.AppendLine(");");
exec.ExecuteNonQuery(sb.ToString());
sb.Clear();
sb.AppendLine("CREATE TABLE salaries (");
sb.AppendLine(" emp_no INT NOT NULL,");
sb.AppendLine(" salary INT NOT NULL,");
sb.AppendLine(" from_date DATE NOT NULL,");
sb.AppendLine(" to_date DATE NOT NULL,");
sb.AppendLine(" PRIMARY KEY (emp_no, from_date)");
sb.AppendLine(");");
exec.ExecuteNonQuery(sb.ToString());
var masters = exec.SelectDynamic("select * from sqlite_master ").ToArray();
foreach (var item in masters)
{
Console.WriteLine(item.name);
}
exec.TransactionComplete();
}
}
public void TestStoreProc()
{
using (DbExecutor db = new DbExecutor(new SqlConnection(@"Data Source=JIGUIXIN\SQLEXPRESS;Initial Catalog=MyTest;Uid=jiguixin;pwd=123456;")))
{
SqlParameter par = new SqlParameter("p2",SqlDbType.Int,4);
par.Direction = ParameterDirection.Output;
//dynamic s2 = new { p1 = 1, par };
var s = db.ExecuteNonQuery((string) "[dbo].[sp_name]", new {p1=1,par}, CommandType.StoredProcedure);
Console.WriteLine(par.Value);
}
}
/// <summary>
/// 添加会员
/// 规则1：如果是后台添加的会员，不设置注册IP，注册设备
/// </summary>
/// <param name="user"></param>
/// <param name="inviteCode">邀请码（可以是会员也可以是代理的邀请码）|手动创建账号不需要填邀请码，但是需要指定AgentID</param>
/// <returns></returns>
protected bool AddUser(Site site, User user, string inviteCode, Language language = Language.CHN)
{
if (!WebAgent.IsUserName(user.UserName, 2, 16))
{
return(this.FaildMessage("用户名格式错误", language));
}
int userId = this.GetUserID(user.SiteID, user.UserName);
if (userId != 0)
{
return(this.FaildMessage("用户名已存在", language));
}
if (!string.IsNullOrEmpty(inviteCode))
{
UserInvite invite = this.GetInviteInfo(site.ID, inviteCode);
if (invite == null)
{
return(this.FaildMessage("邀请码错误", language));
}
User inviteUser = this.GetUserInfo(site.ID, invite.UserID);
if (inviteUser.Type == User.UserType.Member)
{
user.AgentID = inviteUser.AgentID;
}
else
{
user.AgentID = invite.UserID;
}
}
// 上级
if (user.AgentID != 0)
{
User parent = this.GetUserInfo(site.ID, user.AgentID);
if (parent == null)
{
return(this.FaildMessage("上级错误", language));
}
switch (parent.Type)
{
case User.UserType.Partner:
user.Type = User.UserType.Agent;
break;
case User.UserType.Agent:
user.Type = User.UserType.Broker;
break;
case User.UserType.Broker:
user.Type = User.UserType.Member;
break;
}
}
else
{
// 股东账号
user.Type = User.UserType.Partner;
}
#region ======== 默认值设定 ========
user.SiteID = site.ID;
user.CreateAt = DateTime.Now;
user.Currency = site.Currency;
user.Language = site.Language;
UserDetail detail = new UserDetail();
#endregion
using (DbExecutor db = NewExecutor(IsolationLevel.ReadUncommitted))
{
user.AddIdentity(db);
detail.UserID = user.ID;
detail.Add(db);
db.ExecuteNonQuery(new usr_CreateDepth(user.SiteID, user.AgentID, user.ID));
// 邀请码注册人数增加
if (!string.IsNullOrEmpty(inviteCode))
{
db.UpdatePlus(new UserInvite()
{
Member = 1
}, t => t.SiteID == site.ID && t.ID == inviteCode);
}
db.Commit();
}
return(true);
}
/// <summary>
/// 消费订单采集任务，同步保存报表
/// </summary>
public string Execute(out string msg)
{
OrderTaskModel task = GameOrderCaching.Instance().GetOrderTask();
if (!task)
{
msg = "当前没有任务";
return(task);
}
GameSetting setting = GameAgent.Instance().GetGameSetting(task);
if (setting == null || setting.Status != GameStatus.Open)
{
msg = "接口维护中";
return(task);
}
int count = 0;
// 执行订单采集任务
foreach (OrderModel model in setting.Setting.GetOrderLog(task))
{
if (string.IsNullOrEmpty(model.UserName))
{
continue;
}
GameOrder order = new GameOrder(model)
{
UserID = UserAgent.Instance().GetGameUserID(setting.ID, model.UserName, out int siteId),
SiteID = siteId,
GameID = setting.ID,
SettlementAt = new DateTime(1900, 1, 1),
};
if (order.Status.IsFinish())
{
order.SettlementAt = DateTime.Now;
}
long hashCode = GameOrderAgent.Instance().GetOrderHashCode(order.GameID, order.SourceID);
// 未有更新
if (hashCode == order.HashCode)
{
continue;
}
using (DbExecutor db = NewExecutor(IsolationLevel.ReadUncommitted))
{
// 是否需要重新结算
bool reSettlement = false;
// 是否需要结算
bool isSettlement = false;
if (hashCode == 0)
{
order.AddIdentity(db);
isSettlement = order.Status.IsFinish();
}
else
{
// 获取原来的状态和原来的结算日期，判断是否需要重新结算
GameOrder oldOrder = db.ReadInfo <GameOrder>(t => t.GameID == order.GameID && t.SourceID == order.SourceID,
t => t.ID, t => t.SettlementAt, t => t.Status, t => t.BetMoney, t => t.BetAmount, t => t.Money);
// 是否需要重新结算
reSettlement = oldOrder.Status.IsFinish() && oldOrder.Money != order.Money;
// 如果需要重新结算则减去结算过的值
if (reSettlement)
{
db.ExecuteNonQuery(new rpt_SaveUserDate(order.UserID, oldOrder.SettlementAt.Date, order.GameID, order.SiteID, oldOrder.BetMoney * -1M,
oldOrder.BetAmount * -1M, oldOrder.Money * -1M, -1));
}
// 不需要重新结算，且之前已经结算过，则保持原来的结算时间
else if (oldOrder.Status.IsFinish())
{
order.SettlementAt = oldOrder.SettlementAt;
}
db.Update(order, t => t.ID == oldOrder.ID,
t => t.ResultAt, t => t.SettlementAt, t => t.BetAmount, t => t.Money, t => t.Status, t => t.Content, t => t.Status, t => t.UpdateTime, t => t.HashCode, t => t.RawData);
isSettlement = !oldOrder.Status.IsFinish() && order.Status.IsFinish();
}
// 新的结算
if (reSettlement || isSettlement)
{
db.ExecuteNonQuery(new rpt_SaveUserDate(order.UserID, order.SettlementAt.Date, order.GameID, order.SiteID, order.BetMoney,
order.BetAmount, order.Money, 1));
}
db.AddCallback(() =>
{
GameOrderCaching.Instance().SaveHashCode(order.GameID, order.SourceID, order.HashCode);
});
db.Commit();
}
count++;
}
msg = @$ "采集完成，共采集到条数：{count}";
return(task);
}
/// <summary>
/// データベースの内容を初期化する。テスト用？
/// </summary>
public void Clear()
{
DbExecutor.ExecuteNonQuery(
new SQLiteConnection(_connectionString),
@"delete from RecordDescriptions");
}