C# OrmLiteConnectionFactory.OpenDbConnection方法代码示例

        public void Can_use_nested_transactions()
        {
            //must use the factory, because that returns an OrmLiteConnection, that can saves the current transaction
            var factory = new OrmLiteConnectionFactory(ConnectionString, SqlServerDialect.Provider);
            //using(var outerConn = OpenDbConnection()) {
            using(var outerConn = factory.OpenDbConnection()) {
                //(re)create tables
                outerConn.DropAndCreateTable<Can_use_nested_transactions_Table1>();
                outerConn.DropAndCreateTable<Can_use_nested_transactions_Table2>();

                //using(var innerConn = OpenDbConnection()) {//use the factory to get the connections
                using(var innerConn = factory.OpenDbConnection()) {

                    using(var outerTran = outerConn.OpenTransaction()) {
                        outerConn.Insert(new Can_use_nested_transactions_Table1 { Dummy = DateTime.Now });

                        using(var innerTran = innerConn.OpenTransaction()) {
                            //The other transaction inserts into table1, Table2 is not locked
                            innerConn.Insert(new Can_use_nested_transactions_Table2 { Dummy = DateTime.Now });

                            //fails here, because innerTran has overwritten the ThreadStatic OrmLiteConfig.CurrentTransaction
                            outerConn.Insert(new Can_use_nested_transactions_Table1 { Dummy = DateTime.Now });

                            outerConn.Insert(new Can_use_nested_transactions_Table1 { Dummy = DateTime.Now });
                        }
                    }
                }
            }
        }

        public void Can_Perform_CRUD_Operations_On_Table_With_Schema()
        {
            var dbFactory = new OrmLiteConnectionFactory(
                @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\App_Data\Database1.mdf;Integrated Security=True;User Instance=True",
                SqlServerOrmLiteDialectProvider.Instance);
            using (IDbConnection db = dbFactory.OpenDbConnection())
            using (IDbCommand dbCmd = db.CreateCommand())
            {
                CreateSchemaIfNotExists(dbCmd);
                dbCmd.CreateTable<User>(true);

				dbCmd.Insert(new User { Id = 1, Name = "A", CreatedDate = DateTime.Now });
                dbCmd.Insert(new User { Id = 2, Name = "B", CreatedDate = DateTime.Now });
                dbCmd.Insert(new User { Id = 3, Name = "B", CreatedDate = DateTime.Now });

                var lastInsertId = dbCmd.GetLastInsertId();
                Assert.That(lastInsertId, Is.GreaterThan(0));

                var rowsB = dbCmd.Select<User>("Name = {0}", "B");
                Assert.That(rowsB, Has.Count.EqualTo(2));

                var rowIds = rowsB.ConvertAll(x => x.Id);
                Assert.That(rowIds, Is.EquivalentTo(new List<long> { 2, 3 }));

                rowsB.ForEach(x => dbCmd.Delete(x));

                rowsB = dbCmd.Select<User>("Name = {0}", "B");
                Assert.That(rowsB, Has.Count.EqualTo(0));

                var rowsLeft = dbCmd.Select<User>();
                Assert.That(rowsLeft, Has.Count.EqualTo(1));

                Assert.That(rowsLeft[0].Name, Is.EqualTo("A"));
            }
        }

        public void Can_Perform_CRUD_Operations_On_Table_With_Schema()
        {
            var dbFactory = new OrmLiteConnectionFactory(
                "User=SYSDBA;Password=masterkey;Database=ormlite-tests.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;",
                FirebirdOrmLiteDialectProvider.Instance);
            using (IDbConnection db = dbFactory.OpenDbConnection())
            {
                
                db.CreateTable<User>(true);

                db.Insert(new User { Id = 1, Name = "A", CreatedDate = DateTime.Now });
                db.Insert(new User { Id = 2, Name = "B", CreatedDate = DateTime.Now });
                db.Insert(new User { Id = 3, Name = "B", CreatedDate = DateTime.Now });

                var lastInsertId = db.GetLastInsertId();
                Assert.That(lastInsertId, Is.GreaterThan(0));

                var rowsB = db.Select<User>("Name = {0}", "B");
                Assert.That(rowsB, Has.Count.EqualTo(2));

                var rowIds = rowsB.ConvertAll(x => x.Id);
                Assert.That(rowIds, Is.EquivalentTo(new List<long> { 2, 3 }));

                rowsB.ForEach(x => db.Delete(x));

                rowsB = db.Select<User>("Name = {0}", "B");
                Assert.That(rowsB, Has.Count.EqualTo(0));

                var rowsLeft = db.Select<User>();
                Assert.That(rowsLeft, Has.Count.EqualTo(1));

                Assert.That(rowsLeft[0].Name, Is.EqualTo("A"));
            }
        }

		public void Can_query_using_float_in_alernate_culuture()
		{
			var dbFactory = new OrmLiteConnectionFactory(
				GetFileConnectionString(),
				FirebirdOrmLiteDialectProvider.Instance);
			
			using (var db =dbFactory.OpenDbConnection())
			using (var dbCmd = db.CreateCommand())
			{
				dbCmd.CreateTable<Point>(true);

				dbCmd.Insert(new Point { Width = 4, Height = 1.123f, Top = 3.456d, Left = 2.345m});
								
				var points = dbCmd.Select<Point>();

				Console.WriteLine(points.Dump());

				Assert.That(points[0].Width, Is.EqualTo(4));
				Assert.That(points[0].Height, Is.EqualTo(1.123f));
				Assert.That(points[0].Top, Is.EqualTo(3.456d));
				Assert.That(points[0].Left, Is.EqualTo(2.345m));
				
				points = dbCmd.Select<Point>("Height={0}", 1.123f);  // returns no rows! FirebirdSql bug?
				
				Assert.That(points.Count>0);
					
				
				
			}

		}

        public void Can_select_from_FileStream()
        {

            ConnectionString = "Data Source=localhost;Initial Catalog=test2;User Id=test;Password=test;Connect Timeout=120;MultipleActiveResultSets=True;Type System Version=SQL Server 2012;";
            var dialectProvider = SqlServerConverters.Configure(SqlServer2012Dialect.Provider);
            var dbFactory = new OrmLiteConnectionFactory(ConnectionString, dialectProvider);

            using (var db = dbFactory.OpenDbConnection())
            {
                db.DropTable<TestFile>();
                db.CreateTable<TestFile>();

                db.Insert(new TestFile { Contents = "contents".ToUtf8Bytes() });

                db.Select<TestFile>().PrintDump();

                //db.DropTable<FileStream>();
                //db.CreateTable<FileStream>();

                //db.Insert(new FileStream
                //{
                //    Name = "file.txt",
                //    Path = SqlHierarchyId.Parse("/1/2/3/"),
                //    ParentPath = SqlHierarchyId.Parse("/1/2/"),
                //    FileContent = "contents".ToUtf8Bytes(),
                //    FileType = MimeTypes.PlainText,
                //});

                //var q = db.From<FileStream>();
                //db.Select(q);
            }
        }

        public void test_if_ormlite_sql_prepare_update_works_for_image_blobs()
        {
            var dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqlServerOrmLiteDialectProvider.Instance);

            using (var db = dbFactory.OpenDbConnection())
            using (var dbConn = db.CreateCommand())
            {
                dbConn.CreateTable<ImageBlobDto>(true);

                var dto = new ImageBlobDto()
                {
                    Image1 = ImageToBytes(ImageBlobResource.Bild),
                    Image2 = ImageToBytes(ImageBlobResource.Bild2),
                    Image3 = ImageToBytes(ImageBlobResource.Bild3),
                    Complex = new object[] { "Foo", "Bar", "Baz", 1 }
                };

                var provider = OrmLiteConfig.DialectProvider as OrmLiteDialectProviderBase;
                Assert.NotNull(provider);

                using (var insertCmd = db.CreateInsertStatement(dto))
                {
                    var nInserted = insertCmd.ExecuteNonQuery();
                    Assert.Greater(nInserted, 0);   
                }

                dto.Id = 1;
                dto.Complex = new object[] { "Baz", "Bar", "Foo", 2 };
                using (var updateCmd = db.CreateUpdateStatement(dto))
                {
                    var nUpdated = updateCmd.ExecuteNonQuery();
                    Assert.Greater(nUpdated, 0);
                }
            }
        }

		public void datetime_tests__can_use_datetime2()
		{
			var dbFactory = new OrmLiteConnectionFactory(base.ConnectionString, SqlServerOrmLiteDialectProvider.Instance);

			//change to datetime2 - check for higher range and precision
			SqlServerOrmLiteDialectProvider.Instance.UseDatetime2(true);

			using(var conn = dbFactory.OpenDbConnection()) {
				var test_object_ValidForDatetime2 = Table_for_datetime2_tests.get_test_object_ValidForDatetime2();
				var test_object_ValidForNormalDatetime = Table_for_datetime2_tests.get_test_object_ValidForNormalDatetime();

				conn.CreateTable<Table_for_datetime2_tests>(true);

				//normal insert
                var insertedId = conn.Insert(test_object_ValidForDatetime2, selectIdentity:true);

				//read back, and verify precision
                var fromDb = conn.SingleById<Table_for_datetime2_tests>(insertedId);
				Assert.AreEqual(test_object_ValidForDatetime2.ToVerifyPrecision, fromDb.ToVerifyPrecision);

				//update
				fromDb.ToVerifyPrecision = test_object_ValidForDatetime2.ToVerifyPrecision.Value.AddYears(1);
				conn.Update(fromDb);
                var fromDb2 = conn.SingleById<Table_for_datetime2_tests>(insertedId);
				Assert.AreEqual(test_object_ValidForDatetime2.ToVerifyPrecision.Value.AddYears(1), fromDb2.ToVerifyPrecision);


				//check InsertParam
				conn.Insert(test_object_ValidForDatetime2);
			}
		}

		public void datetime_tests__check_default_behaviour()
		{
			var dbFactory = new OrmLiteConnectionFactory(base.ConnectionString, SqlServerOrmLiteDialectProvider.Instance);

			//default behaviour: normal datetime can't hold DateTime values of year 1.
			(SqlServerOrmLiteDialectProvider.Instance as SqlServerOrmLiteDialectProvider).UseDatetime2(false);

			using(var conn = dbFactory.OpenDbConnection()) {
				var test_object_ValidForDatetime2 = Table_for_datetime2_tests.get_test_object_ValidForDatetime2();
				var test_object_ValidForNormalDatetime = Table_for_datetime2_tests.get_test_object_ValidForNormalDatetime();

				conn.CreateTable<Table_for_datetime2_tests>(true);

				//normal insert
                var insertedId = conn.Insert(test_object_ValidForNormalDatetime, selectIdentity:true);

				//insert works, but can't regular datetime's precision is not great enough.
                var fromDb = conn.SingleById<Table_for_datetime2_tests>(insertedId);
				Assert.AreNotEqual(test_object_ValidForNormalDatetime.ToVerifyPrecision, fromDb.ToVerifyPrecision);

				var thrown = Assert.Throws<SqlTypeException>(() => {
                    conn.Insert(test_object_ValidForDatetime2);
				});
                Assert.That(thrown.Message.Contains("SqlDateTime overflow"));

				
				//check InsertParam
				conn.Insert(test_object_ValidForNormalDatetime);
				//InsertParam fails differently:
				var insertParamException = Assert.Throws<System.Data.SqlTypes.SqlTypeException>(() => {
					conn.Insert(test_object_ValidForDatetime2);
				});
				Assert.That(insertParamException.Message.Contains("SqlDateTime overflow"));
			}
		}

		public void Simple_CRUD_example()
		{
			var path = Config.SqliteFileDb;
			if (File.Exists(path))
				File.Delete(path);
			var connectionFactory = new OrmLiteConnectionFactory(path, SqliteOrmLiteDialectProvider.Instance.WithPassword("bob"));
			using (var db = connectionFactory.OpenDbConnection())
			{
				db.CreateTable<User>(true);

				db.Insert(new User {Id = 1, Name = "A", CreatedDate = DateTime.Now});
				db.Insert(new User {Id = 2, Name = "B", CreatedDate = DateTime.Now});
				db.Insert(new User {Id = 3, Name = "B", CreatedDate = DateTime.Now});

				var rowsB = db.Select<User>("Name = {0}", "B");
				var rowsB1 = db.Select<User>(user => user.Name == "B");

				Assert.That(rowsB, Has.Count.EqualTo(2));
				Assert.That(rowsB1, Has.Count.EqualTo(2));

				var rowIds = rowsB.ConvertAll(x => x.Id);
				Assert.That(rowIds, Is.EquivalentTo(new List<long> {2, 3}));

				rowsB.ForEach(x => db.Delete(x));

				rowsB = db.Select<User>("Name = {0}", "B");
				Assert.That(rowsB, Has.Count.EqualTo(0));

				var rowsLeft = db.Select<User>();
				Assert.That(rowsLeft, Has.Count.EqualTo(1));

				Assert.That(rowsLeft[0].Name, Is.EqualTo("A"));
			}
			File.Delete(path);
		}

        public void SaveDateTimeToDatabase()
        {
            var dbFactory = new OrmLiteConnectionFactory(base.ConnectionString, SqlServerOrmLiteDialectProvider.Instance);
            SqlServerOrmLiteDialectProvider.Instance.EnsureUtc(true);

            using (var db = dbFactory.OpenDbConnection())
            {
                var dateTime = new DateTime(2012, 1, 1, 1, 1, 1, DateTimeKind.Local);
                var x = InsertAndSelectDateTime(db, dateTime);
                Assert.AreEqual(DateTimeKind.Utc, x.Test.Kind);
                Assert.AreEqual(x.Test.ToUniversalTime(), dateTime.ToUniversalTime());
                Assert.AreEqual(x.Test.ToLocalTime(), dateTime.ToLocalTime());

                dateTime = new DateTime(2012, 1, 1, 1, 1, 1, DateTimeKind.Utc);
                x = InsertAndSelectDateTime(db, dateTime);
                Assert.AreEqual(DateTimeKind.Utc, x.Test.Kind);
                Assert.AreEqual(x.Test.ToUniversalTime(), dateTime.ToUniversalTime());
                Assert.AreEqual(x.Test.ToLocalTime(), dateTime.ToLocalTime());

                dateTime = new DateTime(2012, 1, 1, 1, 1, 1, DateTimeKind.Unspecified);
                x = InsertAndSelectDateTime(db, dateTime);
                Assert.AreEqual(DateTimeKind.Utc, x.Test.Kind);
                Assert.AreEqual(x.Test.ToUniversalTime(), dateTime);
                Assert.AreEqual(x.Test.ToLocalTime(), dateTime.ToLocalTime());
            }
        }

        public void AutoDispose_ConnectionFactory_disposes_connection()
        {
            OrmLiteConfig.DialectProvider = SqliteOrmLiteDialectProvider.Instance;
            var factory = new OrmLiteConnectionFactory(":memory:", true);

            using (var db = factory.OpenDbConnection())
            {
                db.CreateTable<Shipper>(false);
                db.Insert(new Shipper { CompanyName = "I am shipper" });
            }

            using (var db = factory.OpenDbConnection())
            {
                db.CreateTable<Shipper>(false);
                Assert.That(db.Select<Shipper>(), Has.Count.EqualTo(0));
            }
        }

		public void Shard_1000_Robots_over_10_shards()
		{
			const int NoOfShards = 10;
			const int NoOfRobots = 1000;

			var dbFactory = new OrmLiteConnectionFactory(
				"~/App_Data/robots-master.sqlite".MapAbsolutePath(),
				false, SqliteDialect.Provider);

			//var dbFactory = new OrmLiteConnectionFactory(
			//    "Data Source=localhost;Initial Catalog=RobotsMaster;Integrated Security=SSPI", 
			//    SqlServerDialect.Provider);

			//Create Master Table in Master DB
			dbFactory.Run(db => db.CreateTable<MasterRecord>(overwrite: false));
			NoOfShards.Times(i =>
				                 {
					                 var shardId = "robots-shard" + i;
					                 dbFactory.RegisterConnection(shardId, "~/App_Data/{0}.sqlite".Fmt(shardId).MapAbsolutePath(),
					                                              SqliteDialect.Provider);

					                 //Create Robot table in Shard
					                 dbFactory.OpenDbConnection(shardId).Run(db => db.CreateTable<Robot>(overwrite: false));
				                 });

			var newRobots = NoOfRobots.Times(i => //Create 1000 Robots
			                                 new Robot
				                                 {
					                                 Id = i,
					                                 Name = "R2D" + i,
					                                 CreatedDate = DateTime.UtcNow,
					                                 CellCount = DateTime.UtcNow.ToUnixTimeMs()%100000
				                                 });

			foreach (var newRobot in newRobots)
			{
				using (IDbConnection db = dbFactory.OpenDbConnection()) //Open Connection to Master DB
				{
					db.Insert(new MasterRecord {Id = Guid.NewGuid(), RobotId = newRobot.Id, RobotName = newRobot.Name});
					using (IDbConnection robotShard = dbFactory.OpenDbConnection("robots-shard" + newRobot.Id%NoOfShards)) //Shard DB
					{
						robotShard.Insert(newRobot);
					}
				}
			}
		}

        public void Default_inmemory_ConnectionFactory_reuses_connection()
        {
            OrmLiteConfig.DialectProvider = SqliteDialect.Provider;
            var factory = new OrmLiteConnectionFactory(":memory:");

            using (var db = factory.OpenDbConnection())
            {
                db.CreateTable<Shipper>(false);
                db.Insert(new Shipper { CompanyName = "I am shipper" });
            }

            using (var db = factory.OpenDbConnection())
            {
                db.CreateTable<Shipper>(false);
                Assert.That(db.Select<Shipper>(), Has.Count.EqualTo(1));
            }
        }

        public void NonAutoDispose_ConnectionFactory_reuses_connection()
        {
            var factory = new OrmLiteConnectionFactory(":memory:", false);

            using (var dbConn = factory.OpenDbConnection())
            using (var dbCmd = dbConn.CreateCommand())
            {
                dbCmd.CreateTable<Shipper>(false);
                dbCmd.Insert(new Shipper { CompanyName = "I am shipper" });
            }

            using (var dbConn = factory.OpenDbConnection())
            using (var dbCmd = dbConn.CreateCommand())
            {
                dbCmd.CreateTable<Shipper>(false);
                Assert.That(dbCmd.Select<Shipper>(), Has.Count.EqualTo(1));
            }
        }

        public void NonAutoDispose_ConnectionFactory_reuses_connection()
        {
            var factory = new OrmLiteConnectionFactory("User=SYSDBA;Password=masterkey;Database=ormlite-tests.fdb;DataSource=localhost;Dialect=3;charset=ISO8859_1;", false);

            using (var db = factory.OpenDbConnection())
            {
                db.CreateTable<Shipper>(false);
                db.Insert(new Shipper { CompanyName = "I am shipper 2" });
            }

            using (var db = factory.OpenDbConnection())
            {
                db.CreateTable<Shipper>(false);
                Assert.That(db.Select<Shipper>(), Has.Count.EqualTo(1));

                db.DeleteAll<Shipper>();
                Assert.That(db.Select<Shipper>(), Has.Count.EqualTo(0));
            }
        }