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"); }