private void FindViews(DataTable schemaTable, string[] filters) { StringBuilder sql = new StringBuilder(); StringBuilder where = new StringBuilder(); const string selectTables = "select class_name from db_class where is_system_class='NO' and class_type='VCLASS'"; sql.AppendFormat(CultureInfo.InvariantCulture, selectTables); if (filters != null) { string view_name_pattern = filters[0]; where.AppendFormat(CultureInfo.InvariantCulture, " and class_name LIKE '{0}'", view_name_pattern); sql.Append(@where); } using (CUBRIDCommand cmd = new CUBRIDCommand(sql.ToString(), conn)) { using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader()) { while (reader.Read()) { DataRow row = schemaTable.NewRow(); row["VIEW_CATALOG"] = conn.Database; row["VIEW_SCHEMA"] = conn.Database; row["VIEW_NAME"] = reader.GetString(0); schemaTable.Rows.Add(row); } } } }
/// <summary> /// Gets the procedures matching the procedure name filter. /// </summary> /// <param name="filters"> The procedure name filter, value is {"procedure name pattern"}. It should be a string array with Length==1.<para/> /// If filters == null or Length == 0, the default filters {"%"} is used. If the Length > 1, the first procedure name is used. <para/></param> /// <returns>A <see cref="DataTable" /> that contains procedure schema information and contains <para/> /// columns={"PROCEDURE_NAME", "PROCEDURE_TYPE", "RETURN_TYPE", "ARGUMENTS_COUNT", "LANGUAGE", "TARGET", "OWNER"} /// </returns> public DataTable GetProcedures(string[] filters) { using (DataTable dt = new DataTable("Procedures")) { dt.Columns.Add(new DataColumn("PROCEDURE_NAME", typeof(string))); dt.Columns.Add(new DataColumn("PROCEDURE_TYPE", typeof(string))); dt.Columns.Add(new DataColumn("RETURN_TYPE", typeof(string))); dt.Columns.Add(new DataColumn("ARGUMENTS_COUNT", typeof(int))); dt.Columns.Add(new DataColumn("LANGUAGE", typeof(string))); dt.Columns.Add(new DataColumn("TARGET", typeof(string))); dt.Columns.Add(new DataColumn("OWNER", typeof(string))); string procedureName = "%"; if (filters != null && filters.Length > 0 && filters[0] != null) { procedureName = filters[0]; } string sql = String.Format("select * from db_stored_procedure where sp_name like '{0}'", procedureName); using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader()) { while (reader.Read()) { DataRow row = dt.NewRow(); row["PROCEDURE_NAME"] = reader.GetString(0); row["PROCEDURE_TYPE"] = reader.GetString(1); row["RETURN_TYPE"] = reader.GetString(2); row["ARGUMENTS_COUNT"] = reader.GetInt(3); row["LANGUAGE"] = reader.GetString(4); row["TARGET"] = reader.GetString(5); row["OWNER"] = reader.GetString(6); dt.Rows.Add(row); } } } return(dt); } }
/// <summary> /// Get the schemas of the foreign keys satisfying the foreign key filter. /// </summary> /// <param name="filters"> The foreign key filter, the value is {"table name pattern", "foreign key name pattern"}.<para/> /// If the table name pattern is null, the default "%" is used. If the foreign key name pattern is null, the default "" is used. </param> /// <returns>A <see cref="DataTable" /> that contains foreign key schema information and contains <para/> /// columns {"PKTABLE_NAME", "PKCOLUMN_NAME", "FKTABLE_NAME", "FKCOLUMN_NAME", "KEY_SEQ", "UPDATE_ACTION", "DELETE_ACTION", "FK_NAME", "PK_NAME"} </returns> public DataTable GetForeignKeys(string[] filters) { if (filters == null) throw new ArgumentNullException(Utils.GetStr(MsgId.NoFiltersSpecified)); if (filters.Length > 2) throw new ArgumentException(Utils.GetStr(MsgId.IncorrectNumberOfFilters)); string tableName = filters[0]; string keyName = ""; if (filters.Length > 1) { keyName = filters[1]; } T_CCI_ERROR err = new T_CCI_ERROR(); int handle = CciInterface.cci_schema_info(conn, T_CCI_SCH_TYPE.CCI_SCH_IMPORTED_KEYS, tableName, keyName, (char)0, ref err); if (handle < 0) { throw new CUBRIDException(err.err_msg); } ColumnMetaData[] columnInfos = CciInterface.cci_get_result_info(handle); CUBRIDCommand command = new CUBRIDCommand(null,conn); CUBRIDDataReader reader = new CUBRIDDataReader(command, handle, columnInfos.Length, columnInfos, columnInfos.Length); DataTable dt = new DataTable("ForeignKeys"); dt.Columns.Add("PKTABLE_NAME", typeof(string)); dt.Columns.Add("PKCOLUMN_NAME", typeof(string)); dt.Columns.Add("FKTABLE_NAME", typeof(string)); dt.Columns.Add("FKCOLUMN_NAME", typeof(string)); dt.Columns.Add("KEY_SEQ", typeof(short)); dt.Columns.Add("UPDATE_ACTION", typeof(short)); dt.Columns.Add("DELETE_ACTION", typeof(short)); dt.Columns.Add("FK_NAME", typeof(string)); dt.Columns.Add("PK_NAME", typeof(string)); while (reader.Read()) { DataRow row = dt.NewRow(); row["PKTABLE_NAME"] = reader.GetString(0); row["PKCOLUMN_NAME"] = reader.GetString(1); row["FKTABLE_NAME"] = reader.GetString(2); row["FKCOLUMN_NAME"] = reader.GetString(3); row["KEY_SEQ"] = reader.GetString(4); row["UPDATE_ACTION"] = reader.GetString(5); row["DELETE_ACTION"] = reader.GetString(6); row["FK_NAME"] = reader.GetString(7); row["PK_NAME"] = reader.GetString(8); dt.Rows.Add(row); } return dt; }
/// <summary> /// Get the schemas of the foreign keys satisfying the foreign key filter. /// </summary> /// <param name="filters"> The foreign key filter, the value is {"table name pattern", "foreign key name pattern"}.<para/> /// If the table name pattern is null, the default "%" is used. If the foreign key name pattern is null, the default "" is used. </param> /// <returns>A <see cref="DataTable" /> that contains foreign key schema information and contains <para/> /// columns {"PKTABLE_NAME", "PKCOLUMN_NAME", "FKTABLE_NAME", "FKCOLUMN_NAME", "KEY_SEQ", "UPDATE_ACTION", "DELETE_ACTION", "FK_NAME", "PK_NAME"} </returns> public DataTable GetForeignKeys(string[] filters) { if (filters == null) { throw new ArgumentNullException(Utils.GetStr(MsgId.NoFiltersSpecified)); } if (filters.Length > 2) { throw new ArgumentException(Utils.GetStr(MsgId.IncorrectNumberOfFilters)); } string tableName = filters[0]; string keyName = ""; if (filters.Length > 1) { keyName = filters[1]; } T_CCI_ERROR err = new T_CCI_ERROR(); int handle = CciInterface.cci_schema_info(conn, T_CCI_SCH_TYPE.CCI_SCH_IMPORTED_KEYS, tableName, keyName, (char)0, ref err); if (handle < 0) { throw new CUBRIDException(err.err_msg); } ColumnMetaData[] columnInfos = CciInterface.cci_get_result_info(conn, handle); CUBRIDCommand command = new CUBRIDCommand(null, conn); CUBRIDDataReader reader = new CUBRIDDataReader(command, handle, columnInfos.Length, columnInfos, columnInfos.Length); DataTable dt = new DataTable("ForeignKeys"); dt.Columns.Add("PKTABLE_NAME", typeof(string)); dt.Columns.Add("PKCOLUMN_NAME", typeof(string)); dt.Columns.Add("FKTABLE_NAME", typeof(string)); dt.Columns.Add("FKCOLUMN_NAME", typeof(string)); dt.Columns.Add("KEY_SEQ", typeof(short)); dt.Columns.Add("UPDATE_ACTION", typeof(short)); dt.Columns.Add("DELETE_ACTION", typeof(short)); dt.Columns.Add("FK_NAME", typeof(string)); dt.Columns.Add("PK_NAME", typeof(string)); while (reader.Read()) { DataRow row = dt.NewRow(); row["PKTABLE_NAME"] = reader.GetString(0); row["PKCOLUMN_NAME"] = reader.GetString(1); row["FKTABLE_NAME"] = reader.GetString(2); row["FKCOLUMN_NAME"] = reader.GetString(3); row["KEY_SEQ"] = reader.GetString(4); row["UPDATE_ACTION"] = reader.GetString(5); row["DELETE_ACTION"] = reader.GetString(6); row["FK_NAME"] = reader.GetString(7); row["PK_NAME"] = reader.GetString(8); dt.Rows.Add(row); } return(dt); }
private void LoadTableColumns(DataTable dt, string tableName, string columnRestriction) { string sql = String.Format( "select * from db_attribute where class_name like '{0}' and attr_name like '{1}' order by def_order asc", tableName, columnRestriction); using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn)) { int pos = 1; using (CUBRIDDataReader reader = (CUBRIDDataReader)cmd.ExecuteReader()) { while (reader.Read()) { string colName = reader.GetString(0); DataRow row = dt.NewRow(); row["TABLE_CATALOG"] = conn.Database; row["TABLE_SCHEMA"] = conn.Database; row["TABLE_NAME"] = tableName; row["COLUMN_NAME"] = colName; row["ORDINAL_POSITION"] = pos++; for (int i = 0; i < reader.GetColumnCount(); i++) { switch (reader.GetColumnName(i)) { case "defalut_value": row["COLUMN_DEFAULT"] = reader.GetString(i); break; case "is_nullable": row["IS_NULLABLE"] = reader.GetString(i).Equals("YES"); break; case "data_type": row["DATA_TYPE"] = reader.GetString(i); break; case "prec": row["NUMERIC_PRECISION"] = reader.GetInt(i); break; case "scale": row["NUMERIC_SCALE"] = reader.GetInt(i); break; case "code_set": case "charset": row["CHARACTER_SET"] = reader.GetString(i); break; default: break; } } dt.Rows.Add(row); } } } }