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);
}
public string GetDatabaseName()
{
IDbConnection cn = SqlCePlugin.CreateConnection(context.ConnectionString);
string dbName = cn.Database;
int index = dbName.LastIndexOfAny(new char[] { '\\' });
if (index >= 0)
{
dbName = dbName.Substring(index + 1);
}
return(dbName);
}
static internal IDbCommand CreateCommand(string commandText, string connStr)
{
IDbCommand cmd = _appDomain.CreateInstanceAndUnwrap
(
"System.Data.SqlServerCe," + GetAssemblyVersion(connStr),
"System.Data.SqlServerCe.SqlCeCommand",
false,
BindingFlags.OptionalParamBinding,
null,
new object[] { commandText },
null,
null,
null
) as IDbCommand;
IDbConnection cn = SqlCePlugin.CreateConnection(connStr);
cmd.Connection = cn;
cn.Open();
return(cmd);
}
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);
}
public string GetFullDatabaseName()
{
IDbConnection cn = SqlCePlugin.CreateConnection(context.ConnectionString);
return(cn.Database);
}
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);
}
