private void Form_Database_Load(object sender, EventArgs e)
{
Form.CheckForIllegalCrossThreadCalls = false;
var serverList = ServersConfigClass.Instance.GetList();
foreach (var server in serverList)
{
Servers servers = new Servers();
servers.DatabaseName = server.Database;
servers.Id = server.Id;
servers.Name = server.Server;
servers.Pwd = server.Pwd;
servers.Port = server.Port.ToInt();
servers.Server = server.Server;
servers.Uid = server.Uid;
servers.Type = (DatabaseType)Enum.Parse(typeof(DatabaseType), server.Type);
ServersHelper.AddServers(servers);
TreeNode rootNode = new TreeNode();
rootNode.Name = server.Server;
rootNode.Text = string.Format("{0}({1}{2})",
server.Server,
server.Type.ToString(),
string.IsNullOrWhiteSpace(server.Uid) ? "" : string.Format("-{0}", server.Uid));
rootNode.ImageIndex = 0;
rootNode.SelectedImageIndex = 0;
rootNode.Tag = new Model.TreeNodeTag {
Type = TreeNodeType.ServerNode, Tag = servers
};
treeViewLeft.Nodes.Add(rootNode);
}
}
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);
}
public async Task GetServersListFromApiImpl()
{
var servers = await ServersHelper.GetServersListFromApi(_client);
var unitOfWork = UnitOfWorkHelper.GetUnitOfWork();
unitOfWork.Servers.RemoveRange(unitOfWork.Servers.GetAll());
unitOfWork.Servers.AddRange(servers);
unitOfWork.Save();
}
/// <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 void GetServersListFromDBImpl()
{
var servers = ServersHelper.GetServersListFromDB();
var orderedServers = servers.OrderByDescending(x => x.Distance).ToList();
Servers = new ObservableCollection <Servers>();
var id = 1;
foreach (var server in orderedServers)
{
server.id = id;
Servers.Add(server);
id++;
}
}
private void btnTest_Click(object sender, EventArgs e)
{
this.btnTest.Enabled = false;
var server = this.server.Text.Trim();
var uid = this.uid.Text.Trim();
var pwd = this.pwd.Text.Trim();
var model = new Servers
{
Id = CommonHelper.NewGuid,
Name = server,
Type = DatabaseType.SqlServer,
Server = server,
Uid = uid,
Pwd = pwd
};
ServersHelper.AddServers(model);
//var model = new ConfigServers
//{
// Id = CommonHelper.NewGuid,
// Name = string.Format("{0}({1})", server, DatabaseType.SqlServer.ToString()),
// Type = DatabaseType.SqlServer.ToString(),
// Server = server,
// Uid = uid,
// Pwd = pwd,
//};
//ServersConfigClass.Instance.Add(model);
string err;
var b = new BLL_Database(DatabaseType.SqlServer).TestDatabaseConnnection(model.Id, out err);
if (!b)
{
MessageBox.Show(err, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("连接成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
this.btnTest.Enabled = true;
}
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);
}
