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