C# DbManager.Parameter方法代码示例

		public IDbDataParameter[] GetParameters(DbManager db, object obj)
		{
			var parameters = new IDbDataParameter[_parameters.Count];

			for (var i = 0; i < _parameters.Count; i++)
			{
				var info = _parameters[i];

				//parameters[i] = db.Parameter(info.ParameterName, info.MemberMapper.GetValue(obj));

				var mmi = info.MemberMapper.MapMemberInfo;
				var val = info.MemberMapper.GetValue(obj);

				if (val == null && mmi.Nullable/* && mmi.NullValue == null*/)
				{
					//replace value with DbNull
					val = DBNull.Value;
				}

				if (mmi.IsDbTypeSet)
				{
					parameters[i] = mmi.IsDbSizeSet 
						? db.Parameter(info.ParameterName, val, info.MemberMapper.DbType, mmi.DbSize) 
						: db.Parameter(info.ParameterName, val, info.MemberMapper.DbType);
				}
				else
				{
					parameters[i] = db.Parameter(info.ParameterName, val);
				}
			}

			return parameters;
		}

 public static Common GetCommon(int roadId, double km)
 {
     using (DbManager db = new DbManager())
         return db.SetCommand(@"SELECT TOP 1 r.road_titul as RoadName, r.Kroad, " +
             "o.org_name as ServiceName, o.org_phone as ServicePhone, o.road_pos_beg as ServiceKmBeg, o.road_pos_end as ServiceKmEnd, " +
             "g.org_name as GIBDDName, g.org_phone as GIBDDPhone, g.org_address as GIBDDAddress " +
             "FROM Sys_road r LEFT JOIN GP_org_road o ON (r.Kroad=o.Kroad AND o.road_pos_beg <= @km AND o.road_pos_end >= @km)" +
             "LEFT JOIN GP_gibdd_road g ON (r.Kroad=g.Kroad AND g.road_pos_beg <= @km AND @km <= g.road_pos_end)  WHERE [email protected]",
             db.Parameter("@Kroad", roadId), db.Parameter("@km", km)).ExecuteObject<Common>();
 }

		public void SetValueTest()
		{
			using (DbManager db = new DbManager())
			{
				db.SetCommand("SELECT @par * 2",
					db./*[a]*/Parameter/*[/a]*/("@par", DbType.Int32));

				db./*[a]*/Parameter("@par").Value/*[/a]*/ = 2;

				Assert.AreEqual(4, db.ExecuteScalar<int>());
			}
		}

		public void AssignParameterTest()
		{
			using (DbManager db = new DbManager())
			{
				int n = db
					.SetCommand("SELECT @par1 + @par2",
						db./*[a]*/Parameter/*[/a]*/("@par1", 2),
						db./*[a]*/Parameter/*[/a]*/("@par2", 2))
					.ExecuteScalar<int>();

				Assert.AreEqual(4, n);
			}
		}

		public Person CreatePerson(DbManager db)
		{
			int id = db
				./*[a]*/SetCommand/*[/a]*/(@"
					INSERT INTO Person ( LastName,  FirstName,  Gender)
					VALUES             (@LastName, @FirstName, @Gender)

					SELECT Cast(SCOPE_IDENTITY() as int) PersonID",
					db./*[a]*/Parameter/*[/a]*/("@LastName",  "Frog"),
					db./*[a]*/Parameter/*[/a]*/("@FirstName", "Crazy"),
					db./*[a]*/Parameter/*[/a]*/("@Gender",    Map.EnumToValue(Gender.Male)))
				.ExecuteScalar<int>();

			return GetPersonByID(db, id);
		}

		public void SprocParameterPrefixShouldBeSpecifiedManuallyWhenItIsNotSet()
		{
			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.Parameter("?_lastName", person.LastName),
						db.Parameter("?_firstName", person.FirstName))
					.ExecuteObject<Person>();
				Assert.IsNotNull(person2);
			}
		}

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

		// Insert, Update, and Delete a person.
		//
		public Person GetPersonByID(DbManager db, int id)
		{
			return db
				./*[a]*/SetCommand/*[/a]*/("SELECT * FROM Person WHERE PersonID = @id",
					db./*[a]*/Parameter/*[/a]*/("@id", id))
				.ExecuteObject<Person>();
		}

		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 Insert()
		{
			using (DbManager db = new DbManager())
			{
				_id = db
					.SetCommand(@"
						INSERT INTO Person (
							 FirstName,  LastName,  Gender
						) VALUES (
							@FirstName, @LastName, @Gender
						)
						SELECT Cast(SCOPE_IDENTITY() as int)",
						db.Parameter("@FirstName", "Crazy"),
						db.Parameter("@LastName",  "Frog"),
						db.Parameter("@Gender",    Map.EnumToValue(Gender.Unknown)))
					.ExecuteScalar<int>();
			}
		}

		private int Insert()
		{
			using (DbManager db = new DbManager())
			{
				return db
					.SetCommand(@"
						INSERT INTO Person (
							 FirstName,  LastName,  Gender
						) VALUES (
							@FirstName, @LastName, @Gender
						)
						SELECT Cast(SCOPE_IDENTITY() as int)",
						db.Parameter("@FirstName", "Crazy"),
						db.Parameter("@LastName",  "Frog"),
						db.Parameter("@Gender",    "U"))
					.ExecuteScalar<int>();
			}
		}

		Person GetPersonSqlText(int id)
		{
			using (DbManager db = new DbManager())
			{
				return db
					.SetCommand("SELECT * FROM Person WHERE PersonID = @id",
						db.Parameter("@id", id))
					./*[a]*/ExecuteObject<Person>()/*[/a]*/;
			}
		}

		public void Delete()
		{
			using (DbManager db = new DbManager())
			{
				db
					.SetCommand("DELETE FROM Person WHERE PersonID = @id",
						db.Parameter("@id", _id))
					.ExecuteNonQuery();
			}
		}

		Person GetPersonByID(int id)
		{
			using (DbManager db = new DbManager())
			{
				return db
					.SetCommand("SELECT * FROM Person WHERE PersonID = @id",
						db.Parameter("@id", id))
					.ExecuteObject<Person>();
			}
		}

		Person GetPersonSproc1(int id)
		{
			using (DbManager db = new DbManager())
			{
				return db
					.SetSpCommand("Person_SelectByKey",
						db.Parameter("@id", id))
					./*[a]*/ExecuteObject<Person>()/*[/a]*/;
			}
		}