// The dictionary key is built from a recordset field value ('@' prefix).
//
Dictionary<int, Person> GetPersonDictionary2()
{
using (DbManager db = new DbManager())
{
return db
.SetCommand("SELECT * FROM Person")
./*[a]*/ExecuteDictionary/*[/a]*/<int, Person>(/*[a]*/"@PersonID"/*[/a]*/);
}
}
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()
{
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 Test()
{
using (DbManager db = new DbManager())
{
List<Person> list = db
.SetCommand(@"
SELECT
ps.*,
d.Taxonomy,
p.Diagnosis,
CASE
WHEN d.PersonID IS NOT NULL THEN 'D'
WHEN p.PersonID IS NOT NULL THEN 'P'
END as PersonType
FROM
Person ps
LEFT JOIN Doctor d ON d.PersonID = ps.PersonID
LEFT JOIN Patient p ON p.PersonID = ps.PersonID
ORDER BY
ps.PersonID")
.ExecuteList<Person>();
Assert.AreEqual(list[0].GetType(), /*[a]*/typeof(Doctor)/*[/a]*/);
Assert.AreEqual(list[1].GetType(), /*[a]*/typeof(Patient)/*[/a]*/);
if (list.Count > 2)
Assert.AreEqual(list[2].GetType(), typeof(Person));
}
}
public void Test1()
{
List<Parent> parents = new List<Parent>();
MapResultSet[] sets = new MapResultSet[2];
sets[0] = new MapResultSet(typeof(Parent), parents);
sets[1] = new MapResultSet(typeof(Child));
sets[0].AddRelation(sets[1], "ParentID", "ParentID", "Children");
sets[1].AddRelation(sets[0], "ParentID", "ParentID", "Parent");
using (DbManager db = new DbManager())
{
db
.SetCommand(_parentChildquery)
.ExecuteResultSet(sets);
}
foreach (Parent p in parents)
{
Assert.That(p.IsDirty == false);
foreach (Child c in p.Children)
Assert.That(c.IsDirty == false);
}
}
public void DeleteByMemoID(int MemoID)
{
using (DbManager dbm = new DbManager())
{
dbm.SetCommand(string.Format("DELETE FROM GENMEMODTL WHERE GENMEMOID = {0}", MemoID)).ExecuteNonQuery();
}
}
public void Test()
{
using (DbManager db = new DbManager())
{
List<SimpleObject> list = new List<SimpleObject>();
db
#if MSSQL || SQLCE
.SetCommand(@"
SELECT 0 as [Key], 'value0' as Value UNION
SELECT 1 as [Key], 'value1' as Value UNION
SELECT 2 as [Key], 'value2' as Value")
#else // ORACLE || FIREBIRD || ACCESS
.SetCommand(@"
SELECT 0 as ""Key"", 'value0' as ""Value"" FROM Dual UNION
SELECT 1 as ""Key"", 'value1' as ""Value"" FROM Dual UNION
SELECT 2 as ""Key"", 'value2' as ""Value"" FROM Dual")
#endif
.ExecuteList<SimpleObject>(list);
Assert.IsTrue(list.Count > 0);
}
}
protected override void TearDown()
{
using (var dbManager = new DbManager("PerformanceTest"))
dbManager
.SetCommand("TRUNCATE TABLE Simplests")
.ExecuteNonQuery();
}
public void Test()
{
const string path = @"..\..\..\..\Data\Create Scripts\SqlCe.sql";
using (DbManager db = new DbManager())
{
string cmd = string.Empty;
string term = "GO";
foreach (string s in File.ReadAllLines(path))
{
string line = s.TrimEnd();
if (!line.EndsWith(term))
{
cmd += line + Environment.NewLine;
continue;
}
line = line.Substring(0, line.Length - term.Length).Trim();
Console.WriteLine("Executing script:");
Console.WriteLine(cmd + line);
db
.SetCommand(cmd + line)
.ExecuteNonQuery()
;
Console.WriteLine("Succeeded.");
cmd = string.Empty;
}
}
}
public void DeleteByMemoNo(string MemoNo)
{
using (DbManager dbm = new DbManager())
{
dbm.SetCommand(string.Format("DELETE FROM PRICEPOINT WHERE MEMONO = '{0}'", MemoNo)).ExecuteNonQuery();
}
}
// Select a person list.
//
public IList<Person> GetPersonList()
{
using (DbManager db = new DbManager())
{
return db
./*[a]*/SetCommand/*[/a]*/("SELECT * FROM Person")
.ExecuteList<Person>();
}
}
IList<Person> GetPersonListSqlText()
{
using (DbManager db = new DbManager())
{
return db
.SetCommand("SELECT * FROM Person")
./*[a]*/ExecuteList<Person>()/*[/a]*/;
}
}
List<string> GetNameList2()
{
using (DbManager db = new DbManager())
{
return db
.SetCommand("SELECT * FROM Person")
./*[a]*/ExecuteScalarList/*[/a]*/<string>(/*[a]*/"FirstName"/*[/a]*/);
}
}
private void button4_Click(object sender, EventArgs e)
{
string sql = textBox1.Text.TrimEnd();
if (sql.Length == 0) return;
;
DbManager mgr = new DbManager();
mgr.SetCommand(sql).ExecuteNonQuery();
MessageBox.Show("表创建完成");
}
Dictionary<int, string> GetNameDictionary()
{
using (DbManager db = new DbManager())
{
return db
.SetCommand("SELECT * FROM Person")
./*[a]*/ExecuteScalarDictionary/*[/a]*/<int, string>(/*[a]*/"PersonID"/*[/a]*/, /*[a]*/"FirstName"/*[/a]*/);
}
}
// Complex dictionary key.
//
Dictionary</*[a]*/CompoundValue/*[/a]*/, Person> GetPersonDictionary3()
{
using (DbManager db = new DbManager())
{
return db
.SetCommand("SELECT * FROM Person")
./*[a]*/ExecuteDictionary/*[/a]*/<Person>(new /*[a]*/MapIndex("FirstName", "LastName")/*[/a]*/);
}
}
public void Delete()
{
using (DbManager db = new DbManager())
{
db
.SetCommand("DELETE FROM Person WHERE PersonID = @id",
db.Parameter("@id", _id))
.ExecuteNonQuery();
}
}
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]*/;
}
}
Person GetPersonByID(int id)
{
using (DbManager db = new DbManager())
{
return db
.SetCommand("SELECT * FROM Person WHERE PersonID = @id",
db.Parameter("@id", id))
.ExecuteObject<Person>();
}
}
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>();
}
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 DateTime GetServerDate()
{
DateTime ServerDate = DateTime.Now;
string sResult = string.Empty;
using (DbManager db = new DbManager())
{
ServerDate = Convert.ToDateTime(db.SetCommand("select getdate() as CurrentDate").ExecuteScalar());
}
return ServerDate;
}
// 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>();
}
}
public void Test()
{
using (DbManager db = new DbManager())
{
DataTable dt = db
.SetCommand("SELECT * FROM Person")
./*[a]*/ExecuteDataTable/*[/a]*/();
Assert.AreNotEqual(0, dt.Rows.Count);
}
}
public void ExceptionTest()
{
using (DbManager db = new DbManager())
{
db.OperationException += HandleOperationException;
db
.SetCommand("SELECT * FROM NoSuchTableEverExist")
.ExecuteDataSet();
}
}
public List<PatientHistory> FindAll()
{
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")
.ExecuteList<PatientHistory>();
}
}
public void ScalarDictionaryTest3()
{
using (var db = new DbManager())
{
var table = db
.SetCommand("SELECT * FROM Person")
.ExecuteScalarDictionary(0, typeof(int), 1, typeof(string));
Assert.IsNotNull(table);
Assert.IsTrue(table.Count > 0);
}
}
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);
}
}
