public List <Fields> GetFields(string serverId, string dbName, string tableName)
{
List <Model.Fields> fieldsList = new List <Model.Fields>();
using (MySqlConnection conn = new MySqlConnection(ServersHelper.GetConnectionString(serverId, dbName)))
{
conn.Open();
using (MySqlCommand cmd = new MySqlCommand(string.Format("show full fields from {0}", tableName), conn))
{
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
fieldsList.Add(new Model.Fields()
{
Name = dr[0].ToString(),
Type = GetFieldType(dr[1].ToString()),
Length = GetFieldLength(dr[1].ToString()),
IsNull = "YES" == dr[3].ToString().ToUpper(),
IsPrimaryKey = "PRI" == dr[4].ToString().ToUpper(),
Default = dr[5].ToString(),
IsIdentity = "auto_increment" == dr[6].ToString().ToLower(),
NetType = GetFieldType(GetFieldType(dr[1].ToString()), "YES" == dr[3].ToString().ToUpper()),
SqlType = GetFieldSqlType(GetFieldType(dr[1].ToString())),
Note = dr[8].ToString()
});
}
dr.Close();
dr.Dispose();
}
}
return(fieldsList);
}
/// <summary>
/// 判断一个表的某列是否为自增列
/// </summary>
/// <param name="serverId"></param>
/// <param name="dbName"></param>
/// <param name="tableName"></param>
/// <param name="fieldName"></param>
/// <returns></returns>
private bool IsIdentity(string serverId, string dbName, string tableName, string fieldName)
{
using (SqlConnection conn = new SqlConnection(ServersHelper.GetConnectionString(serverId, dbName)))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "select COLUMNPROPERTY(object_id('" + tableName + "'),'" + fieldName + "','IsIdentity')";
return("1" == cmd.ExecuteScalar().ToString());
}
}
}
/// <summary>
/// 取一个字段的备注说明
/// </summary>
/// <param name="serverId"></param>
/// <param name="dbName"></param>
/// <param name="tableName"></param>
/// <param name="fieldName"></param>
/// <returns></returns>
private string GetFieldNote(string serverId, string dbName, string tableName, string fieldName)
{
using (SqlConnection conn = new SqlConnection(ServersHelper.GetConnectionString(serverId, dbName)))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = @"select value from sys.extended_properties a
left join sys.syscolumns b on a.major_id=b.id and a.minor_id=b.colid
where a.name='MS_Description' and object_id('" + tableName + "')=a.major_id and b.name='" + fieldName + "'";
object obj = cmd.ExecuteScalar();
return(obj == null ? string.Empty : obj.ToString());
}
}
}
public List <string> GetDatabaseList(string serverId)
{
List <string> dbList = new List <string>();
using (SqlConnection conn = new SqlConnection(ServersHelper.GetConnectionString(serverId)))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("select name from sysdatabases", conn))
{
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
dbList.Add(dr.GetString(0));
}
dr.Close();
dr.Dispose();
}
}
return(dbList);
}
public bool TestDatabaseConnnection(string serverId, out string errMessage)
{
SqlConnection conn = new SqlConnection(ServersHelper.GetConnectionString(serverId));
try
{
conn.Open();
errMessage = string.Empty;
return(true);
}
catch (SqlException err)
{
errMessage = err.Message;
return(false);
}
finally
{
conn.Dispose();
}
}
/// <summary>
/// 判断一个表的某列是否为主键
/// </summary>
/// <param name="serverId"></param>
/// <param name="dbName"></param>
/// <param name="tableName"></param>
/// <param name="fieldName"></param>
/// <returns></returns>
private bool IsPrimaryKey(string serverId, string dbName, string tableName, string fieldName)
{
using (SqlConnection conn = new SqlConnection(ServersHelper.GetConnectionString(serverId, dbName)))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "sp_pkeys";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@table_name", tableName));
using (SqlDataAdapter dap = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
dap.Fill(dt);
return(dt.Select("COLUMN_NAME='" + fieldName + "'").Length > 0);
}
}
}
}
public List <Fields> GetFields(string serverId, string dbName, string tableName)
{
List <Fields> fieldsList = new List <Model.Fields>();
Servers server = ServersHelper.GetServers(serverId);
if (server == null)
{
return(fieldsList);
}
using (SqlConnection conn = new SqlConnection(ServersHelper.GetConnectionString(serverId, dbName)))
{
conn.Open();
string sql = string.Format(@"select a.name as f_name,b.name as t_name,[length],a.isnullable as is_null from
sys.syscolumns a inner join sys.types b on b.user_type_id=a.xtype where object_id('{0}')=id order by a.colid", tableName);
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
fieldsList.Add(new Fields()
{
Name = dr.GetString(0),
Type = dr.GetString(1),
Length = GetFieldLength(dr.GetString(1), dr.GetInt16(2)),
IsNull = 1 == dr.GetInt32(3),
IsPrimaryKey = IsPrimaryKey(serverId, dbName, tableName, dr.GetString(0)),
IsIdentity = IsIdentity(serverId, dbName, tableName, dr.GetString(0)),
NetType = GetFieldType(dr.GetString(1), 1 == dr.GetInt32(3)),
SqlType = GetFieldSqlType(dr.GetString(1)),
Note = GetFieldNote(serverId, dbName, tableName, dr.GetString(0))
});
}
dr.Close();
dr.Dispose();
}
}
return(fieldsList);
}
public List <Tables> GetTables(string serverId, string dbName)
{
List <Model.Tables> tblList = new List <Model.Tables>();
using (SqlConnection conn = new SqlConnection(ServersHelper.GetConnectionString(serverId, dbName)))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("SELECT name FROM sysobjects WHERE xtype='u' order by name", conn))
{
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
tblList.Add(new Model.Tables()
{
Name = dr.GetString(0)
});
}
dr.Close();
dr.Dispose();
}
}
return(tblList);
}
public List <Tables> GetTables(string serverId, string dbName)
{
List <Model.Tables> tblList = new List <Model.Tables>();
using (MySqlConnection conn = new MySqlConnection(ServersHelper.GetConnectionString(serverId, dbName)))
{
conn.Open();
using (MySqlCommand cmd = new MySqlCommand(string.Format("show full tables from {0} where table_type!='VIEW'", dbName), conn))
{
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
tblList.Add(new Model.Tables()
{
Name = dr.GetString(0)
});
}
dr.Close();
dr.Dispose();
}
}
return(tblList);
}