List <string> IPlugin.GetPrimaryKeyColumns(string database, string table) { IDataReader reader = null; List <string> primaryKeys = new List <string>(); try { string query = "EXEC sp_pkeys '" + table + "'"; IDbCommand cmd = SqlAzurePlugin.CreateCommand(query, this.context.ConnectionString); reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { primaryKeys.Add(reader.GetString(3)); } } catch (Exception ex) { string s = ex.Message; } finally { if (reader != null) { reader.Close(); } } return(primaryKeys); }
static internal IDbCommand CreateCommand(string commandText, string connStr) { IDbCommand cmd = new SqlCommand(commandText); IDbConnection cn = SqlAzurePlugin.CreateConnection(connStr); cmd.Connection = cn; cn.Open(); return(cmd); }
DataTable IPlugin.GetTableIndexes(string database, string table) { IDataReader reader = null; DataTable metaData = new DataTable(); try { metaData = context.CreateIndexesDataTable(); string query = "SELECT * FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME='" + table + "'"; IDbCommand cmd = SqlAzurePlugin.CreateCommand(query, this.context.ConnectionString); reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { DataRow row = metaData.NewRow(); metaData.Rows.Add(row); row["TABLE_NAME"] = reader["TABLE_NAME"]; row["INDEX_NAME"] = reader["INDEX_NAME"]; row["UNIQUE"] = reader["UNIQUE"]; row["CLUSTERED"] = reader["CLUSTERED"]; row["AUTO_UPDATE"] = reader["AUTO_UPDATE"]; row["SORT_BOOKMARKS"] = reader["SORT_BOOKMARKS"]; row["FILTER_CONDITION"] = reader["FILTER_CONDITION"]; row["NULL_COLLATION"] = reader["NULL_COLLATION"]; row["INITIAL_SIZE"] = reader["INITIAL_SIZE"]; row["CARDINALITY"] = Convert.ToDecimal(reader["CARDINALITY"]); row["COLLATION"] = reader["COLLATION"]; row["COLUMN_NAME"] = reader["COLUMN_NAME"]; row["FILL_FACTOR"] = reader["FILL_FACTOR"]; row["AUTO_UPDATE"] = reader["AUTO_UPDATE"]; row["PRIMARY_KEY"] = reader["PRIMARY_KEY"]; row["NULLS"] = reader["NULLS"]; row["ORDINAL_POSITION"] = reader["ORDINAL_POSITION"]; } } catch (Exception ex) { string s = ex.Message; } finally { if (reader != null) { reader.Close(); } } return(metaData); }
public string GetDatabaseName() { IDbConnection cn = SqlAzurePlugin.CreateConnection(context.ConnectionString); string dbName = cn.Database; int index = dbName.LastIndexOfAny(new char[] { '\\' }); if (index >= 0) { dbName = dbName.Substring(index + 1); } return(dbName); }
DataTable IPlugin.GetViews(string database) { IDataReader reader = null; DataTable metaData = new DataTable(); try { metaData = context.CreateTablesDataTable(); string query = "SELECT * FROM INFORMATION_SCHEMA.VIEWS"; IDbCommand cmd = SqlAzurePlugin.CreateCommand(query, this.context.ConnectionString); reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { DataRow row = metaData.NewRow(); metaData.Rows.Add(row); row["TABLE_CATALOG"] = reader.GetValue(0); row["TABLE_SCHEMA"] = reader.GetValue(1); row["TABLE_NAME"] = reader.GetValue(2); row["VIEW_TEXT"] = reader.GetValue(3); string scratch = (string)row["IS_UPDATABLE"]; row["IS_UPDATABLE"] = (scratch == "NO") ? false : true; } } catch (Exception ex) { string s = ex.Message; } finally { if (reader != null) { reader.Close(); } } return(metaData); }
DataTable IPlugin.GetTables(string database) { IDataReader reader = null; DataTable metaData = new DataTable(); try { metaData = context.CreateTablesDataTable(); // string query = "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='TABLE'"; string query = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ='BASE TABLE'"; IDbCommand cmd = SqlAzurePlugin.CreateCommand(query, this.context.ConnectionString); reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { DataRow row = metaData.NewRow(); metaData.Rows.Add(row); row["TABLE_CATALOG"] = reader.GetValue(0); row["TABLE_SCHEMA"] = reader.GetValue(1); row["TABLE_NAME"] = reader.GetValue(2); } } catch (Exception ex) { string s = ex.Message; } finally { if (reader != null) { reader.Close(); } } return(metaData); }
public string GetFullDatabaseName() { IDbConnection cn = SqlAzurePlugin.CreateConnection(context.ConnectionString); return(cn.Database); }
private void LoadForeignKeysPartTwo(DataTable metaData, string table) { IDataReader fk = null; IDataReader pCols = null; IDataReader fCols = null; string scratch = ""; try { //string query = "SELECT INDEX_NAME FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME='" + table + "' AND PRIMARY_KEY=1"; string query = "EXEC sp_pkeys '" + table + "'"; fk = SqlAzurePlugin.CreateCommand(query, this.context.ConnectionString).ExecuteReader(CommandBehavior.CloseConnection); string pkName = ""; // Get primary key name if (fk.Read()) { pkName = (string)fk.GetValue(5); fk.Close(); } else { return; } // Got it query = "SELECT tc.*, rc.UPDATE_RULE, rc.DELETE_RULE, rc.UNIQUE_CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc " + "JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON tc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME " + "WHERE tc.CONSTRAINT_TYPE='FOREIGN KEY' AND rc.UNIQUE_CONSTRAINT_NAME = '" + pkName + "'"; fk = SqlAzurePlugin.CreateCommand(query, this.context.ConnectionString).ExecuteReader(CommandBehavior.CloseConnection); while (fk.Read()) { //--------------------------------------- // Get the Primary Key and Columns //--------------------------------------- query = "SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME='" + fk["UNIQUE_CONSTRAINT_NAME"] + "'"; pCols = SqlAzurePlugin.CreateCommand(query, this.context.ConnectionString).ExecuteReader(CommandBehavior.CloseConnection); //--------------------------------------- // Get the Foreign Key Columns //--------------------------------------- query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME = '" + fk["CONSTRAINT_NAME"] + "'"; fCols = SqlAzurePlugin.CreateCommand(query, this.context.ConnectionString).ExecuteReader(CommandBehavior.CloseConnection); while (pCols.Read() && fCols.Read()) { DataRow row = metaData.NewRow(); metaData.Rows.Add(row); // The main Information ... row["PK_TABLE_CATALOG"] = DBNull.Value; row["PK_TABLE_SCHEMA"] = DBNull.Value; row["FK_TABLE_CATALOG"] = DBNull.Value; row["FK_TABLE_SCHEMA"] = DBNull.Value; row["FK_TABLE_NAME"] = fk["TABLE_NAME"]; row["PK_TABLE_NAME"] = pCols["TABLE_NAME"]; row["ORDINAL"] = 0; row["FK_NAME"] = fk["CONSTRAINT_NAME"]; row["UPDATE_RULE"] = fk["UPDATE_RULE"]; row["DELETE_RULE"] = fk["DELETE_RULE"]; scratch = (string)fk["IS_DEFERRABLE"]; bool isDeferrable = (scratch == "NO") ? false : true; scratch = (string)fk["INITIALLY_DEFERRED"]; bool initiallyDeferred = (scratch == "NO") ? false : true; if (isDeferrable) { row["DEFERRABILITY"] = initiallyDeferred ? 1 : 2; } else { row["DEFERRABILITY"] = 3; } row["PK_NAME"] = pCols["CONSTRAINT_NAME"]; row["PK_COLUMN_NAME"] = pCols["COLUMN_NAME"]; row["FK_COLUMN_NAME"] = fCols["COLUMN_NAME"]; } } } catch (Exception ex) { string s = ex.Message; } finally { if (fk != null) { fk.Close(); } if (pCols != null) { pCols.Close(); } if (fCols != null) { fCols.Close(); } } }
DataTable IPlugin.GetTableColumns(string database, string table) { IDataReader reader = null; DataTable metaData = new DataTable(); try { metaData = context.CreateColumnsDataTable(); // string query = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='" + table + "'"; string query = "exec sp_columns '" + table + "'"; IDbCommand cmd = SqlAzurePlugin.CreateCommand(query, this.context.ConnectionString); SqlCommand c = cmd as SqlCommand; DataTable t = c.Connection.GetSchema("Columns"); reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); string scratch = ""; string schema = ""; while (reader.Read()) { DataRow row = metaData.NewRow(); metaData.Rows.Add(row); row["TABLE_CATALOG"] = reader["TABLE_QUALIFIER"]; row["TABLE_SCHEMA"] = schema = (string)reader["TABLE_OWNER"]; row["TABLE_NAME"] = reader["TABLE_NAME"]; row["COLUMN_NAME"] = reader["COLUMN_NAME"]; row["ORDINAL_POSITION"] = reader["ORDINAL_POSITION"]; // row["DESCRIPTION"] = reader["DESCRIPTION"]; // Nullable scratch = (string)reader["IS_NULLABLE"]; row["IS_NULLABLE"] = (scratch == "NO") ? false : true; // Column's Default value object o = reader["COLUMN_DEF"]; if (o != DBNull.Value) { row["COLUMN_HASDEFAULT"] = true; row["COLUMN_DEFAULT"] = o; } else { row["COLUMN_HASDEFAULT"] = false; } string type = (string)reader["TYPE_NAME"]; type = type.Replace("identity", ""); type = type.Trim(); int charMax = 0; int precision = 0; short scale = 0; if (reader["CHAR_OCTET_LENGTH"] != DBNull.Value) { charMax = (int)reader["PRECISION"]; } if (reader["PRECISION"] != DBNull.Value) { precision = (int)reader["PRECISION"]; } if (reader["SCALE"] != DBNull.Value) { scale = (short)reader["SCALE"]; } switch (type) { case "decimal": case "float": case "real": case "numeric": case "money": case "smallmoney": case "int": case "smallint": case "tinyint": case "bigint": row["NUMERIC_PRECISION"] = precision; if (scale != 0) { row["NUMERIC_SCALE"] = scale; } break; } row["TYPE_NAME"] = type; row["TYPE_NAME_COMPLETE"] = this.GetDataTypeNameComplete(type, charMax, precision, scale); row["CHARACTER_MAXIMUM_LENGTH"] = charMax; row["IS_COMPUTED"] = (type == "timestamp") ? true : false; row["IS_CONCURRENCY"] = (type == "rowversion" || type == "timestamp") ? true : false; } LoadAutoKeyInfo(metaData, database, table, schema); } catch (Exception ex) { string s = ex.Message; } finally { if (reader != null) { reader.Close(); } } return(metaData); }