public override ColumnSchemaCollection GetViewColumns(ViewSchema view) { ColumnSchemaCollection columns = new ColumnSchemaCollection(); using (IPooledDbConnection conn = connectionPool.Request()) { using (IDbCommand command = conn.CreateCommand(string.Format("SELECT * FROM \"{0}\" WHERE 1 = 0", view.Name))) try { using (IDataReader r = command.ExecuteReader()) { for (int i = 0; i < r.FieldCount; i++) { ColumnSchema column = new ColumnSchema(this, view); column.Name = r.GetName(i); column.DataTypeName = r.GetDataTypeName(i); column.DefaultValue = ""; column.Definition = ""; column.OwnerName = view.OwnerName; column.SchemaName = view.OwnerName; columns.Add(column); } r.Close(); } } catch (Exception e) { QueryService.RaiseException(e); }finally { conn.Release(); } } return(columns); }
private string GetSource(string objectName) { LoggingService.LogDebug("GetSource: " + objectName); IPooledDbConnection conn = connectionPool.Request(); IDbCommand command = conn.CreateCommand( String.Format("EXEC sp_helptext '{0}'", objectName) ); StringBuilder sb = new StringBuilder(); try { using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { sb.Append(r.GetString(0)); } r.Close(); } } } catch (Exception e) { QueryService.RaiseException(e); } conn.Release(); return(sb.ToString()); }
public override DatabaseSchemaCollection GetDatabases() { DatabaseSchemaCollection databases = new DatabaseSchemaCollection(); using (IPooledDbConnection conn = connectionPool.Request()) { //we don't have to change it back afterwards, since the connectionpool will do this for us conn.DbConnection.ChangeDatabase("master"); using (IDbCommand command = conn.CreateCommand("select name from sysdatabases")) { try { using (command) using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { DatabaseSchema db = new DatabaseSchema(this); db.Name = r.GetString(0); databases.Add(db); } r.Close(); } } catch (Exception e) { QueryService.RaiseException(e); } finally { conn.Release(); } } } return(databases); }
public override ColumnSchemaCollection GetTableColumns(TableSchema table) { ColumnSchemaCollection columns = new ColumnSchemaCollection(); using (IPooledDbConnection conn = connectionPool.Request()) { using (IDbCommand command = conn.CreateCommand(string.Format(@"SELECT su.name as owner, so.name as table_name, sc.name as column_name, st.name as date_type, sc.length as column_length, sc.xprec as data_precision, sc.xscale as data_scale, sc.isnullable, sc.colid as column_id FROM dbo.syscolumns sc, dbo.sysobjects so, dbo.systypes st, dbo.sysusers su WHERE sc.id = so.id AND so.xtype in ('U','S') AND so.name = '{0}' AND su.name = '{1}' AND sc.xusertype = st.xusertype AND su.uid = so.uid ORDER BY sc.colid" , table.Name, table.OwnerName))) try { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ColumnSchema column = new ColumnSchema(this, table); column.Name = r.GetString(2); column.DataTypeName = r.GetString(3); column.DefaultValue = String.Empty; column.Comment = String.Empty; column.OwnerName = table.OwnerName; column.SchemaName = table.SchemaName; column.IsNullable = r.GetValue(7).ToString() == "0" ? true : false; column.DataType.LengthRange.Default = r.GetInt16(4); column.DataType.PrecisionRange.Default = r.IsDBNull(5) ? 0 : (int)r.GetByte(5); column.DataType.ScaleRange.Default = r.IsDBNull(6) ? 0 : (int)r.GetByte(6); column.Definition = String.Concat(column.Name, " ", column.DataTypeName, " ", column.DataType.LengthRange.Default > 0 ? "(" + column.DataType.LengthRange.Default + ")" : "", column.IsNullable ? " NULL" : " NOT NULL"); //TODO: append " DEFAULT ..." if column.Default.Length > 0 columns.Add(column); } r.Close(); } } catch (Exception e) { QueryService.RaiseException(e); }finally { conn.Release(); } } return(columns); }
public override ConstraintSchemaCollection GetTableConstraints(TableSchema table) { ConstraintSchemaCollection constraints = new ConstraintSchemaCollection(); using (IPooledDbConnection conn = connectionPool.Request()) { using (IDbCommand command = conn.CreateCommand(string.Format(@"select sysobjects.name, sysobjects.xtype from sysobjects inner join sysobjects sysobjectsParents ON sysobjectsParents.id = sysobjects.parent_obj where sysobjectsParents.name = '{0}' and sysobjects.xtype in ('C', 'UQ', 'F','PK','CK')" , table.Name))) try { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ConstraintSchema constraint = null; switch (r.GetString(1)) { case "F": //foreign key constraint = new ForeignKeyConstraintSchema(this); break; case "PK": //primary key constraint = new PrimaryKeyConstraintSchema(this); break; case "C": case "CK": //check constraint constraint = new CheckConstraintSchema(this); break; case "UQ": constraint = new UniqueConstraintSchema(this); break; default: break; } if (constraint != null) { constraint.Name = r.GetString(0); constraints.Add(constraint); } } r.Close(); } } catch (Exception e) { QueryService.RaiseException(e); }finally { conn.Release(); } } return(constraints); }
private void OnEmptyTableCallback(IPooledDbConnection connection, int result, object state) { connection.Release(); DispatchService.GuiDispatch(delegate() { IdeApp.Workbench.StatusBar.SetMessage(GettextCatalog.GetString("Table emptied")); }); }
protected virtual int ExecuteNonQuery(string sql) { IPooledDbConnection conn = connectionPool.Request(); int result = conn.ExecuteNonQuery(sql); conn.Release(); return(result); }
public override ColumnSchemaCollection GetTableColumns(TableSchema table) { ColumnSchemaCollection columns = new ColumnSchemaCollection(); IPooledDbConnection conn = connectionPool.Request(); IDbCommand command = conn.CreateCommand( "SELECT a.attname, a.attnotnull, a.attlen, " + "typ.typname, adef.adsrc " + "FROM " + " pg_catalog.pg_attribute a LEFT JOIN " + " pg_catalog.pg_attrdef adef " + " ON a.attrelid=adef.adrelid " + " AND a.attnum=adef.adnum " + " LEFT JOIN pg_catalog.pg_type t ON a.atttypid=t.oid, " + " pg_catalog.pg_type typ " + "WHERE " + " a.attrelid = (SELECT oid FROM pg_catalog.pg_class " + " WHERE relname='" + table.Name + "') " + "AND a.attnum > 0 AND NOT a.attisdropped " + "AND a.atttypid = typ.oid " + "ORDER BY a.attnum;" ); try { using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ColumnSchema column = new ColumnSchema(this, table); column.Name = r.GetString(0); column.DataTypeName = r.GetString(3); column.IsNullable = r.GetBoolean(1); column.DefaultValue = r.IsDBNull(4) ? null : r.GetString(4); column.DataType.LengthRange.Default = r.GetInt32(2); // StringBuilder sb = new StringBuilder(); // sb.AppendFormat("{0} {1}{2}", // column.Name, // column.DataTypeName, // (column.DataType.LengthRange.Default > 0) ? ("(" + column.DataType.LengthRange.Default + ")") : ""); // sb.AppendFormat(" {0}", column.IsNullable ? "NULL" : "NOT NULL"); // if (column.DefaultValue.Length > 0) // sb.AppendFormat(" DEFAULT {0}", column.DefaultValue); // column.Definition = sb.ToString(); columns.Add(column); } r.Close(); }; } } catch (Exception e) { QueryService.RaiseException(e); } conn.Release(); return(columns); }
//http://www.sqlite.org/lang_createview.html public override void CreateView(ViewSchema view) { IPooledDbConnection conn = connectionPool.Request(); IDbCommand command = conn.CreateCommand(view.Definition); using (command) conn.ExecuteNonQuery(command); conn.Release(); }
private void OnSelectCommandThreaded(IPooledDbConnection connection, DataTable table, object state) { connection.Release(); DispatchService.GuiDispatch(delegate() { QueryResultView view = new QueryResultView(table); IdeApp.Workbench.OpenDocument(view, true); }); }
public override ProcedureSchemaCollection GetProcedures() { ProcedureSchemaCollection procedures = new ProcedureSchemaCollection(); IPooledDbConnection conn = connectionPool.Request(); IDbCommand command = conn.CreateCommand( "SELECT pc.proname, pc.oid::integer, pl.lanname, pc.prosrc " + "FROM " + " pg_proc pc, " + " pg_user pu, " + " pg_type pt, " + " pg_language pl " + "WHERE pc.proowner = pu.usesysid " + "AND pc.prorettype = pt.oid " + "AND pc.prolang = pl.oid " + "UNION " + "SELECT pc.proname, pt.oid::integer, pl.lanname, pc.prosrc " + "FROM " + " pg_proc pc, " + " pg_user pu, " + " pg_type pt, " + " pg_language pl " + "WHERE pc.proowner = pu.usesysid " + "AND pc.prorettype = 0 " + "AND pc.prolang = pl.oid;" ); try { using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ProcedureSchema procedure = new ProcedureSchema(this); procedure.Name = r.GetString(0); procedure.Definition = r.GetString(3); procedure.LanguageName = r.GetString(2); if (!r.IsDBNull(1) && r.GetInt32(1) <= LastSystemOID) { procedure.IsSystemProcedure = true; } procedures.Add(procedure); } r.Close(); } } } catch (Exception e) { QueryService.RaiseException(e); } conn.Release(); return(procedures); }
private void ExecuteQueryThreaded(IPooledDbConnection connection, DataSet result, object state) { connection.Release(); TimeSpan duration = DateTime.Now.Subtract(queryStart); DispatchService.GuiDispatch(delegate() { notebook.ShowAll(); string msg = String.Concat( AddinCatalog.GetPluralString("Query executed ({0} result table)", "Query executed ({0} result tables)", result.Tables.Count), Environment.NewLine, AddinCatalog.GetString("Query duration: {0}", duration.ToString()) ); SetQueryState(false, String.Format(msg, result.Tables.Count)); }); if (stoppedQueries.Contains(state)) { stoppedQueries.Remove(state); return; } if (result != null) { foreach (DataTable table in result.Tables) { DispatchService.GuiDispatch(delegate() { MonoDevelop.Database.Components.DataGrid grid = new MonoDevelop.Database.Components.DataGrid(); grid.DataSource = table; grid.DataBind(); string msg = String.Concat(Environment.NewLine, AddinCatalog.GetString("Table"), ": ", table.TableName, Environment.NewLine, "\t", AddinCatalog.GetString("Affected Rows"), ": ", table.Rows.Count); status.Buffer.Text += msg; TabLabel label = new TabLabel(new Label(table.TableName), ImageService.GetImage("md-db-table", IconSize.Menu)); label.CloseClicked += new EventHandler(OnResultTabClose); notebook.AppendPage(grid, label); notebook.ShowAll(); this.Document.ReadOnly = false; notebook.Page = notebook.NPages - 1; }); } } if (result == null || result.Tables.Count == 0) { DispatchService.GuiDispatch(delegate() { status.Buffer.Text += AddinCatalog.GetString("No Results"); this.Document.ReadOnly = false; }); } }
public override TableSchemaCollection GetTables() { TableSchemaCollection tables = new TableSchemaCollection(); using (IPooledDbConnection conn = connectionPool.Request()) { using (IDbCommand command = conn.CreateCommand(@"SELECT su.name AS owner, so.name as table_name, so.id as table_id, so.crdate as created_date, so.xtype as table_type FROM dbo.sysobjects so, dbo.sysusers su WHERE xtype IN ('S','U') AND su.uid = so.uid ORDER BY 1, 2" )) { try { using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { TableSchema table = new TableSchema(this); table.Name = r.GetString(1); if (r.GetString(4) == "S") { table.IsSystemTable = true; } else if (Array.Exists(system_tables, delegate(string s) { return(s == table.Name); })) { table.IsSystemTable = true; } else { table.IsSystemTable = false; } table.OwnerName = r.GetString(0); table.Definition = GetTableDefinition(table); tables.Add(table); } r.Close(); } } } catch (Exception e) { QueryService.RaiseException(e); } finally { conn.Release(); } } } return(tables); }
public override UserSchemaCollection GetUsers() { UserSchemaCollection users = new UserSchemaCollection(); IPooledDbConnection conn = connectionPool.Request(); IDbCommand command = conn.CreateCommand("SELECT * FROM pg_user;"); try { using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { UserSchema user = new UserSchema(this); user.Name = r.GetString(0); user.UserId = String.Format("{0}", r.GetValue(1)); user.Expires = r.IsDBNull(6) ? DateTime.MinValue : r.GetDateTime(6); //user.Options["createdb"] = r.GetBoolean (2); //user.Options["createuser"] = r.GetBoolean (3); user.Password = r.GetString(5); StringBuilder sb = new StringBuilder(); sb.AppendFormat("-- User: \"{0}\"\n\n", user.Name); sb.AppendFormat("-- DROP USER {0};\n\n", user.Name); sb.AppendFormat("CREATE USER {0}", user.Name); sb.AppendFormat(" WITH SYSID {0}", user.UserId); if (user.Password != "********") { sb.AppendFormat(" ENCRYPTED PASSWORD {0}", user.Password); } //sb.AppendFormat (((bool) user.Options["createdb"]) ? // " CREATEDB" : " NOCREATEDB"); //sb.AppendFormat (((bool) user.Options["createuser"]) ? // " CREATEUSER" : " NOCREATEUSER"); if (user.Expires != DateTime.MinValue) { sb.AppendFormat(" VALID UNTIL {0}", user.Expires); } sb.Append(";"); user.Definition = sb.ToString(); users.Add(user); } r.Close(); } } } catch (Exception e) { QueryService.RaiseException(e); } conn.Release(); return(users); }
//http://msdn2.microsoft.com/en-us/library/aa258843(SQL.80).aspx public override void DropDatabase(DatabaseSchema database) { using (IPooledDbConnection conn = connectionPool.Request()) { using (IDbCommand command = conn.CreateCommand(string.Concat("DROP DATABASE ", database.Name))) try { command.ExecuteNonQuery(); } catch (Exception e) { QueryService.RaiseException(e); }finally { conn.Release(); } } }
//http://msdn2.microsoft.com/en-us/library/aa258257(SQL.80).aspx public override void CreateDatabase(DatabaseSchema database) { IPooledDbConnection conn = connectionPool.Request(); IDbCommand command = conn.CreateCommand("CREATE DATABASE " + database.Name); try { using (command) command.ExecuteNonQuery(); } catch (Exception e) { QueryService.RaiseException(e); } conn.Release(); }
// see: http://dev.mysql.com/doc/refman/5.1/en/views-table.html public override ViewSchemaCollection GetViews() { ViewSchemaCollection views = new ViewSchemaCollection(); using (IPooledDbConnection conn = connectionPool.Request()) { using (IDbCommand command = conn.CreateCommand(string.Format( @"SELECT TABLE_NAME, TABLE_SCHEMA FROM information_schema.VIEWS where TABLE_SCHEMA = '{0}' ORDER BY TABLE_NAME" , ConnectionPool.ConnectionContext.ConnectionSettings.Database))){ try { // Views are supported in mysql since version 5. if (GetMainVersion(command) >= 5) { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ViewSchema view = new ViewSchema(this); view.Name = r.GetString(0); view.OwnerName = r.GetString(1); using (IPooledDbConnection conn2 = connectionPool.Request()) { using (IDbCommand command2 = conn2.CreateCommand(string.Concat( "SHOW CREATE TABLE `", view.Name, "`;"))) { using (IDataReader r2 = command2.ExecuteReader()) { r2.Read(); view.Definition = r2.GetString(1); } } conn2.Release(); } views.Add(view); } r.Close(); } } } catch (Exception e) { QueryService.RaiseException(e); } finally { conn.Release(); } } } return(views); }
public override ViewSchemaCollection GetViews() { ViewSchemaCollection views = new ViewSchemaCollection(); IPooledDbConnection conn = connectionPool.Request(); IDbCommand command = conn.CreateCommand( "SELECT v.schemaname, v.viewname, v.viewowner, v.definition," + " (c.oid <= " + LastSystemOID + "), " + "(SELECT description from pg_description pd, " + " pg_class pc WHERE pc.oid=pd.objoid AND pc.relname=" + " v.viewname) " + "FROM pg_views v, pg_class c " + "WHERE v.viewname = c.relname " + "ORDER BY viewname" ); try { using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ViewSchema view = new ViewSchema(this); view.Name = r.GetString(1); view.OwnerName = r.GetString(2); view.SchemaName = r.GetString(0); view.IsSystemView = r.GetBoolean(4); view.Comment = r.IsDBNull(5) ? null : r.GetString(5); // StringBuilder sb = new StringBuilder(); // sb.AppendFormat ("-- View: {0}\n", view.Name); // sb.AppendFormat ("-- DROP VIEW {0};\n\n", view.Name); // sb.AppendFormat ("CREATE VIEW {0} AS (\n", view.Name); // string core = r.GetString(3); // sb.AppendFormat (" {0}\n);", core.Substring (0, core.Length-1)); // view.Definition = sb.ToString (); views.Add(view); } r.Close(); } } } catch (Exception e) { QueryService.RaiseException(e); } conn.Release(); return(views); }
//http://msdn2.microsoft.com/en-US/library/aa238878(SQL.80).aspx private void Rename(string oldName, string newName, string type) { IPooledDbConnection conn = connectionPool.Request(); IDbCommand command = conn.CreateStoredProcedure( String.Format("EXEC sp_rename '{0}', '{1}', '{2}'", oldName, newName, type) ); try { using (command) command.ExecuteNonQuery(); } catch (Exception e) { QueryService.RaiseException(e); } conn.Release(); }
public override ViewSchemaCollection GetViews() { ViewSchemaCollection views = new ViewSchemaCollection(); using (IPooledDbConnection conn = connectionPool.Request()) { using (IDbCommand command = conn.CreateCommand(@"SELECT su.name AS owner, so.name as table_name, so.id as table_id, so.crdate as created_date, so.xtype as table_type FROM dbo.sysobjects so, dbo.sysusers su WHERE xtype = 'V' AND su.uid = so.uid ORDER BY 1, 2" )) try { using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ViewSchema view = new ViewSchema(this); view.Name = r.GetString(1); view.SchemaName = r.GetString(0); view.OwnerName = r.GetString(0); StringBuilder sb = new StringBuilder(); sb.AppendFormat("-- View: {0}\n", view.Name); sb.AppendFormat("-- DROP VIEW {0};\n\n", view.Name); sb.AppendFormat(" {0}\n);", GetSource("[" + view.OwnerName + "].[" + view.Name + "]")); view.Definition = sb.ToString(); views.Add(view); } r.Close(); } } } catch (Exception e) { QueryService.RaiseException(e); } finally { conn.Release(); } } return(views); }
private void OnCreateTableThreaded(object state) { object[] objs = state as object[]; ISchemaProvider provider = objs[0] as ISchemaProvider; TableSchema table = objs[1] as TableSchema; BaseNode node = objs[2] as BaseNode; LoggingService.LogDebug("ADD TABLE: {0}", table.Definition); IPooledDbConnection conn = provider.ConnectionPool.Request(); conn.ExecuteNonQuery(table.Definition); conn.Release(); node.Refresh(); }
public override ConstraintSchemaCollection GetTableConstraints(TableSchema table) { ConstraintSchemaCollection constraints = new ConstraintSchemaCollection(); IPooledDbConnection conn = connectionPool.Request(); IDbCommand command = conn.CreateCommand("select name, xtype from sysobjects where xtype in ('F','PK','CK')"); //TODO: unique try { using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ConstraintSchema constraint = null; switch (r.GetString(1)) { case "F": //foreign key constraint = new ForeignKeyConstraintSchema(this); break; case "PK": //primary key constraint = new PrimaryKeyConstraintSchema(this); break; case "CK": //check constraint constraint = new CheckConstraintSchema(this); break; default: break; } if (constraint != null) { constraint.Name = r.GetString(0); constraints.Add(constraint); } } r.Close(); } } } catch (Exception e) { QueryService.RaiseException(e); } conn.Release(); return(constraints); }
public override ConstraintSchemaCollection GetColumnConstraints(TableSchema table, ColumnSchema column) { ConstraintSchemaCollection constraints = new ConstraintSchemaCollection(); using (IPooledDbConnection conn = connectionPool.Request()) { using (IDbCommand command = conn.CreateCommand(String.Format("DESCRIBE {0}", table.Name))) { try { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { if (r.IsDBNull(3) || String.Compare(r.GetString(0), column.Name, true) != 0) { continue; } string key = r.GetString(3).ToUpper(); ConstraintSchema constraint = null; if (key.Contains("PRI")) { constraint = CreatePrimaryKeyConstraintSchema("pk_" + column.Name); } else if (key.Contains("UNI")) { constraint = CreateUniqueConstraintSchema("uni_" + column.Name); } else { continue; } constraint.IsColumnConstraint = true; constraint.OwnerName = r.GetString(0); constraints.Add(constraint); } r.Close(); } } catch (Exception e) { QueryService.RaiseException(e); } finally { conn.Release(); } } } return(constraints); }
public override ProcedureSchemaCollection GetProcedures() { ProcedureSchemaCollection procedures = new ProcedureSchemaCollection(); using (IPooledDbConnection conn = connectionPool.Request()) { using (IDbCommand command = conn.CreateCommand(@"SELECT su.name AS owner, so.name as proc_name, so.id as proc_id, so.crdate as created_date, so.xtype as proc_type FROM dbo.sysobjects so, dbo.sysusers su WHERE xtype = 'P' AND su.uid = so.uid ORDER BY 1, 2" )) try { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ProcedureSchema procedure = new ProcedureSchema(this); procedure.Name = r.GetString(1); procedure.OwnerName = r.GetString(0); procedure.LanguageName = "TSQL"; procedure.Definition = GetSource("[" + procedure.OwnerName + "].[" + procedure.Name + "]"); if (Array.Exists(system_procs, delegate(string s) { return(s == procedure.Name); })) { procedure.IsSystemProcedure = true; } else { procedure.IsSystemProcedure = false; } procedures.Add(procedure); } r.Close(); } } catch (Exception e) { QueryService.RaiseException(e); }finally { conn.Release(); } } return(procedures); }
// see: http://dev.mysql.com/doc/refman/5.1/en/routines-table.html public override ProcedureSchemaCollection GetProcedures() { ProcedureSchemaCollection procedures = new ProcedureSchemaCollection(); IPooledDbConnection conn = connectionPool.Request(); IDbCommand command = conn.CreateCommand( "SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA ='" + ConnectionPool.ConnectionContext.ConnectionSettings.Database + "' ORDER BY ROUTINE_NAME" ); try { using (command) { if (GetMainVersion(command) >= 5) { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ProcedureSchema procedure = new ProcedureSchema(this); procedure.Name = r.GetString(0); procedure.OwnerName = r.GetString(1); procedure.IsSystemProcedure = r.GetString(2).ToLower().Contains("system"); IPooledDbConnection conn2 = connectionPool.Request(); IDbCommand command2 = conn2.CreateCommand("SHOW CREATE PROCEDURE `" + procedure.Name + "`;"); using (IDataReader r2 = command2.ExecuteReader()) { r2.Read(); procedure.Definition = r2.GetString(2); } conn2.Release(); procedures.Add(procedure); } r.Close(); } } //else: do nothing, since procedures are only supported since mysql 5.x } } catch (Exception e) { QueryService.RaiseException(e); } conn.Release(); return(procedures); }
public override ColumnSchemaCollection GetViewColumns(ViewSchema view) { ColumnSchemaCollection columns = new ColumnSchemaCollection(); IPooledDbConnection conn = connectionPool.Request(); IDbCommand command = conn.CreateCommand( "SELECT attname, typname, attlen, attnotnull " + "FROM " + " pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_attrdef adef " + " ON a.attrelid=adef.adrelid " + " AND a.attnum=adef.adnum " + " LEFT JOIN pg_catalog.pg_type t ON a.atttypid=t.oid " + "WHERE " + " a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='" + view.Name + "') " + " AND a.attnum > 0 AND NOT a.attisdropped " + " ORDER BY a.attnum;" ); try { using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ColumnSchema column = new ColumnSchema(this, view); column.Name = r.GetString(0); column.DataTypeName = r.GetString(1); column.SchemaName = view.SchemaName; column.IsNullable = r.GetBoolean(3); column.DataType.LengthRange.Default = r.GetInt32(2); columns.Add(column); } r.Close(); }; } } catch (Exception e) { QueryService.RaiseException(e); } conn.Release(); return(columns); }
// see: http://dev.mysql.com/doc/refman/5.1/en/views-table.html public override ViewSchemaCollection GetViews() { ViewSchemaCollection views = new ViewSchemaCollection(); IPooledDbConnection conn = connectionPool.Request(); IDbCommand command = conn.CreateCommand( "SELECT TABLE_NAME, TABLE_SCHEMA FROM information_schema.VIEWS where TABLE_SCHEMA = '" + ConnectionPool.ConnectionContext.ConnectionSettings.Database + "' ORDER BY TABLE_NAME" ); try { using (command) { if (GetMainVersion(command) >= 5) { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ViewSchema view = new ViewSchema(this); view.Name = r.GetString(0); view.OwnerName = r.GetString(1); IPooledDbConnection conn2 = connectionPool.Request(); IDbCommand command2 = conn2.CreateCommand("SHOW CREATE TABLE `" + view.Name + "`;"); using (IDataReader r2 = command2.ExecuteReader()) { r2.Read(); view.Definition = r2.GetString(1); } conn2.Release(); views.Add(view); } r.Close(); } } //else: do nothing, since views are only supported since mysql 5.x } } catch (Exception e) { QueryService.RaiseException(e); } conn.Release(); return(views); }
//http://msdn2.microsoft.com/en-us/library/aa258257(SQL.80).aspx public override void CreateDatabase(DatabaseSchema database) { SqlServerDatabaseSchema schema = (SqlServerDatabaseSchema)database; StringBuilder db = new StringBuilder("CREATE DATABASE "); string newLine = Environment.NewLine; db.Append(schema.Name); if (schema.FileName != string.Empty && schema.Name != string.Empty) { db.AppendLine(); db.Append("ON "); db.AppendFormat("{0}(NAME = {1},", newLine, schema.LogicalName); db.AppendFormat("{0}FILENAME = '{1}'", newLine, schema.FileName); if (schema.Size.Size > 0) { db.AppendFormat(",{0}SIZE = {1}{2}", newLine, schema.Size.Size.ToString(), schema.Size.Type); } if (schema.MaxSize.Size > 0) { db.AppendFormat(",{0}MAXSIZE = {1}{2}", newLine, schema.MaxSize.Size.ToString(), schema.MaxSize.Type); } if (schema.FileGrowth.Size > 0) { db.AppendFormat(",{0}FILEGROWTH = {1}{2}", newLine, schema.FileGrowth.Size.ToString(), schema.FileGrowth.Type == SizeType.PERCENTAGE ? "%" : schema.FileGrowth.Type.ToString()); } db.Append(")"); } if (schema.Collation != null) { db.AppendFormat("{0}COLLATE {1}{0}", newLine, schema.Collation.Name); } using (IPooledDbConnection conn = connectionPool.Request()) { using (IDbCommand command = conn.CreateCommand(db.ToString())) try { command.ExecuteNonQuery(); } catch (Exception e) { QueryService.RaiseException(e); }finally { conn.Release(); } } }
protected virtual void TestClickedThreaded(object state) { DatabaseConnectionSettings settings = state as DatabaseConnectionSettings; DatabaseConnectionContext context = new DatabaseConnectionContext(settings); IDbFactory fac = DbFactoryService.GetDbFactory(settings.ProviderIdentifier); bool success = false; string error = null; FakeConnectionPool pool = null; IPooledDbConnection conn = null; try { pool = new FakeConnectionPool(fac, fac.ConnectionProvider, context); success = pool.Initialize(); error = pool.Error; } catch (System.Data.DataException ex) { error = ex.Message; success = false; } finally { if (conn != null) { conn.Release(); conn.Dispose(); } if (pool != null) { pool.Close(); } } DispatchService.GuiDispatch(delegate() { buttonTest.Sensitive = true; if (success) { labelTest.Text = AddinCatalog.GetString("Test Succeeded."); } else { labelTest.Text = AddinCatalog.GetString("Test Failed: {0}.", error); } }); }
public override ProcedureSchemaCollection GetProcedures() { ProcedureSchemaCollection procedures = new ProcedureSchemaCollection(); IPooledDbConnection conn = connectionPool.Request(); IDbCommand command = conn.CreateCommand( "SELECT su.name AS owner, so.name as proc_name, so.id as proc_id, " + " so.crdate as created_date, so.xtype as proc_type " + "FROM dbo.sysobjects so, dbo.sysusers su " + "WHERE xtype = 'P' " + "AND su.uid = so.uid " + "ORDER BY 1, 2" ); try { using (command) { using (IDataReader r = command.ExecuteReader()) { while (r.Read()) { ProcedureSchema procedure = new ProcedureSchema(this); procedure.Name = r.GetString(1); procedure.OwnerName = r.GetString(0); procedure.LanguageName = "TSQL"; StringBuilder sb = new StringBuilder(); sb.AppendFormat("-- Procedure: {0}\n", procedure.Name); sb.AppendFormat(" {0}\n);", GetSource("[" + procedure.OwnerName + "].[" + procedure.Name + "]")); procedure.Definition = sb.ToString(); // FIXME : get sysproc or not procedures.Add(procedure); } r.Close(); } } } catch (Exception e) { QueryService.RaiseException(e); } conn.Release(); return(procedures); }
private void ExecuteQueryThreaded (IPooledDbConnection connection, DataSet result, object state) { connection.Release (); TimeSpan duration = DateTime.Now.Subtract (queryStart); DispatchService.GuiDispatch (delegate () { notebook.ShowAll (); string msg = String.Concat ( AddinCatalog.GetPluralString ("Query executed ({0} result table)", "Query executed ({0} result tables)", result.Tables.Count), Environment.NewLine, AddinCatalog.GetString ("Query duration: {0}", duration.ToString ()) ); SetQueryState (false, String.Format (msg, result.Tables.Count)); }); if (stoppedQueries.Contains (state)) { stoppedQueries.Remove (state); return; } if (result != null) { foreach (DataTable table in result.Tables) { DispatchService.GuiDispatch (delegate () { MonoDevelop.Database.Components.DataGrid grid = new MonoDevelop.Database.Components.DataGrid (); grid.DataSource = table; grid.DataBind (); string msg = String.Concat (Environment.NewLine, AddinCatalog.GetString ("Table"), ": ",table.TableName, Environment.NewLine, "\t", AddinCatalog.GetString ("Affected Rows"), ": ", table.Rows.Count); status.Buffer.Text += msg; TabLabel label = new TabLabel (new Label (table.TableName), ImageService.GetImage ("md-db-table", IconSize.Menu)); label.CloseClicked += new EventHandler (OnResultTabClose); notebook.AppendPage (grid, label); notebook.ShowAll (); this.Document.ReadOnly = false; notebook.Page = notebook.NPages -1; }); } } if (result == null || result.Tables.Count == 0) { DispatchService.GuiDispatch (delegate () { status.Buffer.Text += AddinCatalog.GetString ("No Results"); this.Document.ReadOnly = false; }); } }
private void OnSelectCommandThreaded (IPooledDbConnection connection, DataTable table, object state) { connection.Release (); DispatchService.GuiDispatch (delegate () { QueryResultView view = new QueryResultView (table); IdeApp.Workbench.OpenDocument (view, true); }); }
private void OnEmptyTableCallback (IPooledDbConnection connection, int result, object state) { connection.Release (); DispatchService.GuiDispatch (delegate () { IdeApp.Workbench.StatusBar.SetMessage (GettextCatalog.GetString ("Table emptied")); }); }