public void OpenTable(Query query, Sql8rTable table, bool editable)
{
if (_editorPresenter.EditTable(query, table.GetFullObjectName()))
{
_dataGridPresenter.OpenTable(query, table, editable);
}
}
public Sql8rServer GetServer()
{
var s = new Sql8rServer(_settings);
string dbConn = ConnectionString;
setServerInfo(ref s, dbConn);
OdbcConnection cn = new OdbcConnection(dbConn);
cn.Open();
// DataTable schema = cn.GetSchema(); // collectionName, nbrOfRestriction, nbrOfIden
// foreach (DataRow row in schema.Rows)
// {
// string collection = row["CollectionName"].ToString();
// "MetaDataCollections" "DataSourceInformation"
// "DataTypes" "Restrictions" "ReservedWords"
// "Columns" "Indexes" "Procedures" "ProcedureColumns"
// "ProcedureParameters" "Tables"
// }
Sql8rDatabase db = new Sql8rDatabase("main", 0);
s.Databases.Add(db);
DataTable dtColumns = cn.GetSchema("Columns");
DataTable dtTables = cn.GetSchema("Tables"); // collectionName, nbrOfRestriction, nbrOfIden
foreach (DataRow row in dtTables.Rows)
{
string tCategory = row["TABLE_CAT"].ToString();
string tSchema = row["TABLE_SCHEM"].ToString();
string tName = row["TABLE_NAME"].ToString();
bool tIsSystem = row["TABLE_TYPE"].ToString().ToString().Equals("SYSTEM TABLE", StringComparison.InvariantCultureIgnoreCase) ? true : false;
string tRemarks = row["REMARKS"].ToString();
Sql8rTable t = new Sql8rTable(tName, tSchema, tIsSystem, 0);
int id = 0;
foreach (DataRow column in dtColumns.Select(string.Format("TABLE_NAME = '{0}'", t.Name)))
{
string taSchema = column["TABLE_SCHEM"].ToString();
string taName = column["TABLE_NAME"].ToString();
string cName = column["COLUMN_NAME"].ToString();
string cDataType = column["DATA_TYPE"].ToString();
string cTypeName = column["TYPE_NAME"].ToString();
int cSize = int.Parse(column["COLUMN_SIZE"].ToString());
Sql8rColumn c = new Sql8rColumn(cName, false, cTypeName, cSize, id++);
t.Columns.Add(c.ObjectId, c);
}
db.Tables.Add(t);
}
return(s);
}
public Query GetTableContent(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, bool editable)
{
string sql = ResourceReader.GetResourceSql("SQL8r.Logic.SqlServer.Queries.UI.OpenTable.sql");
string dbSQL = string.Format(sql, database.Name, table.Schema, table.Name);
var conn = new SqlConnection(ConnectionString);
conn.Open();
conn.ChangeDatabase(database.Name);
var cmd = new SqlCommand(dbSQL, conn);
var sdaDatabases = new SqlDataAdapter(cmd);
if (editable)
{
var scb = new SqlCommandBuilder(sdaDatabases);
sdaDatabases.UpdateCommand = scb.GetUpdateCommand();
sdaDatabases.InsertCommand = scb.GetInsertCommand();
sdaDatabases.DeleteCommand = scb.GetDeleteCommand();
}
var dsDatabases = new DataTable("TableContent");
sdaDatabases.Fill(dsDatabases);
var query = new Query(_settings, server.Name, database.Name, dbSQL, dsDatabases);
query.Adapter = sdaDatabases;
return(query);
}
public Query GetAlterColumnQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, Sql8rColumn column)
{
string sql = string.Format(sqlAlterColumn, database.Name, table.GetFullObjectName(), column.Name);
var query = new Query(_settings, server.Name, database.Name, sql);
return(query);
}
public Query GetTableContent(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, bool editable)
{
string sql = "SELECT * FROM {0};";
string dbSQL = string.Format(sql, table.Name);
string dbConn = ConnectionString;
using (var conn = new SQLiteConnection(dbConn))
{
conn.Open();
var cmd = new SQLiteCommand(dbSQL, conn);
var sdaDatabases = new SQLiteDataAdapter(cmd);
if (editable)
{
var scb = new SQLiteCommandBuilder(sdaDatabases);
sdaDatabases.UpdateCommand = scb.GetUpdateCommand();
sdaDatabases.InsertCommand = scb.GetInsertCommand();
sdaDatabases.DeleteCommand = scb.GetDeleteCommand();
}
var dsDatabases = new DataTable("TableContent");
sdaDatabases.Fill(dsDatabases);
var query = new Query(_settings, server.Name, database.Name, dbSQL, dsDatabases);
query.Adapter = sdaDatabases;
return(query);
}
}
public void ShowUsage(Sql8rServer s, Sql8rDatabase db, Sql8rTable t, string fileName)
{
_s = s;
_db = db;
_t = t;
bindData(t);
_view.Text = string.Format("{0}: {1}", MainPresenter.MdiTabKeys.TableUsage, fileName);
}
public Query GetAlterColumnQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, Sql8rColumn column)
{
// throw new System.NotImplementedException();
string sql = string.Format(sqlAlterColumn, table.GetFullObjectName(), column.Name);
var query = new Query(_settings, server.Name, database.Name, sql);
return(query);
}
public Sql8rServer GetServer()
{
// string sqlDatabases = "SELECT type, name, tbl_name, rootpage, sql FROM main.SQLITE_MASTER;";
var s = new Sql8rServer(_settings);
string dbConn = ConnectionString;
setServerInfo(ref s, dbConn);
var sdaTables = new SQLiteDataAdapter(sqlDatabases, dbConn);
var dtTables = new DataTable("TablesEtc");
sdaTables.Fill(dtTables);
string dbName = _settings.DatabaseName;
if (File.Exists(_settings.DatabaseName))
{
dbName = Path.GetFileName(_settings.DatabaseName);
}
var db = new Sql8rDatabase(dbName, 0);
s.Databases.Add(db);
var systable = new Sql8rTable("SQLITE_MASTER", "main", true, 0);
addColumns(systable);
db.Tables.Add(systable);
foreach (DataRow drTable in dtTables.Select("type = 'table'"))
{
string t_name = drTable.Field <string>("name");
long t_rootpage = drTable.Field <long>("rootpage");
// string sql = drTable.Field<string>("sql");
var table = new Sql8rTable(t_name, "", false, t_rootpage);
addColumns(table);
foreach (DataRow drIndex in dtTables.Select("type = 'index' AND tbl_name = '" + t_name + "'"))
{
string i_name = drIndex.Field <string>("name");
long i_rootpage = drIndex.Field <long>("rootpage");
var index = new Sql8rIndex(i_name, false, false, i_rootpage);
table.Indexes.Add(index.Name, index);
}
db.Tables.Add(table);
//if (type.Equals("trigger", StringComparison.InvariantCultureIgnoreCase))
//{
// var trigger = new Sql8rTrigger(name, "", false);
//}
}
return(s);
}
public void OpenTable(Query query, Sql8rTable table, bool editable)
{
string fileName = table.GetFullObjectName();
setEditability(editable, !editable);
bindDataTable(query, table);
_view.tslDataName.Text = fileName;
}
private Sql8rTable addTable(DataRow drTable, long dbId, string dbName, string dbConn, DataTable dtColumns, DataTable dtIndexes)
{
string tName = drTable["tName"].ToString();
string sName = drTable["sName"].ToString();
bool isSystemObject = bool.Parse(drTable["isSystemObject"].ToString());
long objectId = long.Parse(drTable["objectId"].ToString(), CultureInfo.InvariantCulture);
var t = new Sql8rTable(tName, sName, isSystemObject, objectId);
if (!dbName.Equals("tempdb", StringComparison.OrdinalIgnoreCase))
{
string objectName = string.Format(CultureInfo.InvariantCulture,
"{0}.{1}", sName, tName);
setTableSpaceUsage(ref t, dbConn, dbName, objectName);
}
DataRow[] columnRows = dtColumns.Select(string.Format("objectId = {0}", objectId));
foreach (DataRow drColumn in columnRows)
{
Sql8rColumn c = addColumn(drColumn);
if (!t.Columns.ContainsKey(c.ObjectId))
{
t.Columns.Add(c.ObjectId, c);
}
}
DataRow[] indexRows = dtIndexes.Select(string.Format("objectId = {0}", objectId));
//string iSQL = string.Format(sqlIndexes, dbName, objectId);
//var dtIndexes = new DataTable("Indexes");
//var sdaIndexes = new SqlDataAdapter(iSQL, dbConn);
//sdaIndexes.Fill(dtIndexes);
foreach (DataRow drIndex in indexRows)
{
string iName = drIndex["iName"].ToString();
int indexId = int.Parse(drIndex["indexId"].ToString(), CultureInfo.InvariantCulture);
int iType = int.Parse(drIndex["iType"].ToString(), CultureInfo.InvariantCulture);
bool isPrimaryKey = bool.Parse(drIndex["isPrimaryKey"].ToString());
// 0=HEAP, 1=CLUSTERED, 2=UNCLUSTERED, 3=XML
var i = new Sql8rIndex(iName, iType == 1, isPrimaryKey, indexId);
setIndexSpaceUsage(ref i, dbConn, dbName, dbId, t.ObjectId, indexId);
t.Indexes.Add(i.Name, i);
}
return(t);
}
public Sql8rServer GetServer()
{
var s = new Sql8rServer(_settings);
Sql8rDatabase db = new Sql8rDatabase("MYDB.FDB", 0);
Sql8rTable t = new Sql8rTable("table1", "", false, 0);
db.Tables.Add(t);
s.Databases.Add(db);
return(s);
}
private int getMaxIndexSpaceUsed(Sql8rTable t)
{
int max = 1;
foreach (Sql8rIndex i in t.Indexes.Values)
{
if (i.SpaceUsed > max)
{
max = i.SpaceUsed;
}
}
return(max);
}
public Query GetTableQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table)
{
var s = new Server(server.Name);
Database db = s.Databases[database.Name];
Table v = db.Tables[table.Name, table.Schema];
StringCollection tableSqls = v.Script();
string viewSql =
string.Format("{0}{1}{2}{3}{4}{5}", tableSqls[0], Environment.NewLine, tableSqls[1], Environment.NewLine,
Environment.NewLine, tableSqls[2]);
var query = new Query(_settings, server.Name, database.Name, viewSql);
return(query);
}
private void setTableSpaceUsage(ref Sql8rTable t, string dbConn, string dbName, string objectName)
{
string spaceSQL =
string.Format(sqlTableSpaceUsed, dbName, objectName);
var dtSpace = new DataTable("Space");
var sdaSpace = new SqlDataAdapter(spaceSQL, dbConn);
sdaSpace.Fill(dtSpace);
if (dtSpace.Rows.Count > 0)
{
DataRow drSpace = dtSpace.Rows[0];
double dsu = double.Parse(drSpace[3].ToString().Replace(" KB", ""), CultureInfo.InvariantCulture);
double isu = double.Parse(drSpace[4].ToString().Replace(" KB", ""), CultureInfo.InvariantCulture);
t.SetSpaceStats(dsu, isu);
}
}
//public MySqlDatabaseHandler()
//{
//}
//public MySqlDatabaseHandler(ServerConnectionSettings si)
//{
// _settings = si;
//}
#region IDatabaseManager Members
public Query GetTableContent(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, bool editable)
{
string sql = ResourceReader.GetResourceSql("SQL8r.Logic.MySQL.Queries.UI.OpenTable.sql");
string dbConn = ConnectionString;
string dbSQL = string.Format(sql, database.Name, table.Name);
var mdaDatabases = new MySqlDataAdapter(dbSQL, dbConn);
var dsDatabases = new DataTable("TableContent");
mdaDatabases.Fill(dsDatabases);
var q = new Query(_settings, server.Name, database.Name, dbSQL, dsDatabases);
return(q);
}
public Query GetTableContent(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, bool editable)
{
string dbConn = ConnectionString;
string dbSQL = string.Format("SELECT * FROM [{0}]", table.Name);
var sdaDatabases = new OdbcDataAdapter(dbSQL, dbConn);
var dsDatabases = new DataTable("TableContent");
sdaDatabases.Fill(dsDatabases);
var query = new Query(_settings, server.Name, database.Name, dbSQL, dsDatabases);
return(query);
//return dsDatabases;
}
public Query GetTableQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table)
{
string sql = ResourceReader.GetResourceSql("SQL8r.Logic.MySQL.Queries.UI.EditTable.sql");
string dbConn = ConnectionString;
string dbSQL = string.Format(sql, database.Name, table.Name);
var dtTables = new DataTable("ViewTable");
var mdaTables = new MySqlDataAdapter(dbSQL, dbConn);
mdaTables.Fill(dtTables);
DataRow drDatabase = dtTables.Rows[0];
string def = drDatabase["Create Table"].ToString();
var q = new Query(_settings, server.Name, database.Name, def);
return(q);
}
private void bindData(Sql8rTable t)
{
_view.txtDataSpaceUsage.Text = string.Format("Data Space: {0} kb", t.DataSpaceUsed);
_view.txtIndexSpaceUsage.Text = string.Format("Index Space: {0} kb", t.IndexSpaceUsed);
_view.lvwIndexes.Items.Clear();
var fragmentationBar = new UltraProgressBar();
fragmentationBar.Appearance.BackColor = Color.GreenYellow;
var spaceBar = new UltraProgressBar();
int max = getMaxIndexSpaceUsed(t);
spaceBar.Maximum = max;
spaceBar.Text = "[Value]";
_view.lvwIndexes.MainColumn.DataType = typeof(string);
_view.lvwIndexes.SubItemColumns[0].DataType = typeof(double);
_view.lvwIndexes.SubItemColumns[0].EditorControl = fragmentationBar;
_view.lvwIndexes.SubItemColumns[1].DataType = typeof(int);
_view.lvwIndexes.SubItemColumns[1].EditorControl = spaceBar;
_view.lvwIndexes.SubItemColumns[2].DataType = typeof(string);
var ulvis = new List <UltraListViewItem>(t.Indexes.Count);
foreach (Sql8rIndex i in t.Indexes.Values)
{
var ulvsiFragmentation = new UltraListViewSubItem();
ulvsiFragmentation.Value = i.AvgFragmentation;
var ulvsiSpaceUsed = new UltraListViewSubItem();
ulvsiSpaceUsed.Value = i.SpaceUsed;
var ulvsiRecommendation = new UltraListViewSubItem();
i.Recommendation = recommend(i);
ulvsiRecommendation.Value = i.Recommendation;
var ulvi = new UltraListViewItem(i.Name, new[] { ulvsiFragmentation, ulvsiSpaceUsed, ulvsiRecommendation });
ulvi.Tag = i;
ulvi.Appearance.Image = MainPresenter.Instance.View.TreeImageList.Images[11];
ulvis.Add(ulvi);
}
_view.lvwIndexes.Items.AddRange(ulvis.ToArray());
}
public Query GetTableContent(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, bool editable)
{
// const string inventoryDbName = @"C:\Users\niklas\Desktop\inventory.db";
// const string inventoryDbName = @"C:\Users\niklas\Desktop\mydb.fdb";
// string connectionString = "ServerType=1;User=SYSDBA;Password=masterkey;Dialect=3;Database=";
// FbDataAdapter da = new FbDataAdapter("SELECT * FROM table1", connectionString + this.textBox1.Text);
string sql = "SELECT * FROM {0};";
string dbSQL = string.Format(sql, "table1");
string dbConn = ConnectionString;
dbConn = @"ServerType=1;User=SYSDBA;Password=masterkey;Dialect=3;Database=MYDB.FDB";
FbConnection conn = new FbConnection(dbConn);
conn.Open();
// conn.ChangeDatabase(database.Name);
var cmd = new FbCommand(dbSQL, conn);
var sdaDatabases = new FbDataAdapter(cmd);
if (editable)
{
var scb = new FbCommandBuilder(sdaDatabases);
sdaDatabases.UpdateCommand = scb.GetUpdateCommand();
sdaDatabases.InsertCommand = scb.GetInsertCommand();
sdaDatabases.DeleteCommand = scb.GetDeleteCommand();
}
var dsDatabases = new DataTable("TableContent");
sdaDatabases.Fill(dsDatabases);
var query = new Query(_settings, server.Name, database.Name, dbSQL, dsDatabases);
query.Adapter = sdaDatabases;
return(query);
}
public new Query GetReorganizeIndexQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, Sql8rIndex index)
{
string sql = ResourceReader.GetResourceSql("SQL8r.Logic.SqlServer.Queries.UI.ReorganizeIndex.sql");
string head = string.Format(sql, database.Name, index.Name, table.Schema, table.Name, index.ObjectId);
var query = new Query(Settings, server.Name, database.Name, head);
return(query);
}
// private ServerConnectionSettings _settings;
#region IPerformanceManager Members
//public ServerVersionId ManagerName
//{
// get { return ServerVersionId.SqlServer_2008; }
//}
// public ServerConnectionSettings Settings
// {
// set { _settings = value; }
//}
public new Query GetRebuildIndexQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, Sql8rIndex index)
{
string sql = ResourceReader.GetResourceSql("SQL8r.Logic.SqlServer.Queries.UI.RebuildIndex.sql");
string head = string.Format(sql, database.Name, index.Name, table.Schema, table.Name);
if (server.Edition == ServerEditions.EnterpriseEdition)
{
head += " WITH ONLINE = ON";
}
var query = new Query(Settings, server.Name, database.Name, head);
return(query);
}
public Sql8rServer GetServer()
{
var myConnection = new OracleConnection();
myConnection.ConnectionString = @"USER ID=hr;PASSWORD=hr;DATA SOURCE=//localhost/xe";
//_settings.GetConnectionString();
var s = new Sql8rServer(_settings);
OracleDataReader myReader = null;
var db = new Sql8rDatabase("OracleTest", 0);
try
{
myConnection.Open();
var myCommand = new OracleCommand();
myCommand.CommandText = "SELECT table_name, owner FROM all_tables";
myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
var t = new Sql8rTable(myReader.GetString(0), "",
myReader.GetString(1) == "SYSTEM" || myReader.GetString(1) == "SYS", 0);
db.Tables.Add(t);
var myColumnConnection = new OracleConnection();
//myColumnConnection.ConnectionString = _settings.GetConnectionString();
myColumnConnection.ConnectionString = @"USER ID=hr;PASSWORD=hr;DATA SOURCE=//localhost/xe";
OracleDataReader myColumnReader = null;
try
{
myColumnConnection.Open();
var myColumnCommand = new OracleCommand();
myColumnCommand.CommandText = string.Format(CultureInfo.InvariantCulture,
"SELECT column_name FROM user_tab_cols WHERE table_name = '{0}'",
t.Name);
myColumnCommand.CommandType = CommandType.Text;
myColumnCommand.Connection = myColumnConnection;
myColumnReader = myColumnCommand.ExecuteReader();
while (myColumnReader.Read())
{
var c = new Sql8rColumn(myColumnReader.GetString(0), false, "", 0, 0);
t.Columns.Add(c.ObjectId, c);
}
}
finally
{
myColumnReader.Close();
myColumnConnection.Close();
}
}
myReader.Close();
myCommand.CommandText = "SELECT view_name, owner FROM all_views";
myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
var v = new Sql8rView(myReader.GetString(0), "",
myReader.GetString(1) == "SYSTEM" || myReader.GetString(1) == "SYS", 0);
db.Views.Add(v);
}
myReader.Close();
myCommand.CommandText = "SELECT UNIQUE(object_name) FROM all_procedures";
myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
var sp = new Sql8rProcedure(myReader.GetString(0), "", false, 0);
db.Procedures.Add(sp.Name, sp);
}
myReader.Close();
s.Databases.Add(db);
}
finally
{
myConnection.Close();
}
return(s);
}
public Query GetAddIndexQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table)
{
throw new NotImplementedException();
}
public Query GetAlterColumnQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, Sql8rColumn column)
{
throw new NotImplementedException();
}
public Query GetTableContent(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, bool editable)
{
throw new NotImplementedException();
}
public DraggableTable(Sql8rServer server, Sql8rDatabase database, Sql8rTable table)
{
_server = server;
_database = database;
_table = table;
}
private Sql8rDatabase createDb(string dbConn, DataRow drDatabase)
{
string dbName = drDatabase["name"].ToString();
int databaseId = int.Parse(drDatabase["databaseId"].ToString());
var db = new Sql8rDatabase(dbName, databaseId);
log.Debug(string.Format("Adding database: {0}", dbName));
// -- Columns
string cSQL = string.Format(sqlColumns, dbName);
var dtColumns = new DataTable("Columns");
var sdaColumns = new SqlDataAdapter(cSQL, dbConn);
sdaColumns.Fill(dtColumns);
// -- Indexes
string iSQL = string.Format(sqlIndexes, dbName);
var dtIndexes = new DataTable("Indexes");
var sdaIndexes = new SqlDataAdapter(iSQL, dbConn);
sdaIndexes.Fill(dtIndexes);
// -- Tables
string tSQL = string.Format(sqlTables, dbName);
var dtTables = new DataTable("Tables");
var sdaTables = new SqlDataAdapter(tSQL, dbConn);
sdaTables.Fill(dtTables);
foreach (DataRow drTable in dtTables.Rows)
{
Sql8rTable t = addTable(drTable, db.ObjectId, dbName, dbConn, dtColumns, dtIndexes);
db.Tables.Add(t);
}
log.Debug(string.Format("Added {0} tables", dtTables.Rows.Count));
// -- Views
string vSQL = string.Format(sqlViews, dbName);
var dtViews = new DataTable("Views");
var sdaViews = new SqlDataAdapter(vSQL, dbConn);
sdaViews.Fill(dtViews);
foreach (DataRow drView in dtViews.Rows)
{
Sql8rView v = addView(drView, db.ObjectId, dbName, dbConn, dtColumns, dtIndexes);
db.Views.Add(v);
}
log.Debug(string.Format("Added {0} views", dtViews.Rows.Count));
// -- Procedures
string pSQL = string.Format(sqlProcedures, dbName);
var dtProcedures = new DataTable("Procedures");
var sdaProcedures = new SqlDataAdapter(pSQL, dbConn);
sdaProcedures.Fill(dtProcedures);
foreach (DataRow drProcedure in dtProcedures.Rows)
{
Sql8rProcedure p = addProcedure(drProcedure);
db.Procedures.Add(p.Name, p);
}
// -- Functions
string fknSQL = string.Format(sqlFunctions, dbName);
var dtFunctions = new DataTable("Functions");
var sdaFunctions = new SqlDataAdapter(fknSQL, dbConn);
sdaFunctions.Fill(dtFunctions);
foreach (DataRow drFunction in dtFunctions.Rows)
{
Sql8rFunction f = addFunction(drFunction);
db.Functions.Add(f.Name, f);
}
// -- Assemblies
//string asmSQL = string.Format(sqlAssemblies, dbName);
//var dtAssemblies = new DataTable("Assemblies");
//var sdaAssemblies = new SqlDataAdapter(asmSQL, dbConn);
//sdaAssemblies.Fill(dtAssemblies);
//foreach (DataRow drAssembly in dtAssemblies.Rows)
//{
// Sql8rAssembly a = addAssembly(drAssembly);
// db.Assemblies.Add(a.Name, a);
//}
//s.Databases.Add(db);
log.Debug(string.Format("Created database: {0}", dbName));
return(db);
}
public Sql8rServer GetServer()
{
var myConnection = new MySqlConnection();
myConnection.ConnectionString = ConnectionString;
var s = new Sql8rServer(_settings);
MySqlDataReader myReader = null;
try
{
myConnection.Open();
var dbTable = new DataTable();
dbTable.Locale = CultureInfo.InvariantCulture;
string dbSQL = ResourceReader.GetResourceSql("SQL8r.Logic.MySQL.Queries.UI.GetServerDatabases.sql");
var myAdapter = new MySqlDataAdapter(dbSQL, myConnection);
myAdapter.Fill(dbTable);
foreach (DataRow row in dbTable.Rows)
{
var db = new Sql8rDatabase(row.ItemArray[0].ToString(), 0);
string sql = ResourceReader.GetResourceSql("SQL8r.Logic.MySQL.Queries.UI.GetServerTables.sql");
string tSQL = string.Format(sql, db.Name);
var myCommand = new MySqlCommand();
myCommand.CommandText = tSQL;
myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myReader = myCommand.ExecuteReader();
MySqlDataReader myColumnReader = null;
while (myReader.Read())
{
var t = new Sql8rTable(myReader.GetString(0), "", false, 0);
db.Tables.Add(t);
var myColumnConnection = new MySqlConnection();
myColumnConnection.ConnectionString = String.Format(
"{0};database={1}",
ConnectionString,
db.Name);
try
{
myColumnConnection.Open();
sql = ResourceReader.GetResourceSql("SQL8r.Logic.MySQL.Queries.UI.GetServerColumns.sql");
string cSQL = string.Format(sql, t.Name);
var myColumnCommand = new MySqlCommand();
myColumnCommand.CommandText = cSQL;
myColumnCommand.CommandType = CommandType.Text;
myColumnCommand.Connection = myColumnConnection;
myColumnReader = myColumnCommand.ExecuteReader();
while (myColumnReader.Read())
{
var reg = new Regex(@"[a-z]+");
Match match = reg.Match(myColumnReader.GetString(1));
string colType = match.Value;
reg = new Regex(@"[0-9]+");
match = reg.Match(myColumnReader.GetString(1));
int maxLength = 0;
if (match.Value != "")
{
maxLength = Convert.ToInt32(match.Value);
}
var c = new Sql8rColumn(myColumnReader.GetString(0),
myColumnReader.GetString(3) == "PRI",
colType, maxLength, 0);
t.Columns.Add(c.ObjectId, c);
}
}
finally
{
myColumnReader.Close();
myColumnConnection.Close();
}
}
myReader.Close();
myCommand.CommandText = string.Format(
"SHOW FULL TABLES FROM {0} WHERE Table_type = 'VIEW';",
db.Name);
myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
var v = new Sql8rView(myReader.GetString(0), "", false, 0);
// hej Johan, breaking change: objectid
db.Views.Add(v);
var myColumnConnection = new MySqlConnection();
myColumnConnection.ConnectionString = String.Format(
"{0};database={1}",
ConnectionString,
db.Name);
try
{
myColumnConnection.Open();
sql = ResourceReader.GetResourceSql("SQL8r.Logic.MySQL.Queries.UI.GetServerColumns.sql");
string cSQL = string.Format(sql, v.Name);
var myColumnCommand = new MySqlCommand();
myColumnCommand.CommandText = cSQL;
myColumnCommand.CommandType = CommandType.Text;
myColumnCommand.Connection = myColumnConnection;
myColumnReader = myColumnCommand.ExecuteReader();
while (myColumnReader.Read())
{
var reg = new Regex(@"[a-z]+");
Match match = reg.Match(myColumnReader.GetString(1));
string colType = match.Value;
reg = new Regex(@"[0-9]+");
match = reg.Match(myColumnReader.GetString(1));
int maxLength = 0;
if (match.Value != "")
{
maxLength = Convert.ToInt32(match.Value);
}
var c = new Sql8rColumn(myColumnReader.GetString(0),
myColumnReader.GetString(3) == "PRI",
colType, maxLength, 0);
v.Columns.Add(c.ObjectId, c);
}
}
finally
{
myColumnReader.Close();
myColumnConnection.Close();
}
}
myReader.Close();
myCommand.CommandText = "SELECT ROUTINE_NAME " +
"FROM INFORMATION_SCHEMA.ROUTINES " +
"WHERE ROUTINE_SCHEMA='" + db.Name + "' " +
"AND ROUTINE_TYPE='PROCEDURE'" + ";";
myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
var sp = new Sql8rProcedure(myReader.GetString(0), "", false, 0);
db.Procedures.Add(sp.Name, sp);
}
myReader.Close();
myCommand.CommandText = "SELECT ROUTINE_NAME " +
"FROM INFORMATION_SCHEMA.ROUTINES " +
"WHERE ROUTINE_SCHEMA='" + db.Name + "' " +
"AND ROUTINE_TYPE='FUNCTION'" + ";";
myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
var fkn = new Sql8rFunction(myReader.GetString(0), "", false, 0);
db.Functions.Add(fkn.Name, fkn);
}
myReader.Close();
s.Databases.Add(db);
}
}
finally
{
myConnection.Close();
}
return(s);
}
public Query GetAddColumnQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table)
{
throw new System.NotImplementedException();
}
public Query GetReorganizeIndexQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, Sql8rIndex index)
{
throw new System.NotImplementedException();
}