public static void SqlQueryTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
object ret = null;
var users = context.SqlQuery <User>("select Id as id,Name as name,'asdsd' as Name,ByteArray from Users where Name=@name", DbParam.Create("@name", "lu1"));
var list = users.ToList();
ret = context.SqlQuery <int?>("select Id from Users").ToList();
ConsoleHelper.WriteLineAndReadKey();
}
static void ChloeSqlQueryTest(int takeCount)
{
using (MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString))
{
var list = context.SqlQuery <TestEntity>(string.Format("select top {0} * from TestEntity", takeCount.ToString())).ToList();
}
}
static void ChloeSqlQueryTest(int takeCount, int loops)
{
using (MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString))
{
for (int i = 0; i < loops; i++)
{
int id = 0;
var list = context.SqlQuery <TestEntity>(string.Format("select top {0} * from TestEntity where Id>@Id", takeCount.ToString()), DbParam.Create("@Id", id)).ToList();
}
}
}
static async void SqlServerTableDefineToHTML()
{
var htmlTask = GetHtmlDocOriginAsync();
var connectString = appConfiguration.GetConnectionString("mssql");
var dbcontext = new MsSqlContext(new DefaultDbConnectionFactory(connectString));
//数据库名
var dbname = dbcontext.SqlQuery <string>("select db_name()").First();
var html = await htmlTask;
}
public static void ExecuteCommandText()
{
List <User> users = context.SqlQuery <User>("select * from Users where Age > @age", DbParam.Create("@age", 12)).ToList();
int rowsAffected = context.Session.ExecuteNonQuery("update Users set name=@name where Id = 1", DbParam.Create("@name", "Chloe"));
/*
* 执行存储过程:
* User user = context.SqlQuery<User>("Proc_GetUser", CommandType.StoredProcedure, DbParam.Create("@id", 1)).FirstOrDefault();
* rowsAffected = context.Session.ExecuteNonQuery("Proc_UpdateUserName", CommandType.StoredProcedure, DbParam.Create("@name", "Chloe"));
*/
ConsoleHelper.WriteLineAndReadKey();
}
public static void SqlQueryTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
object ret = null;
//Dictionary<string, object> dic = new Dictionary<string, object>();
//dic.Add("@p", "shuxin");
var users = context.SqlQuery <User>("select Id as id,Name as name,'asdsd' as Name,ByteArray from Users where Name=@name", DbParam.Create("@name", "lu11"));
try
{
var list = users.ToList();
ret = context.SqlQuery <int?>("select Id from Users").ToList();
}
catch
{
ConsoleHelper.WriteLineAndReadKey();
}
ConsoleHelper.WriteLineAndReadKey();
}
static void ProcTest()
{
object ret = null;
DbParam id1 = new DbParam("id1", 12);
DbParam name1 = new DbParam("name1", "shuxin");
DbParam age = new DbParam("age", null, typeof(int))
{
Direction = ParamDirection.Output
};
DbParam name2 = new DbParam("name2", "so", typeof(string))
{
Direction = ParamDirection.InputOutput
};
ret = context.SqlQuery <User>("Proc_Test", CommandType.StoredProcedure, id1, name1, age, name2).ToList();
//ret = context.Session.ExecuteScalar("Proc_Test", CommandType.StoredProcedure, id1, name1, age, name2);
}
public static void SqlQueryTest()
{
MsSqlContext context = new MsSqlContext(DbHelper.ConnectionString);
object ret = null;
var users = context.SqlQuery<User>("select Id as id,Name as name,'asdsd' as Name,ByteArray from Users where Name=@name", DbParam.Create("@name", "lu1"));
var list = users.ToList();
ret = context.SqlQuery<int?>("select Id from Users").ToList();
ConsoleHelper.WriteLineAndReadKey();
}
static async void SqlServerTableDefineToHTML(string prefix)
{
var htmlTask = GetHtmlDocOriginAsync();
#region 查询
var connectString = appConfiguration.GetConnectionString("mssql");
var dbcontext = new MsSqlContext(new DefaultDbConnectionFactory(connectString));
//数据库名
var dbname = dbcontext.SqlQuery <string>("select db_name()").First();
//所有表名及描述
var tables_desc = dbcontext.SqlQuery <KeyValuePair <string, string> >($@"select tbs.name [Key],ds.value [Value] from {dbname}..sysobjects tbs
left join sys.extended_properties ds on tbs.id=ds.major_id and ds.minor_id=0 where tbs.xtype='U' and tbs.name <> 'sysdiagrams' order by [Key]").ToList();//dbname..可省略
if (string.IsNullOrEmpty(prefix))
{
tables_desc = tables_desc.Where(p => p.Key.StartsWith(prefix)).ToList();
}
//所有表设计
var tables_design = dbcontext.SqlQuery <TableDesign>(@"SELECT obj.name AS 表名,
col.colorder AS 序号,
col.name AS 列名,
ISNULL(ep.[value], '') AS 列说明,
t.name AS 数据类型,
col.length AS 长度MS,
ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数,
CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识,
CASE WHEN EXISTS (SELECT 1
FROM dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = si.name
AND so.xtype = 'PK'
WHERE sc.id = col.id
AND sc.colid = col.colid ) THEN '√'
ELSE '' END AS 主键,
CASE WHEN col.isnullable = 1 THEN '√' ELSE '' END AS 空值,
ISNULL(comm.text, '') AS 默认值
FROM dbo.syscolumns col
LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype
INNER JOIN dbo.sysobjects obj ON col.id = obj.id AND obj.xtype = 'U' AND obj.status >= 0 ------(xtype = 'U'非用户表)
LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id
LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id AND col.colid = ep.minor_id AND ep.name = 'MS_Description'
LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id AND epTwo.minor_id = 0 AND epTwo.name = 'MS_Description'
ORDER BY [表名],[序号]").ToList();
#endregion
#region 拼接字符串
var char13 = "\r\n";
var top_0 = dbname;
var left_1 = string.Join(char13, tables_desc.Select(t => $"<tr><td><a href='#{t.Key}'>{t.Key} {t.Value}</a></td></tr>"));
var right_2 = new StringBuilder();
foreach (var tb in tables_desc)
{
right_2.Append($@"<a name='{tb.Key}'></a>
<table class='det' cellspacing='1' cellpadding='0'>
<thead>
<tr>
<th colspan='10'>{tb.Key} {tb.Value}</th>
</tr>
<tr>
<th width='30'>序号</th>
<th width='140'>列名</th>
<th>列说明</th>
<th width='100'>数据类型</th>
<th width='50'>长度</th>
<th width='30'>小数</th>
<th width='30'>标识</th>
<th width='30'>主键</th>
<th width='30'>空值</th>
<th width='70'>默认值</th>
</tr>
</thead>
<tbody>").Append(char13);
//单个表设计
var tb_designs = tables_design.Where(t => t.表名 == tb.Key).ToList();
foreach (var design in tb_designs)
{
right_2.Append($@"<tr>
<td>{design.序号}</td>
<td>{design.列名}</td>
<td>{design.列说明}</td>
<td>{design.数据类型}</td>
<td>{design.长度MS}</td>
<td>{design.小数}</td>
<td>{design.标识}</td>
<td>{design.主键}</td>
<td>{design.空值}</td>
<td>{design.默认值}</td>
</tr>").Append(char13);
}
right_2.Append("</tbody></table><div class='totop' onclick='javascript:location.href=\"#top\"' href='#top'>TOP</div>");
}
#endregion
var html = await htmlTask;
html = string.Format(html, top_0, left_1, right_2);
File.WriteAllText(top_0 + ".html", html);
}