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);
}
}
}
}
internal CUBRIDDataReader ExecuteInternal()
{
T_CCI_ERROR err = new T_CCI_ERROR();
int ret = CciInterface.cci_execute(handle, (char)CCIExecutionOption.CCI_EXEC_QUERY_ALL, 0, ref err);
if (ret < 0)
{
throw new CUBRIDException(err.err_msg);
}
//T_CCI_COL_INFO res;
columnInfos = CciInterface.cci_get_result_info(conn, handle);
dataReader = new CUBRIDDataReader(this, handle, ret, columnInfos, ret);
return(dataReader);
}
/// <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>
/// Executes the query and returns the first column of the first row in the result set returned by the query. All other columns and rows are ignored.
/// </summary>
/// <returns> The first column of the first row in the result set. </returns>
public override object ExecuteScalar()
{
object ret = null;
BindParameters();
using (CUBRIDDataReader dr = ExecuteInternal())
{
if (dr.Read())
{
ret = dr.GetValue(0);
}
if (ret == null)
{
if (dr.GetColumnCount() != 0)
{
ret = DBNull.Value;
}
}
dr.Close();
}
return(ret);
}
internal CUBRIDDataReader ExecuteInternal()
{
T_CCI_ERROR err = new T_CCI_ERROR();
int ret = CciInterface.cci_execute(handle, (char)CCIExecutionOption.CCI_EXEC_QUERY_ALL, 0, ref err);
if (ret < 0)
{
throw new CUBRIDException (err.err_msg);
}
//T_CCI_COL_INFO res;
columnInfos = CciInterface.cci_get_result_info (handle);
dataReader = new CUBRIDDataReader (this, handle, ret, columnInfos, ret);
return dataReader;
}
/// <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);
}
}
}
}