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 r.Kroad=@Kroad",
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]*/;
}
}
string GetFirstName(int id)
{
using (DbManager db = new DbManager())
{
return db
.SetCommand("SELECT FirstName FROM Person WHERE PersonID = @id",
db.Parameter("@id", id))
./*[a]*/ExecuteScalar/*[/a]*/<string>();
}
}
public static DataTable GetData(string source, DateTime? from, int id)
{
var dataTable = new DataTable();
switch (source)
{
case "image":
if (from == null)
{
if (id == 0)
{
var sql = "SELECT TOP 10 kimage, kcamera, load_time FROM gp_image order by load_time desc";
using (DbManager db = new DbManager())
return db.SetCommand(sql).ExecuteDataTable();
}
else
{
var sql = "SELECT TOP 10 kimage, kcamera, load_time FROM gp_image where kcamera=@id order by load_time desc";
using (DbManager db = new DbManager())
return db.SetCommand(sql, db.Parameter("@id", id)).ExecuteDataTable();
}
}
else
{
if (id == 0)
{
var sql = "SELECT TOP 10 kimage, kcamera, load_time FROM gp_image where load_time <= @from order by load_time desc";
using (DbManager db = new DbManager())
return db.SetCommand(sql, db.Parameter("@from", from)).ExecuteDataTable();
}
else
{
var sql = "SELECT TOP 10 kimage, kcamera, load_time FROM gp_image where load_time <= @from and kcamera=@id order by load_time desc";
using (DbManager db = new DbManager())
return db.SetCommand(sql, db.Parameter("@from", from), db.Parameter("@id", id))
.ExecuteDataTable();
}
}
}
return dataTable;
}
// BLToolkit data access method.
//
public List<Person> /*[a]*/GetList/*[/a]*/(Gender gender)
{
/*[a]*/using/*[/a]*/ (/*[a]*/DbManager/*[/a]*/ db = new DbManager(/*[a]*/"DemoConnection"/*[/a]*/))
{
return db
./*[a]*/SetCommand/*[/a]*/(
"SELECT * FROM Person WHERE Gender = @gender",
db./*[a]*/Parameter/*[/a]*/("@gender", /*[a]*/Map.EnumToValue/*[/a]*/(gender)))
./*[a]*/ExecuteList/*[/a]*/<Person>();
}
}
// Select a person.
//
public Person GetPersonByID1(int id)
{
using (DbManager db = new DbManager())
{
// Pass a parameter using the [b]Parameter[/b] method.
//
return db
./*[a]*/SetSpCommand/*[/a]*/("Person_SelectByKey",
db./*[a]*/Parameter/*[/a]*/("@id", id))
.ExecuteObject<Person>();
}
}
public Person CreatePerson(DbManager db)
{
int id = db
./*[a]*/SetSpCommand/*[/a]*/("Person_Insert",
db./*[a]*/Parameter/*[/a]*/("@LastName", "Frog"),
db./*[a]*/Parameter/*[/a]*/("@MiddleName", null),
db./*[a]*/Parameter/*[/a]*/("@FirstName", "Crazy"),
db./*[a]*/Parameter/*[/a]*/("@Gender", Map.EnumToValue(Gender.Male)))
.ExecuteScalar<int>();
return GetPersonByID(db, id);
}
public void DirectTest()
{
using (DbManager db = new DbManager())
{
string paramName = (string) db.DataProvider.Convert("name", ConvertType.NameToQueryParameter);
string fieldName = (string) db.DataProvider.Convert("FirstName", ConvertType.NameToQueryField);
db.SetCommand(string.Format("SELECT {0} = {1} FROM Person WHERE PersonID = 1", paramName, fieldName)
, db.OutputParameter(paramName, DbType.String, 50))
.ExecuteNonQuery();
Assert.AreEqual("John", db.Parameter(paramName).Value);
}
}
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 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();
// Execute.
//
db
.SetSpCommand("Person_Insert")
./*[a]*/ExecuteForEach/*[/a]*/<Person>(list);
// 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();
}
}
public PatientHistory FindById(int id)
{
using (var db = new DbManager())
{
return db
.SetCommand(@"select ph.*,p.Name PatientName,d.Name DoctorName
from PatientHistory ph inner join Patient p
on ph.PatientID = p.ID inner join Doctor d
on ph.DoctorID = d.ID
WHERE ph.ID = @id",
db.Parameter("@id", id))
.ExecuteObject<PatientHistory>();
}
}
public IDbDataParameter[] GetParameters(DbManager db, object[] key)
{
if (_parameters.Count != key.Length)
throw new DataAccessException("Parameter list does match key list.");
var parameters = new IDbDataParameter[_parameters.Count];
for (var i = 0; i < _parameters.Count; i++)
{
var info = _parameters[i];
parameters[i] = db.Parameter(info.ParameterName, key[i]);
}
return parameters;
}
public void ReturnValueTest()
{
using (DbManager db = new DbManager())
{
/*
* CREATE Function Scalar_ReturnParameter()
* RETURNS int
* AS
* BEGIN
* RETURN 12345
* END
*/
db
.SetSpCommand("Scalar_ReturnParameter")
.ExecuteNonQuery();
int n = (int)db./*[a]*/Parameter("@RETURN_VALUE").Value/*[/a]*/;
Assert.AreEqual(12345, n);
}
}
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] = val != DBNull.Value
? db.Parameter(info.ParameterName, val)
: db.Parameter(info.ParameterName, val, info.MemberMapper.GetDbType());
}
if (mmi.KeyGenerator is SequenceKeyGenerator && ActionName == "InsertWithIdentity")
{
parameters[i] = db.OutputParameter(info.ParameterName, val);
}
}
return parameters;
}
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 NativeConnection()
{
string connectionString = DbManager.GetConnectionString(null);
using (DbManager db = new DbManager(new SqlConnection(connectionString)))
{
db
.SetSpCommand ("Person_SelectByName",
db.Parameter("@firstName", "John"),
db.Parameter("@lastName", "Pupkin"))
.ExecuteScalar();
}
}
public void ExecuteObject2Sql()
{
using (DbManager db = new DbManager())
{
DataTypeSqlTest dt = (DataTypeSqlTest)db
.SetCommand("SELECT * FROM DataTypeTest WHERE DataTypeID = " + db.DataProvider.Convert("id", ConvertType.NameToQueryParameter),
db.Parameter("id", 2))
.ExecuteObject(typeof(DataTypeSqlTest));
TypeAccessor.WriteConsole(dt);
}
}
