C# DbManager.CreateParameters方法代码示例

		public void SetCommandWorksCorrectlyWithSprocParameterPrefixSet()
		{
			var oldPrefix = MySqlDataProvider.SprocParameterPrefix;
			MySqlDataProvider.SprocParameterPrefix = "_";

			try
			{
				using (var db = new DbManager(ProviderName.MySql))
				{
					var person = db.SetCommand(
							"SELECT * FROM Person WHERE PersonID = ?PersonID",
							db.Parameter("?PersonID", 1))
						.ExecuteObject<Person>();

					Assert.IsNotNull(person);
					Assert.AreEqual(1, person.ID);

					var person2 = db.SetCommand(
							"SELECT * FROM Person WHERE FirstName = ?firstName AND LastName = ?lastName",
							db.CreateParameters(person))
						.ExecuteObject<Person>();

					Assert.IsNotNull(person2);
					Assert.AreEqual(person, person2);
				}
			}
			finally
			{
				MySqlDataProvider.SprocParameterPrefix = oldPrefix;
			}
		}

		public void Test()
		{
			const int testValue = 0;

			NullableInt ni = new NullableInt();

			ni.I  = testValue;
			ni.II = testValue;
			
			using (DbManager db = new DbManager())
			{
				IDbDataParameter[] ps = db.CreateParameters(ni);

				foreach (IDbDataParameter p in ps)
				{
					switch (p.ParameterName)
					{
						case "@I":
							Assert.AreEqual(testValue, p.Value);
							break;
						case "@II":
							Assert.AreEqual(testValue, p.Value);
							break;
					}
				}
			}
		}

		public void CompositeObjectTest()
		{
			using (DbManager db = new DbManager())
			{
				IDbDataParameter[] prms = db.CreateParameters(new Entity());
				Assert.AreEqual(3, prms.Length);
			}
		}

		public void Test()
		{
			List<Person> list = new List<Person>
			{
				new Person { FirstName = "John", LastName = "Smith", Gender = Gender.Male   },
				new Person { FirstName = "Jane", LastName = "Smith", Gender = Gender.Female }
			};

			using (DbManager db = new DbManager())
			{
				db.BeginTransaction();

				// Prepare command.
				//
				db
					.SetSpCommand("Person_Insert",
						db.CreateParameters(list[0]))
					./*[a]*/Prepare/*[/a]*/();

				// Insert.
				//
				foreach (Person person in list)
				{
					db./*[a]*/AssignParameterValues/*[/a]*/(person);
					db.ExecuteNonQuery();
				}

				// Check the result.
				//
				list = db
					.SetCommand(
						"SELECT * FROM Person WHERE LastName = @lastName",
						db.Parameter("@lastName", "Smith"))
					.ExecuteList<Person>();

				Assert.GreaterOrEqual(2, list.Count);

				// Cleanup.
				//
				db
					.SetCommand(
						"DELETE FROM Person WHERE LastName = @lastName",
						db.Parameter("@lastName", "Smith"))
					.ExecuteNonQuery();

				db.CommitTransaction();
			}
		}

		int InsertPerson(Person person)
		{
			using (DbManager db = new DbManager())
			{
				return db
					.SetCommand(@"
						INSERT INTO Person
							( LastName,  FirstName,  MiddleName,  Gender)
						VALUES
							(@LastName, @FirstName, @MiddleName, @Gender)

						SELECT Cast(SCOPE_IDENTITY() as int)",
						db.CreateParameters(person))
					.ExecuteScalar<int>();
			}
		}

		void UpdatePerson(Person person)
		{
			using (DbManager db = new DbManager())
			{
				db
					.SetCommand(@"
						UPDATE
							Person
						SET
							LastName   = @LastName,
							FirstName  = @FirstName,
							MiddleName = @MiddleName,
							Gender     = @Gender
						WHERE
							PersonID = @PersonID",
						db.CreateParameters(person))
					.ExecuteNonQuery();
			}
		}

		public void Test()
		{
			Person person = new Person();

			person.FirstName = "John";
			person.LastName  = "Smith";
			person.Gender    = Gender.Male;

			using (DbManager db = new DbManager())
			{
				db.BeginTransaction();

				// Execute.
				//
				db
					.SetSpCommand("Person_Insert", db.CreateParameters(person))
					./*[a]*/ExecuteNonQuery/*[/a]*/();

				// Check the result.
				//
				person = db
					.SetCommand(
						"SELECT * FROM Person WHERE LastName = @lastName",
						db.Parameter("@lastName", "Smith"))
					.ExecuteObject<Person>();

				Assert.IsNotNull(person);

				// Cleanup.
				//
				db
					.SetCommand(
						"DELETE FROM Person WHERE LastName = @lastName",
						db.Parameter("@lastName", "Smith"))
					./*[a]*/ExecuteNonQuery/*[/a]*/();

				db.CommitTransaction();
			}
		}

		public void ParameterPrefixTest()
		{
			var oldPrefix = MySqlDataProvider.SprocParameterPrefix;
			MySqlDataProvider.SprocParameterPrefix = "_";

			try
			{
				using (var db = new DbManager(ProviderName.MySql))
				{
					var person = db.SetSpCommand("GetPersonById", db.Parameter("?ID", 1)).ExecuteObject<Person>();
					Assert.IsNotNull(person);

					var person2 = db.SetSpCommand("GetPersonByName", db.CreateParameters(person)).ExecuteObject<Person>();
					Assert.IsNotNull(person2);

					Assert.AreEqual(person, person2);
				}
			}
			finally
			{
				MySqlDataProvider.SprocParameterPrefix = oldPrefix;
			}
		}

		public void CreateParametersTest()
		{
			IDbDataParameter[] parameters;
			var p = (Person)TypeAccessor.CreateInstance(typeof(Person));
			p.ID         = 12345;
			p.First.Name = "Crazy";
			p.Last.Name  = "Frog";
			p.Name       = "Froggy";
			p.Type       = typeof(DbManager);

			using (var db = new DbManager())
			{
				parameters = db.CreateParameters(p);
			}

			Assert.IsNotNull(parameters);
			Assert.AreEqual(7, parameters.Length);

			foreach (var parameter in parameters)
				Console.WriteLine("{0}: {1}", parameter.ParameterName, parameter.Value);
		}

		public void CreateParametersStructTest()
		{
			var dbInfo = new DBInfo { TimeValue = DateTime.Now };

			using (var db = new DbManager())
			{
				var parameters = db.CreateParameters(dbInfo);
				
				Assert.IsNotNull(parameters);
				Assert.AreEqual(1, parameters.Length);
				Assert.AreEqual(dbInfo.TimeValue, parameters[0].Value);
				
			}
		}

		public void CreateManyParametersTest()
		{
			FirstPart  f = new FirstPart();
			SecondPart s = new SecondPart();

			f.FirstName = "John";
			s.LastName = "Pupkin";

			using (DbManager db = new DbManager())
			{
				Person p = (Person)db
					.SetSpCommand ("Person_SelectByName", db.CreateParameters(f), db.CreateParameters(s))
					.ExecuteObject(typeof(Person));
				
				Assert.IsNotNull(p);
				Assert.AreEqual(f.FirstName, p.FirstName);
				Assert.AreEqual(s.LastName,  p.LastName);
			}
		}

		public void CreateParametersTest()
		{
			using (var db = new DbManager())
			{
				var dt = new DataTypeTest
				{
					ID        = 12345,
					Binary_   = new byte[2] {1, 2},
#if !ORACLE
					Boolean_  = true,
					Guid_     = Guid.Empty,
#endif
					Byte_     = 250,
					Bytes_    = new byte[] { 2, 1 },
					DateTime_ = DateTime.Now,
					Decimal_  = 9876543210.0m,
					Double_   = 12345.67890,
					Int16_    = 12345,
					Int32_    = 1234567890,
					Int64_    = 1234567890123456789,
					Money_    = 99876543210.0m,
					Single_   = 1234.0f,
					String_   = "Crazy Frog",

					Char_     = 'F',
					SByte_    = 123,
					//UInt16_   = 65432,
					//UInt32_   = 4000000000,
					//UInt64_   = 12345678901234567890,
#if !SQLCE
					Stream_   = new MemoryStream(5),
					Xml_      = new XmlTextReader(new StringReader("<xml/>")),
#endif
				};

				var parameters = db.CreateParameters(dt);

				Assert.IsNotNull(parameters);
				Assert.AreEqual(ObjectMapper<DataTypeTest>.Instance.Count, parameters.Length);

				foreach (MemberMapper mm in ObjectMapper<DataTypeTest>.Instance)
				{
					var paramName = (string)db.DataProvider.Convert(mm.Name, db.GetConvertTypeToParameter());
					var p         = parameters.First(obj => obj.ParameterName == paramName);

					Assert.IsNotNull(p);
					Assert.AreEqual(mm.GetValue(dt), p.Value, mm.MemberName);
				}
			}
		}

		public void CreateParametersSqlTest()
		{
			using (DbManager db = new DbManager())
			{
				DataTypeSqlTest dt = new DataTypeSqlTest();
				
				dt.ID        = 12345;
				dt.Binary_   = new SqlBinary(new byte[2] {1, 2});
				dt.Boolean_  = new SqlBoolean(1);
				dt.Byte_     = new SqlByte(250);
				dt.DateTime_ = new SqlDateTime(DateTime.Now);
				dt.Decimal_  = new SqlDecimal(9876543210.0m);
				dt.Double_   = new SqlDouble(12345.67890);
				dt.Guid_     = new SqlGuid(Guid.Empty);
				dt.Int16_    = new SqlInt16(12345);
				dt.Int32_    = new SqlInt32(1234567890);
				dt.Int64_    = new SqlInt64(1234567890123456789);
				dt.Money_    = new SqlMoney(99876543210.0m);
				dt.Single_   = new SqlSingle(1234.0f);
				dt.String_   = new SqlString("Crazy Frog");

#if !SQLCE
				dt.Bytes_    = new SqlBytes(new byte[2] {2, 1});
				dt.Chars_    = new SqlChars(new char[2] {'B', 'L'});
				dt.Xml_      = new SqlXml(new XmlTextReader(new StringReader("<xml/>")));
#endif

				var parameters = db.CreateParameters(dt);

				Assert.IsNotNull(parameters);
				Assert.AreEqual(ObjectMapper<DataTypeSqlTest>.Instance.Count, parameters.Length);

				foreach (MemberMapper mm in ObjectMapper<DataTypeSqlTest>.Instance)
				{
					var pName = (string)db.DataProvider.Convert(mm.Name, db.GetConvertTypeToParameter());
					var p     = Array.Find(parameters, obj => obj.ParameterName == pName);

					Assert.IsNotNull(p);
					Assert.AreEqual(mm.GetValue(dt), p.Value);
				}
			}
		}

		public void InsertAndMapBack()
		{
			Person e = new Person();
			e.FirstName = "Crazy";
			e.LastName  = "Frog";
			e.Gender    =  Gender.Other;

			using (DbManager db = new DbManager())
			{
				db
					.SetSpCommand("Person_Insert", db.CreateParameters(e, new string[] { "PersonID" }, null, null))
					.ExecuteObject(e);

				Assert.IsTrue(e.ID > 0);

				// Cleanup.
				//
				db
					.SetSpCommand("Person_Delete", db.CreateParameters(e))
					.ExecuteNonQuery();
			}
		}

		public void MapDataRow()
		{
			DataTable dataTable = new DataTable();
			dataTable.Columns.Add("ID",             typeof(int));
			dataTable.Columns.Add("outputID",       typeof(int));
			dataTable.Columns.Add("inputOutputID",  typeof(int));
			dataTable.Columns.Add("str",            typeof(string));
			dataTable.Columns.Add("outputStr",      typeof(string));
			dataTable.Columns.Add("inputOutputStr", typeof(string));

			DataRow dataRow = dataTable.Rows.Add(new object[]{5, 0, 10, "5", null, "10"});

			using (DbManager db = new DbManager())
			{
				db
					.SetSpCommand("OutRefTest", db.CreateParameters(dataRow,
						new string[] {      "outputID",      "outputStr" },
						new string[] { "inputOutputID", "inputOutputStr" },
						null))
					.ExecuteNonQuery(dataRow);
			}

			Assert.AreEqual(5,     dataRow["outputID"]);
			Assert.AreEqual(15,    dataRow["inputOutputID"]);
			Assert.AreEqual("5",   dataRow["outputStr"]);
			Assert.AreEqual("510", dataRow["inputOutputStr"]);
		}