List <string> IPlugin.GetPrimaryKeyColumns(string database, string table) { IDataReader reader = null; List <string> primaryKeys = new List <string>(); try { string query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.INDEXES where Table_Name='" + table + "' AND PRIMARY_KEY=1"; IDbCommand cmd = SqlCePlugin.CreateCommand(query, this.context.ConnectionString); reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { primaryKeys.Add(reader.GetString(0)); } } catch (Exception ex) { string s = ex.Message; } finally { if (reader != null) { reader.Close(); } } return(primaryKeys); }
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 = SqlCePlugin.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); }
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'"; IDbCommand cmd = SqlCePlugin.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); }
private void LoadForeignKeysPartTwo(DataTable metaData, string table) { IDataReader fk = null; IDataReader pCols = null; IDataReader fCols = null; try { string query = "SELECT INDEX_NAME FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME='" + table + "' AND PRIMARY_KEY=1"; fk = SqlCePlugin.CreateCommand(query, this.context.ConnectionString).ExecuteReader(CommandBehavior.CloseConnection); string pkName = ""; // Get primary key name if (fk.Read()) { pkName = (string)fk.GetValue(0); 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 = SqlCePlugin.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 = SqlCePlugin.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 = SqlCePlugin.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"]; bool isDeferrable = (bool)fk["IS_DEFERRABLE"]; bool initiallyDeferred = (bool)fk["INITIALLY_DEFERRED"]; 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 + "'"; IDbCommand cmd = SqlCePlugin.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["TABLE_CATALOG"]; row["TABLE_SCHEMA"] = reader["TABLE_SCHEMA"]; row["TABLE_NAME"] = reader["TABLE_NAME"]; row["COLUMN_NAME"] = reader["COLUMN_NAME"]; row["ORDINAL_POSITION"] = reader["ORDINAL_POSITION"]; row["DESCRIPTION"] = reader["DESCRIPTION"]; if (reader["IS_NULLABLE"] != DBNull.Value) { string isNullable = reader.GetString(reader.GetOrdinal("IS_NULLABLE")); isNullable = isNullable.ToUpper(); row["IS_NULLABLE"] = (isNullable == "NO") ? false : true; } if (reader.GetBoolean(reader.GetOrdinal("COLUMN_HASDEFAULT"))) { row["COLUMN_HASDEFAULT"] = true; row["COLUMN_DEFAULT"] = reader["COLUMN_DEFAULT"]; } if (reader["AUTOINC_INCREMENT"] != DBNull.Value) { row["IS_AUTO_KEY"] = true; row["AUTO_KEY_SEED"] = Convert.ToInt32(reader["AUTOINC_SEED"]); row["AUTO_KEY_INCREMENT"] = Convert.ToInt32(reader["AUTOINC_INCREMENT"]); } string type = (string)reader["DATA_TYPE"]; int charMax = 0; short precision = 0; short scale = 0; if (reader["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value) { charMax = (int)reader["CHARACTER_MAXIMUM_LENGTH"]; } if (reader["NUMERIC_PRECISION"] != DBNull.Value) { precision = (short)reader["NUMERIC_PRECISION"]; } if (reader["NUMERIC_SCALE"] != DBNull.Value) { scale = (short)reader["NUMERIC_SCALE"]; } row["TYPE_NAME"] = type; row["TYPE_NAME_COMPLETE"] = this.GetDataTypeNameComplete(type, charMax, precision, scale); row["NUMERIC_PRECISION"] = precision; row["NUMERIC_SCALE"] = scale; row["CHARACTER_MAXIMUM_LENGTH"] = charMax; row["IS_COMPUTED"] = (type == "timestamp") ? true : false; row["IS_CONCURRENCY"] = (type == "rowversion" || type == "timestamp") ? true : false; } } catch (Exception ex) { string s = ex.Message; } finally { if (reader != null) { reader.Close(); } } return(metaData); }