예제 #1
0
        protected void test2_Click(object sender, EventArgs e)
        {
            StringBuilder msg = new StringBuilder();

            //测试增删改
            using (var session = DefaultDbFactory.GetDbSession())
            {
                session.BeginTranscation();
                #region 一、自增主键实体 CUD
                msg.AppendFormat("一、自增主键实体 CUD:{0}", Environment.NewLine);
                //1、新增实体(Id自增长)
                User u = new User()
                {
                    name = "张飞", email = "*****@*****.**", birthday = DateTime.Now
                };
                var LastId = session.Insert(u);
                msg.AppendFormat("1、新增实体(Id自增长),新增后返回的Id:{0} {1}", LastId, Environment.NewLine);
                var LastUser = session.SingleById <User>(LastId);
                msg.AppendFormat("1、新增实体(Id自增长),新增后重新获取的实体信息:{0} {1}", LastUser.name, Environment.NewLine);
                //2、更新实体
                LastUser.name  = "张飞-New";
                LastUser.email = "*****@*****.**";
                var updateResult1 = session.Update(LastUser);
                msg.AppendFormat("1、新增实体(Id自增长),更新后的结果:{0} {1}", updateResult1, Environment.NewLine);
                var updateUser = session.First <User>("where email=@0", "*****@*****.**");
                msg.AppendFormat("1、新增实体(Id自增长),更新后获取用户的信息:{0} {1}", updateUser.name, Environment.NewLine);
                #endregion

                #region 二、GUID主键实体 CUD
                msg.AppendFormat("二、GUID主键实体 CUD:{0}", Environment.NewLine);
                //1、新增实体(Guid类型主键)
                News newsInfo = new News()
                {
                    Name = "翟晓东", Title = "风云科技", Flag = true, Remark = "gsfdsdf"
                };
                var LastGuidId = session.Insert(newsInfo);
                msg.AppendFormat("1、新增实体(Guid类型主键),新增后返回的Id:{0} {1}", LastGuidId, Environment.NewLine);
                var LastUser2 = session.SingleById <News>(LastGuidId);
                msg.AppendFormat("1、新增实体(Guid类型主键),新增后重新获取的实体信息:{0} {1}", LastUser2.Name, Environment.NewLine);
                //2、更新实体
                //LastUser2.Name = "翟晓东-New3333333";
                session.DbContext.UpdateMany <News>().Where(x => x.Id == LastUser2.Id)
                //.ExcludeDefaults()
                .OnlyFields(x => x.Name)
                .Execute(new News()
                {
                    Name = "翟晓东-New3333333"
                });
                //var upr = session.Update(LastUser2);
                //msg.AppendFormat("1、新增实体(Guid类型主键),更新后的结果:{0} {1}", upr, Environment.NewLine);
                var updateUser2 = session.First <News>("where Name=@0", "翟晓东-New3333333");
                msg.AppendFormat("1、新增实体(Guid类型主键),新增后重新获取的实体信息:{0} {1}", updateUser2.Name, Environment.NewLine);
                #endregion
                session.Commit();
            }
            txtMessage.Value = msg.ToString();
        }
예제 #2
0
        protected void text4_Click(object sender, EventArgs e)
        {
            //测试删除
            StringBuilder msg = new StringBuilder();

            using (var session = DefaultDbFactory.GetDbSession(true))
            {
                msg.AppendFormat("六、测试删除:{0}", Environment.NewLine);
                //1、删除指定实体
                var user    = session.SingleById <User>(1);
                int delFlag = session.Delete(user);
                msg.AppendFormat("1、删除指定实体,返回结果:{0} {1}", delFlag, Environment.NewLine);
                //2、通过主键删除
                var delFlag2 = session.Delete <User>(3);
                msg.AppendFormat("2、通过主键删除,返回结果:{0} {1}", delFlag2, Environment.NewLine);
                session.Commit();
            }
            txtMessage.Value = msg.ToString();
        }
예제 #3
0
 /// <summary>
 /// 初始化NPoco-ORM数据库仓储
 /// </summary>
 public static void Register()
 {
     DefaultDbFactory.Setup();
 }
예제 #4
0
        protected void test1_Click(object sender, EventArgs e)
        {
            StringBuilder msg = new StringBuilder();

            //测试查询
            using (var session = DefaultDbFactory.GetDbSession(true))
            {
                #region 一、Single 查询唯一实体(有且仅有一个,多于一个会报错)
                msg.AppendFormat("一、Single 查询唯一实体(有且仅有一个,多于一个会报错):{0}", Environment.NewLine);
                //1、通过Id获取唯一实体
                var modelById = session.SingleById <User>(1);
                msg.AppendFormat("1、通过Id获取唯一实体:{0}{1}", modelById.name, Environment.NewLine);
                //2、自定义查询条件获取唯一实体
                var smodel = session.Single <User>("where id=@0 and name=@1", 1, "翟晓东");
                msg.AppendFormat("2、自定义查询条件获取唯一实体:{0}{1}", smodel.email, Environment.NewLine);
                //3、构建查询条件
                SearchSqlBuilder singleSql = new SearchSqlBuilder();
                singleSql.Create(cond => {
                    //自定义查询的列,不重写则默认为"*"
                    cond.Select("name", "email", "birthday");
                    cond.WhereIf(1 == 1, "id=@0", 2)
                    .WhereIf(1 == 12, "name=@0", "");
                    //指定排序
                    //cond.OrderBy("birthday DESC");
                });
                var smodel2 = session.Single <User>(singleSql);
                msg.AppendFormat("3、构建查询条件:{0}{1}", smodel2.name, Environment.NewLine);
                #endregion

                #region 二、First 查询匹配的第一个实体(有多个则返回集合中的第一条数据)
                msg.AppendFormat("二、First 查询匹配的第一个实体(有多个则返回集合中的第一条数据):{0}", Environment.NewLine);
                //1、自定义查询条件获匹配的第一个实体
                var fmodel = session.First <User>("where name like @0", "费云帆%");
                msg.AppendFormat("1、自定义查询条件获匹配的第一个实体:{0}{1}", fmodel.name, Environment.NewLine);
                //2、构建查询条件
                SearchSqlBuilder firstSql = new SearchSqlBuilder();
                firstSql.Create(cond => {
                    //自定义查询的列,不重写则默认为"*"
                    //cond.Select("name", "email", "birthday");
                    cond.WhereIf(1 == 1, "convert(varchar(10),birthday,120)=@0", "2016-12-07")
                    .WhereIf(1 == 2, "name=@0", "fff");
                    //指定排序
                    cond.OrderBy("birthday DESC");
                });
                var fmodel2 = session.First <User>(firstSql);
                msg.AppendFormat("2、构建查询条件:{0}{1}", fmodel2.name, Environment.NewLine);
                #endregion

                #region  、Query 获取实体集合
                msg.AppendFormat("三、Query 获取实体集合:{0}", Environment.NewLine);
                //1、自定义查询条件获取集合
                SearchSqlBuilder sqlList1 = new SearchSqlBuilder();
                sqlList1.Create(cond => {
                    //自定义查询的列,不重写则默认为"*"
                    //cond.Select("name", "email", "birthday");
                    cond.WhereIf(1 == 1, "convert(varchar(10),birthday,120)=@0", "2016-12-07")
                    .WhereIf(1 == 2, "name=@0", "fff");
                    //指定排序
                    cond.OrderBy("birthday DESC");
                });
                var modelList = session.Query <User>(sqlList1);
                msg.AppendFormat("1、自定义查询条件获取集合:{0}{1}", modelList.Count(), Environment.NewLine);
                //2、获取分页数据
                SearchSqlBuilder sqlList2 = new SearchSqlBuilder();
                sqlList2.CreatePage(cond => {
                    //自定义查询的列,不重写则默认为"*"
                    //cond.Select("name", "email", "birthday");
                    cond.WhereIf(1 == 12, "ISNULL(name,'')<>@0", "1")
                    .WhereIf(1 == 12, "id>@0", 0);
                    //指定排序
                    cond.OrderBy("id DESC");
                }, 2, 2);
                var pageEntity = session.QueryPage <User>(sqlList2);
                msg.AppendFormat("2、获取分页数据:{0}{1}", pageEntity.TotalItems, Environment.NewLine);
                #endregion

                #region 四、执行Sql语句获取DataTable
                msg.AppendFormat("四、执行Sql语句获取DataTable:{0}", Environment.NewLine);
                //1、执行SQL语句
                var tb = session.ExcuteQuery("SELECT * FROM Users WHERE 1=1 AND id>@0", 1);
                msg.AppendFormat("1、执行SQL语句:{0}{1}", tb.Rows.Count, Environment.NewLine);
                //2、使用Sql类构建查询条件
                Sql sqlBuild = new Sql();
                sqlBuild.Select("*").From("NewsInfo").Where("Name<>@0", "").OrderBy("id DESC");
                var tb2 = session.ExcuteQuery(sqlBuild);
                msg.AppendFormat("2、使用Sql类构建查询条件:{0}{1}", tb2.Rows.Count, Environment.NewLine);
                #endregion

                #region 五、执行SQL命令
                msg.AppendFormat("五、执行SQL命令:{0}", Environment.NewLine);
                //1、执行SQL命令返回受影响的行数
                var result1 = session.ExcuteNoQuery("UPDATE Users SET Remark=@0 WHERE id=@1", "更新测试", 1);
                msg.AppendFormat("1、执行SQL命令返回受影响的行数:{0}{1}", result1, Environment.NewLine);
                //2、执行SQL命令返回第一行第一例的数据
                var result2 = session.ExcuteScalar <int>("SELECT COUNT(1) FROM NewsInfo WHERE 1=1");
                msg.AppendFormat("2、执行SQL命令返回第一行第一例的数据:{0}{1}", result2, Environment.NewLine);
                #endregion

                #region 六、获取动态类型
                var dynamic1     = session.Query <object[]>("SELECT * FROM Users where 1=1");
                var dynamic2     = session.DbContext.Fetch <dynamic>("SELECT * FROM Users where 1=1");
                var dynamic3     = session.DbContext.Fetch <Dictionary <string, object> >("SELECT * FROM Users where 1=1");
                var dynamicTable = dynamic3.ToDataTable();
                #endregion
            }
            txtMessage.Value = msg.ToString();
        }
예제 #5
0
        protected void text3_Click(object sender, EventArgs e)
        {
            /*
             * Debug.Assert(1 == 0, "error message", "参数不能为空!");
             * System.Diagnostics.Trace.Assert(1==0,"错误:","个方法士大夫十分");
             * TextWriterTraceListener _DebugLog = new TextWriterTraceListener(System.IO.File.CreateText("Debug_Output.txt"));
             * Debug.Listeners.Add(_DebugLog);
             * Debug.AutoFlush = true;
             * Debug.WriteLine("Debug 余小章");
             * System.Diagnostics.Trace.WriteLine("Release 余小章");
             */

            List <IDictionary <string, object> > testDics = new List <IDictionary <string, object> >();

            testDics.Add(new Dictionary <string, object> {
                { "Id", Guid.NewGuid() },
                { "Name", "水电费水电费" },
                { "CrTime", DateTime.Now },
                { "Fees", null },
                { "Nums", 33 },
                { "IsDeleted", true }
            });
            testDics.Add(new Dictionary <string, object> {
                { "Id", "dererer" },
                { "Name", "232FDD水电费是否" },
                { "CrTime", null },
                { "Fees", 33 },
                { "Nums", 22.2 },
                { "IsDeleted", 1 }
            });
            testDics.Add(new Dictionary <string, object> {
                { "Id", null },
                { "Name", "232324454" },
                { "CrTime", "1733-12-12 12:12" },
                { "Fees", 33.3 },
                { "Nums", 13 },
                { "IsDeleted", "false" }
            });
            var testDT = testDics.ToDataTable();

            //测试存储过程
            StringBuilder msg = new StringBuilder();

            using (var session = DefaultDbFactory.GetDbSession(true))
            {
                msg.AppendFormat("五、调用存储过程:{0}", Environment.NewLine);
                var outputParam = new System.Data.SqlClient.SqlParameter("rtnStr", SqlDbType.VarChar, 2000);
                outputParam.Direction = ParameterDirection.Output;
                var dt = session.QueryStoredProcedure("SP_GetUser", new StoreParameter("id", 3), new StoreParameter("name", "翟晓东"), new StoreParameter("rtnStr", outputParam));
                msg.AppendFormat("1、调用存储过程,获取DataTable:{0} {1}", dt.Rows.Count, Environment.NewLine);

                var outputParam2 = new System.Data.SqlClient.SqlParameter("rtnStr", SqlDbType.VarChar, 2000);
                outputParam2.Direction = ParameterDirection.Output;
                var suserList = session.QueryStoredProcedure <User>("SP_GetUser", new StoreParameter("id", 3), new StoreParameter("name", ""), new StoreParameter("rtnStr", outputParam2));
                msg.AppendFormat("2、调用存储过程,获取List<T>:{0} {1}", suserList.Count(), Environment.NewLine);

                var outputParam3 = new System.Data.SqlClient.SqlParameter("rtnStr", SqlDbType.VarChar, 2000);
                outputParam3.Direction = ParameterDirection.Output;
                var singleDD = session.SingleStoredProcedure <User>("SP_GetUser", new StoreParameter("id", 2), new StoreParameter("name", ""), new StoreParameter("rtnStr", outputParam3));
                msg.AppendFormat("3、调用存储过程,获取单个实体:{0} {1}", singleDD.name, Environment.NewLine);
            }
            txtMessage.Value = msg.ToString();
        }