static void TestDataSetAndOQL(object para)
{
string sql = "select top 10 * from Table_User";
Table_User user = new Table_User();
user.MapNewTableName("Table_User");
//user.MapNewTableName("Table_User");
OQL qt = new OQL(user);
qt.Select().Where(qt.Condition.IN(user.Name, new object[] { "a", "b", "c" })).OrderBy(user.Name);
//qt.Select().Where(cmp => cmp.Comparer(user.Name, "in", new string[] { "a", "b", "c" }));
//OQL qt = OQL.From(user).Select().END;
//qt.TopCount = 10;
qt.Limit(10, 10);
qt.PageWithAllRecordCount = 0;
AdoHelper db = MyDB.GetDBHelper();
//测试下面2种方式对连接数量的影响，经测试，发现 MyDB.Instance 跟 MyDB.GetDBHelper() 没有区别。
//List<Table_User> list = EntityQuery<Table_User>.QueryList(qt);
//DataSet ds = MyDB.Instance.ExecuteDataSet(sql);
//测试连接会话，下面db的连接会在using 结束后关闭
using (db.OpenSession())
{
List <Table_User> list = EntityQuery <Table_User> .QueryList(qt, db);
DataSet ds = db.ExecuteDataSet(sql);
}
}
static void TestPocoQuery()
{
string sql = "SELECT top 100000 UID,Sex,Height,Birthday,Name FROM Table_User";
AdoHelper db = MyDB.Instance;
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
Console.Write("query by DataSet,begin...");
sw.Start();
DataSet ds = db.ExecuteDataSet(sql);
sw.Stop();
Console.WriteLine("end,used time(ms){0}", sw.ElapsedMilliseconds);
//PocoQuery query = new PocoQuery();
////预热
//List<UserPoco> list1 = query.QueryList<UserPoco>(db.ExecuteDataReader("SELECT top 1 * FROM Table_User"));
sw.Reset();
Console.Write("query by AdoHelper Poco Query ,begin...");
sw.Start();
//for (int i = 0; i < 100; i++)
//{
List <UserPoco> list = db.QueryList <UserPoco>(sql);
//}
sw.Stop();
Console.WriteLine("end,used time(ms){0}", sw.ElapsedMilliseconds);
sw.Reset();
Console.Write("query by PDF.NET EntityQuery,begin...");
sw.Start();
List <Table_User> list3 = EntityQuery <Table_User> .QueryList(db.ExecuteDataReader(sql));
sw.Stop();
Console.WriteLine("end,used time(ms){0}", sw.ElapsedMilliseconds);
sw.Reset();
Console.Write("query by PDF.NET AdoHelper (handle),begin...");
sw.Start();
//UID,Sex,Height,Birthday,Name
IList <UserPoco> list4 = db.GetList <UserPoco>(reader =>
{
return(new UserPoco()
{
UID = reader.IsDBNull(0) ? 0 : reader.GetInt32(0),
Sex = reader.IsDBNull(1) ? false : reader.GetBoolean(1),
Height = reader.IsDBNull(2) ? 0 : reader.GetFloat(2),
Birthday = reader.IsDBNull(3) ? default(DateTime) : reader.GetDateTime(3),
Name = reader.IsDBNull(4) ? null : reader.GetString(4)
});
}, sql, null);
sw.Stop();
Console.WriteLine("end,used time(ms){0}", sw.ElapsedMilliseconds);
System.Threading.Thread.Sleep(1000);
}
protected void Button2_Click(object sender, EventArgs e)
{
string sql = @"SELECT `工号`,`姓名`
FROM `雇员表`
Where `职务名称`='ss'
ORDER BY `姓名` asc";
AdoHelper db = MyDB.Instance;
try
{
DataSet ds = db.ExecuteDataSet(sql);
this.lblErrMsg.Text = "test ok.";
}
catch (Exception ex)
{
this.lblErrMsg.Text = "Test Error:" + ex.Message;
}
}
static void Main(string[] args)
{
string sql = @"SELECT `工号`,`姓名`
FROM `雇员表`
Where `职务名称`='ss'
ORDER BY `姓名` asc";
AdoHelper db = MyDB.Instance;
try
{
DataSet ds = db.ExecuteDataSet(sql);
Console.WriteLine("test db access ok.");
}
catch (Exception ex)
{
Console.WriteLine("Test Error:" + ex.Message);
}
Console.Read();
}
///// <summary>
///// 未实现
///// </summary>
//public static void 连表查询InnerJoin()
//{
// SODUser user = new SODUser();
// SODUserRemark userRemark = new SODUserRemark();
// var q = OQL.From(user)
// .InnerJoin(userRemark)
// .On(user.ID, userRemark.UserId)
// .Select(user.ID, user.LogName, userRemark.Remark); //选取指定的字段
// AdoHelper db = MyDB.GetDBHelperByConnectionName("connStr");
// EntityContainer ec = new EntityContainer(q, db);
// ec.Execute(); //可以省略此行调用
// var mapUser1 = ec.Map<SODUser>().ToList();
// var mapGroup1 = ec.Map<SODUserRemark>().ToList();
//}
#endregion ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#region 执行
public static void 执行存储过程()
{
AdoHelper db = MyDB.GetDBHelperByConnectionName("connStr");
var ds1 = db.ExecuteDataSet("[GetSodUser]", CommandType.StoredProcedure, new System.Data.IDataParameter[] { db.GetParameter("@id", 1) });
}
public DataTable Select(int count)
{
return(_dbHelper.ExecuteDataSet(string.Format(SqlString.Select, count)).Tables[0]);
}