public override IList <ClassColumn> GetListColumn(IDbConnection connection, IDbTransaction trans, string DatabaseName, string TableName)
{
StringBuilder sql = new StringBuilder();
if (!String.IsNullOrEmpty(TableName))
{
sql.Append(" select * from information_schema.columns where table_schema = '" + "public" + "' and table_name ='" + TableName + "' ");
}
else
{
sql.Append(" select * from information_schema.columns where table_schema = '" + "public" + "' ");
}
DataTable dt = myBaseDao.DoGetDataTable(connection, trans, sql.ToString());
IList <ClassColumn> list = new List <ClassColumn>();
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
ClassColumn record = new ClassColumn();
if (row != null)
{
if (row["table_name"] != System.DBNull.Value)
{
record.TableName = row["table_name"].ToString();
}
if (row["column_name"] != System.DBNull.Value)
{
record.ColumnName = row["column_name"].ToString();
}
if (row["data_type"] != System.DBNull.Value)
{
record.ColumnDefine += " " + row["data_type"].ToString();
}
if (row["is_nullable"] != System.DBNull.Value)
{
if (row["is_nullable"].ToString() == "NO")
{
record.ColumnDefine += " " + "NOT NULL";
if (row["ordinal_position"] != System.DBNull.Value)
{
if ((int)row["ordinal_position"] == 1)
{
record.isPRIMARY = true;
}
}
}
}
}
list.Add(record);
}
}
return(list);
}
public override IList <ClassColumn> GetListIndex(IDbConnection connection, IDbTransaction trans, string DatabaseName, string TableName)
{
StringBuilder sql = new StringBuilder();
if (!String.IsNullOrEmpty(TableName))
{
sql.Append(String.Format("SELECT relname FROM pg_class WHERE oid IN (SELECT indexrelid FROM pg_index, pg_class WHERE pg_class.relname='{0}' AND pg_class.oid=pg_index.indrelid AND indisunique != 't' AND indisprimary != 't')", TableName));
DataTable dt = myBaseDao.DoGetDataTable(connection, trans, sql.ToString());
IList <ClassColumn> list = new List <ClassColumn>();
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
ClassColumn record = new ClassColumn();
if (row != null)
{
if (row["relname"] != System.DBNull.Value)
{
record.ColumnName = row["relname"].ToString();
}
}
list.Add(record);
}
}
return(list);
}
else
{
sql.Append("SELECT idx.indrelid :: REGCLASS AS table_name, i.relname AS index_name FROM pg_index AS idx JOIN pg_class AS i ON i.oid = idx.indexrelid JOIN pg_am AS am ON i.relam = am.oid JOIN pg_namespace AS NS ON i.relnamespace = NS.OID JOIN pg_user AS U ON i.relowner = U.usesysid WHERE NOT nspname LIKE 'pg%' and idx.indisunique != 't' and idx.indisprimary != 't';");
DataTable dt = myBaseDao.DoGetDataTable(connection, trans, sql.ToString());
IList <ClassColumn> list = new List <ClassColumn>();
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
ClassColumn record = new ClassColumn();
if (row != null)
{
if (row["table_name"] != System.DBNull.Value)
{
record.TableName = row["table_name"].ToString();
}
if (row["index_name"] != System.DBNull.Value)
{
record.ColumnName = row["index_name"].ToString();
}
}
list.Add(record);
}
}
return(list);
}
}
public override IList <ClassColumn> GetListColumn(IDbConnection connection, IDbTransaction trans, string DatabaseName, string TableName)
{
StringBuilder sql = new StringBuilder();
sql.Append(" pragma table_info(" + TableName + "); ");
DataTable dt = myBaseDao.DoGetDataTable(connection, trans, sql.ToString());
IList <ClassColumn> list = new List <ClassColumn>();
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
ClassColumn record = new ClassColumn();
if (row != null)
{
if (row["name"] != System.DBNull.Value)
{
record.ColumnName = row["name"].ToString();
}
if (row["Type"] != System.DBNull.Value)
{
record.ColumnDefine += " " + row["Type"].ToString();
}
record.isPRIMARY = false;
if (row["pk"] != System.DBNull.Value)
{
if (row["pk"].ToString() == "1")
{
record.isPRIMARY = true;
record.ColumnDefine += " PRIMARY KEY ";
}
}
}
list.Add(record);
}
}
return(list);
}
