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); }