private Sql8rProcedure addProcedure(DataRow drProcedure)
{
string pName = drProcedure["pName"].ToString();
string sName = drProcedure["sName"].ToString();
bool isSystemObject = bool.Parse(drProcedure["isSystemObject"].ToString());
long objectId = long.Parse(drProcedure["objectId"].ToString(), CultureInfo.InvariantCulture);
var p = new Sql8rProcedure(pName, sName, isSystemObject, objectId);
return(p);
}
public Query GetProcedureQuery(Sql8rServer server, Sql8rDatabase database, Sql8rProcedure procedure)
{
string sqlProcedureHeader = string.Empty;
string sqlProcedureBody = string.Empty;
var myConnection = new MySqlConnection();
myConnection.ConnectionString = String.Format(
"{0};database={1}",
ConnectionString,
database.Name);
MySqlDataReader myReader = null;
string sql = ResourceReader.GetResourceSql("SQL8r.Logic.MySQL.Queries.UI.EditProcedure.sql");
string dbSQL = string.Format(sql, procedure.Name);
try
{
myConnection.Open();
var myCommand = new MySqlCommand();
myCommand.CommandText = dbSQL;
myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
sqlProcedureBody = myReader.GetString(2);
}
}
finally
{
myReader.Close();
myConnection.Close();
}
string viewSql = string.Format("{0}{1}{2}", sqlProcedureHeader, Environment.NewLine, sqlProcedureBody);
var q = new Query(_settings, server.Name, database.Name, viewSql);
return(q);
}
public Query GetProcedureQuery(Sql8rServer server, Sql8rDatabase database, Sql8rProcedure procedure)
{
string sql = ResourceReader.GetResourceSql("SQL8r.Logic.SqlServer.Queries.UI.EditProcedure.sql");
string dbSQL = string.Format(sql, database.Name, procedure.ObjectId);
var dtTables = new DataTable("Tables")
{
Locale = CultureInfo.InvariantCulture
};
var sdaTables = new SqlDataAdapter(dbSQL, ConnectionString);
sdaTables.Fill(dtTables);
DataRow drDatabase = dtTables.Rows[0];
string def = drDatabase["definition"].ToString();
var query = new Query(_settings, server.Name, database.Name, def);
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 GetExecuteProcedureQuery(Sql8rServer server, Sql8rDatabase database, Sql8rProcedure procedure)
{
throw new NotImplementedException();
}
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 GetExecuteProcedureQuery(Sql8rServer server, Sql8rDatabase database, Sql8rProcedure procedure)
{
var myConnection = new MySqlConnection();
myConnection.ConnectionString = String.Format(
"{0};database={1}",
ConnectionString,
database.Name);
MySqlDataReader myReader = null;
string paramsString = string.Empty;
try
{
myConnection.Open();
var myCommand = new MySqlCommand();
myCommand.CommandText = string.Format(
"SHOW CREATE PROCEDURE `{0}`",
procedure.Name);
myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
paramsString = myReader.GetString(2);
}
}
finally
{
myReader.Close();
myConnection.Close();
}
string head = string.Format("EXEC {0}", procedure.Name);
var reg = new Regex(@"(?<=\x28)([^\n\r]+)(?=\x29)");
Match match = reg.Match(paramsString);
paramsString = match.Groups[1].Value;
char[] paramsSplitter = { ',' };
string[] paramsSet = paramsString.Split(paramsSplitter);
foreach (string param in paramsSet)
{
char[] paramPartSplitter = { ' ' };
string[] paramPart = param.TrimStart().Split(paramPartSplitter);
head = string.Format("{0} {1} {2}=<{3}>", head, paramPart[0], paramPart[1], paramPart[2]);
}
var q = new Query(_settings, server.Name, database.Name, head);
return(q);
}
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 Query GetExecuteProcedureQuery(Sql8rServer server, Sql8rDatabase database, Sql8rProcedure procedure)
{
string head = string.Format("EXEC {0}.{1}", procedure.Schema, procedure.Name);
string use = string.Format("USE {0} ", database.Name);
string vars = "";
string dbSQL = string.Format(sqlParameters, database.Name, procedure.ObjectId);
var sdaParameters = new SqlDataAdapter(dbSQL, ConnectionString);
var dtParameters = new DataTable("Parameters");
sdaParameters.Fill(dtParameters);
string sep = ",";
int count = dtParameters.Rows.Count;
for (int i = 0; i < count; i++)
{
DataRow drDatabase = dtParameters.Rows[i];
int paramId = int.Parse(drDatabase["parameterId"].ToString());
string paramName = drDatabase["pName"].ToString();
bool isOutput = bool.Parse(drDatabase["isOutput"].ToString());
string typeName = drDatabase["tName"].ToString();
if (i == count - 1)
{
sep = "";
}
if (!isOutput)
{
head = string.Format("{0} {1}=<{2}>{3}", head, paramName, typeName, sep);
}
else if (paramId > 0)
{
vars = string.Format("DECLARE {0} {1};{2}", paramName, typeName, Environment.NewLine);
head = string.Format("{0} {1} OUTPUT{2}", head, paramName, sep);
}
else
{
vars = string.Format("DECLARE @outparam {0};{1}", typeName, Environment.NewLine);
}
}
string sql = string.Format("{0}{1}{2}{3}{4}", use, Environment.NewLine, vars, Environment.NewLine, head);
var query = new Query(_settings, server.Name, database.Name, sql);
return(query);
}
