public bool ConnectToDataBase(string strConnect) { sqlConnection = new MySqlConnection(strConnect.ToString()); try { sqlConnection.Open(); listTables = new List<string>(); using (DataTable dt = sqlConnection.GetSchema("Tables")) { if (dt != null && dt.Rows.Count > 0) { listTables.Capacity = dt.Rows.Count; foreach (DataRow row in dt.Rows) listTables.Add(row["table_name"].ToString()); } } sqlConnection.Close(); //string query = "select * from sys.tables where type_desc = 'USER_TABLE'"; } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); return false; } return true; }
public void ListDatabases(bool showAll) { var connectionString = Settings.DefaultConnection.GetConnectionString(); using (var connection = new MySqlConnection(connectionString)) { Logger.WriteLine("Databases:"); var databases = connection.GetSchema("Tables"); foreach (DataRow database in databases.Rows) { Logger.WriteLine("Name: {0}", database["database_name"]); } } }
private static DataSet GetProcData(MySqlConnection connection, string spName) { string schema = String.Empty; string name = spName; int dotIndex = spName.IndexOf("."); if (dotIndex != -1) { schema = spName.Substring(0, dotIndex); name = spName.Substring(dotIndex + 1, spName.Length - dotIndex - 1); } string[] restrictions = new string[4]; restrictions[1] = schema.Length > 0 ? schema : connection.CurrentDatabase(); restrictions[2] = name; DataTable procTable = connection.GetSchema("procedures", restrictions); if (procTable.Rows.Count > 1) { throw new MySqlException(Resources.ProcAndFuncSameName); } if (procTable.Rows.Count == 0) { throw new MySqlException(String.Format(Resources.InvalidProcName, name, schema)); } DataSet ds = new DataSet(); ds.Tables.Add(procTable); // we don't use GetSchema here because that would cause another // query of procedures and we don't need that since we already // know the procedure we care about. ISSchemaProvider isp = new ISSchemaProvider(connection); string[] rest = isp.CleanRestrictions(restrictions); try { DataTable parametersTable = isp.GetProcedureParameters(rest, procTable); ds.Tables.Add(parametersTable); } catch (Exception) { } return(ds); }
protected override void LoadChildren( ) { string myConnectionString = ""; using ( MySqlConnection connection = new MySqlConnection(myConnectionString) ) { connection.Open( ); DataTable schema = connection.GetSchema("Columns"); List<string> TableNames = new List<string>( ); //TODO use linq here foreach ( DataRow row in schema.Rows ) { if( row[2].ToString() == table_.TableName) base.Children.Add(new FieldViewModel(new Field(row[3].ToString( )), this)); } } }
public override IList<DataBaseSchema> GetDataBases(System.Data.Common.DbConnectionStringBuilder connectionstr) { IList<DataBaseSchema> list = null; using (MySqlConnection connection = new MySqlConnection(connectionstr.ConnectionString)) { connection.Open(); DataTable databases = connection.GetSchema(SqlClientMetaDataCollectionNames.Databases, connectionstr.DataBase()); if (databases != null && databases.Rows.Count > 0) { list = new List<DataBaseSchema>(); foreach (DataRow database in databases.Rows) { string name = (string)database["database_name"]; DataBaseSchema db = new DataBaseSchema(name, name); list.Add(db); } } } return list; }
protected override bool DbDatabaseExists(DbConnection connection, int? commandTimeout, StoreItemCollection storeItemCollection) { if (connection == null) throw new ArgumentNullException("connection"); MySqlConnection conn = connection as MySqlConnection; if (conn == null) throw new ArgumentException(Resources.ConnectionMustBeOfTypeMySqlConnection, "connection"); MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder(); builder.ConnectionString = conn.ConnectionString; string dbName = builder.Database; builder.Database = "mysql"; using (MySqlConnection c = new MySqlConnection(builder.ConnectionString)) { c.Open(); DataTable table = c.GetSchema("Databases", new string[] { dbName }); if (table != null && table.Rows.Count == 1) return true; return false; } }
public override IList<ColumnSchema> GetColumns(System.Data.Common.DbConnectionStringBuilder connectionstr, string tablename) { IList<ColumnSchema> list = null; using (MySqlConnection connection = new MySqlConnection(connectionstr.ConnectionString)) { connection.Open(); DataTable columns = connection.GetSchema(SqlClientMetaDataCollectionNames.Columns, new string[] { null, null, tablename, null }); if (columns != null && columns.Rows.Count > 0) { list = new List<ColumnSchema>(); DataView dv = columns.DefaultView; dv.Sort = "ORDINAL_POSITION asc"; foreach (DataRowView table in dv) { string name = string.Format("{0}({1})", table["COLUMN_NAME"], table["DATA_TYPE"]); ColumnSchema column = new ColumnSchema(name); list.Add(column); } } } return list; }
public List<table> getTables(string cadconexion, string database) { MySql.Data.MySqlClient.MySqlConnection conexion = null; try { List<table> lista = new List<table>(); conexion = new MySql.Data.MySqlClient.MySqlConnection(cadconexion); miComando = new MySqlCommand(""); miComando.Connection = conexion; conexion.ConnectionString = cadconexion; conexion.Open(); System.Data.DataTable dt = new System.Data.DataTable(); dt = conexion.GetSchema("Tables", new String[] { null, database, null, null }); foreach (System.Data.DataRow rowDatabase in dt.Rows) { table tab = new table(); tab.Name = rowDatabase["table_name"].ToString(); tab.TargetName = tab.Name; lista.Add(tab); } return lista; } catch (Exception ep) { // lo.tratarError(ep, "Error en dbClass.new", ""); return null; } finally { conexion.Close(); } }
private void comMySqlData_DropDown(object sender, EventArgs e) { if (this.comMySqlData.Items.Count != 0) return; MySqlConnection conn = new MySqlConnection(); conn.ConnectionString = GetMySql(); try { conn.Open(); } catch (System.Exception ex) { MessageBox.Show(rm.GetString("Info75") + ex.Message, rm.GetString("MessageboxError"), MessageBoxButtons.OK, MessageBoxIcon.Error); return; } DataTable tb = conn.GetSchema("Databases"); foreach (DataRow r in tb.Rows) { this.comMySqlData.Items.Add(r[1].ToString()); } }
public static bool DatabaseHelperTableExists(string tableName) { using (MySqlConnection connection = new MySqlConnection(ConnectionString.GetWriteConnectionString())) { string[] restrictions = new string[4]; restrictions[2] = tableName; connection.Open(); DataTable table = connection.GetSchema("Tables", restrictions); connection.Close(); if (table != null) { return (table.Rows.Count > 0); } } return false; }
private void FillMySql() { if (this.comTableName.Items.Count != 0) return; MySqlConnection conn = new MySqlConnection(); conn.ConnectionString = this.txtDataSource.Text; try { conn.Open(); } catch (System.Exception ex) { MessageBox.Show(rm.GetString("Error12") + ex.Message, rm.GetString("MessageboxError"), MessageBoxButtons.OK, MessageBoxIcon.Error); return; } DataTable tb = conn.GetSchema("Tables"); foreach (DataRow r in tb.Rows) { this.comTableName.Items.Add(r[2].ToString()); } }
private bool GetMySQLDataBases(ref bool bInDataBase, string DataBaseToFind) { string conxString = m_SQL_Connection.ServerConnectionString; try { using (MySqlConnection sqlConx = new MySqlConnection(conxString)) { sqlConx.Open(); DataTable tblDatabases = sqlConx.GetSchema("Databases"); sqlConx.Close(); bInDataBase = false; this.listBox_DataBaseNames.Items.Clear(); foreach (DataRow row in tblDatabases.Rows) { string DataBaseName; DataBaseName = row["database_name"].ToString(); this.listBox_DataBaseNames.Items.Add(DataBaseName); if (DataBaseToFind != null) { if (DataBaseToFind.Length > 0) { if (DataBaseName.Equals(DataBaseToFind)) { bInDataBase = true; } } } //Console.WriteLine("Database: " + row["database_name"]); } return true; } } catch (Exception ex) { MessageBox.Show(this, lngConn.s_Error_Can_not_get_Databases_on_Server.s + txt_ServerName.Text + "\n" + lngConn.s_Execption.s + " = " + ex.Message + "\"", lngConn.s_Error.s, MessageBoxButtons.OK, MessageBoxIcon.Error); return false; } }
/// <summary> /// Returns the current version of the membership schema /// </summary> private static int GetSchemaVersion(MySqlConnection connection) { string[] restrictions = new string[4]; restrictions[2] = "mysql_Membership"; DataTable dt = connection.GetSchema("Tables", restrictions); if (dt.Rows.Count == 0) return 0; return Convert.ToInt32(dt.Rows[0]["TABLE_COMMENT"]); }
private static DataSet GetProcData(MySqlConnection connection, string spName) { string schema = String.Empty; string name = spName; int dotIndex = spName.IndexOf("."); if (dotIndex != -1) { schema = spName.Substring(0, dotIndex); name = spName.Substring(dotIndex + 1, spName.Length - dotIndex - 1); } string[] restrictions = new string[4]; restrictions[1] = schema.Length > 0 ? schema : connection.CurrentDatabase(); restrictions[2] = name; DataTable procTable = connection.GetSchema("procedures", restrictions); if (procTable.Rows.Count > 1) throw new MySqlException(Resources.ProcAndFuncSameName); if (procTable.Rows.Count == 0) throw new MySqlException(String.Format(Resources.InvalidProcName, name, schema)); DataSet ds = new DataSet(); ds.Tables.Add(procTable); // we don't use GetSchema here because that would cause another // query of procedures and we don't need that since we already // know the procedure we care about. ISSchemaProvider isp = new ISSchemaProvider(connection); string[] rest = isp.CleanRestrictions(restrictions); try { DataTable parametersTable = isp.GetProcedureParameters(rest, procTable); ds.Tables.Add(parametersTable); } catch (Exception) { } return ds; }
/// <summary> /// Executes the service /// </summary> /// <param name="values">The values.</param> /// <param name="theWorkspace">The workspace.</param> /// <returns></returns> public StringBuilder Execute(Dictionary<string, StringBuilder> values, IWorkspace theWorkspace) { Dev2JsonSerializer serializer = new Dev2JsonSerializer(); if(values == null) { throw new InvalidDataContractException("No parameter values provided."); } string database = null; StringBuilder tmp; values.TryGetValue("Database", out tmp); if(tmp != null) { database = tmp.ToString(); } if(string.IsNullOrEmpty(database)) { var res = new DbTableList("No database set."); Dev2Logger.Log.Debug("No database set."); return serializer.SerializeToBuilder(res); } DbSource dbSource; DbSource runtimeDbSource = null; try { dbSource = serializer.Deserialize<DbSource>(database); if(dbSource.ResourceID != Guid.Empty) { runtimeDbSource = ResourceCatalog.Instance.GetResource<DbSource>(theWorkspace.ID, dbSource.ResourceID); } } catch(Exception e) { Dev2Logger.Log.Error(e); var res = new DbTableList("Invalid JSON data for Database parameter. Exception: {0}", e.Message); return serializer.SerializeToBuilder(res); } if(runtimeDbSource == null) { var res = new DbTableList("Invalid Database source"); Dev2Logger.Log.Debug("Invalid Database source"); return serializer.SerializeToBuilder(res); } if(string.IsNullOrEmpty(runtimeDbSource.DatabaseName) || string.IsNullOrEmpty(runtimeDbSource.Server)) { var res = new DbTableList("Invalid database sent {0}.", database); Dev2Logger.Log.Debug(String.Format("Invalid database sent {0}.", database)); return serializer.SerializeToBuilder(res); } try { Dev2Logger.Log.Info("Get Database Tables. " + dbSource.DatabaseName); var tables = new DbTableList(); DataTable columnInfo; switch(dbSource.ServerType) { case enSourceType.SqlDatabase: { using (var connection = new SqlConnection(dbSource.ConnectionString)) { connection.Open(); columnInfo = connection.GetSchema("Tables"); } break; } default: { using (var connection = new MySqlConnection(dbSource.ConnectionString)) { connection.Open(); columnInfo = connection.GetSchema("Tables"); } break; } } if(columnInfo != null) { foreach(DataRow row in columnInfo.Rows) { var tableName = row["TABLE_NAME"] as string; var schema = row["TABLE_SCHEMA"] as string; tableName = '[' + tableName + ']'; var dbTable = tables.Items.Find(table => table.TableName == tableName && table.Schema == schema); if(dbTable == null) { dbTable = new DbTable { Schema = schema, TableName = tableName, Columns = new List<IDbColumn>() }; tables.Items.Add(dbTable); } } } if(tables.Items.Count == 0) { tables.HasErrors = true; const string ErrorFormat = "The login provided in the database source uses {0} and most probably does not have permissions to perform the following query: " + "\r\n\r\n{1}SELECT * FROM INFORMATION_SCHEMA.TABLES;{2}"; if(dbSource.AuthenticationType == AuthenticationType.User) { tables.Errors = string.Format(ErrorFormat, "SQL Authentication (User: '******')", "EXECUTE AS USER = '******';\r\n", "\r\nREVERT;"); } else { tables.Errors = string.Format(ErrorFormat, "Windows Authentication", "", ""); } } return serializer.SerializeToBuilder(tables); } catch(Exception ex) { var tables = new DbTableList(ex); return serializer.SerializeToBuilder(tables); } }
public void GetProcedureParametersDoesNotRequireSelectFromMySqlProceduresTable() { if (Version < new Version(5, 5, 3)) return; suExecSQL(String.Format("GRANT ALL ON `{0}`.* to 'simpleuser' identified by 'simpleuser'", database0)); execSQL("DROP PROCEDURE IF EXISTS spTest"); execSQL(@"CREATE PROCEDURE spTest(id INT, name VARCHAR(20)) BEGIN SELECT name; END"); string connStr = GetConnectionString("simpleuser", "simpleuser", true) + ";use procedure bodies=false"; using (MySqlConnection c = new MySqlConnection(connStr)) { c.Open(); string[] restrictions = new string[4]; restrictions[1] = c.Database; restrictions[2] = "spTest"; DataTable procTable = c.GetSchema("procedures", restrictions); ISSchemaProvider isp = new ISSchemaProvider(c); string[] rest = isp.CleanRestrictions(restrictions); MySqlSchemaCollection parametersTable = isp.GetProcedureParameters(rest, new MySqlSchemaCollection( procTable )); Assert.IsNotNull(parametersTable); } }
public override IList<ViewSchema> GetViews(System.Data.Common.DbConnectionStringBuilder connectionstr) { IList<ViewSchema> list = null; using (MySqlConnection connection = new MySqlConnection(connectionstr.ConnectionString)) { connection.Open(); DataTable views = connection.GetSchema(SqlClientMetaDataCollectionNames.Views, new string[] { null, null, null,null}); if (views != null && views.Rows.Count > 0) { list = new List<ViewSchema>(); foreach (DataRow table in views.Rows) { string name = string.Format("{0}", table["TABLE_NAME"]); string des = string.Format("{0}.{1}", table["TABLE_SCHEMA"], table["TABLE_NAME"]); ViewSchema view = new ViewSchema(name,des); list.Add(view); } } } return list; }
public Dictionary<string, Table> Tables() { // NB: By default. MySql table names are case insensitive Dictionary<string, Table> tables = new Dictionary<string, Table>(StringComparer.OrdinalIgnoreCase); string schema = Regex.Match(AppSettings.Default.ConnectionString, "database=(.*?);").Groups[1].Value; using(MySqlConnection conn = new MySqlConnection(AppSettings.Default.ConnectionString)) { conn.Open(); DataTable tabs = conn.GetSchema("Tables"); DataTable cols = conn.GetSchema("Columns"); DataTable fkeyCols = conn.GetSchema("Foreign Key Columns"); DataTable indexes = conn.GetSchema("Indexes"); DataTable indexCols = conn.GetSchema("IndexColumns"); DataTable views = conn.GetSchema("Views"); DataTable viewCols = conn.GetSchema("ViewColumns"); foreach(DataRow table in tabs.Rows) { string name = table["TABLE_NAME"].ToString(); string filter = "TABLE_NAME = " + Quote(name); Field[] fields = cols.Select(filter, "ORDINAL_POSITION") .Select(c => new Field(c["COLUMN_NAME"].ToString(), typeFor(c["DATA_TYPE"].ToString()), lengthFromColumn(c), c["IS_NULLABLE"].ToString() == "YES", c["EXTRA"].ToString().Contains("auto_increment"), c["COLUMN_DEFAULT"] == System.DBNull.Value ? null : c["COLUMN_DEFAULT"].ToString())).ToArray(); List<Index> tableIndexes = new List<Index>(); foreach (DataRow ind in indexes.Select(filter + " AND PRIMARY = 'True'")) { string indexName = ind["INDEX_NAME"].ToString(); tableIndexes.Add(new Index("PRIMARY", indexCols.Select(filter + " AND INDEX_NAME = " + Quote(indexName), "ORDINAL_POSITION") .Select(r => fields.First(f => f.Name == r["COLUMN_NAME"].ToString())).ToArray())); } foreach (DataRow ind in indexes.Select(filter + " AND PRIMARY = 'False' AND UNIQUE = 'True'")) { string indexName = ind["INDEX_NAME"].ToString(); tableIndexes.Add(new Index(indexName, indexCols.Select(filter + " AND INDEX_NAME = " + Quote(indexName), "ORDINAL_POSITION") .Select(r => fields.First(f => f.Name == r["COLUMN_NAME"].ToString())).ToArray())); } tables[name] = new Table(name, fields, tableIndexes.ToArray()); } foreach (DataRow fk in fkeyCols.Rows) { // MySql 5 incorrectly returns lower case table and field names here Table detail = tables[fk["TABLE_NAME"].ToString()]; Table master = tables[fk["REFERENCED_TABLE_NAME"].ToString()]; Field masterField = FieldFor(master, fk["REFERENCED_COLUMN_NAME"].ToString()); FieldFor(detail, fk["COLUMN_NAME"].ToString()).ForeignKey = new ForeignKey(master, masterField); } foreach (DataRow table in views.Select("TABLE_SCHEMA = " + Quote(schema))) { string name = table["TABLE_NAME"].ToString(); string filter = "VIEW_NAME = " + Quote(name); Field[] fields = viewCols.Select(filter, "ORDINAL_POSITION") .Select(c => new Field(c["COLUMN_NAME"].ToString(), typeFor(c["DATA_TYPE"].ToString()), lengthFromColumn(c), c["IS_NULLABLE"].ToString() == "YES", false, c["COLUMN_DEFAULT"] == System.DBNull.Value ? null : c["COLUMN_DEFAULT"].ToString())).ToArray(); Table updateTable = null; tables.TryGetValue(Regex.Replace(name, "^.*_", ""), out updateTable); tables[name] = new View(name, fields, new Index[] { new Index("PRIMARY", fields[0]) }, table["VIEW_DEFINITION"].ToString(), updateTable); } } return tables; }
public override IList<DataBaseEntity> GetDataBases(ServiceSite site) { IList<DataBaseEntity> list = null; using (MySqlConnection connection = new MySqlConnection(site.DbConnectionStringBuilder.ConnectionString)) { connection.Open(); DataTable databases = connection.GetSchema(SqlClientMetaDataCollectionNames.Databases, new string[] { null }); if (databases != null && databases.Rows.Count > 0) { list = new List<DataBaseEntity>(); foreach (DataRow database in databases.Rows) { string name = (string)database["database_name"]; MySqlConnectionStringBuilder con = new MySqlConnectionStringBuilder(site.DbConnectionStringBuilder.ConnectionString); con.Database = name; DataBaseEntity db = new DataBaseEntity(con,name); db.Service = site; list.Add(db); } } } return list; }
public static List<string> GetTables() { List<string> TableNames = new List<string>(); try { using (MySqlConnection connection = new MySqlConnection(connString)) { connection.Open(); DataTable schema = connection.GetSchema("Tables"); foreach (DataRow row in schema.Rows) { TableNames.Add(row[2].ToString()); } } } catch(Exception ex) { MessageBox.Show(ex.Message); } return TableNames; }
public override IList<ProcedureEntity> GetProcedures(DataBaseEntity database) { IList<ProcedureEntity> list = null; using (MySqlConnection connection = new MySqlConnection(database.DbConnectionStringBuilder.ConnectionString)) { connection.Open(); DataTable procedures = connection.GetSchema(SqlClientMetaDataCollectionNames.Procedures, new string[] { null, null, null, "PROCEDURE" }); if (procedures != null && procedures.Rows.Count > 0) { list = new List<ProcedureEntity>(); foreach (DataRow procedure in procedures.Rows) { string name = string.Format("{0}", procedure["SPECIFIC_NAME"]); string des = string.Format("{0}.{1}", procedure["ROUTINE_SCHEMA"], procedure["SPECIFIC_NAME"]); ProcedureEntity proc = new ProcedureEntity(database.DbConnectionStringBuilder,name, des); proc.DataBase = database; list.Add(proc); } } } return list; }
/// <summary> /// 获取架构信息 /// </summary> /// <param name="connectionString"></param> /// <param name="collectionName"></param> /// <param name="restrictionValues"></param> /// <returns></returns> public static DataTable GetSchema(string connectionString, string collectionName, string[] restrictionValues) { MySqlConnection connection = new MySqlConnection(connectionString); DataTable schema = new DataTable(); try { connection.Open(); if (!string.IsNullOrEmpty(collectionName)) { if (restrictionValues != null && restrictionValues != null && restrictionValues.Length > 0) { schema = connection.GetSchema(collectionName, restrictionValues); } else { schema = connection.GetSchema(collectionName); } } else { schema = connection.GetSchema(); } } catch { schema = null; } finally { connection.Close(); } return schema; }
public override IList<TableEntity> GetTables(DataBaseEntity database) { IList<TableEntity> list = null; using (MySqlConnection connection = new MySqlConnection(database.DbConnectionStringBuilder.ConnectionString)) { connection.Open(); DataTable tables = connection.GetSchema(SqlClientMetaDataCollectionNames.Tables, new string[] { null, null, null, "BASE TABLE" }); if (tables != null && tables.Rows.Count > 0) { list = new List<TableEntity>(); foreach (DataRow table in tables.Rows) { string name = string.Format("{0}", table["TABLE_NAME"]); string des = string.Format("{0}.{1}", table["TABLE_SCHEMA"], table["TABLE_NAME"]); TableEntity t = new TableEntity(database.DbConnectionStringBuilder,name, des); t.DataBase = database; list.Add(t); } } } return list; }
private static int GetSchemaVersion(string connectionString) { // retrieve the current schema version using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); MySqlCommand cmd = new MySqlCommand("SELECT * FROM my_aspnet_schemaversion", conn); try { object ver = cmd.ExecuteScalar(); if (ver != null) return (int)ver; } catch (MySqlException ex) { if (ex.Number != (int)MySqlErrorCode.NoSuchTable) throw; string[] restrictions = new string[4]; restrictions[2] = "mysql_Membership"; DataTable dt = conn.GetSchema("Tables", restrictions); if (dt.Rows.Count == 1) return Convert.ToInt32(dt.Rows[0]["TABLE_COMMENT"]); } return 0; } }
public override IList<ViewEntity> GetViews(DataBaseEntity database) { IList<ViewEntity> list = null; using (MySqlConnection connection = new MySqlConnection(database.DbConnectionStringBuilder.ConnectionString)) { connection.Open(); DataTable views = connection.GetSchema(SqlClientMetaDataCollectionNames.Views, new string[] { null, null, null, null }); if (views != null && views.Rows.Count > 0) { list = new List<ViewEntity>(); foreach (DataRow table in views.Rows) { string name = string.Format("{0}", table["TABLE_NAME"]); string des = string.Format("{0}.{1}", table["TABLE_SCHEMA"], table["TABLE_NAME"]); ViewEntity view = new ViewEntity(database.DbConnectionStringBuilder,name, des); view.DataBase = database; list.Add(view); } } } return list; }
private DataTable GetTableColumns(string tName) { DataTable tc = new DataTable(); try { if (this.raExportAccess.Checked == true) { OleDbConnection conn = new OleDbConnection(); conn.ConnectionString = this.txtDataSource.Text; conn.Open(); string[] Restrictions = new string[4]; Restrictions[2] = tName; tc = conn.GetSchema("Columns", Restrictions); return tc; } else if (this.raExportMSSQL.Checked == true) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = this.txtDataSource.Text; conn.Open(); string[] Restrictions = new string[4]; Restrictions[2] = tName; tc = conn.GetSchema("Columns", Restrictions); return tc; } else if (this.raExportMySql.Checked == true) { MySqlConnection conn = new MySqlConnection(); conn.ConnectionString = this.txtDataSource.Text; conn.Open(); string[] Restrictions = new string[4]; Restrictions[2] = tName; tc = conn.GetSchema("Columns", Restrictions); return tc; } return tc; } catch (System.Exception) { return null; } }
public override IList<ColumnEntity> GetColumns(TableEntity table) { IList<ColumnEntity> list = null; DataTable columns = new DataTable(); columns.Locale = CultureInfo.CurrentCulture; DataTable keycolumns = new DataTable(); keycolumns.Locale = CultureInfo.CurrentCulture; using (MySqlConnection connection = new MySqlConnection(table.DbConnectionStringBuilder.ConnectionString)) { connection.Open(); #region //DataTable columns = connection.GetSchema(SqlClientMetaDataCollectionNames.Columns, new string[] { null, null, table.Name, null }); //if (columns != null && columns.Rows.Count > 0) //{ // list = new List<ColumnEntity>(); // DataView dv = columns.DefaultView; // dv.Sort = "ORDINAL_POSITION asc"; // foreach (DataRowView view in dv) // { // string name = string.Format("{0}",view["COLUMN_NAME"]); // string description = string.Format("{0}({1})", view["COLUMN_NAME"], view["DATA_TYPE"]); // ColumnEntity column = new ColumnEntity(table.DbConnectionStringBuilder,name); // column.Description = description; // column.DataType = DbType.MySqlParse(view["DATA_TYPE"].ToString()); // column.Table = table; // list.Add(column); // } //} #endregion keycolumns = connection.GetSchema("Foreign Key Columns", new string[] { null, null, table.Name, null }); MySqlCommand command = connection.CreateCommand(); command.CommandText = string.Format(CultureInfo.CurrentCulture, "SELECT * FROM {0} limit 1", table.Name); using (IDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo)) { columns = reader.GetSchemaTable(); reader.Close(); } } if (columns != null) { list = new List<ColumnEntity>(); foreach (DataRow row in columns.Rows) { ColumnEntity column = new ColumnEntity(table.DbConnectionStringBuilder, row["ColumnName"].ToString()); column.Description = string.Format("{0}", row["ColumnName"].ToString()); column.AllowDBNull = bool.Parse(row["AllowDBNull"].ToString()); column.DataType = (Type)row["DataType"]; column.IsIdentity = (bool)row["IsAutoIncrement"]; column.IsPrimaryKey = (bool)row["IsKey"]; column.IsReadOnly = (bool)row["IsReadOnly"]; column.IsUnique = (bool)row["IsUnique"]; column.Table = table; list.Add(column); } if (keycolumns != null && keycolumns.Rows.Count > 0) { foreach (DataRow row in keycolumns.Rows) { foreach (ColumnEntity c in list) { if (c.Name.Equals(row["COLUMN_NAME"].ToString())) { c.IsForeignKey = true; break; } } //TABLE_NAME //COLUMN_NAME //REFERENCED_TABLE_NAME //REFERENCED_COLUMN_NAME } } } return list; }
public bool TestConnection(string strConnect) { sqlConnection = new MySqlConnection(strConnect.ToString()); try { //sqlConnection.ConnectionString = strConnect; //DataTable schemaTable = sqlConnection.GetSchema("Databases"); sqlConnection.Open(); listDatabases = new List<string>(); using (DataTable dt = sqlConnection.GetSchema("Databases")) { //List<string> list = new List<string>(); if (dt != null && dt.Rows.Count > 0) { listDatabases.Capacity = dt.Rows.Count; foreach (DataRow row in dt.Rows) listDatabases.Add(row["database_name"].ToString()); } //return list; } sqlConnection.Close(); /* string query = "SELECT name FROM sys.databases WHERE database_id > 4"; DataTable table = new DataTable("Dbs"); SqlDataAdapter adapter = new SqlDataAdapter(query, sqlConnection); adapter.Fill(table); sqlConnection.Close(); for (int i = 0; i < table.Rows.Count; i++) listDatabases.Add(table.Rows[i][0].ToString()); //foreach(DataRowCollection row in table.Rows) //listDatabases.Add(row[0].ToString()); */ return true; } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); return false; } }
public List<field> getFields(string cadconexion, string database, string table) { MySqlConnection conexion = null; try { // ' ESTO SERIA PARA LA TABLA COMMENTS //' PARA SACARLO CON SQL... // SELECT *, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS where table_name = 'cars' List<field> lista = new List<field>(); // Retrieve a list of primary keys for a table. //List<String> primaryKeys = getKeys(cadconexion, table); conexion = new MySqlConnection(cadconexion); miComando = new MySqlCommand(""); miComando.Connection = conexion; conexion.ConnectionString = cadconexion; conexion.Open(); System.Data.DataTable dt = new System.Data.DataTable(); dt = conexion.GetSchema("Columns", new String[] { null, database, table, null }); foreach (System.Data.DataRow row in dt.Rows) { field fi = new field(); fi.Name = row[3].ToString(); fi.targetName = row[3].ToString(); string tipo = null; tipo = row[7].ToString(); switch (tipo) { case "text": fi.type = field.fieldType._text; break; case "mediumtext": fi.type = field.fieldType._text; break; case "char": fi.type = field.fieldType._string; break; case "nchar": fi.type = field.fieldType._string; break; case "varchar": fi.type = field.fieldType._string; break; case "nvarchar": fi.type = field.fieldType._string; break; case "binary": fi.type = field.fieldType._string; break; case "varbinary": fi.type = field.fieldType._string; break; case "mediumint": fi.type = field.fieldType._integer; break; case "smallint": fi.type = field.fieldType._integer; break; case "int": fi.type = field.fieldType._integer; break; case "numeric": fi.type = field.fieldType._integer; break; case "tinyint": fi.type = field.fieldType._boolean; break; case "boolean": fi.type = field.fieldType._boolean; break; case "bool": fi.type = field.fieldType._boolean; break; case "bit": fi.type = field.fieldType._boolean; break; case "bigint": fi.type = field.fieldType._double; break; case "double": fi.type = field.fieldType._double; break; case "float": fi.type = field.fieldType._double; break; case "smalldatetime": fi.type = field.fieldType._date; break; case "datetime": fi.type = field.fieldType._date; break; case "date": fi.type = field.fieldType._date; break; case "timestamp": fi.type = field.fieldType._date; break; default: fi.type = field.fieldType._string; break; } fi.targetType = fi.type; fi.allowNulls = sf.boolean(row["IS_NULLABLE"]); fi.size = sf.entero(row["CHARACTER_MAXIMUM_LENGTH"]); // if the size is > 250 and type is string.... if (fi.size >= 250) { switch (tipo) { case "mediumtext": fi.type = field.fieldType._text; fi.targetType = field.fieldType._text; break; case "char": fi.type = field.fieldType._text; fi.targetType = field.fieldType._text; break; case "nchar": fi.type = field.fieldType._text; fi.targetType = field.fieldType._text; break; case "varchar": fi.type = field.fieldType._text; fi.targetType = field.fieldType._text; break; case "nvarchar": fi.type = field.fieldType._text; fi.targetType = field.fieldType._text; break; } } // fi.comment = sf.Cadena(tbl.Rows(i)!COLUMN_COMMENT); fi.defaultValue = sf.cadena(row["COLUMN_DEFAULT"]); //try //{ // if (primaryKeys.Contains(fi.Name)) // { // fi.isKey = true; // // fi.autoNumber = true; // } //} //catch (Exception) //{ //} //fi.autoNumber = sf.boolean(row["COLUMN_KEY"]); // fi.isKey = sf.boolean(row["COLUMN_KEY"]); fi.decimals = sf.entero(row["NUMERIC_PRECISION"]); // // Retrieve the column's default value. // fi.defaultValue = ((row["COLUMN_DEFAULT"] as DBNull) // != null) ? "Null" : row["COLUMN_DEFAULT"].ToString(); // // Retrieve the column's precision. // //column.Precision = ((row["NUMERIC_PRECISION"] as DBNull) // // != null) ? 0 : Int32.Parse(row["NUMERIC_PRECISION"].ToString()); // // Retrieve the column's scale. //// column.Scale = ((row["NUMERIC_SCALE"] as DBNull) != null) ? 0 : Int32.Parse(row["NUMERIC_SCALE"].ToString()); // // Specify if the column is a primary key. // fi.isKey = primaryKeys.Contains(row["COLUMN_NAME"].ToString()); // // Specify that the column is not an identity column. // //column.IsIdentity = false; // // Retrieve the column length. // fi.size = ((OleDbType)Int32.Parse(row["DATA_TYPE"].ToString()) != OleDbType.WChar) ? -1 : Int32.Parse(row["CHARACTER_MAXIMUM_LENGTH"].ToString()); // // Append the column to the list. //fi.size = sf.Entero(tbl.Rows(i)!CHARACTER_MAXIMUM_LENGTH) // fi.comment = sf.Cadena(tbl.Rows(i)!COLUMN_COMMENT) //fi.allowNulls = sf.boolean(row["IS_NULLABLE"]);//tbl.Rows(i)!IS_NULLABLE) // fi.defaultValue = sf.Cadena(tbl.Rows(i)!COLUMN_DEFAULT) // fi.autoNumber = sf.boolean(tbl.Rows(i)!COLUMN_KEY) // fi.decimals = sf.Entero(tbl.Rows(i)!NUMERIC_PRECISION) lista.Add(fi); } return lista; } catch (Exception ep) { // lo.tratarError(ep, "Error en dbClass.new", ""); return null; } finally { conexion.Close(); } }
private void UserForm_Load(object sender, EventArgs e) { connection = new MySqlConnection(connectionString); connection.Open(); DataTable schema = connection.GetSchema("Tables"); foreach (DataRow row in schema.Rows) { listBox1.Items.Add(row[2].ToString()); } dt.Columns[0].ReadOnly = true; }
/// <summary> /// 查询数据库中的所有数据类型信息 /// </summary> /// <returns></returns> public DataTable GetSchema() { using (MySqlConnection connection = new MySqlConnection(connectionString)) { connection.Open(); DataTable data = connection.GetSchema("TABLES", new string[0]); connection.Close(); return data; } }