public List <dynamic> GetColumns(Util.SqlHelper sqlHelper, string dataBaseName, string tableName)
{
var list = new List <dynamic>();
var dr = sqlHelper.ExecuteReader("select name from sysibm.syscolumns where upper(tbname)=upper('" + tableName + "')");
while (dr.Read())
{
dynamic eo = new ExpandoObject();
eo.name = dr["name"].ToString().FirstLetterToUpper();
list.Add(eo);
}
dr.Close();
dr.Dispose();
return(list);
}
public List <dynamic> GetTables(Util.SqlHelper sqlHelper, string dataBaseName)
{
var list = new List <dynamic>();
var dr = sqlHelper.ExecuteReader("select TABLE_NAME name from user_tables");
while (dr.Read())
{
dynamic eo = new ExpandoObject();
eo.name = dr["name"].ToString().FirstLetterToUpper();
list.Add(eo);
}
dr.Close();
dr.Dispose();
return(list);
}
public List <dynamic> GetTables(Util.SqlHelper sqlHelper, string dataBaseName)
{
var list = new List <dynamic>();
var dr = sqlHelper.ExecuteReader("select name from sysibm.systables where type='T' and upper(creator) = upper('" + getUser(sqlHelper.DbConnectionString) + "')");
while (dr.Read())
{
dynamic eo = new ExpandoObject();
eo.name = dr["name"].ToString().FirstLetterToUpper();
list.Add(eo);
}
dr.Close();
dr.Dispose();
return(list);
}
public List <dynamic> GetColumns(Util.SqlHelper sqlHelper, string dataBaseName, string tableName)
{
var list = new List <dynamic>();
var dr = sqlHelper.ExecuteReader("select COLUMN_NAME name, DATA_TYPE type,DATA_LENGTH length,NULLABLE isnullable from all_tab_columns where upper(table_name)=upper('" + tableName + "') and owner='" + getUser(sqlHelper.DbConnectionString) + "'");
while (dr.Read())
{
dynamic eo = new ExpandoObject();
eo.name = dr["name"].ToString().FirstLetterToUpper();
eo.type = dr["type"].ToString();
eo.length = dr["length"].ToString();
eo.isnullable = dr["isnullable"].ToString();
list.Add(eo);
}
dr.Close();
dr.Dispose();
return(list);
}
public TaskLogDAL(string connectString)
{
dbHelper = new Util.SqlHelper(connectString);
}
public string GetColumnType(Util.SqlHelper sqlHelper, string dataBaseName, string tableName, string columnName)
{
if (tableName != _lastTableName || _tableInfos == null)
{
_tableInfos = new List <dynamic>();
DbDataReader dr =
sqlHelper.ExecuteReader(string.Format("SELECT format_type(a.atttypid,a.atttypmod) as data_type,a.attname as column_name from pg_class as c,pg_attribute as a where c.relname = '{0}' and a.attrelid = c.oid and a.attnum>0 ", tableName));
while (dr.Read())
{
string type = dr["data_type"].ToString().ToLower();
#region typedef
//cidr,inet =ipv4 or ipv6 / interval timespan
if (type == "macaddr" || type == "interval" || type == "inet" || type == "cidr" ||
type == "bit" || type == "bit varying" || type == "character" ||
type == "text" || type == "character varying")
{
type = "string";
}
else if (type == "real")
{
type = "float";
}
else if (type == "double precision")
{
type = "double";
}
else if (type.Contains("numeric") || type == "money")
{
type = "decimal";
}
else if (type == "bigint" || type == "bigserial")
{
type = "long";
}
else if (type == "integer" || type == "serial")
{
type = "int";
}
else if (type == "smallint")
{
type = "short";
}
else if (type == "boolean")
{
type = "bool";
}
else if (type == "date" || type.Contains("time"))
{
type = "DateTime";
}
else if (type == "bytea")
{
type = "byte[]";
}
else
{
type = "string";
}
#endregion
dynamic tableInfo = new ExpandoObject();
tableInfo.columnName = dr["column_name"].ToString();
tableInfo.type = type;
_tableInfos.Add(tableInfo);
}
dr.Close();
dr.Dispose();
}
_lastTableName = tableName;
foreach (dynamic item in _tableInfos)
{
if (item.columnName == columnName)
{
return(item.type);
}
}
return("string");
}
public List <dynamic> GetColumns(Util.SqlHelper sqlHelper, string dataBaseName, string tableName)
{
return(sqlHelper.ExecuteDynamic(string.Format("select column_name as name,data_type as type,character_maximum_length length,is_nullable as isnullable from information_schema.columns where table_name='{0}'", tableName)));
}
public List <dynamic> GetTables(Util.SqlHelper sqlHelper, string dataBaseName)
{
return
(sqlHelper.ExecuteDynamic(
"select tablename as name from pg_tables where tablename not like 'pg_%' and tablename not like 'sql_%' order by tablename"));
}
public object GetPrimaryKey(Util.SqlHelper sqlHelper, string dataBaseName, string tableName)
{
return
(sqlHelper.ExecuteScalar(string.Format(
"select pg_attribute.attname as name from pg_constraint inner join pg_class on pg_constraint.conrelid = pg_class.oid inner join pg_attribute on pg_attribute.attrelid = pg_class.oid and pg_attribute.attnum = pg_constraint.conkey[1] inner join pg_type on pg_type.oid = pg_attribute.atttypid where pg_class.relname = '{0}' and pg_constraint.contype='p'", tableName)));
}
public object GetPrimaryKey(Util.SqlHelper sqlHelper, string dataBaseName, string tableName)
{
object pk = sqlHelper.ExecuteScalar("select column_name from user_cons_columns where constraint_name=(select constraint_name from user_constraints where upper(table_name)=upper('" + tableName + "') and constraint_type='P')");
return(pk == null ? null : pk.ToString().FirstLetterToUpper());
}
public string GetColumnType(Util.SqlHelper sqlHelper, string dataBaseName, string tableName, string columnName)
{
if (tableName != _lastTableName || _tableInfos == null)
{
_tableInfos = new List <dynamic>();
var dr =
sqlHelper.ExecuteReader("select column_name name,data_type typename from all_tab_columns where upper(table_name)=upper('" + tableName + "') and owner='" + getUser(sqlHelper.DbConnectionString) + "'");
while (dr.Read())
{
string type = dr["typename"].ToString().ToLower();
#region typedef
if (type == "varchar" || type == "char" || type == "varchar2")
{
type = "string";
}
else if (type == "float" || type == "integer" || type == "number")
{
type = "decimal";
}
else if (type == "interval year to month")
{
type = "int";
}
else if (type == "date" || type == "timestamp")
{
type = "DateTime";
}
else if (type == "interval day to second")
{
type = "TimeSpan";
}
else if (type == "bfile" || type == "blob" || type == "long raw" || type == "raw")
{
type = "byte[]";
}
else
{
type = "string";
}
#endregion
dynamic tableInfo = new ExpandoObject();
tableInfo.columnName = dr["name"].ToString().FirstLetterToUpper();
tableInfo.type = type;
_tableInfos.Add(tableInfo);
}
dr.Close();
dr.Dispose();
}
_lastTableName = tableName;
foreach (dynamic item in _tableInfos)
{
if (item.columnName == columnName)
{
return(item.type);
}
}
return("string");
}
public object GetPrimaryKey(Util.SqlHelper sqlHelper, string dataBaseName, string tableName)
{
object pk = sqlHelper.ExecuteScalar("select name from sysibm.syscolumns where upper(tbname) = upper('" + tableName + "') and keyseq > 0 order by keyseq asc");
return(pk == null ? null : pk.ToString().FirstLetterToUpper());
}
public string GetColumnType(Util.SqlHelper sqlHelper, string dataBaseName, string tableName, string columnName)
{
if (tableName != _lastTableName || _tableInfos == null)
{
_tableInfos = new List <dynamic>();
var dr =
sqlHelper.ExecuteReader("select name,typename from sysibm.syscolumns where upper(tbname)=upper('" + tableName + "')");
while (dr.Read())
{
string type = dr["typename"].ToString().ToLower();
#region typedef
if (type == "varchar" || type == "char" || type == "character")
{
type = "string";
}
else if (type == "real")
{
type = "float";
}
else if (type == "double" || type == "double precision" || type == "float")
{
type = "double";
}
else if (type == "decimal" || type == "numeric" || type == "num" || type == "dec" || type == "decfloat")
{
type = "decimal";
}
else if (type == "bigint")
{
type = "long";
}
else if (type == "integer")
{
type = "int";
}
else if (type == "smallint")
{
type = "short";
}
else if (type == "date" || type == "time" || type == "timestamp")
{
type = "DateTime";
}
else if (type == "xml" || type == "varbinary" || type == "binary" || type == "char for bit data" || type == "long varchar for bit data" || type == "blob" || type == "rowid")
{
type = "byte[]";
}
else
{
type = "string";
}
#endregion
dynamic tableInfo = new ExpandoObject();
tableInfo.columnName = dr["name"].ToString().FirstLetterToUpper();
tableInfo.type = type;
_tableInfos.Add(tableInfo);
}
dr.Close();
dr.Dispose();
}
_lastTableName = tableName;
foreach (dynamic item in _tableInfos)
{
if (item.columnName == columnName)
{
return(item.type);
}
}
return("string");
}
