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);
}
}
IList<Person> GetPersonListSproc()
{
using (DbManager db = new DbManager())
{
return db
.SetSpCommand("Person_SelectAll")
./*[a]*/ExecuteList<Person>()/*[/a]*/;
}
}
void GetCustomPersonList(IList list)
{
using (DbManager db = new DbManager())
{
db
.SetSpCommand("Person_SelectAll")
./*[a]*/ExecuteList(list, typeof(Person))/*[/a]*/;
}
}
public List<LogEntity> GetByUserId(int userId)
{
using (var db = new DbManager())
{
return db
.SetSpCommand("GetLogsByUserId", userId)
.ExecuteList<LogEntity>();
}
}
public LogEntity GetById(int id)
{
using (var db = new DbManager())
{
return db
.SetSpCommand("GetLogById", id)
.ExecuteObject<LogEntity>();
}
}
public List<LogEntity> GetAll()
{
using (var db = new DbManager())
{
return db
.SetSpCommand("GetAllLogs")
.ExecuteList<LogEntity>();
}
}
// Select a person list.
//
public IList<Person> GetPersonList()
{
using (DbManager db = new DbManager())
{
return db
./*[a]*/SetSpCommand/*[/a]*/("Person_SelectAll")
.ExecuteList<Person>();
}
}
Person GetPersonSproc2(int id)
{
using (DbManager db = new DbManager())
{
return db
.SetSpCommand("Person_SelectByKey", id)
./*[a]*/ExecuteObject<Person>()/*[/a]*/;
}
}
public void ReturnValueTest2()
{
using (DbManager db = new DbManager())
{
int n = db
.SetSpCommand("Scalar_ReturnParameter")
.ExecuteScalar<int>(ScalarSourceType.ReturnValue);
Assert.AreEqual(12345, n);
}
}
public Person GetPersonByID2(int id)
{
using (DbManager db = new DbManager())
{
// Pass a parameter using the [b]params[/b] parameter of the SetSpCommand method.
//
return db
./*[a]*/SetSpCommand/*[/a]*/("Person_SelectByKey", /*[a]*/id/*[/a]*/)
.ExecuteObject<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 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 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();
}
}
static void Main(string[] args)
{
using (var db = new DbManager())
{
db.SetSpCommand(""); // ok
db.SetInsertCommand(""); // ok
db.SetUpdateCommand(""); // ok
db.Prepare(); // ok
db.ExecuteNonQuery(); // ok
db.SetCommand(""); // ERROR:
// error CS0584: Internal compiler error: Object reference not set to an instance of an object
// error CS0201: Only assignment, call, increment, decrement, and new object expressions can be used as a statement
}
}
public void ReturnValueTest()
{
using (DbManager db = new DbManager())
{
/*
* CREATE Function Scalar_ReturnParameter()
* RETURNS int
* AS
* BEGIN
* RETURN 12345
* END
*/
int n = db
.SetSpCommand("Scalar_ReturnParameter")
./*[a]*/ExecuteScalar/*[/a]*/<int>(/*[a]*/ScalarSourceType.ReturnValue/*[/a]*/);
Assert.AreEqual(12345, n);
}
}
public void TestFailResultSet1()
{
MapResultSet[] sets = new MapResultSet[2];
sets[0] = new MapResultSet(typeof(Master));
sets[1] = new MapResultSet(typeof(Slave));
sets[0].AddRelation(sets[1], "MasterID", "ID", "Slaves");
using (DbManager db = new DbManager())
{
db
#if ORACLE
.SetSpCommand("ResultSetTest")
#else
.SetCommand(SqlResultSet)
#endif
.ExecuteResultSet(sets);
}
}
public void OutputParameterAsReturnValueTest()
{
using (DbManager db = new DbManager())
{
/*
* CREATE Procedure Scalar_OutputParameter
* @outputInt int = 0 output,
* @outputString varchar(50) = '' output
* AS
* BEGIN
* SET @outputInt = 12345
* SET @outputString = '54321'
* END
*/
string returnValue = db
.SetSpCommand("Scalar_OutputParameter")
./*[a]*/ExecuteScalar/*[/a]*/<string>(/*[a]*/ScalarSourceType.OutputParameter/*[/a]*/, /*[a]*/"outputString"/*[/a]*/);
Assert.AreEqual("54321", returnValue);
}
}
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 void TestResultSet()
{
MapResultSet[] sets = new MapResultSet[2];
sets[0] = new MapResultSet(typeof(Master));
sets[1] = new MapResultSet(typeof(Slave));
sets[0].AddRelation(sets[1], "MasterID", "MasterID", "Slaves");
using (DbManager db = new DbManager())
{
db
#if ORACLE
.SetSpCommand("ResultSetTest")
#else
.SetCommand(SqlResultSet)
#endif
.ExecuteResultSet(sets);
}
Assert.AreEqual(7, ((Slave)(((Master)sets[0].List[0]).Slaves[1])).ID);
}
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 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 ParameterSymbolMayBeOmitedForSpCommand()
{
// I am not sure this is a good thing though
// Maybe we need to be more strict on syntax and on the library users
// and do not allow different syntax (omiting parameter symbol)?
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);
Assert.AreEqual(person, person2);
var oldPrefix = MySqlDataProvider.SprocParameterPrefix;
MySqlDataProvider.SprocParameterPrefix = "_";
try
{
var person3 = db.SetSpCommand("GetPersonById", db.Parameter("ID", 1)).ExecuteObject<Person>();
Assert.AreEqual(person, person3);
}
finally
{
MySqlDataProvider.SprocParameterPrefix = oldPrefix;
}
}
}
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 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 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 MapReturnValue()
{
ReturnParameter e = new ReturnParameter();
using (DbManager db = new DbManager())
{
db
.SetSpCommand("Scalar_ReturnParameter")
.ExecuteNonQuery("Value", e);
}
Assert.AreEqual(12345, e.Value);
}
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 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();
}
}
private string GenerateAccountCode()
{
string sResult = string.Empty;
using (DbManager db = new DbManager())
{
db.SetSpCommand("Get_AccountSequence", db.Parameter("@GUID_NUMBER", "TEST")).ExecuteNonQuery();
long lData = Convert.ToInt32(db.Parameter("@RETURN_VALUE").Value);
sResult = string.Format("ACCT-{0:00000}", lData);
}
return sResult;
}
public void PrefixIsAddedWhenRetrievingParameterFromDbManager()
{
var oldPrefix = MySqlDataProvider.SprocParameterPrefix;
MySqlDataProvider.SprocParameterPrefix = "_";
try
{
using (var db = new DbManager(ProviderName.MySql))
{
db.SetSpCommand("GetPersonById", db.Parameter("?ID", 1)).Prepare();
foreach (var personID in new[] { 1, 2 })
{
// prefix is not specified but it will be added internally before retrieving parameter from
// command parameters
db.Parameter("?ID").Value = personID;
var person = db.ExecuteObject<Person>();
Assert.IsNotNull(person);
Assert.AreEqual(personID, person.ID);
// specifying prefix is also ok
db.Parameter("?_ID").Value = personID;
person = db.ExecuteObject<Person>();
Assert.IsNotNull(person);
Assert.AreEqual(personID, person.ID);
}
}
}
finally
{
MySqlDataProvider.SprocParameterPrefix = oldPrefix;
}
}
