List <ColumnInfo> LoadColumns(TableInfo tbl, IList <ColumnInfo> dataColumnDTOs, IList <ColumnInfo> dataColumnCommentDTOs)
{
var result = new List <ColumnInfo>();
if (dataColumnDTOs != null && dataColumnDTOs.Count > 0)
{
var currentTableColumns = dataColumnDTOs.Where(pr => pr.TableName == tbl.TableName);
int index = 1;
foreach (var item in currentTableColumns)
{
ColumnInfo col = new ColumnInfo();
col.ColumnName = item.ColumnName;
col.RawType = (item.RawType);
col.DefaultValue = item.DefaultValue;
col.ColumnLength = (item.ColumnLength);
col.ColumnPrecision = (item.ColumnPrecision);
col.ColumnScale = (item.ColumnScale);
col.OrdinalPosition = index;// CleanUpHelper.ToInt(item.OrdinalPosition.ToString());
col.IsNullable = item.IsNullable.ToString() == "Y";
col.PropertyName = CleanUpHelper.CleanUp(col.ColumnName);
col.PropertyType = GetPropertyType(col.RawType, (item.ColumnScale), item.ColumnPrecision);
col.DataType = GetDataType(col.PropertyType);
var objTableComent = dataColumnCommentDTOs.FirstOrDefault(pr => pr.TableName == tbl.TableName && pr.ColumnName == col.ColumnName);
col.ColumnDescription = objTableComent != null ? objTableComent.ColumnDescription : "";
result.Add(col);
index++;
}
}
return(result);
}
List <ColumnInfo> LoadColumns(TableInfo tbl, IList <ColumnInfo> dataColumnDTOs, IList <ColumnInfo> dataColumnDTO2s)
{
var result = new List <ColumnInfo>();
if (dataColumnDTO2s != null && dataColumnDTO2s.Count > 0)
{
var currentTableColumns = dataColumnDTO2s.Where(pr => pr.Schema == tbl.Schema && pr.TableName == tbl.TableName);
foreach (var item in currentTableColumns)
{
ColumnInfo col = new ColumnInfo();
col.ColumnName = item.ColumnName;
col.RawType = (item.RawType);
col.PropertyName = CleanUpHelper.CleanUp(col.ColumnName);
col.PropertyType = GetPropertyType(col.RawType);
col.DataType = GetDataType(col.PropertyType);
col.DefaultValue = item.DefaultValue;
col.CharacterMaximumLength = (item.CharacterMaximumLength);
col.OrdinalPosition = ToInt(item.OrdinalPosition.ToString());
var objTableComent = dataColumnDTOs.FirstOrDefault(pr => pr.TableName == tbl.TableName && pr.ColumnName == col.ColumnName);
col.ColumnScale = objTableComent != null ? objTableComent.ColumnScale : 0;
col.ColumnPrecision = objTableComent != null ? objTableComent.ColumnPrecision : 0;
col.ColumnDescription = objTableComent != null ? objTableComent.ColumnDescription : "";
col.ColumnLength = objTableComent != null ? objTableComent.ColumnLength : col.ColumnLength;
col.IsNullable = item.IsNullable.ToString() == "YES";
col.IsAutoIncrement = Convert.ToInt32(item.IsAutoIncrement) == 1;
result.Add(col);
}
}
return(result);
}
private static void CleanUp(CleanUpOptions options)
{
// Kills the existing process
var procList = Process.GetProcessesByName("ListenToIt.Runner");
foreach (var proc in procList)
{
proc.Kill();
}
var cleanupHelper = new CleanUpHelper(options);
cleanupHelper.Merge();
// Restart the Runner
new Process {
StartInfo = new ProcessStartInfo {
FileName = Path.Combine(Path.GetFullPath("../"), "ListenToIt.Runner.exe"),
CreateNoWindow = false,
UseShellExecute = true
}
}.Start();
}
private void _btnCleanSystem_Click(object sender, System.EventArgs e)
{
CleanUpHelper frm = new CleanUpHelper();
frm.ShowDialog();
}
public override List <TableInfo> GetTableInfos(bool isCache = true)
{
if (isCache == true)
{
if (cacheTableInfos != null)
{
return(cacheTableInfos);
}
}
List <TableInfo> tables = new List <TableInfo>();
string TABLE_SQL = @"select tbl_name TABLENAME, sql CREATESQL from sqlite_master where type='table' order by rootpage ";
using (IDataReader reader = ExecuteQuery((TABLE_SQL)))
{
while (reader.Read())
{
TableInfo tbl = new TableInfo();
tbl.TableName = reader["TABLENAME"].ToString();
tbl.CreateSQL = reader["CREATESQL"].ToString();
tables.Add(tbl);
}
}
foreach (var table in tables)
{
table.Columns = new List <ColumnInfo>();
using (IDataReader reader = ExecuteQuery(string.Format("PRAGMA table_info([{0}])", table.TableName)))
{
while (reader.Read())
{
ColumnInfo col = new ColumnInfo();
col.ColumnName = reader["name"].ToString();
//col.ColumnLength = item.Size;
col.IsPrimaryKey = reader["pk"].ToString() == "1";
col.IsNullable = reader["notnull"].ToString() == "0";
col.DefaultValue = reader["dflt_value"];
col.RawType = reader["type"].ToString();
col.PropertyName = CleanUpHelper.CleanUp(col.ColumnName);
col.PropertyType = GetPropertyType(col.RawType);
col.DataType = GetDataType(col.PropertyType);
table.Columns.Add(col);
}
}
//var columns = GetColumns(table.TableName);
//if (columns != null)
//{
// foreach (var item in columns)
// {
// ColumnInfo col = new ColumnInfo();
// col.ColumnName = item.Name;
// col.ColumnLength = item.Size;
// col.IsPrimaryKey = item.IsPrimaryKey;
// col.IsNullable = !item.IsNotNull ;
// col.DefaultValue = item.DefaultValue;
// col.RawType = item.TypeString;
// col.PropertyName = CleanUpHelper.CleanUp(col.ColumnName);
// col.PropertyType = GetPropertyType(col.RawType);
// col.DataType = GetDataType(col.PropertyType);
// table.Columns.Add(col);
// }
//}
}
if (isCache == true)
{
cacheTableInfos = tables;
}
return(tables);
}
public override List <TableInfo> GetTableInfos(bool isCache = true)
{
if (isCache == true)
{
if (cacheTableInfos != null)
{
return(cacheTableInfos);
}
}
List <TableInfo> tables = new List <TableInfo>();
// string TABLE_SQL = @"
//SELECT *
//FROM information_schema.tables
//WHERE (table_type='BASE TABLE' ) AND TABLE_SCHEMA = '{0}'
//";
string TABLE_SQL = @"
SELECT *
FROM information_schema.tables
WHERE (table_type='BASE TABLE' )
" ;
//@"select TABLE_NAME as Name,TABLE_COMMENT as Description from information_schema.tables
// where TABLE_SCHEMA=(select database()) AND TABLE_TYPE='BASE TABLE'";
var schemaStr = string.IsNullOrEmpty(_schemaName) ? string.Empty : " AND TABLE_SCHEMA = '" + (_schemaName) + "' ";
TABLE_SQL = TABLE_SQL + schemaStr;
using (IDataReader reader = ExecuteQuery(string.Format(TABLE_SQL, _schemaName)))
{
while (reader.Read())
{
TableInfo tbl = new TableInfo();
tbl.TableName = reader["TABLE_NAME"].ToString();
tbl.Schema = reader["TABLE_SCHEMA"].ToString();
tbl.TableDescription = reader["TABLE_COMMENT"].ToString();
tables.Add(tbl);
}
}
Database.EnsureOpenConnection();
//this will return everything for the DB
var schema = ((System.Data.Common.DbConnection)Database.Connection).GetSchema("COLUMNS");// ((System.Data.Common.DbConnection)_connection).GetSchema("COLUMNS");
//loop again - but this time pull by table name
foreach (var item in tables)
{
item.Columns = new List <ColumnInfo>();
//pull the columns from the schema
var columns = schema.Select("TABLE_NAME='" + item.TableName + "'");
foreach (var row in columns)
{
ColumnInfo col = new ColumnInfo();
col.ColumnName = row["COLUMN_NAME"].ToString();
col.RawType = row["COLUMN_TYPE"].ToString();
col.PropertyName = CleanUpHelper.CleanUp(col.ColumnName);
col.PropertyType = GetPropertyType(col.RawType);
col.DataType = GetDataType(col.PropertyType);
col.DefaultValue = row["COLUMN_DEFAULT"];
col.ColumnScale = ToInt(row["NUMERIC_SCALE"].ToString());
col.ColumnPrecision = ToInt(row["NUMERIC_PRECISION"].ToString());
col.ColumnLength = ToInt(row["CHARACTER_MAXIMUM_LENGTH"].ToString());
col.OrdinalPosition = ToInt(row["ORDINAL_POSITION"].ToString());
col.IsNullable = row["IS_NULLABLE"].ToString() == "YES";
col.IsPrimaryKey = row["COLUMN_KEY"].ToString() == "PRI";
col.IsAutoIncrement = row["extra"].ToString().ToLower().IndexOf("auto_increment") >= 0;
col.ColumnDescription = row["COLUMN_COMMENT"].ToString();
item.Columns.Add(col);
}
}
if (isCache == true)
{
cacheTableInfos = tables;
}
return(tables);
}
List <ColumnInfo> LoadColumns(TableInfo tbl)
{
string COLUMN_SQL = @" SELECT
col.table_schema ,
col.table_name ,
col.ordinal_position,
col.column_name ,
col.data_type udt_name,
col.character_maximum_length length,
col.numeric_precision precision,
col.numeric_scale scale,
col.is_nullable,
col.column_default ,
des.description
FROM
information_schema.columns col LEFT JOIN pg_description des
ON col.table_name::regclass = des.objoid
AND col.ordinal_position = des.objsubid
WHERE
table_schema = @schemaName
AND table_name =@tableName
ORDER BY
ordinal_position;";
Dictionary <string, object> p = new Dictionary <string, object>();
p.Add("tableName", tbl.TableName);
p.Add("schemaName", tbl.Schema);
var rdr = Database.ExecuteReader(COLUMN_SQL, p);
var result = new List <ColumnInfo>();
using ( rdr )
{
while (rdr.Read())
{
ColumnInfo col = new ColumnInfo();
col.ColumnName = rdr["column_name"].ToString();
col.RawType = (rdr["udt_name"].ToString());
col.IsNullable = rdr["is_nullable"].ToString() == "YES";
col.IsAutoIncrement = rdr["column_default"].ToString().StartsWith("nextval(");
col.PropertyName = CleanUpHelper.CleanUp(col.ColumnName);
col.PropertyType = GetPropertyType(col.RawType);
col.DataType = GetDataType(col.PropertyType);
col.DefaultValue = rdr["column_default"];
col.ColumnLength = ToInt(rdr["length"].ToString());
col.OrdinalPosition = ToInt(rdr["ordinal_position"].ToString());
col.ColumnScale = ToInt(rdr["scale"].ToString());
col.ColumnPrecision = ToInt(rdr["precision"].ToString());
col.ColumnDescription = rdr["description"].ToString();
result.Add(col);
}
}
return(result);
//using (var cmd = _dbFactory.CreateCommand(COLUMN_SQL, _connection))
//{
// var p = cmd.CreateParameter();
// p.ParameterName = "@tableName";
// p.Value = tbl.TableName;
// cmd.Parameters.Add(p);
// var p1 = cmd.CreateParameter();
// p1.ParameterName = "@schemaName";
// p1.Value = tbl.Schema;
// cmd.Parameters.Add(p1);
// var result = new List<ColumnInfo>();
// using (IDataReader rdr = cmd.ExecuteReader())
// {
// while (rdr.Read())
// {
// ColumnInfo col = new ColumnInfo();
// col.ColumnName = rdr["column_name"].ToString();
// col.RawType = (rdr["udt_name"].ToString());
// col.IsNullable = rdr["is_nullable"].ToString() == "YES";
// col.IsAutoIncrement = rdr["column_default"].ToString().StartsWith("nextval(");
// col.PropertyName = CleanUpHelper.CleanUp(col.ColumnName);
// col.PropertyType = GetPropertyType(col.RawType);
// col.DataType = GetDataType(col.PropertyType);
// col.DefaultValue = rdr["column_default"];
// col.ColumnLength = ToInt(rdr["length"].ToString());
// col.OrdinalPosition = ToInt(rdr["ordinal_position"].ToString());
// col.ColumnScale = ToInt(rdr["scale"].ToString());
// col.ColumnPrecision = ToInt(rdr["precision"].ToString());
// col.ColumnDescription = rdr["description"].ToString();
// result.Add(col);
// }
// }
// return result;
//}
}
