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