private void BindGrid(DbConnection conn)
{
conn.Open();
_schema = new DataSet();
var schema = conn.GetSchema();
foreach (DataRow dataRow in schema.Rows)
{
var tableName = dataRow["CollectionName"].ToString();
if(!_schema.Tables.Contains(tableName))
{
var dt = conn.GetSchema(tableName);
dt.TableName = tableName;
_schema.Tables.Add(dt);
}
}
conn.Close();
dgSchema.DataSource = _schema.Tables[0];
cbTable.DataSource = _schema.Tables[0];
cbTable.DisplayMember = "CollectionName";
cbTable.ValueMember = "CollectionName";
_previousWidth = dgSchema.Width;
_previousState = WindowState;
}
internal Table(string tableName, DbConnection connection, TableDesignerDoc owner)
{
_owner = owner;
_oldname = tableName;
_connection = connection;
_name = tableName;
_owner.Name = _name;
_catalog = _connection.Database; // main
ReloadDefinition();
if (_key == null) _key = new PrimaryKey(_connection, this, null);
if (_exists)
{
using (DataTable tbl = connection.GetSchema("ForeignKeys", new string[] { Catalog, null, Name }))
{
foreach (DataRow row in tbl.Rows)
{
_fkeys.Add(new ForeignKey(connection, this, row));
_oldfkeys.Add(new ForeignKey(connection, this, row));
}
}
}
using (DataTable tbl = connection.GetSchema("Columns", new string[] { Catalog, null, Name }))
{
foreach (DataRow row in tbl.Rows)
{
_columns.Add(new Column(row, this));
}
}
}
protected override DataTable StoredProcedureArguments(string storedProcedureName, DbConnection connection)
{
//it's not reported in the MetaDataCollections, but it is there.
var dt = connection.GetSchema("PROCEDUREPARAMETERS", new[] { null, null, storedProcedureName, null });
dt.TableName = ProcedureParametersCollectionName;
return dt;
}
/// <summary>
/// pulls a list of datatable names from the database and caches them.
/// (this list does not refresh, because this reader is read-only)
/// </summary>
/// <returns></returns>
public List <string> TableNames()
{
if (!IsOpen())
{
return(null);
}
if ((_tablenames != null) && (_tablenames.Count > 0))
{
return(_tablenames);
}
DataTable dtTables = _conn.GetSchema("Tables");
if ((dtTables != null) && (dtTables.Rows.Count > 0))
{
_tablenames = new List <string>();
foreach (DataRow row in dtTables.Rows)
{
string name = (row["TABLE_NAME"] as string);
if (name.StartsWith("MSys"))
{
continue;
}
_tablenames.Add(name);
}
return(_tablenames);
}
return(null);
}
// SchemaReader.ReadSchema
public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
{
var result = new Tables();
var cmd = factory.CreateCommand();
cmd.Connection = connection;
cmd.CommandText = TABLE_SQL;
//pull the tables in a reader
using (cmd)
{
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Table tbl = new Table();
tbl.Name = rdr["TABLE_NAME"].ToString();
tbl.Schema = rdr["TABLE_SCHEMA"].ToString();
tbl.IsView = String.Compare(rdr["TABLE_TYPE"].ToString(), "View", true) == 0;
tbl.CleanName = CleanUp(tbl.Name);
tbl.ClassName = Inflector.MakeSingular(tbl.CleanName);
result.Add(tbl);
}
}
}
//this will return everything for the DB
var schema = connection.GetSchema("COLUMNS");
//loop again - but this time pull by table name
foreach (var item in result)
{
item.Columns = new List<Column>();
//pull the columns from the schema
var columns = schema.Select("TABLE_NAME='" + item.Name + "'");
foreach (var row in columns)
{
var type = GetPropertyType(row);
Column col = new Column();
col.Name = row["COLUMN_NAME"].ToString();
col.PropertyName = CleanUp(col.Name);
col.PropertyType = type;
col.CustomType = type == null
? row["DATA_TYPE"].ToString().ToLowerInvariant()
: null;
col.Size = GetDatatypeSize(row["DATA_TYPE"].ToString());
col.Precision = GetDatatypePrecision(row["DATA_TYPE"].ToString());
col.IsNullable = row["IS_NULLABLE"].ToString() == "YES";
col.IsPrimaryKey = row["COLUMN_KEY"].ToString() == "PRI";
col.IsAutoIncrement = row["extra"].ToString().ToLower().IndexOf("auto_increment") >= 0;
item.Columns.Add(col);
}
}
return result;
}
ExamineDatabaseDialog(DbConnection dbConnection)
{
this.dbConnection = dbConnection;
InitializeComponent();
Collections = new ObservableCollection<string>(dbConnection.GetSchema().AsEnumerable().Select(row => row["CollectionName"]).Cast<string>());
}
protected override void LoadTables(DbConnection Connection, Dictionary<string, TableInfo> Tables)
{
base.LoadTables(Connection, Tables);
var dt = Connection.GetSchema("IndexColumns");
foreach (DataRow dr in dt.Rows)
if (Convert.ToInt32(dr[8]) == 56)
Tables[dr[5] as string].Columns[dr[6] as string].IsKey = true;
}
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
public DAODatabaseHelper(DbConnection connection)
{
_connection = connection;
DbTypesConverter.ProviderTypeMap = BuildProviderTypeMap();
_tableDefs = new TableDefsHelper(connection);
DataTable dbTables = _connection.GetSchema("Tables");
foreach (DataRow r in dbTables.Rows)
{
TableDefHelper tableDef = new TableDefHelper(r["TABLE_NAME"].ToString(), r["TABLE_NAME"].ToString(), true);
AddColumnsToTableDef(tableDef);
AddIndexesToTableDef(tableDef);
_tableDefs.Add(tableDef,false);
}
}
public static DataTable GetSchemaDataTable(this DbConnection dbConnection, string collectionName, string[]?restrictionValues = null)
{
var doOpenClose = dbConnection.State != ConnectionState.Open;
if (doOpenClose)
{
dbConnection.Open();
}
var dt = dbConnection.GetSchema(collectionName, restrictionValues);
if (doOpenClose)
{
dbConnection.Close();
}
return(dt);
}
public string GetSQLCreateColumnsInTable(DbConnection dbConnection, string tableName)
{
string sqlColumns = "";
string convertedColumnName;
DataTable dataTable = dbConnection.GetSchema("COLUMNS", new string[] { null, null, tableName, null });
foreach (DataRow dataRow in dataTable.Rows)
{
convertedColumnName = convertName.Column(tableName, dataRow["COLUMN_NAME"].ToString());
sqlColumns += GetSQLColumn(convertedColumnName, dataRow);
}
sqlColumns = sqlColumns.Remove(sqlColumns.Length - 1);
return string.Format("({0})", sqlColumns);
}
private static string EnsureTableName(DbConnection connection, string tabName)
{
if (string.IsNullOrEmpty(tabName))
{
DataTable worksheets = connection.GetSchema("Tables");
foreach (DataRow row in worksheets.Rows)
{
tabName = (string)row["TABLE_NAME"];
if (tabName.EndsWith("$") || tabName.EndsWith("$'"))
return tabName;
}
}
else if (!tabName.EndsWith("$"))
tabName += "$";
return tabName;
}
internal View(string viewName, DbConnection connection, ViewDesignerDoc parent)
{
_owner = parent;
_name = viewName;
_oldname = viewName;
_catalog = connection.Database;
_connection = connection;
_owner.Name = _name;
if (String.IsNullOrEmpty(viewName) == false)
{
using (DataTable tbl = connection.GetSchema("Views", new string[] { Catalog, null, Name }))
{
if (tbl.Rows.Count > 0)
{
_sql = tbl.Rows[0]["VIEW_DEFINITION"].ToString();
StringBuilder builder = new StringBuilder();
builder.Append(_sql);
builder.AppendLine(";");
_triggers.Clear();
_oldtriggers.Clear();
using (DataTable ttbl = _connection.GetSchema("Triggers", new string[] { Catalog, null, Name }))
{
foreach (DataRow row in ttbl.Rows)
{
ViewTrigger t = new ViewTrigger(this, row);
_triggers.Add(t);
_oldtriggers.Add(((ICloneable)t).Clone() as ViewTrigger);
builder.AppendFormat("{0};\r\n", t.OriginalSql);
}
}
_oldsql = builder.ToString();
}
else
{
_oldname = null;
}
}
}
}
/// <summary>
/// Extracts the command information from the command object and add specific information based on the factory being use.
/// </summary>
/// <param name="connection">The connection to extract the information from.</param>
/// <param name="pivotCommand">Command to be processed.</param>
private static void DeriveParametersFromProcedureCode(DbConnection connection, DbCommand pivotCommand)
{
DataTable dbObjects = connection.GetSchema("Procedures", new String[] { null, null, pivotCommand.CommandText });
if (dbObjects.Rows.Count > 0)
{
String procText = dbObjects.Rows[0]["PROCEDURE_DEFINITION"].ToString();
String[] procLines = procText.Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
if ((procLines.Length > 0) && procLines[0].StartsWith("PARAMETERS", StringComparison.InvariantCultureIgnoreCase))
{
procLines = procLines[0].ToUpper().Replace("PARAMETERS", "").Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
foreach (String paraminfo in procLines)
{
string[] param = paraminfo.Split(" ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
DbParameter parameter = pivotCommand.CreateParameter();
parameter.ParameterName = param[0];
parameter.DbType = getDbType(param[1]);
pivotCommand.Parameters.Add(parameter);
}
}
}
}
public static Version?GetVersion(this DbConnection conn)
{
Version?version = null;
var isNotOpen = conn.State != ConnectionState.Open;
try {
if (isNotOpen)
{
conn.Open();
}
Version.TryParse(conn.ServerVersion?.Split(' ')[0], out version);
if (version == null)
{
version = new DataSourceInformationRow(conn.GetSchema(DbMetaDataCollectionNames.DataSourceInformation).Rows[0]).Version;
}
if (isNotOpen)
{
conn.Close();
}
} catch {
}
return(version);
}
private IEnumerable<string> GetDatabases(DbConnection connection)
{
if (connection != null) {
connection.Open();
using (DataTable databases = connection.GetSchema("Databases")) {
foreach (DataRow row in databases.Rows)
yield return row[0].ToString();
}
}
}
private void CreateRestrictions(DbConnection connection)
{
_restrictions = new DataTable("Restrictions");
_restrictions.Locale = CultureInfo.InvariantCulture;
_restrictions.Columns.Add("CollectionName", typeof(string));
_restrictions.Columns.Add("RestrictionNumber", typeof(int));
_restrictions.Columns.Add("RestrictionName", typeof(string));
try
{
var collections = connection.GetSchema(DbMetaDataCollectionNames.MetaDataCollections);
foreach (DataRow row in collections.Rows)
{
//every collections has catalog/ owner/ table restrictions
_restrictions.Rows.Add(row["CollectionName"].ToString(), 0, "OWNER");
_restrictions.Rows.Add(row["CollectionName"].ToString(), 1, "NA");
_restrictions.Rows.Add(row["CollectionName"].ToString(), 2, "TABLE");
}
}
catch (NullReferenceException)
{
Console.WriteLine("Could not load metadataCollections"); //NpgSql again
}
}
private void LoadRestrictions(DbConnection connection)
{
try
{
_restrictions = connection.GetSchema(DbMetaDataCollectionNames.Restrictions);
}
catch (NotSupportedException)
{
//SqlLite provider doesn't support this
//recreate it- the first is always
CreateRestrictions(connection);
}
catch (NullReferenceException)
{
//NpgSql error
CreateRestrictions(connection);
}
}
protected ICollection<Column> CreateColumnsCollection(DbConnection connection, string[] restrictions)
{
// get column schema of specific table
var columnSchemas = connection.GetSchema("Columns", restrictions);
var columnsCollection = new List<Column>();
foreach (DataRow columSchemasRow in columnSchemas.Rows)
{
var columnName = columSchemasRow["COLUMN_NAME"].ToString();
var columnType = columSchemasRow["DATA_TYPE"].ToString();
columnsCollection.Add(new Column(columnName, columnType, -1));
}
return columnsCollection;
}
internal Index(DbConnection cnn, Table table, DataRow index)
{
_table = table;
if (index != null)
{
_name = index["INDEX_NAME"].ToString();
_unique = (bool)index["UNIQUE"];
_definition = index["INDEX_DEFINITION"].ToString();
using (DataTable tbl = cnn.GetSchema("IndexColumns", new string[] { table.Catalog, null, table.Name, Name }))
{
foreach (DataRow row in tbl.Rows)
{
_columns.Add(new IndexColumn(this, row));
}
}
}
}
public DbHelper(ConnectionStringSettings connectionString)
{
var file = connectionString.ElementInformation.Source;
if ("web.connections.config".Equals(Path.GetFileName(file), StringComparison.InvariantCultureIgnoreCase))
{
file = Path.Combine(Path.GetDirectoryName(file), "web.config");
}
var xconfig = XDocument.Load(file);
var provider = xconfig.XPathSelectElement("/configuration/system.data/DbProviderFactories/add[@invariant='" + connectionString.ProviderName + "']");
factory = (DbProviderFactory)Activator.CreateInstance(Type.GetType(provider.Attribute("type").Value, true));
builder = factory.CreateCommandBuilder();
connect = factory.CreateConnection();
connect.ConnectionString = connectionString.ConnectionString;
connect.Open();
mysql = connectionString.ProviderName.ToLower().Contains("mysql");
if (mysql)
{
CreateCommand("set @@session.sql_mode = concat(@@session.sql_mode, ',NO_AUTO_VALUE_ON_ZERO')").ExecuteNonQuery();
}
columns = connect.GetSchema("Columns");
whereExceptions["calendar_calendar_item"] = " where calendar_id in (select id from calendar_calendars where tenant = {0}) ";
whereExceptions["calendar_calendar_user"] = "******";
whereExceptions["calendar_event_item"] = " inner join calendar_events on calendar_event_item.event_id = calendar_events.id where calendar_events.tenant = {0} ";
whereExceptions["calendar_event_user"] = "******";
whereExceptions["crm_entity_contact"] = " inner join crm_contact on crm_entity_contact.contact_id = crm_contact.id where crm_contact.tenant_id = {0} ";
whereExceptions["crm_entity_tag"] = " inner join crm_tag on crm_entity_tag.tag_id = crm_tag.id where crm_tag.tenant_id = {0} ";
whereExceptions["files_folder_tree"] = " inner join files_folder on folder_id = id where tenant_id = {0} ";
whereExceptions["forum_answer_variant"] = " where answer_id in (select id from forum_answer where tenantid = {0})";
whereExceptions["forum_topic_tag"] = " where topic_id in (select id from forum_topic where tenantid = {0})";
whereExceptions["forum_variant"] = " where question_id in (select id from forum_question where tenantid = {0})";
whereExceptions["projects_project_participant"] = " inner join projects_projects on projects_project_participant.project_id = projects_projects.id where projects_projects.tenant_id = {0} ";
whereExceptions["projects_following_project_participant"] = " inner join projects_projects on projects_following_project_participant.project_id = projects_projects.id where projects_projects.tenant_id = {0} ";
whereExceptions["projects_project_tag"] = " inner join projects_projects on projects_project_tag.project_id = projects_projects.id where projects_projects.tenant_id = {0} ";
whereExceptions["tenants_tenants"] = " where id = {0}";
whereExceptions["core_acl"] = " where tenant = {0} or tenant = -1";
whereExceptions["core_subscription"] = " where tenant = {0} or tenant = -1";
whereExceptions["core_subscriptionmethod"] = " where tenant = {0} or tenant = -1";
}
private static string EnsureTableName(DbConnection connection, string tabName)
{
if (string.IsNullOrEmpty(tabName))
{
//use GetSchema to find the first sheet == table name
DataTable worksheets = connection.GetSchema("Tables");
foreach (DataRow row in worksheets.Rows)
{
//this can also return Excel named ranges
tabName = (string)row["TABLE_NAME"];
//so look for sheets (excel puts $ after the name and may single-quote the name)
if (tabName.EndsWith("$") || tabName.EndsWith("$'"))
return tabName;
//otherwise we'll fall through with whatever we find
}
}
//they supplied a worksheet name; ensure always has $ suffix
else if (!tabName.EndsWith("$"))
tabName += "$";
return tabName;
}
private static IEnumerable<string> GetTableNames(DbConnection memory)
{
var dt = memory.GetSchema("Tables");
return from DataRow row in dt.Rows
select (string) row["TABLE_NAME"];
}
protected virtual void LoadTables(DbConnection Connection, Dictionary<string, TableInfo> Tables)
{
var dt = Connection.GetSchema("Tables");
foreach (DataRow r in dt.Rows)
{
var info = new TableInfo();
info.Name = r[TableNameColumnIndex] as string;
Tables.Add(info.Name, info);
}
dt = Connection.GetSchema("Columns");
foreach (DataRow r in dt.Rows)
{
var cinfo = new ColumnInfo();
cinfo.TableName = r[2] as string;
cinfo.Name = r[3] as string;
LoadColumn(r, cinfo);
cinfo.Type = TranslateType(cinfo.DbType);
Tables[cinfo.TableName].Columns.Add(cinfo.Name, cinfo);
}
}
/// <summary>
/// Get all the stored procedures (owner required for Oracle- otherwise null).
/// </summary>
/// <param name="connection">The connection.</param>
/// <returns></returns>
protected virtual DataTable StoredProcedures(DbConnection connection)
{
string collectionName = ProceduresCollectionName;
if (!SchemaCollectionExists(connection, collectionName)) return CreateDataTable(collectionName);
string[] restrictions = SchemaRestrictions.ForOwner(connection, collectionName);
return connection.GetSchema(collectionName, restrictions);
}
protected override string GetDbProviderManifestToken(DbConnection connection)
{
if (connection == null)
throw new ArgumentNullException("connection");
DataTable dsInfo = connection.GetSchema(DbMetaDataCollectionNames.DataSourceInformation);
if (dsInfo.Rows.Count == 0)
return "0";
string version = dsInfo.Rows[0].Field<string>("DataSourceInternalProductVersion");
return version;
}
private static List<IndexSchema> GetIndexes(DbConnection con, TableSchema eTable)
{
var aStore = new List<IndexSchema>();
string[] restrict3 = {null, null, eTable.Name};
string[] restrict4 = {null, null, eTable.Name, null};
// INDEX_TYPE = 0 - ascending, 1 - descending
restrict4[0] = null;
restrict4[1] = null;
restrict4[2] = eTable.Name;
restrict4[3] = null;
var dtShema = con.GetSchema("Indexes", restrict3);
aStore.Clear();
for (var x = 0; x < dtShema.Rows.Count; x++)
{
var cRow = dtShema.Rows[x];
var cName = cRow["INDEX_NAME"].ToString();
if (eTable.IsKeyExist(cName, ConstraintType.Unique) ||
eTable.IsKeyExist(cName, ConstraintType.KeyPrimary) ||
eTable.IsKeyExist(cName, ConstraintType.KeyForeign))
continue;
var eIndex = new IndexSchema();
var columns = "";
eIndex.Name = cName;
eIndex.Unique = Convert.ToBoolean(cRow["IS_UNIQUE"], CultureInfo.InvariantCulture);
if (cRow["INDEX_TYPE"] == DBNull.Value)
eIndex.Sort = SortOrder.Ascending;
else
eIndex.Sort = Convert.ToInt32(cRow["INDEX_TYPE"], CultureInfo.InvariantCulture) == 0
? SortOrder.Ascending
: SortOrder.Descending;
eIndex.IsActive = !Convert.ToBoolean(cRow["IS_INACTIVE"], CultureInfo.InvariantCulture);
restrict4[3] = cName;
var dtShemaCols = con.GetSchema("IndexColumns", restrict4);
var dtv = dtShemaCols.DefaultView;
//dtv.RowFilter = "INDEX_NAME = '" + cName + "'";
dtv.Sort = "ORDINAL_POSITION ASC";
for (var y = 0; y < dtv.Count; y++)
columns += (dtv[y]["COLUMN_NAME"] + ", ");
columns = columns.Remove(columns.Length - 2, 2);
eIndex.Columns = columns;
aStore.Add(eIndex);
}
return aStore;
}
private DataTable RunGetSchema(DbConnection connection, string collectionName, string tableName)
{
string[] restrictions = SchemaRestrictions.ForTable(connection, collectionName, tableName);
try
{
return connection.GetSchema(collectionName, restrictions);
}
catch (DbException exception)
{
//Postgresql throws this nasty error with a restriction. We'll carry on.
Console.WriteLine("Provider returned error for " + collectionName + ": " + exception.Message);
return CreateDataTable(collectionName);
}
catch (SqlNullValueException exception)
{
//MySQL can't run this without a table (it does a SHOW INDEX FROM table so you get the above error)
Console.WriteLine("Provider returned error for " + collectionName + ": " + exception.Message);
return CreateDataTable(collectionName);
}
catch (ArgumentException exception)
{
//Intersystems requires table name
Console.WriteLine("Provider returned error for " + collectionName + ": " + exception.Message);
return CreateDataTable(collectionName);
}
}
private static DataTable MetadataCollections(DbConnection connection)
{
try
{
return connection.GetSchema(DbMetaDataCollectionNames.MetaDataCollections);
}
catch (Exception e)
{
//some versions of NpgSql may throw a NullReferenceException
Console.WriteLine(e);
var metadataCollections = CreateDataTable("Metadata");
metadataCollections.Columns.Add("CollectionName", typeof(string));
return metadataCollections;
}
}
private static List<KeySchema> GetKeys(DbConnection con, SchemaNamedElement eTable)
{
var aStore = new List<KeySchema>();
var aHash = new List<string>();
string[] restrict3 = {null, null, null};
#region Primary
restrict3[0] = null;
restrict3[1] = null;
restrict3[2] = eTable.Name;
var dtShema = con.GetSchema("PrimaryKeys", restrict3);
aHash.Clear();
for (var x = 0; x < dtShema.Rows.Count; x++)
{
var cRow = dtShema.Rows[x];
var cName = cRow["PK_NAME"].ToString();
if (aHash.Contains(cName))
continue;
var eKey = new KeySchema();
var columns = "";
aHash.Add(cName);
eKey.KeyType = ConstraintType.KeyPrimary;
eKey.Name = cName;
var dtv = dtShema.DefaultView;
dtv.RowFilter = "PK_NAME = '" + cName + "'";
dtv.Sort = "ORDINAL_POSITION ASC";
for (var y = 0; y < dtv.Count; y++)
columns += (dtv[y]["COLUMN_NAME"] + ", ");
columns = columns.Remove(columns.Length - 2, 2);
eKey.Columns = columns;
aStore.Add(eKey);
}
#endregion
#region Foreign
restrict3[0] = null;
restrict3[1] = null;
restrict3[2] = eTable.Name;
dtShema = con.GetSchema("ForeignKeys", restrict3);
var dtShemaCols = con.GetSchema("ForeignKeyColumns", restrict3);
for (var x = 0; x < dtShema.Rows.Count; x++)
{
var cRow = dtShema.Rows[x];
var cName = cRow["INDEX_NAME"].ToString();
var eKey = new KeySchema();
string columns = "", rcolumns = "";
eKey.KeyType = ConstraintType.KeyForeign;
eKey.Name = cName;
eKey.RelTable = cRow["REFERENCED_TABLE_NAME"].ToString();
var uRule = cRow["UPDATE_RULE"].ToString();
var dRule = cRow["DELETE_RULE"].ToString();
switch (uRule)
{
case "RESTRICT":
eKey.UpdateRule = LinkRule.None;
break;
case "CASCADE":
eKey.UpdateRule = LinkRule.Cascade;
break;
case "SET NULL":
eKey.UpdateRule = LinkRule.SetNull;
break;
case "SET DEFAULT":
eKey.UpdateRule = LinkRule.SetDefault;
break;
}
switch (dRule)
{
case "RESTRICT":
eKey.DeleteRule = LinkRule.None;
break;
case "CASCADE":
eKey.DeleteRule = LinkRule.Cascade;
break;
case "SET NULL":
eKey.DeleteRule = LinkRule.SetNull;
break;
case "SET DEFAULT":
eKey.DeleteRule = LinkRule.SetDefault;
break;
}
var dtv = dtShemaCols.DefaultView;
dtv.RowFilter = "CONSTRAINT_NAME = '" + cName + "'";
dtv.Sort = "ORDINAL_POSITION ASC";
for (var y = 0; y < dtv.Count; y++)
{
columns += (dtv[y]["COLUMN_NAME"] + ", ");
rcolumns += (dtv[y]["REFERENCED_COLUMN_NAME"] + ", ");
}
columns = columns.Remove(columns.Length - 2, 2);
rcolumns = rcolumns.Remove(rcolumns.Length - 2, 2);
eKey.Columns = columns;
eKey.RelColumns = rcolumns;
aStore.Add(eKey);
}
#endregion
#region Unique
restrict3[0] = null;
restrict3[1] = null;
restrict3[2] = eTable.Name;
dtShema = con.GetSchema("UniqueKeys", restrict3);
aHash.Clear();
for (var x = 0; x < dtShema.Rows.Count; x++)
{
var cRow = dtShema.Rows[x];
var cName = cRow["UK_NAME"].ToString();
if (aHash.Contains(cName))
continue;
var eKey = new KeySchema();
var columns = "";
aHash.Add(cName);
eKey.KeyType = ConstraintType.Unique;
eKey.Name = cName;
var dtv = dtShema.DefaultView;
dtv.RowFilter = "UK_NAME = '" + cName + "'";
dtv.Sort = "ORDINAL_POSITION ASC";
for (var y = 0; y < dtv.Count; y++)
columns += (dtv[y]["COLUMN_NAME"] + ", ");
columns = columns.Remove(columns.Length - 2, 2);
eKey.Columns = columns;
aStore.Add(eKey);
}
#endregion
return aStore;
}
private void btnOpen_Click(object sender, EventArgs e)
{
btnClose_Click(sender, null);
// main.
string sFactory = cboProvider.Text.Trim();
try {
m_provider = DbProviderFactories.GetFactory(sFactory);
}
catch (Exception ex) {
OutLog(ex.Message);
MessageBox.Show(this, ex.ToString(), this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (null == m_provider) return;
try {
// DbConnection.
try {
OutLog("Create DbConnection:");
m_conn = m_provider.CreateConnection();
OutLog(string.Format("\t{0}", m_conn.GetType().ToString()));
}
catch (Exception ex) {
OutLog(ex.Message);
MessageBox.Show(this, ex.ToString(), this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
try {
m_conn.Dispose();
}
catch (Exception ex2) {
OutLog(ex2.ToString());
}
m_conn = null;
return;
}
if (null == m_conn) return;
// open.
try {
m_conn.ConnectionString = txtConnstr.Text.Trim();
m_conn.Open();
}
catch (Exception ex) {
OutLog(ex.Message);
MessageBox.Show(this, ex.ToString(), this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
try {
m_conn.Dispose();
}
catch (Exception ex2) {
OutLog(ex2.ToString());
}
m_conn = null;
return;
}
}
finally {
if (null == m_conn) {
m_provider = null;
}
}
// done.
bool isconn = true;
btnOpen.Enabled = !isconn;
btnClose.Enabled = isconn;
btnExec.Enabled = isconn;
cboProvider.Enabled = !isconn;
txtConnstr.ReadOnly = isconn;
OutLog("Db opened.");
// show tables.
DataTable dt = m_conn.GetSchema("Tables");
grdTable.DataSource = dt;
m_CurTableName = null;
tbcInput.SelectedIndex = 1;
}
public DbHelper(int tenant, ConnectionStringSettings connectionString)
{
this.tenant = tenant;
factory = connectionString.ProviderName == "System.Data.SQLite" ? GetSQLiteFactory() : GetMySqlFactory();
builder = factory.CreateCommandBuilder();
connect = factory.CreateConnection();
connect.ConnectionString = connectionString.ConnectionString;
connect.Open();
if (factory.GetType().Name == "MySqlClientFactory")
{
CreateCommand("set @@session.sql_mode = concat(@@session.sql_mode, ',NO_AUTO_VALUE_ON_ZERO')").ExecuteNonQuery();
}
columns = connect.GetSchema("Columns");
whereExceptions["calendar_calendar_item"] = " where calendar_id in (select id from calendar_calendars where tenant = " + tenant + ") ";
whereExceptions["calendar_calendar_user"] = "******" + tenant + ") ";
whereExceptions["calendar_event_item"] = " where event_id in (select id from calendar_events where tenant = " + tenant + ") ";
whereExceptions["calendar_event_user"] = "******" + tenant + ") ";
whereExceptions["crm_entity_contact"] = " where contact_id in (select id from crm_contact where tenant_id = " + tenant + ") ";
whereExceptions["crm_entity_tag"] = " where tag_id in (select id from crm_tag where tenant_id = " + tenant + ") ";
whereExceptions["files_folder_tree"] = " where folder_id in (select id from files_folder where tenant_id = " + tenant + ") ";
whereExceptions["forum_answer_variant"] = " where answer_id in (select id from forum_answer where tenantid = " + tenant + ")";
whereExceptions["forum_topic_tag"] = " where topic_id in (select id from forum_topic where tenantid = " + tenant + ")";
whereExceptions["forum_variant"] = " where question_id in (select id from forum_question where tenantid = " + tenant + ")";
whereExceptions["projects_project_participant"] = " where project_id in (select id from projects_projects where tenant_id = " + tenant + ")";
whereExceptions["projects_following_project_participant"] = " where project_id in (select id from projects_projects where tenant_id = " + tenant + ")";
whereExceptions["projects_project_tag"] = " where project_id in (select id from projects_projects where tenant_id = " + tenant + ")";
whereExceptions["projects_project_tag_change_request"] = " where project_id in (select id from projects_projects where tenant_id = " + tenant + ")";
whereExceptions["tenants_tenants"] = " where id = " + tenant;
whereExceptions["webstudio_widgetstate"] = " where widgetcontainerid in (select id from webstudio_widgetcontainer where tenantid = " + tenant + ")";
whereExceptions["core_usersecurity"] = " where userid in (select id from core_user where tenant = " + tenant + ")";
whereExceptions["core_acl"] = " where tenant = " + tenant + " or tenant = -1";
whereExceptions["core_subscription"] = " where tenant = " + tenant + " or tenant = -1";
whereExceptions["core_subscriptionmethod"] = " where tenant = " + tenant + " or tenant = -1";
}
/// <summary>The get db types.</summary>
/// <param name="connection">The connection.</param>
/// <returns>A dictionary of named <see cref="DbModelType"/> objects supported by the database.</returns>
/// <exception cref="ArgumentNullException">If the <paramref name="connection"/> is null.</exception>
public virtual Dictionary<string, DbModelType> GetDbTypes(DbConnection connection)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
Dictionary<string, DbModelType> dbTypes = new Dictionary<string, DbModelType>();
DataTable dataTypes = connection.GetSchema("DataTypes");
foreach (DataRow row in dataTypes.Rows)
{
string typeName = SafeGetString(row, "TypeName");
int columnSize = SafeGetInt(row, "ColumnSize");
DbModelType dbType = new DbModelType(typeName, columnSize);
if (!dbTypes.ContainsKey(typeName.ToLower()))
{
dbTypes.Add(typeName.ToLower(), dbType);
}
dbType.CreateFormat = SafeGetString(row, "CreateFormat");
dbType.CreateParameters = SafeGetString(row, "CreateParameters");
dbType.LiteralPrefix = SafeGetString(row, "LiteralPrefix");
dbType.LiteralSuffix = SafeGetString(row, "LiteralSuffix");
dbType.SystemType = Type.GetType(SafeGetString(row, "DataType"));
dbType.ProviderDbType = SafeGetString(row, "ProviderDbType");
}
return dbTypes;
}
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);
}
protected virtual void LoadDataTypes(DbConnection Connection, Dictionary<string, Type> Types)
{
var dt = Connection.GetSchema("DataTypes");
foreach (DataRow r in dt.Rows)
Types.Add(r[0] as string, Type.GetType(r[5] as string));
}
