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"]);
}
public void MapOutput()
{
OutRefTest o = new OutRefTest();
using (DbManager db = new DbManager())
{
db
.SetSpCommand("OutRefTest", db.CreateParameters(o,
new string[] { "outputID", "outputStr" },
new string[] { "inputOutputID", "inputOutputStr" },
null))
.ExecuteNonQuery(o);
}
Assert.AreEqual(5, o.outputID);
Assert.AreEqual(15, o.inputOutputID);
Assert.AreEqual("5", o.outputStr);
Assert.AreEqual("510", o.inputOutputStr);
}
public Person UpdatePerson(DbManager db, Person person)
{
db
./*[a]*/SetSpCommand/*[/a]*/("Person_Update", db./*[a]*/CreateParameters/*[/a]*/(person))
.ExecuteNonQuery();
return GetPersonByID(db, person.ID);
}
public void CreateParametersTest()
{
using (DbManager db = new DbManager())
{
DataTypeTest dt = new DataTypeTest();
dt.ID = 12345;
dt.Binary_ = new byte[2] {1, 2};
#if !ORACLE
dt.Boolean_ = true;
dt.Guid_ = Guid.Empty;
#endif
dt.Byte_ = 250;
dt.Bytes_ = new byte[2] {2, 1};
dt.DateTime_ = DateTime.Now;
dt.Decimal_ = 9876543210.0m;
dt.Double_ = 12345.67890;
dt.Int16_ = 12345;
dt.Int32_ = 1234567890;
dt.Int64_ = 1234567890123456789;
dt.Money_ = 99876543210.0m;
dt.Single_ = 1234.0f;
dt.String_ = "Crazy Frog";
dt.Char_ = 'F';
dt.SByte_ = 123;
dt.UInt16_ = 65432;
dt.UInt32_ = 4000000000;
dt.UInt64_ = 12345678901234567890;
#if !SQLCE
dt.Stream_ = new MemoryStream(5);
dt.Xml_ = new XmlTextReader(new StringReader("<xml/>"));
dt.XmlDoc_ = new XmlDocument(); dt.XmlDoc_.LoadXml("<xmldoc/>");
#endif
IDbDataParameter[] parameters = db.CreateParameters(dt);
Assert.IsNotNull(parameters);
Assert.AreEqual(ObjectMapper<DataTypeTest>.Instance.Count, parameters.Length);
foreach (MemberMapper mm in ObjectMapper<DataTypeTest>.Instance)
{
string paramName = (string)db.DataProvider.Convert(mm.Name, db.GetConvertTypeToParameter());
IDbDataParameter p = Array.Find(parameters,
delegate(IDbDataParameter obj) { return obj.ParameterName == paramName; });
Assert.IsNotNull(p);
Assert.AreEqual(mm.GetValue(dt), p.Value);
}
}
}
public void CreateParametersTest()
{
Person person = new Person();
person.FirstName = "John";
person.LastName = "Smith";
person.Gender = Gender.Male;
using (DbManager db = new DbManager())
{
db.BeginTransaction();
// Prepare command.
//
int id = db
.SetSpCommand("Person_Insert",
db./*[a]*/CreateParameters/*[/a]*/(person))
.ExecuteScalar<int>();
// Check the result.
//
person = db
.SetCommand(
"SELECT * FROM Person WHERE PersonID = @id",
db.Parameter("@id", id))
.ExecuteObject<Person>();
Assert.IsNotNull(person);
// Cleanup.
//
db
.SetCommand(
"DELETE FROM Person WHERE PersonID = @id",
db.Parameter("@id", id))
.ExecuteNonQuery();
db.CommitTransaction();
}
}
public Person UpdatePerson(DbManager db, Person person)
{
db
./*[a]*/SetCommand/*[/a]*/(@"
UPDATE
Person
SET
LastName = @LastName,
FirstName = @FirstName,
Gender = @Gender
WHERE
PersonID = @PersonID",
db./*[a]*/CreateParameters/*[/a]*/(person))
.ExecuteNonQuery();
return GetPersonByID(db, person.ID);
}
