private void addColumns(IColumnedObject table) { string sql = string.Format(sqlColumns, table.Name); string dbConn = ConnectionString; var cn = new SQLiteConnection(dbConn); cn.Open(); var cmd = new SQLiteCommand(sql, cn); SQLiteDataReader rd = cmd.ExecuteReader(CommandBehavior.SchemaOnly); DataTable dt = rd.GetSchemaTable(); rd.Close(); foreach (DataRow dr in dt.Rows) { string columnName = dr.Field <string>("ColumnName"); bool isKey = dr.Field <bool>("IsKey"); string dataTypeName = dr.Field <string>("DataTypeName"); // string provDataTypeName = dr.Field<string>("ProviderSpecificDataType"); short?numericPrecision = dr.Field <short?>("NumericPrecision"); int id = dr.Field <int>("ColumnOrdinal"); int numPres = -1; if (numericPrecision.HasValue) { numPres = numericPrecision.Value; } Sql8rColumn c = new Sql8rColumn(columnName, isKey, dataTypeName, numPres, id); table.Columns.Add(c.ObjectId, c); } }
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); }
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); }
private Sql8rColumn addColumn(DataRow drColumn) { string cName = drColumn["cName"].ToString(); string typeName = drColumn["typeName"].ToString(); int maxLength = int.Parse(drColumn["maxLength"].ToString(), CultureInfo.InvariantCulture); bool inPrimaryKey = false; if (!drColumn.IsNull("isPrimaryKey")) { inPrimaryKey = bool.Parse(drColumn["isPrimaryKey"].ToString()); } int columnId = int.Parse(drColumn["columnId"].ToString(), CultureInfo.InvariantCulture); var c = new Sql8rColumn(cName, inPrimaryKey, typeName, maxLength, columnId); return(c); }
private Sql8rView addView(DataRow drView, long dbId, string dbName, string dbConn, DataTable dtColumns, DataTable dtIndexes) { string vName = drView["vName"].ToString(); string sName = drView["sName"].ToString(); bool isSystemObject = bool.Parse(drView["isSystemObject"].ToString()); long objectId = long.Parse(drView["objectId"].ToString(), CultureInfo.InvariantCulture); var v = new Sql8rView(vName, sName, isSystemObject, objectId); DataRow[] rows = dtColumns.Select(string.Format("objectId = {0}", objectId)); foreach (DataRow drColumn in rows) { Sql8rColumn c = addColumn(drColumn); if (!v.Columns.ContainsKey(c.ObjectId)) { v.Columns.Add(c.ObjectId, c); } } return(v); }
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 GetAlterColumnQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, Sql8rColumn column) { 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 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 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); }