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);
}
