private void LoadDescriptions() { try { string select = @"SELECT objName, value FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'procedure', null, null, null)"; OleDbConnection cn = new OleDbConnection(dbRoot.ConnectionString); cn.Open(); cn.ChangeDatabase("[" + this.Database.Name + "]"); OleDbDataAdapter adapter = new OleDbDataAdapter(select, cn); DataTable dataTable = new DataTable(); adapter.Fill(dataTable); cn.Close(); Procedure p; foreach(DataRow row in dataTable.Rows) { p = this[row["objName"] as string] as Procedure; if(null != p) { p._row["DESCRIPTION"] = row["value"] as string; } } } catch { } }
public void run() { Exception exp = null; OleDbConnection con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); con.Open(); OleDbCommand cmd = new OleDbCommand("Select * From Orders", con); OleDbDataReader rdr = cmd.ExecuteReader(); //change a connection's state without closing the datareader (should fail try { BeginCase("InvalidOperationException"); try { con.ChangeDatabase("msdb"); ExpectedExceptionNotCaught(typeof(InvalidOperationException).FullName); } catch (InvalidOperationException ex) { ExpectedExceptionCaught(ex); } } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} if (con.State == ConnectionState.Open) con.Close(); }
private void LoadSubViewInfo() { _views = (Views)this.dbRoot.ClassFactory.CreateViews(); _tables = (Tables)this.dbRoot.ClassFactory.CreateTables(); try { string select = "SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE WHERE VIEW_NAME = '" + this.Name + "' AND VIEW_SCHEMA = '" + this.Schema + "';"; OleDbConnection cn = new OleDbConnection(dbRoot.ConnectionString); cn.Open(); cn.ChangeDatabase(this.Database.Name); OleDbDataAdapter adapter = new OleDbDataAdapter(select, cn); DataTable dataTable = new DataTable(); adapter.Fill(dataTable); cn.Close(); string entity = ""; Table table; View view; foreach(DataRow row in dataTable.Rows) { entity = row["TABLE_NAME"] as string; // It might be a table or a view table = this.Database.Tables[entity] as Table; if(null != table) { // It's a table _tables.AddTable(table); } else { // Check for View view = this.Database.Views[entity] as View; if(null != view) { // It's a table _views.AddView(view); } } } } catch {} }
override internal void LoadAll() { try { string select = "SELECT * FROM INFORMATION_SCHEMA.DOMAINS"; OleDbConnection cn = new OleDbConnection(dbRoot.ConnectionString); cn.Open(); cn.ChangeDatabase("[" + this.Database.Name + "]"); OleDbDataAdapter adapter = new OleDbDataAdapter(select, cn); DataTable metaData = new DataTable(); adapter.Fill(metaData); cn.Close(); PopulateArray(metaData); } catch {} }
public void run() { Exception exp = null; OleDbConnection con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); con.Open(); //Currently not running on Oracle/DB2: .Net-Failed, GH:Pass //not runnig on postgres because "The 'current catalog' property is not supported by the 'Mainsoft.JDBC.OLEDB.1' provider." if (ConnectedDataProvider.GetDbType(con) != DataBaseServer.Oracle && ConnectedDataProvider.GetDbType(con) != DataBaseServer.DB2 && ConnectedDataProvider.GetDbType(con) != DataBaseServer.Sybase && ConnectedDataProvider.GetDbType(con) != DataBaseServer.PostgreSQL) { try { BeginCase("Change DataBase"); con.ChangeDatabase("GHTDB_EX"); Compare(con.Database , "GHTDB_EX"); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} try { BeginCase("Check DataBase Changed "); OleDbCommand cmd = new OleDbCommand("select count(*) from Customers", con); object obj = cmd.ExecuteScalar(); Compare(obj != null, true); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} if (con.State == ConnectionState.Open) con.Close(); } }
private string LoadViewSource() { string text = string.Empty; OleDbConnection cn = null; OleDbDataReader reader = null; try { string select = string.Format(@"SELECT CASE WHEN encrypted = 1 THEN NULL ELSE com.text END as Source FROM sysobjects o, syscomments com WHERE o.id = object_id(N'[{0}].[{1}]') and com.id=o.id and com.status=2 order by colid;", this.Schema, this.Name); cn = new OleDbConnection(dbRoot.ConnectionString); cn.Open(); cn.ChangeDatabase(this.Database.Name); OleDbCommand cmd = cn.CreateCommand(); cmd.CommandText = select; try { reader = cmd.ExecuteReader(); while (reader.Read()) { text += reader[0].ToString(); } reader.Close(); } catch { if (reader != null) reader.Close(); } if (text == string.Empty) { select = string.Format(@"SELECT CASE WHEN encrypted = 1 THEN NULL ELSE com.text END as Source FROM sysobjects o, syscomments com WHERE o.id = object_id(N'[{0}].[{1}]') and com.id=o.id order by colid;", this.Schema, this.Name); cmd = cn.CreateCommand(); cmd.CommandText = select; reader = cmd.ExecuteReader(); while (reader.Read()) { text += reader[0].ToString(); } reader.Close(); cn.Close(); } cn.Close(); text = text.TrimStart(' ', '\r', '\n', '\t'); } catch { if (reader != null) reader.Close(); if ((cn != null) && (cn.State != ConnectionState.Closed) && (cn.State != ConnectionState.Broken) ) cn.Close(); } return text; }
private void LoadExtraData(string name, string type) { try { string dbName = ("T" == type) ? this.Table.Database.Name : this.View.Database.Name; string schema = ("T" == type) ? this.Table.Schema : this.View.Schema; string select = "EXEC [" + dbName + "].dbo.sp_columns '" + name + "', '" + schema + "'"; using (OleDbConnection cn = new OleDbConnection(dbRoot.ConnectionString)) { cn.Open(); cn.ChangeDatabase("[" + dbName + "]"); OleDbDataAdapter adapter = new OleDbDataAdapter(select, cn); DataTable dataTable = new DataTable(); adapter.Fill(dataTable); if (this._array.Count > 0) { Column col = this._array[0] as Column; f_TypeName = new DataColumn("TYPE_NAME", typeof(string)); col._row.Table.Columns.Add(f_TypeName); string typeName = ""; DataRowCollection rows = dataTable.Rows; int count = this._array.Count; Column c = null; for (int index = 0; index < count; index++) { c = (Column)this[index]; typeName = rows[index]["TYPE_NAME"] as string; if (typeName.EndsWith(" identity")) { typeName = typeName.Replace(" identity", ""); typeName = typeName.Replace("()", ""); c._row["TYPE_NAME"] = typeName; } else { c._row["TYPE_NAME"] = typeName; } } } select = @"select COLUMN_NAME, DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where table_schema = '" + schema + @"' and table_catalog='" + dbName + @"' and table_name='" + name + @"' and DATA_TYPE in ('nvarchar', 'varchar', 'varbinary') and character_maximum_length=-1 and character_octet_length=-1;"; adapter = new OleDbDataAdapter(select, cn); dataTable = new DataTable(); adapter.Fill(dataTable); Column colz = null; foreach (DataRow row in dataTable.Rows) { colz = this[row["COLUMN_NAME"] as string] as Column; if (null != colz) { colz._row["TYPE_NAME"] = row["DATA_TYPE"] as string; } } cn.Close(); } } catch {} }
private void LoadAutoKeyInfo() { try { string select = @"SELECT TABLE_NAME, COLUMN_NAME, IDENT_SEED('[" + this.Table.Name + "]') AS AUTO_KEY_SEED, IDENT_INCR('[" + this.Table.Name + "]') AS AUTO_KEY_INCREMENT " + "FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + this.Table.Name + "' AND TABLE_SCHEMA = '" + this.Table.Schema + "' AND " + "columnproperty(object_id('[" + this.Table.Schema + "].[" + this.Table.Name + "]'), COLUMN_NAME, 'IsIdentity') = 1"; OleDbConnection cn = new OleDbConnection(dbRoot.ConnectionString); cn.Open(); cn.ChangeDatabase("[" + this.Table.Database.Name + "]"); OleDbDataAdapter adapter = new OleDbDataAdapter(select, cn); DataTable dataTable = new DataTable(); adapter.Fill(dataTable); cn.Close(); if(dataTable.Rows.Count > 0) { f_AutoKeySeed = new DataColumn("AUTO_KEY_SEED", typeof(System.Int32)); f_AutoKeyIncrement = new DataColumn("AUTO_KEY_INCREMENT", typeof(System.Int32)); f_AutoKey = new DataColumn("AUTO_INCREMENT", typeof(Boolean)); Column col = this._array[0] as Column; col._row.Table.Columns.Add(f_AutoKeySeed); col._row.Table.Columns.Add(f_AutoKeyIncrement); col._row.Table.Columns.Add(f_AutoKey); DataRowCollection rows = dataTable.Rows; DataRow row; for(int i = 0; i < rows.Count; i++) { row = rows[i]; col = this[row["COLUMN_NAME"]] as Column; col._row["AUTO_KEY_SEED"] = row["AUTO_KEY_SEED"]; col._row["AUTO_KEY_INCREMENT"] = row["AUTO_KEY_INCREMENT"]; col._row["AUTO_INCREMENT"] = true; } } } catch(Exception ex) { string s = ex.Message; } }
private void LoadDescriptions() { try { string select = @"SELECT objName, value FROM ::fn_listextendedproperty ('MS_Description', 'user', '" + this.Table.Schema + @"', 'table', '" + this.Table.Name + @"', 'column', null) UNION SELECT objName, value FROM ::fn_listextendedproperty ('MS_Description', 'schema', '" + this.Table.Schema + @"', 'table', '" + this.Table.Name + @"', 'column', null)"; OleDbConnection cn = new OleDbConnection(dbRoot.ConnectionString); cn.Open(); cn.ChangeDatabase("[" + this.Table.Database.Name + "]"); OleDbDataAdapter adapter = new OleDbDataAdapter(select, cn); DataTable dataTable = new DataTable(); adapter.Fill(dataTable); cn.Close(); Column c; foreach(DataRow row in dataTable.Rows) { c = this[row["objName"] as string] as Column; if(null != c) { c._row["DESCRIPTION"] = row["value"] as string; } } } catch(Exception ex) { string s = ex.Message; } }
virtual public ADODB.Recordset ExecuteSql(string sql) { Recordset oRS = new Recordset(); OleDbConnection cn = null; OleDbDataReader reader = null; try { cn = new OleDbConnection(dbRoot.ConnectionString); cn.Open(); try { cn.ChangeDatabase(this.Name); } catch { } // some databases don't have the concept of catalogs. Catch this and throw it out OleDbCommand command = new OleDbCommand(sql, cn); command.CommandType = CommandType.Text; reader = command.ExecuteReader(); DataTable schema; string dataType, fieldname; int length; bool firstTime = true; while (reader.Read()) { if (firstTime) { schema = reader.GetSchemaTable(); foreach (DataRow row in schema.Rows) { fieldname = row["ColumnName"].ToString(); dataType = row["DataType"].ToString(); length = Convert.ToInt32(row["ColumnSize"]); oRS.Fields.Append(fieldname, GetADOType(dataType), length, FieldAttributeEnum.adFldIsNullable, System.Reflection.Missing.Value); } oRS.Open(System.Reflection.Missing.Value, System.Reflection.Missing.Value, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, 1); firstTime = false; } oRS.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value); for(int i = 0; i < reader.FieldCount; i++) { if (reader[i] is System.Guid) { oRS.Fields[i].Value = "{" + reader[i].ToString() + "}"; } else { oRS.Fields[i].Value = reader[i]; } } } cn.Close(); //Move to the first record if (!firstTime) { oRS.MoveFirst(); } else { oRS = null; } } catch (Exception ex) { if ((reader != null) && (!reader.IsClosed)) { reader.Close(); reader = null; } if ((cn != null) && (cn.State == ConnectionState.Open)) { cn.Close(); cn = null; } throw ex; } return oRS; }