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.SYSTEM_COLUMNS WHERE TABLE_NAME='" + table + "'" + " AND IS_PRIMARY_KEY = 1";
IDbCommand cmd = EffiProzPlugin.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.SYSTEM_INDEXINFO WHERE TABLE_NAME='" + table + "'";
IDbCommand cmd = EffiProzPlugin.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["NON_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"] = reader["CARDINALITY"] != DBNull.Value ? Convert.ToDecimal(reader["CARDINALITY"]) : 0.0M;
//row["COLLATION"] = reader["ASC_OR_DESC"];
row["COLUMN_NAME"] = reader["COLUMN_NAME"];
//row["FILL_FACTOR"] = reader["FILL_FACTOR"];
//row["AUTO_UPDATE"] = reader["AUTO_UPDATE"];
row["PRIMARY_KEY"] = reader["PRIMARY_INDEX"];
//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.GetViews(string database)
{
IDataReader reader = null;
DataTable metaData = new DataTable();
try
{
metaData = context.CreateTablesDataTable();
string query = "SELECT * FROM INFORMATION_SCHEMA.VIEWS";
IDbCommand cmd = EffiProzPlugin.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.SYSTEM_TABLES WHERE TABLE_TYPE='TABLE'";
IDbCommand cmd = EffiProzPlugin.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;
string scratch = "";
try
{
string query = "SELECT PK_NAME FROM INFORMATION_SCHEMA.SYSTEM_PRIMARYKEYS WHERE TABLE_NAME='" + table + "'";
fk = EffiProzPlugin.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 = EffiProzPlugin.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 = EffiProzPlugin.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 = EffiProzPlugin.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.SYSTEM_COLUMNS WHERE TABLE_NAME='" + table + "'";
IDbCommand cmd = EffiProzPlugin.CreateCommand(query, this.context.ConnectionString);
System.Data.Common.DbConnection c = (System.Data.Common.DbConnection)cmd.Connection;
DataTable t = c.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_CAT"];
row["TABLE_SCHEMA"] = schema = (string)reader["TABLE_SCHEM"];
row["TABLE_NAME"] = reader["TABLE_NAME"];
row["COLUMN_NAME"] = reader["COLUMN_NAME"];
row["ORDINAL_POSITION"] = reader["ORDINAL_POSITION"];
// row["DESCRIPTION"] = reader["DESCRIPTION"];
// Nullable
row["IS_NULLABLE"] = reader["IS_NULLABLE"];
// Column's Default value
object o = reader["COLUMN_DEF"];
if (o != DBNull.Value)
{
row["COLUMN_HASDEFAULT"] = true;
row["COLUMN_DEFAULT"] = scratch;
}
else
{
row["COLUMN_HASDEFAULT"] = false;
}
string type = (string)reader["TYPE_NAME"];
int charMax = 0;
int precision = 0;
int scale = 0;
if (reader["CHAR_OCTET_LENGTH"] != DBNull.Value)
{
charMax = (int)reader["CHAR_OCTET_LENGTH"];
}
if (reader["NUM_PREC_RADIX"] != DBNull.Value)
{
precision = (int)reader["NUM_PREC_RADIX"];
}
if (reader["DECIMAL_DIGITS"] != DBNull.Value)
{
scale = (int)reader["DECIMAL_DIGITS"];
}
row["TYPE_NAME"] = type;
row["TYPE_NAME_COMPLETE"] = this.GetDataTypeNameComplete(type, charMax, precision, scale);
row["NUMERIC_PRECISION"] = precision;
row["NUMERIC_SCALE"] = scale;
row["CHARACTER_OCTET_LENGTH"] = charMax;
row["AUTO_KEY_SEED"] = reader["IDENTITY_START"];
row["AUTO_KEY_INCREMENT"] = reader["IDENTITY_INCREMENT"];
row["IS_AUTO_KEY"] = reader["IS_AUTOINCREMENT"];
// 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);
}