public IList <MoneyViewModel> GetMonthData(int?year, int?month) { IBetweenPredicate predicates = null; if (year.HasValue && month.HasValue) { //每月一號 DateTime monthStart = new DateTime(year.Value, month.Value, 1); //每月最後一天 DateTime monthEnd = monthStart.AddMonths(1).AddDays(-1); BetweenValues betweenValues = new BetweenValues { Value1 = monthStart, Value2 = monthEnd, }; predicates = Predicates.Between <AccountBook>(f => f.Dateee, betweenValues); } var sort = new List <ISort> { Predicates.Sort <AccountBook>(x => x.Dateee), Predicates.Sort <AccountBook>(x => x.Categoryyy) }; var source = this._accountBookRepository.GetList(predicates, sort); var viewModels = source.Select(x => new MoneyViewModel { Id = x.Id, Amount = x.Amounttt, Category = x.Categoryyy.ParseEnum <CategoryType>(), CreateTime = x.Dateee }).ToList(); return(viewModels); }
public static void Pres() { var gp1 = Predicates.Group(GroupOperator.And); var predicate = Predicates.Field <UserInfo>(f => f.Name, Operator.Like, "Lead", true); gp1.Add(predicate); predicate.AddTo(gp1); var predicateProperty = Predicates.Property <PredicateTestEntity, PredicateTestEntity2>(f => f.Name, Operator.Le, f => f.Value, true); var predicateExists = Predicates.Exists <PredicateTestEntity2>(null, true); BetweenValues values = new BetweenValues(); var predicateBetween = Predicates.Between <PredicateTestEntity>(f => f.Name, values, true); var predicateGroup = Predicates.Group(GroupOperator.Or, predicate, predicateExists); var sortGroup = Predicates.SortGroup(); var Sort = Predicates.Sort <PredicateTestEntity>(f => f.Name, false); sortGroup.Add(Sort); sortGroup.Add(Sort); }
public void Between_ReturnsSetupPredicate() { BetweenValues values = new BetweenValues(); var predicate = Predicates.Between <PredicateTestEntity>(f => f.Name, values, true); Assert.AreEqual("Name", predicate.PropertyName); Assert.AreEqual(values, predicate.Value); Assert.AreEqual(true, predicate.Not); }
public IEnumerable <Visitor> FindBetween(DateTime from, DateTime to) { var predicate = Predicates.Between <SQLDatamodels.Visitor>(b => b.AddedDate, new BetweenValues() { Value1 = from, Value2 = to }); var visitors = _sqlconnection.GetListAsync <SQLDatamodels.Visitor>(predicate).Result; var toret = _mapper.Map <IEnumerable <Visitor> >(visitors); return(toret); }
public void Between_ReturnsSetupPredicate() { BetweenValues values = new BetweenValues { Value1 = 12, Value2 = 24 }; var pred = Predicates.Between <PredicateTestEntity>(f => f.Id, values, true); Dictionary <string, object> parameters = new Dictionary <string, object>(); string result = pred.GetSql(parameters); Assert.AreEqual("([PredicateTestEntity].[Id] NOT BETWEEN @Id_0 AND @Id_1)", result); Assert.AreEqual(12, parameters["@Id_0"]); Assert.AreEqual(24, parameters["@Id_1"]); }
public void MySQLBuildTest() { IDapperExtensionsConfiguration conf = new DapperExtensionsConfiguration(typeof(AutoClassMapper <>), new List <Assembly>(), new MySqlDialect()); SqlGeneratorImpl sqlGeneratorImpl = new SqlGeneratorImpl(conf); IFieldPredicate nameFieldPredicate = Predicates.Field <User>(p => p.Name, Operator.Like, "уе%"); string namesql = nameFieldPredicate.GetSql(sqlGeneratorImpl, new Dictionary <string, object>()); Assert.Equal("(`User`.`Name` LIKE @Name_0)", namesql); List <string> valueList = new List <string>() { "1", "2", "3" }; IFieldPredicate nameFieldPredicate2 = Predicates.Field <User>(p => p.Name, Operator.Eq, valueList); string namesql2 = nameFieldPredicate2.GetSql(sqlGeneratorImpl, new Dictionary <string, object>()); Assert.Equal("(`User`.`Name` IN (@Name_0, @Name_1, @Name_2))", namesql2); IBetweenPredicate idFieldPredicate = Predicates.Between <User>(p => p.Name, new BetweenValues { Value1 = 1, Value2 = 10 }, true); string idsql = idFieldPredicate.GetSql(sqlGeneratorImpl, new Dictionary <string, object>()); Assert.Equal("(`User`.`Name` NOT BETWEEN @Name_0 AND @Name_1)", idsql); IPropertyPredicate propertyPredicate = Predicates.Property <User, Role>(u => u.RoleId, Operator.Eq, r => r.Id, true); string propertysql = propertyPredicate.GetSql(sqlGeneratorImpl, new Dictionary <string, object>()); Assert.Equal("(`User`.`RoleId` <> `Role`.`Id`)", propertysql); IExistsPredicate existsPredicate = Predicates.Exists <User>(nameFieldPredicate, true); string existssql = existsPredicate.GetSql(sqlGeneratorImpl, new Dictionary <string, object>()); Assert.Equal("(NOT EXISTS (SELECT 1 FROM `User` WHERE (`User`.`Name` LIKE @Name_0)))", existssql); IList <IPredicate> predList = new List <IPredicate> { nameFieldPredicate, idFieldPredicate }; IPredicateGroup predGroup1 = Predicates.Group(GroupOperator.And, predList.ToArray()); IList <IPredicate> predList2 = new List <IPredicate> { predGroup1, existsPredicate }; IPredicateGroup predGroup2 = Predicates.Group(GroupOperator.Or, predList2.ToArray()); string groupsql = predGroup2.GetSql(sqlGeneratorImpl, new Dictionary <string, object>()); string res = "(((`User`.`Name` LIKE @Name_0) AND (`User`.`Name` NOT BETWEEN @Name_1 AND @Name_2)) OR (NOT EXISTS (SELECT 1 FROM `User` WHERE (`User`.`Name` LIKE @Name_3))))"; Assert.Equal(groupsql, res); }
public void Between() { var fieldPredicate = new FieldPredicate <TestEntity>(); var betweenValues = new BetweenValues { Value1 = 10, Value2 = 20 }; var predicate = Predicates.Between <TestEntity>(x => x.Id, new BetweenValues { Value1 = 10, Value2 = 20 }, true); Assert.True(predicate.Not); Assert.Equal(betweenValues, predicate.Value); Assert.Equal("Id", predicate.PropertyName); }
public void MiscPredicatesTests() { AssertPredicate(Predicates.True(), PredicateDataSerializerHook.TruePredicate); AssertPredicate(Predicates.False(), PredicateDataSerializerHook.FalsePredicate); AssertPredicate(Predicates.Not(Predicates.True()), PredicateDataSerializerHook.NotPredicate); AssertPredicate(Predicates.And(), PredicateDataSerializerHook.AndPredicate); AssertPredicate(Predicates.Or(), PredicateDataSerializerHook.OrPredicate); AssertPredicate(Predicates.EqualTo("name", 33), PredicateDataSerializerHook.EqualPredicate); AssertPredicate(Predicates.Value("name").IsEqualTo(33), PredicateDataSerializerHook.EqualPredicate); AssertPredicate(Predicates.NotEqualTo("name", 33), PredicateDataSerializerHook.NotEqualPredicate); AssertPredicate(Predicates.Value("name").IsNotEqualTo(33), PredicateDataSerializerHook.NotEqualPredicate); AssertPredicate(Predicates.GreaterThan("name", 33), PredicateDataSerializerHook.GreaterLessPredicate); AssertPredicate(Predicates.Value("name").IsGreaterThan(33), PredicateDataSerializerHook.GreaterLessPredicate); AssertPredicate(Predicates.GreaterThanOrEqualTo("name", 33), PredicateDataSerializerHook.GreaterLessPredicate); AssertPredicate(Predicates.Value("name").IsGreaterThanOrEqualTo(33), PredicateDataSerializerHook.GreaterLessPredicate); AssertPredicate(Predicates.LessThan("name", 33), PredicateDataSerializerHook.GreaterLessPredicate); AssertPredicate(Predicates.Value("name").IsLessThan(33), PredicateDataSerializerHook.GreaterLessPredicate); AssertPredicate(Predicates.LessThanOrEqualTo("name", 33), PredicateDataSerializerHook.GreaterLessPredicate); AssertPredicate(Predicates.Value("name").IsLessThanOrEqualTo(33), PredicateDataSerializerHook.GreaterLessPredicate); AssertPredicate(Predicates.Between("name", 33, 44), PredicateDataSerializerHook.BetweenPredicate); AssertPredicate(Predicates.Value("name").IsBetween(33, 44), PredicateDataSerializerHook.BetweenPredicate); AssertPredicate(Predicates.In("name", 33, 44), PredicateDataSerializerHook.InPredicate); AssertPredicate(Predicates.Value("name").IsIn(33, 44), PredicateDataSerializerHook.InPredicate); AssertPredicate(Predicates.Like("name", "expression"), PredicateDataSerializerHook.LikePredicate); AssertPredicate(Predicates.Value("name").IsLike("expression"), PredicateDataSerializerHook.LikePredicate); AssertPredicate(Predicates.ILike("name", "expression"), PredicateDataSerializerHook.ILikePredicate); AssertPredicate(Predicates.Value("name").IsILike("expression"), PredicateDataSerializerHook.ILikePredicate); AssertPredicate(Predicates.Match("name", "regex"), PredicateDataSerializerHook.RegexPredicate); AssertPredicate(Predicates.Value("name").Matches("regex"), PredicateDataSerializerHook.RegexPredicate); AssertPredicate(Predicates.InstanceOf("className"), PredicateDataSerializerHook.InstanceofPredicate); AssertPredicate(Predicates.In("name", 1, 2, 3), PredicateDataSerializerHook.InPredicate); AssertPredicate(Predicates.Sql("sql"), PredicateDataSerializerHook.SqlPredicate); AssertPredicate(Predicates.Key("property").IsILike("expression"), PredicateDataSerializerHook.ILikePredicate); AssertPredicate(Predicates.Value().IsILike("expression"), PredicateDataSerializerHook.ILikePredicate); }
public IEnumerable <ShortUrl> FindBetween(DateTime from, DateTime to) { try { var predicate = Predicates.Between <SQLDatamodels.ShortUrls>(b => b.AddedDate, new BetweenValues() { Value1 = from, Value2 = to }); var urls = _sqlconnection.GetListAsync <SQLDatamodels.ShortUrls>(predicate).Result; var toret = _mapper.Map <IEnumerable <ShortUrl> >(urls); return(toret); } catch (Exception ex) { throw new RepositoryException("Error retrieving ShortUrl from repository. " + ex.Message); } }
public static async Task Main(string[] args) { var options = new HazelcastOptionsBuilder() .With(args) .WithConsoleLogger() .Build(); // create an Hazelcast client and connect to a server running on localhost options.Serialization.AddPortableFactory(PortableFactory.FactoryId, new PortableFactory()); await using var client = await HazelcastClientFactory.StartNewClientAsync(options); // Get a Distributed Map called "users" await using var users = await client.GetMapAsync <string, User>("users"); // Add some users to the Distributed Map await GenerateUsers(users); // Create a Predicate from a String (a SQL like Where clause) var sqlQuery = Predicates.Sql("active AND age BETWEEN 18 AND 21)"); // Creating the same Predicate as above but with a builder var criteriaQuery = Predicates.And( Predicates.EqualTo("active", true), Predicates.Between("age", 18, 21) ); // Get result collections using the two different Predicates var result1 = await users.GetValuesAsync(sqlQuery); var result2 = await users.GetValuesAsync(criteriaQuery); // Print out the results Console.WriteLine("Result1:"); foreach (var result in result1) { Console.WriteLine(result); } Console.WriteLine("Result2:"); foreach (var result in result2) { Console.WriteLine(result); } }
private static string GetList() { Stopwatch sw = new Stopwatch(); sw.Start(); IPredicateGroup group = new PredicateGroup() { Operator = GroupOperator.Or, Predicates = new List <IPredicate>() }; IBetweenPredicate bet = Predicates.Between <Person>(f => f.Sex, new BetweenValues { Value1 = 0, Value2 = 2 }); group.Predicates.Add(bet); IPredicate pre = Predicates.Field <Person>(f => f.FirstName, Operator.Like, "First"); group.Predicates.Add(pre); IList <Person> result = PersonRepository.Value.GetList(group); sw.Stop(); return(string.Format("共获取{0}条记录,耗时:{1}毫秒", result.Count, sw.ElapsedMilliseconds)); }
public void Between_GeneratesAndRunsProperSql() { Person p1 = new Person { Active = true, FirstName = "Alpha", LastName = "Bar", DateCreated = DateTime.UtcNow }; Person p2 = new Person { Active = true, FirstName = "Beta", LastName = "Bar", DateCreated = DateTime.UtcNow }; Person p3 = new Person { Active = true, FirstName = "Gamma", LastName = "Omega", DateCreated = DateTime.UtcNow }; Impl.Insert <Person>(Connection, new[] { p1, p2, p3 }, null, null); var pred = Predicates.Between <Person>(p => p.LastName, new BetweenValues { Value1 = "Aaa", Value2 = "Bzz" }); var result = Impl.GetList <Person>(Connection, pred, null, null, null, true).ToList(); Assert.AreEqual(2, result.Count); Assert.AreEqual("Alpha", result[0].FirstName); Assert.AreEqual("Beta", result[1].FirstName); }
static void Main(string[] args) { Dictionary <string, object> dic = new Dictionary <string, object>(); //sql server { //Random rand = new Random(); //using (IDbConnection db = DapperFactory.GetConnection(Enums.MyDbType.SqlServer, "Data Source=localhost;Initial Catalog=test;Integrated Security=True")) //{ // int effectRows; // string id = rand.Next(1, 2000000000).ToString(); // Student stu = new Student { ID = id, NAME = "春夏之交", AGE = 25, TIME = DateTime.Now }; // //单个插入 // { // effectRows = db.Execute("insert into Student(ID,NAME,TIME) values(@ID,@NAME,@TIME)", stu); // } // //多个插入 // { // var stuList = Enumerable.Range(0, 5).Select(i => new Student() // { // ID = rand.Next(1, 2147483647).ToString(), // NAME = "安徽", // AGE = i, // TIME = DateTime.Now // }); // var result = db.Execute("insert into Student(ID,NAME,TIME,AGE) values(@ID,@NAME,@TIME,@AGE)", stuList); // } // //字典参数插入 // { // dic["ID"] = id + "1"; // dic["NAME"] = "陈兆杰" + DateTime.Now.ToString(); // dic["TIME"] = DateTime.Now; // dic["AGE"] = 25; // effectRows = db.Execute("insert into Student(ID,NAME,TIME,AGE) values(@ID,@NAME,@TIME,@AGE)", dic); // } // //更新 // { // var stu1 = db.QuerySingleOrDefault<Student>("select * from Student where ID=@ID", new Student { ID = id }); // stu1.NAME = "陈兆杰"; // effectRows = db.Execute("UPDATE Student SET NAME=@NAME WHERE ID =@ID", stu1); // } // //删除 // { // dic["ID"] = id; // effectRows = db.Execute("DELETE FROM Student WHERE ID = @ID", dic); // } // //事务 // { // db.Open(); // var tran = db.BeginTransaction(); // try // { // Dictionary<string, object> dicParam = new Dictionary<string, object>(); // dicParam["ID"] = Guid.NewGuid().ToString(); // dicParam["NAME"] = "陈兆杰"; // dicParam["TIME"] = DateTime.Now; // effectRows = db.Execute("insert into Student(ID,NAME,TIME) values(@ID,@NAME,@TIME)", dicParam, tran); // tran.Commit(); // } // catch (Exception ex) // { // tran.Rollback(); // } // } // //多结果查询(Read表顺序,要与sql语句表的顺序一致,不然会有问题) // { // string sql = @" // select * from Student where ID = @ID; // select * from Course;"; // using (var multi = db.QueryMultiple(sql, new { id = "1" })) // { // var orders = multi.Read<Student>().ToList(); // var customer = multi.Read<Course>().FirstOrDefault(); // } // } // //单个查询 // { // //var stu4 = db.QuerySingle<Student>("select * from Student where ID=@ID", new { ID = "1" });//数据超过一条会报错,没有数据会报错 // //var stu5 = db.QuerySingleOrDefault<Student>("select * from Student where ID=@ID", new { ID = "1" });//数据超过一条会报错,没有数据是默认为null // //var stu6 = db.QueryFirstOrDefault<Student>("select * from Student where ID=@ID", new { ID = "1" });//没有数据时默认为null // //var stu7 = db.QueryFirst<Student>("select * from Student where ID=@ID", new { ID = "1" });//没有数据会报错 // } // //多个查询 // { // var list = db.Query<Student>("SELECT * FROM Student").ToList(); // list = db.Query<Student>("SELECT * FROM Student where ID in @IDS", new { IDS = new String[] { "1786863176", "1963140912" } }).ToList(); // } // //多表查询 // { // string sql = @"select s.*,c.ID,c.CourseName from Student s,Course c where s.ID =c.ID and s.ID=@ID"; // //方法1: // var list2 = db.Query<Student, Course, StudentDto>(sql, (student, course) => // { // StudentDto stuDto = new StudentDto(); // stuDto.NAME = student.NAME; // stuDto.ID = student.ID; // course.ID = student.ID; // stuDto.Course = course; // return stuDto; // }, new { ID = "1786863176" }, splitOn: "ID,CourseName").ToList(); // //方法2: // var dynamic = db.Query(sql, new { ID = "1786863176" }).ToList(); // } // //调用存储过程 // { // var param = new DynamicParameters(); // param.Add("@StuId", "859713145"); // param.Add("@Name", dbType: DbType.String, direction: ParameterDirection.Output, size: 50);//输出值需要赋值size // param.Add("@result", dbType: DbType.String, direction: ParameterDirection.ReturnValue, size: 50);//输出值需要赋值size // db.Execute("TestProc", param, commandType: CommandType.StoredProcedure); // string Name = param.Get<string>("Name"); // int? ReturnValue = param.Get<int>("@result"); // } //} } //oracle 存储过程 { using (IDbConnection db = DapperFactory.GetConnection(Enums.MyDbType.Oracle, @"User ID=czj;Password=123456;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))")) { //ado原生调用存储过程 { db.Open(); var command = (OracleCommand)db.CreateCommand(); command.BindByName = true; string returnValue = ""; List <OracleParameter> paraList = new List <OracleParameter> { new OracleParameter("spassword", OracleDbType.Varchar2, "123456", ParameterDirection.Input), new OracleParameter("id", OracleDbType.Varchar2, Guid.NewGuid().ToString(), ParameterDirection.Input), new OracleParameter("smobilephone", OracleDbType.Varchar2, "15298506403", ParameterDirection.Input), new OracleParameter("semail", OracleDbType.Varchar2, "*****@*****.**", ParameterDirection.Input), new OracleParameter("returnValue", OracleDbType.Varchar2, 1000, returnValue, ParameterDirection.Output) }; command.CommandText = "proTest1"; command.CommandType = CommandType.StoredProcedure; command.Parameters.AddRange(paraList.ToArray()); var effectRows = command.ExecuteScalar(); } //dapper调用存储过程 { //var param = new DynamicParameters(); //param.Add("returnValue", null, DbType.String, ParameterDirection.Output, 1000); //param.Add("id", "1234569499", DbType.String, ParameterDirection.Input); //param.Add("smobilephone", "15298506403", DbType.String, ParameterDirection.Input); //param.Add("semail", "*****@*****.**", DbType.String, ParameterDirection.Input); //param.Add("spassword", "123456", DbType.String, ParameterDirection.Input); //param.AddDynamicParams(new OracleParameter(":spassword", OracleDbType.NVarchar2, "123456", ParameterDirection.Input)); //param.AddDynamicParams(new OracleParameter(":smobilephone", OracleDbType.NVarchar2, "15298506403", ParameterDirection.Input)); //param.AddDynamicParams(new OracleParameter(":semail", OracleDbType.NVarchar2, "*****@*****.**", ParameterDirection.Input)); //param.AddDynamicParams(new OracleParameter(":returnValue", OracleDbType.NVarchar2, ParameterDirection.Output).Size = 1000); //var obj=db.ExecuteScalar("PROTEST1", param, commandType: CommandType.StoredProcedure); } } } //DapperExtensions.dll { Random rand = new Random(); using (IDbConnection db = DapperFactory.GetConnection(Enums.MyDbType.SqlServer, "Data Source=localhost;Initial Catalog=test;Integrated Security=True")) { string id = rand.Next(1, 2000000000).ToString(); Student stu = new Student { ID = id, NAME = "陈兆杰", AGE = 25, TIME = DateTime.Now }; //Insert { var effectRows = db.Insert(stu);//返回主键值 } //Update { stu.TIME = DateTime.Now; if (db.Update(stu)) { //成功 } else { //失败 } } //删除 { //方法一 { //if (db.Delete(stu)) //{ // //成功 //} //else //{ // //失败 //} } //方法二 { var filedPred = Predicates.Field <Student>(o => o.ID, Operator.Eq, stu.ID); if (db.Delete <Student>(filedPred)) { //成功 } else { //失败 } } //查询list { IList <ISort> sortlist = new List <ISort>(); sortlist.Add(new Sort { PropertyName = "ID", Ascending = false }); //排序条件 降序 IList <IPredicate> preList = new List <IPredicate>(); preList.Add(Predicates.Field <Student>(o => o.ID, Operator.Eq, "1786863176")); //搜索条件,Operator有很多种的类型如eq是等于、like相当于是sql的like用法还有Lt、Le等 BetweenValues betweenValues = new BetweenValues(); //搜索条件,between搜索两个值之间的数据 betweenValues.Value1 = "1786863176"; betweenValues.Value2 = "9786863176"; preList.Add(Predicates.Between <Student>(o => o.ID, betweenValues)); IPredicateGroup predGroup = Predicates.Group(GroupOperator.And, preList.ToArray());//确认多个搜索条件的连接方式AND 或者 OR var list = db.GetList <Student>(predGroup, sortlist).ToList(); } } } } }