public static ArrayList GetListTables(string filter)
{
ArrayList list = new ArrayList();
try
{
DataTable dt = SqlHelperSqlBase.ExecuteDataTable(Session.ConnectionString, CommandType.Text, "select * from systables order by 1,2");
foreach (DataRow dr in dt.Rows)
{
if ((dr["CREATOR"].ToString() != "SYSADM") && (dr["CREATOR"].ToString() != "SYSSQL"))
{
if (!filter.Equals(string.Empty))
{
if (dr["NAME"].ToString().StartsWith(filter))
{
list.Add(dr["NAME"].ToString());
}
}
else
{
list.Add(dr["NAME"].ToString());
}
}
}
}
catch (Exception ex)
{
}
return(list);
}
private Dictionary <string, KeyColumn> GetKeys(string tableName)
{
Dictionary <string, KeyColumn> keys = new Dictionary <string, KeyColumn>();
string query = string.Format("SELECT A.COLNAME,B.COLTYPE,B.LENGTH FROM SYSPKCONSTRAINTS A,SYSCOLUMNS B WHERE TBNAME = '{0}' AND B.NAME= A.COLNAME AND B.TBNAME= A.NAME", tableName);
DataTable dt = SqlHelperSqlBase.ExecuteDataTable(Session.ConnectionString, CommandType.Text, query);
KeyColumn column = new KeyColumn();
string tableNamePrevious = string.Empty;
//column = new KeyColumn();
//column.Name = "B07_SN_PK";
//column.Type = "INTEGER";
//column.Size = 4;
//keys.Add(column.Name, column);
foreach (DataRow dr in dt.Rows)
{
column = new KeyColumn();
column.Name = dr["A.COLNAME"].ToString();
column.Type = dr["B.COLTYPE"].ToString();
column.Size = int.Parse(dr["B.LENGTH"].ToString());
column.NetType = GetDBType(column.Type);
if (!keys.ContainsKey(column.Name))
{
keys.Add(column.Name, column);
}
}
return(keys);
}
private static SQLBaseDataReader GetReaderInformationPlus(string tableName, string columnName)
{
string sqlDatabase = string.Format("SELECT value as DescriptionPlus FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '{0}', 'column', '{1}')", tableName, columnName);
SQLBaseConnection connection = new SQLBaseConnection(Session.ConnectionString);
try
{
if (connection == null)
{
return(null);
}
SQLBaseDataReader dr = SqlHelperSqlBase.ExecuteReader(connection, CommandType.Text, sqlDatabase);
// if (dr.HasRows)
// return dr;
// else
return(null);
}
catch
{
return(null);
}
}
public static DataTable GetIndex(string tableName)
{
return(SqlHelperSqlBase.ExecuteDataTable(Session.ConnectionString, CommandType.Text, string.Format("SELECT SYSKEYS.IXNAME,SYSKEYS.COLNAME,SYSKEYS.COLSEQ,SYSINDEXES.TBNAME,SYSINDEXES.UNIQUERULE FROM SYSKEYS INNER JOIN SYSINDEXES ON SYSKEYS.IXNAME = SYSINDEXES.NAME WHERE SYSINDEXES.TBNAME = '{0}' AND SYSINDEXES.UNIQUERULE = 'U' ORDER BY SYSKEYS.IXNAME,SYSKEYS.COLSEQ", tableName)));
}
public static DataTable GetRelationship(string tableName)
{
return(SqlHelperSqlBase.ExecuteDataTable(Session.ConnectionString, CommandType.Text, string.Format("SELECT * FROM SYSFKCONSTRAINTS WHERE NAME = '{0}'", tableName)));
}
public static DataTable GetColumns(string tableName)
{
return(SqlHelperSqlBase.ExecuteDataTable(Session.ConnectionString, CommandType.Text, string.Format("SELECT * FROM SYSCOLUMNS WHERE TBNAME = '{0}'", tableName)));
}
