public static T Scalar <T>(DatabaseConnectionOption databaseInfo, string sql, object param = null)
{
using (var conn = DatabaseFactory.Connection(databaseInfo))
{
return(conn.ExecuteScalar <T>(sql, param));
}
}
private void btn_login_Click(object sender, EventArgs e)
{
DatabaseConnectionOption databaseInfo = new DatabaseConnectionOption()
{
DatabaseType = (DatabaseTypeEnum)Enum.Parse(typeof(DatabaseTypeEnum), cb_db_type.SelectedValue.ToString()),
Server = txt_server.Text,
Port = txt_port.Text,
Pwd = txt_pwd.Text,
DefaultDatabase = txt_default_db.Text,
Login = txt_login.Text
};
try
{
//查询所有数据库名称
var dbs = Utils.GetDatabases(databaseInfo);
_buildDbsShow(BuildDbs(dbs));
AppContext.DatabaseInfo = databaseInfo;
this.Close();
MessageBox.Show("链接成功");
}
catch (Exception ex)
{
MessageBox.Show("链接失败。。。");
}
}
public static T Find <T>(DatabaseConnectionOption databaseInfo, string sql, object param = null)
{
using (var conn = DatabaseFactory.Connection(databaseInfo))
{
return(conn.QueryFirstOrDefault <T>(sql, param));
}
}
public static IEnumerable <T> Query <T>(DatabaseConnectionOption databaseInfo, string sql, object param = null)
{
using (var conn = DatabaseFactory.Connection(databaseInfo))
{
return(conn.Query <T>(sql, param));
}
}
public static IEnumerable <string> GetDatabases(DatabaseConnectionOption databaseInfo)
{
switch (databaseInfo.DatabaseType)
{
case DatabaseTypeEnum.Mysql:
return(DapperContext.Query <string>(databaseInfo, "show databases"));
case DatabaseTypeEnum.SqlServer:
return(DapperContext.Query <string>(databaseInfo, " select name from master.dbo.SysDatabases"));
default:
throw new NotSupportedException("未支持");
}
}
/// <summary>
///获取指定表的结构信息
/// </summary>
/// <param name="databaseInfo"></param>
/// <param name="db"></param>
/// <param name="tb"></param>
/// <returns></returns>
public static IEnumerable <TableColumnInfo> GetColumns(DatabaseConnectionOption databaseInfo, string db, string tb)
{
switch (databaseInfo.DatabaseType)
{
case DatabaseTypeEnum.Mysql:
return(DapperContext.Query <TableColumnInfo>(databaseInfo, $"select if(IS_NULLABLE='YES',1,0) IsNullAble,if(column_key='PRI',1,0)IsPrimary,column_type ColumnType,column_default DefaultValue,column_comment ColumnComment,data_type DataType,column_name ColumnName from information_schema.columns where table_schema='{db}' and table_name='{tb}'"));
case DatabaseTypeEnum.SqlServer:
return(DapperContext.Query <TableColumnInfo>(databaseInfo, $"use {db} select syscolumns.name as 'ColumnName',systypes.name as 'DataType',sys.extended_properties.value as 'ColumnComment' from syscolumns inner join sysobjects on syscolumns.id=sysobjects.id inner join systypes on syscolumns.xtype=systypes.xtype left join sys.extended_properties on sys.extended_properties.major_id=syscolumns.id and sys.extended_properties.minor_id=syscolumns.colorder where sysobjects.name='{tb}' and systypes.name<>'sysname' order by sys.extended_properties.minor_id asc"));
default:
throw new NotSupportedException("未支持");
}
}
/// <summary>
/// 获取数据库的表信息
/// </summary>
/// <param name="databaseInfo"></param>
/// <param name="db"></param>
/// <returns></returns>
public static IEnumerable <TableInfo> GetTables(DatabaseConnectionOption databaseInfo, string db)
{
switch (databaseInfo.DatabaseType)
{
case DatabaseTypeEnum.Mysql:
return(DapperContext.Query <TableInfo>(databaseInfo, $"select table_name TableName,table_comment TableComment from information_schema.tables where table_schema='{db}' and table_type='base table'"));
case DatabaseTypeEnum.SqlServer:
return(DapperContext.Query <TableInfo>(databaseInfo, $"use {db} select a.name AS 'TableName', g.[value] AS 'TableComment' from sys.tables a left join sys.extended_properties g on (a.object_id = g.major_id AND g.minor_id = 0)"));
default:
throw new NotSupportedException("未支持");
}
}