public static void TestUpdate()
{
using (SmartORMClient _db = new SmartORMClient(connStr))
{
bool result = _db.Update<Student>(new { UserName = "******" }, o => o.ID == 1);
Console.WriteLine(result.ToString());
}
}
public static void TestDelete()
{
using (SmartORMClient _db = new SmartORMClient(connStr))
{
bool result = _db.Delete<Student>(o => o.ID == 2);
Console.WriteLine(result.ToString());
}
}
public static void TestAdd()
{
using (SmartORMClient _db = new SmartORMClient(connStr))
{
object result = _db.Insert<Student>(new Student() { UserName = "******", UserPassword = "******", UserEmail = "*****@*****.**" });
Console.WriteLine(result.ToString());
}
}
protected void Page_Load(object sender, EventArgs e)
{
string connStr = "Server=.;uid=sa;pwd=landa;database=ORMTest";
using (SmartORMClient db = new SmartORMClient(connStr))
{
//db.ClassGenerating.CreateClassFiles(db, Server.MapPath("~/Models"), "Models");
//object result = db.Insert<Student>(new Student() { Name = "张三", Sex = "男", ClassId = "09461", StudentNo = "0946101" });
List<Student> result = db.Queryable<Student>().Where(c => c.Id > 1).ToList(); ;
}
}
/// <summary>
/// 创建SQL实体文件
/// </summary>
public void CreateClassFiles(SmartORMClient db, string fileDirectory, string nameSpace = null)
{
//查询表字段结构
string queryTablesDetail = @"select
[TableName]=c.Name,
[TableDesc]=isnull(f.[value],''),
[ColumnName]=a.Name,
[ColumnNum]=a.Column_id,
[Identity]=case when is_identity=1 then '√' else '' end,
[PK]=case when exists(select 1 from sys.objects where parent_object_id=a.object_id and type=N'PK' and name in
(select Name from sys.indexes where index_id in
(select indid from sysindexkeys where colid=a.column_id)))
then '√' else '' end,
[Type]=b.Name,
[ByteSize]=case when a.[max_length]=-1 and b.Name!='xml' then 'max/2G'
when b.Name='xml' then ' 2^31-1 Bytes/2G'
else rtrim(a.[max_length]) end,
[Length]=ColumnProperty(a.object_id,a.Name,'Precision'),
[IsNull]=case when a.is_nullable=1 then '√' else '' end,
[ColumnDesc]=isnull(e.[value],''),
[DefaultVal]=isnull(d.text,'')
from
sys.columns a
left join
sys.types b on a.user_type_id=b.user_type_id
inner join
sys.objects c on a.object_id=c.object_id and c.Type='U'
left join
syscomments d on a.default_object_id=d.ID
left join
sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1
left join
sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1";
var tables = db.GetDataTable(@"select
ROW_NUMBER() OVER (ORDER BY a.object_id) AS No,
a.name AS tableName,
isnull(g.[value],'-') AS tableDesc
from
sys.tables a left join sys.extended_properties g
on (a.object_id = g.major_id AND g.minor_id = 0)");
var columns = db.GetDataTable(queryTablesDetail);
if (tables != null && tables.Rows.Count > 0)
{
foreach (DataRow dr in tables.Rows)
{
string tableName = dr["tableName"].ToString();
string tableDesc = dr["tableDesc"].ToString();
var currentTable = db.GetDataTable(string.Format("select top 1 * from {0}", tableName));
var classCode = DataTableToClass(currentTable, tableName, tableDesc, nameSpace, columns.Select("TableName = '" + tableName + "'"));
FileHelper.WriteText(fileDirectory.TrimEnd('\\') + "\\" + tableName + ".cs", classCode);
}
}
}
public static void TestGetList()
{
using (SmartORMClient _db = new SmartORMClient(connStr))
{
//List<Student> list = _db.Query<Student>("select * from Student", new { UserName = "******" }).ToList();
List<Student> list = _db.Queryable<Student>().Where(c => c.ID != 3).OrderBy("UserName").OrderByDescing("UserEmail").PageIndex(1).PageSize(10).ToList();
foreach (var item in list)
{
Console.WriteLine(item.UserName + "\t" + item.UserEmail);
}
}
}
/// <summary>
/// 创建SQL实体文件,指定表名
/// </summary>
public void CreateClassFilesByTableNames(SmartORMClient db, string fileDirectory, string nameSpace, params string[] tableNames)
{
//var tables = db.GetDataTable("select name from sysobjects where xtype='U'");
var tables = db.GetDataTable(@"select
ROW_NUMBER() OVER (ORDER BY a.object_id) AS No,
a.name AS tableName,
isnull(g.[value],'-') AS tableDesc
from
sys.tables a left join sys.extended_properties g
on (a.object_id = g.major_id AND g.minor_id = 0)");
if (tables != null && tables.Rows.Count > 0)
{
foreach (DataRow dr in tables.Rows)
{
string tableName = dr["tableName"].ToString().ToLower();
if (tableNames.Any(it => it.ToLower() == tableName))
{
var currentTable = db.GetDataTable(string.Format("select top 1 * from {0}", tableName));
var classCode = DataTableToClass(currentTable, tableName, nameSpace);
FileHelper.WriteText(fileDirectory.TrimEnd('\\') + "\\" + tableName + ".cs", classCode);
}
}
}
}
public static void TestUpdate2()
{
using (SmartORMClient _db = new SmartORMClient(connStr))
{
List<string> exceptionList = new List<string>() { "zhifu2", "zhifu3", "zhifu4" };
//_db.Update<MachineGenModel>(new { MachineStatus = 2 }, o => exceptionList.Contains(o.MachineName) && o.MachineStatus == 0); //正常变为异常
//_db.Update<MachineGenModel>(new { MachineStatus = 1 }, o => o.MachineName.Contains("zhifu1"));
List<string> typelist = new List<string>() { "6", "7", "8" };
DateTime dt = DateTime.Now.AddHours(-2);
_db.Delete<MachineLogGenModel>(c => typelist.Contains(c.LogType) && c.CreatedTime < dt); //清空2小时前的心跳包日志
}
}
public static void TestSingle()
{
using (SmartORMClient _db = new SmartORMClient(connStr))
{
UserGenModel user = _db.Queryable<UserGenModel>().Where(c => c.UserName == "test").Single();
}
}
public static void TestQuery()
{
using (SmartORMClient _db = new SmartORMClient(connStr))
{
List<StudentModel> list = _db.Query<StudentModel>("select A.*,B.Name from student as A left join Class as B on A.ClassID = B.ID");
foreach (var item in list)
{
Console.WriteLine(item.UserName + " " + item.Name);
}
}
}
public static void TestGetList()
{
using (SmartORMClient _db = new SmartORMClient(connStr))
{
/*
List<MachineGenModel> list = _db.Queryable<MachineGenModel>().Where(c=>c.MachineName.Contains("zhifu1")).ToList();
foreach (var item in list)
{
Console.WriteLine(item.MachineName + ":" + (item.CreatedTime != null ? item.CreatedTime.ToString("yyyy-MM-dd HH:mm:ss") : ""));
}
*/
object result = _db.GetScalar("select count(*) from (SELECT A.MachineName,A.MachineStatus,A.IsWatch,B.LastLogTime FROM sys_machine AS A LEFT JOIN (SELECT MAX(CreatedTime) AS LastLogTime,PayerName FROM machine_log GROUP BY PayerName) AS B ON A.MachineName = B.PayerName ) X where IsWatch = TRUE and ( MachineStatus=0 or ( MachineStatus=2 and LastLogTime > '" + DateTime.Now.AddMinutes(-3).ToString("yyyy-MM-dd HH:mm:ss") + "'))", null);
Console.WriteLine(Convert.ToInt32(result));
//List<Student> list = _db.Query<Student>("select * from Student", new { UserName = "******" }).ToList();
/*
List<Student> list = _db.Queryable<Student>().Where(c => c.ID != 2).OrderBy("UserName").OrderByDescing("UserEmail").PageIndex(2).PageSize(5).ToList();
foreach (var item in list)
{
Console.WriteLine(item.UserName + "\t" + item.UserEmail);
}
* */
//List<Student> list = _db.Queryable<Student>().Where(c => c.ID == 2).ToList();
//object result = _db.Insert<Student>(new Student() { UserName = "******", UserPassword = "******", UserEmail = "*****@*****.**" });
//Console.WriteLine(result.ToString());
//_db.Queryable<Student>()
/*
string startTime = DateTime.Now.AddMinutes(-2).ToString("yyyy-MM-dd HH:mm:ss");//起始时间
string endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");//结束时间
string sqlFind = "select A.MachineName,B.Counts from sys_machine as A left join (select count(*) as Counts ,PayerName from machine_log where CreatedTime > '" + startTime + "' and CreatedTime < '" + endTime + "' and LogType in (6,7,8) GROUP BY PayerName order by PayerName ) as B on A.MachineName = B.PayerName where A.MachineStatus <> 1 and A.IsWatch = 1"; // 发送心跳包、支付成功、支付验证
List<MachineActiveModel> list = _db.Query<MachineActiveModel>(sqlFind);
foreach (var item in list)
{
Console.WriteLine(item.MachineName + ":" + item.Counts);
}
*/
}
}
public static void TestAdd()
{
/*
using (SmartORMClient _db = new SmartORMClient(connStr))
{
object result = _db.Insert<Student>(new Student() { UserName = "******", UserPassword = "******", UserEmail = "*****@*****.**" });
Console.WriteLine(result.ToString());
}
* */
using (SmartORMClient _db = new SmartORMClient(connStr))
{
object result = _db.Insert<UserGenModel>(new UserGenModel() { LoginName = "abc", LoginPwd = "123456", UserName = "******", UserPhone = "111111", UserEmail = "", UserStatus = 1 });
Console.WriteLine(result.ToString());
}
}
/// <summary>
/// 根据表名获取实体类的字符串
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="className"></param>
/// <returns></returns>
public string TableNameToClass(SmartORMClient db, string tableName, string classDesc)
{
var dt = db.GetDataTable(string.Format("select top 1 * from {0}", tableName));
var reval = DataTableToClass(dt, tableName, classDesc);
return reval;
}
/// <summary>
/// 根据SQL语句获取实体类的字符串
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="className"></param>
/// <returns></returns>
public string SqlToClass(SmartORMClient db, string sql, string className, string classDesc)
{
using (SqlConnection conn = new SqlConnection(db.ConnectionString))
{
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandText = sql;
DataTable dt = new DataTable();
SqlDataAdapter sad = new SqlDataAdapter(command);
sad.Fill(dt);
var reval = DataTableToClass(dt, className, classDesc);
return reval;
}
}
