internal static TableDefinition GetDefinitionFromTableName(string tableName, IConnectionManager connection)
{
TableDefinition result = new TableDefinition(tableName);
TableColumn curCol = null;
var readMetaSql = new SqlTask($"Read column meta data for table {tableName}",
$@" select cols.name, tpes.name as colname, cols.is_nullable, cols.is_identity
from sys.columns cols
inner join sys.tables tbl
on cols.object_id = tbl.object_id
inner join sys.schemas sc
on tbl.schema_id = sc.schema_id
inner join sys.systypes tpes
on tpes.xtype = cols.system_type_id
where sc.name + '.' + tbl.name = '{tableName}'
and tbl.type = 'U'
and tpes.name <> 'sysname'"
, () => { curCol = new TableColumn(); }
, () => { result.Columns.Add(curCol); }
, reader => {
curCol.Name = DbTask.GetValueFromReader(reader, "name") + "";
curCol.DataType = DbTask.GetValueFromReader(reader, "colname") + "";
curCol.AllowNulls = bool.Parse(DbTask.GetValueFromReader(reader, "is_nullable") + "");
curCol.IsIdentity = bool.Parse(DbTask.GetValueFromReader(reader, "is_identity") + "");
}
)
{
DisableLogging = true,
DisableExtension = true,
ConnectionManager = connection
};
readMetaSql.ExecuteReader();
return(result);
}
public static TableDefinition GetDefinitionFromTableName(string tableName)
{
TableDefinition result = new TableDefinition(tableName);
TableColumn curCol = null;
var readMetaSql = new SqlTask($"Read column meta data for table {tableName}",
$@" select cols.name, tpes.name, cols.is_nullable, cols.is_identity
from sys.columns cols
inner join sys.tables tbl
on cols.object_id = tbl.object_id
inner join sys.schemas sc
on tbl.schema_id = sc.schema_id
inner join sys.systypes tpes
on tpes.xtype = cols.system_type_id
where sc.name + '.' + tbl.name = '{tableName}'
and tbl.type = 'U'
and tpes.name <> 'sysname'"
, () => { curCol = new TableColumn(); }
, () => { result.Columns.Add(curCol); }
, name => curCol.Name = name.ToString()
, colname => curCol.DataType = colname.ToString()
, is_nullable => curCol.AllowNulls = (bool)is_nullable
, is_identity => curCol.IsIdentity = (bool)is_identity
)
{
DisableLogging = true
};
readMetaSql.ExecuteReader();
return(result);
}
public override void Execute()
{
LoadProcess = new LoadProcess();
var sql = new SqlTask(this, Sql)
{
DisableLogging = true,
DisableExtension = true,
Actions = new List <Action <object> >()
{
result => {
LoadProcess.LoadProcessKey = (int)DbTask.GetValueFromReader(result, "LoadProcessKey");
LoadProcess.StartDate = (DateTime)DbTask.GetValueFromReader(result, "StartDate");
LoadProcess.TransferCompletedDate = (DateTime?)DbTask.GetValueFromReader(result, "TransferCompletedDate");
LoadProcess.EndDate = (DateTime?)DbTask.GetValueFromReader(result, "EndDate");
LoadProcess.ProcessName = (string)DbTask.GetValueFromReader(result, "ProcessName");
LoadProcess.StartMessage = (string)DbTask.GetValueFromReader(result, "StartMessage");
LoadProcess.IsRunning = (bool)DbTask.GetValueFromReader(result, "IsRunning");
LoadProcess.EndMessage = (string)DbTask.GetValueFromReader(result, "EndMessage");
LoadProcess.WasSuccessful = (bool)DbTask.GetValueFromReader(result, "WasSuccessful");
LoadProcess.AbortMessage = (string)DbTask.GetValueFromReader(result, "AbortMessage");
LoadProcess.WasAborted = (bool)DbTask.GetValueFromReader(result, "WasAborted");
LoadProcess.IsFinished = (bool)DbTask.GetValueFromReader(result, "IsFinished");
LoadProcess.IsTransferCompleted = (bool)DbTask.GetValueFromReader(result, "IsTransferCompleted");
}
}
};
if (ReadOption == ReadOptions.ReadAllProcesses)
{
sql.BeforeRowReadAction = () => AllLoadProcesses = new List <LoadProcess>();
sql.AfterRowReadAction = () => AllLoadProcesses.Add(LoadProcess);
}
sql.ExecuteReader();
}
public void ExecuteReaderMultiColumn(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture tc = new TwoColumnsTableFixture(connection, "MultiColumnRead");
tc.InsertTestData();
List <MySimpleRow> asIsResult = new List <MySimpleRow>();
List <MySimpleRow> toBeResult = new List <MySimpleRow>()
{
new MySimpleRow(1, "Test1"),
new MySimpleRow(2, "Test2"),
new MySimpleRow(3, "Test3")
};
MySimpleRow CurColumn = new MySimpleRow();
//Act
SqlTask.ExecuteReader(connection,
"Test execute reader",
$"SELECT * FROM {tc.QB}MultiColumnRead{tc.QE}"
, () => CurColumn = new MySimpleRow()
, () => asIsResult.Add(CurColumn)
, colA => CurColumn.Col1 = int.Parse(colA.ToString())
, colB => CurColumn.Col2 = (string)colB
);
//Assert
Assert.Equal(toBeResult, asIsResult);
}
public void ExecuteReaderWithParameter(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture tc = new TwoColumnsTableFixture(connection, "ExecuteReaderWithPar");
tc.InsertTestData();
List <int> asIsResult = new List <int>();
List <int> toBeResult = new List <int>()
{
2
};
List <QueryParameter> parameter = new List <QueryParameter>()
{
new QueryParameter("par1", "NVARCHAR(10)", "Test2")
};
//Act
string PP = "@";
if (connection.GetType() == typeof(OracleConnectionManager))
{
PP = ":";
}
SqlTask.ExecuteReader(connection, "Test execute reader",
$"SELECT {tc.QB}Col1{tc.QE} FROM {tc.QB}ExecuteReaderWithPar{tc.QE} WHERE {tc.QB}Col2{tc.QE} = {PP}par1", parameter,
colA => asIsResult.Add(int.Parse(colA.ToString())));
//Assert
Assert.Equal(toBeResult, asIsResult);
}
public void Execute()
{
LoadProcess = new LoadProcess();
var sql = new SqlTask(this, Sql)
{
DisableLogging = true,
Actions = new List <Action <object> >()
{
col => LoadProcess.Id = Convert.ToInt64(col),
col => LoadProcess.StartDate = (DateTime)col,
col => LoadProcess.EndDate = (DateTime?)col,
col => LoadProcess.Source = (string)col,
col => LoadProcess.ProcessName = (string)col,
col => LoadProcess.StartMessage = (string)col,
col => LoadProcess.IsRunning = Convert.ToInt16(col) > 0 ? true : false,
col => LoadProcess.EndMessage = (string)col,
col => LoadProcess.WasSuccessful = Convert.ToInt16(col) > 0 ? true : false,
col => LoadProcess.AbortMessage = (string)col,
col => LoadProcess.WasAborted = Convert.ToInt16(col) > 0 ? true : false,
}
};
if (ReadOption == ReadOptions.ReadAllProcesses)
{
sql.BeforeRowReadAction = () => AllLoadProcesses = new List <LoadProcess>();
sql.AfterRowReadAction = () => AllLoadProcesses.Add(LoadProcess);
}
sql.ExecuteReader();
}
public override void Execute()
{
LoadProcess = new LoadProcess();
var sql = new SqlTask(this, Sql)
{
DisableLogging = true,
DisableExtension = true,
Actions = new List <Action <object> >()
{
col => LoadProcess.LoadProcessKey = (int)col,
col => LoadProcess.StartDate = (DateTime)col,
col => LoadProcess.TransferCompletedDate = (DateTime?)col,
col => LoadProcess.EndDate = (DateTime?)col,
col => LoadProcess.ProcessName = (string)col,
col => LoadProcess.StartMessage = (string)col,
col => LoadProcess.IsRunning = (bool)col,
col => LoadProcess.EndMessage = (string)col,
col => LoadProcess.WasSuccessful = (bool)col,
col => LoadProcess.AbortMessage = (string)col,
col => LoadProcess.WasAborted = (bool)col,
col => LoadProcess.IsFinished = (bool)col,
col => LoadProcess.IsTransferCompleted = (bool)col
}
};
if (ReadOption == ReadOptions.ReadAllProcesses)
{
sql.BeforeRowReadAction = () => AllLoadProcesses = new List <LoadProcess>();
sql.AfterRowReadAction = () => AllLoadProcesses.Add(LoadProcess);
}
sql.ExecuteReader();
}
private static TableDefinition ReadTableDefinitionFromSQLite(string tableName, IConnectionManager connection)
{
TableDefinition result = new TableDefinition(tableName);
TableColumn curCol = null;
var readMetaSql = new SqlTask($"Read column meta data for table {tableName}",
$@"PRAGMA table_info(""{tableName}"")"
, () => { curCol = new TableColumn(); }
, () => { result.Columns.Add(curCol); }
, cid => {; }
, name => curCol.Name = name.ToString()
, type => curCol.DataType = type.ToString()
, notnull => curCol.AllowNulls = (long)notnull == 1 ? true : false
, dftl_value => curCol.DefaultValue = dftl_value?.ToString()
, pk => curCol.IsPrimaryKey = (long)pk == 1 ? true : false
)
{
DisableLogging = true,
ConnectionManager = connection
};
readMetaSql.ExecuteReader();
//if (result.Columns.Where(col => col.IsPrimaryKey).Count() == 1)
//{
// var pkCol = result.Columns.Where(col => col.IsPrimaryKey).First();
// if (pkCol.DataType.ToUpper() == "INTEGER")
// {
// pkCol.IsIdentity = true;
// pkCol.IdentityIncrement = 1;
// pkCol.IdentitySeed = 1;
// }
//}
return(result);
}
private void ReadAll()
{
SqlTask sqlT = CreateSqlTask(SqlForRead);
DefineActions(sqlT, ColumnNamesEvaluated);
sqlT.ExecuteReader();
CleanupSqlTask(sqlT);
}
private static TableDefinition ReadTableDefinitionFromPostgres(string tableName, IConnectionManager connection)
{
TableDefinition result = new TableDefinition(tableName);
TableColumn curCol = null;
var readMetaSql = new SqlTask($"Read column meta data for table {tableName}",
$@"
SELECT cols.column_name
, cols.data_type
, CASE WHEN cols.is_nullable = 'NO' THEN 0 ELSE 1 END AS ""is_nullable""
, CASE WHEN cols.column_default IS NOT NULL AND substring(cols.column_default,0,8) = 'nextval' THEN 1 ELSE 0 END AS ""serial""
, CASE WHEN tccu.column_name IS NULL THEN 0 ELSE 1 END AS ""primary_key""
, cols.column_default
, cols.collation_name
, cols.generation_expression
FROM INFORMATION_SCHEMA.COLUMNS cols
INNER JOIN INFORMATION_SCHEMA.TABLES tbl
ON cols.table_name = tbl.table_name
AND cols.table_schema = tbl.table_schema
AND cols.table_catalog = tbl.table_catalog
LEFT JOIN INFORMATION_SCHEMA.table_constraints tc
ON cols.table_name = tc.table_name
AND cols.table_schema = tc.table_schema
AND cols.table_catalog = tc.table_catalog
AND tc.constraint_type = 'PRIMARY KEY'
LEFT JOIN information_schema.constraint_column_usage tccu
ON cols.table_name = tccu.table_name
AND cols.table_schema = tccu.table_schema
AND cols.table_catalog = tccu.table_catalog
AND tccu.constraint_name = tc.constraint_name
AND tccu.constraint_schema = tc.constraint_schema
AND tccu.constraint_catalog = tc.constraint_catalog
AND cols.column_name = tccu.column_name
WHERE(cols.table_name = '{tableName}' OR CONCAT(cols.table_schema, '.', cols.table_name) = '{tableName}')
AND cols.table_catalog = CURRENT_DATABASE()
ORDER BY cols.ordinal_position
"
, () => { curCol = new TableColumn(); }
, () => { result.Columns.Add(curCol); }
, column_name => curCol.Name = column_name.ToString()
, data_type => curCol.DataType = data_type.ToString()
, is_nullable => curCol.AllowNulls = (int)is_nullable == 1 ? true : false
, serial => curCol.IsIdentity = (int)serial == 1 ? true : false
, primary_key => curCol.IsPrimaryKey = (int)primary_key == 1 ? true : false
, column_default => curCol.DefaultValue = column_default?.ToString().ReplaceIgnoreCase("::character varying", "")
, collation_name => curCol.Collation = collation_name?.ToString()
, generation_expression => curCol.ComputedColumn = generation_expression?.ToString()
)
{
DisableLogging = true,
ConnectionManager = connection
};
readMetaSql.ExecuteReader();
return(result);
}
private static TableDefinition ReadTableDefinitionFromMySqlServer(IConnectionManager connection, ObjectNameDescriptor TN)
{
TableDefinition result = new TableDefinition(TN.ObjectName);
TableColumn curCol = null;
var readMetaSql = new SqlTask(
$@"
SELECT cols.column_name
, CASE WHEN cols.data_type IN ('varchar','char') THEN CONCAT (cols.data_type,'(',cols.character_maximum_length, ')')
WHEN cols.data_type IN ('decimal') THEN CONCAT (cols.data_type,'(',cols.numeric_precision,',', cols.numeric_scale, ')')
ELSE cols.data_type
END AS 'data_type'
, CASE WHEN cols.is_nullable = 'NO' THEN 0 ELSE 1 END AS 'is_nullable'
, CASE WHEN cols.extra IS NOT NULL AND cols.extra = 'auto_increment' THEN 1 ELSE 0 END AS 'auto_increment'
, CASE WHEN isnull(k.constraint_name) THEN 0 ELSE 1 END AS 'primary_key'
, cols.column_default
, cols.collation_name
, cols.generation_expression
, cols.column_comment
FROM INFORMATION_SCHEMA.COLUMNS cols
INNER JOIN INFORMATION_SCHEMA.TABLES tbl
ON cols.table_name = tbl.table_name
AND cols.table_schema = tbl.table_schema
AND cols.table_catalog = tbl.table_catalog
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
ON cols.table_name = k.table_name
AND cols.table_schema = k.table_schema
AND cols.table_catalog = k.table_catalog
AND cols.column_name = k.column_name
AND k.constraint_name = 'PRIMARY'
WHERE ( cols.table_name = '{TN.UnquotatedFullName}' OR CONCAT(cols.table_catalog,'.',cols.table_name) = '{TN.UnquotatedFullName}')
AND cols.table_schema = DATABASE()
ORDER BY cols.ordinal_position
"
, () => { curCol = new TableColumn(); }
, () => { result.Columns.Add(curCol); }
, column_name => curCol.Name = column_name.ToString()
, data_type => curCol.DataType = data_type.ToString()
, is_nullable => curCol.AllowNulls = (int)is_nullable == 1 ? true : false
, auto_increment => curCol.IsIdentity = (int)auto_increment == 1 ? true : false
, primary_key => curCol.IsPrimaryKey = (int)primary_key == 1 ? true : false
, column_default => curCol.DefaultValue = column_default?.ToString()
, collation_name => curCol.Collation = collation_name?.ToString()
, generation_expression => curCol.ComputedColumn = generation_expression?.ToString()
, comment => curCol.Comment = comment?.ToString()
)
{
DisableLogging = true,
ConnectionManager = connection,
TaskName = $"Read column meta data for table {TN.ObjectName}"
};
readMetaSql.ExecuteReader();
return(result);
}
public void TestExecuteReaderSingleColumn()
{
List <int> asIsResult = new List <int>();
List <int> toBeResult = new List <int>()
{
1, 2, 3
};
SqlTask.ExecuteReader("Test execute reader", "SELECT * FROM (VALUES (1),(2),(3)) MyTable(a)",
colA => asIsResult.Add((int)colA));
CollectionAssert.AreEqual(asIsResult, toBeResult);
}
public void UsingBatchTransformation()
{
var orderSource = new MemorySource <Order>();
orderSource.DataAsList.Add(new Order()
{
OrderNumber = 815, CustomerName = "John"
});
orderSource.DataAsList.Add(new Order()
{
OrderNumber = 4711, CustomerName = "Jim"
});
var batchTrans = new BatchTransformation <Order>()
{
BatchSize = 100,
BatchTransformationFunc =
batch =>
{
var names = string.Join(",", batch.Select(cust => $"'{cust.CustomerName}'"));
string curName = ""; int curId = 0;
Dictionary <string, int> idByName = new Dictionary <string, int>();
var sql = new SqlTask()
{
ConnectionManager = SqlConnection,
Sql = $"SELECT DISTINCT Name, Id FROM CustomerTable WHERE Name IN ({names})",
AfterRowReadAction = () => { idByName.Add(curName, curId); },
Actions = new List <Action <object> >()
{
name => curName = (string)name,
id => curId = (int)id
}
};
sql.ExecuteReader();
foreach (var row in batch)
{
row.CustomerId = idByName[row.CustomerName];
}
return(batch);
}
};
var dest = new MemoryDestination <Order>();
orderSource.LinkTo(batchTrans).LinkTo(dest);
Network.Execute(orderSource);
foreach (var result in dest.Data)
{
Console.WriteLine($"Customer {result.CustomerName} has id {result.CustomerId}");
}
}
public void TestExecuteReaderWithParameter()
{
List <int> asIsResult = new List <int>();
List <int> toBeResult = new List <int>()
{
1
};
List <QueryParameter> parameter = new List <QueryParameter>()
{
new QueryParameter("par1", "int", 1)
};
SqlTask.ExecuteReader("Test execute reader", "SELECT * FROM (VALUES (1),(2),(3)) MyTable(a) where a = @par1", parameter,
colA => asIsResult.Add((int)colA));
CollectionAssert.AreEqual(asIsResult, toBeResult);
}
private static void GetColumnsFromMySqlServer(IConnectionManager connectionManager, ObjectNameDescriptor TN, List <TableColumn> columns)
{
TableColumn curCol = null;
var readMetaSql = new SqlTask($"Read column meta data for table {TN.ObjectName}",
$@"
SELECT cols.column_name
, cols.data_type
, CASE WHEN cols.is_nullable = 'NO' THEN 0 ELSE 1 END AS 'is_nullable'
, CASE WHEN cols.extra IS NOT NULL AND cols.extra = 'auto_increment' THEN 1 ELSE 0 END AS 'auto_increment'
, CASE WHEN isnull(k.constraint_name) THEN 0 ELSE 1 END AS 'primary_key'
, cols.column_default
, cols.collation_name
, cols.generation_expression
FROM INFORMATION_SCHEMA.COLUMNS cols
INNER JOIN INFORMATION_SCHEMA.TABLES tbl
ON cols.table_name = tbl.table_name
AND cols.table_schema = tbl.table_schema
AND cols.table_catalog = tbl.table_catalog
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
ON cols.table_name = k.table_name
AND cols.table_schema = k.table_schema
AND cols.table_catalog = k.table_catalog
AND cols.column_name = k.column_name
AND k.constraint_name = 'PRIMARY'
WHERE ( cols.table_name = '{TN.UnquotatedFullName}' OR CONCAT(cols.table_catalog,'.',cols.table_name) = '{TN.UnquotatedFullName}')
AND cols.table_schema = DATABASE()
ORDER BY cols.ordinal_position
"
, () => { curCol = new TableColumn(); }
, () => { columns.Add(curCol); }
, column_name => curCol.Name = column_name.ToString()
, data_type => curCol.DataType = data_type.ToString()
, is_nullable => curCol.AllowNulls = (int)is_nullable == 1 ? true : false
, auto_increment => curCol.IsIdentity = (int)auto_increment == 1 ? true : false
, primary_key => curCol.IsPrimaryKey = (int)primary_key == 1 ? true : false
, column_default => curCol.DefaultValue = column_default?.ToString()
, collation_name => curCol.Collation = collation_name?.ToString()
, generation_expression => curCol.ComputedColumn = generation_expression?.ToString()
)
{
DisableLogging = true,
ConnectionManager = connectionManager
};
readMetaSql.ExecuteReader();
}
static void Main(string[] args)
{
//Set up the connection manager to master
var masterConnection = new SqlConnectionManager("Data Source=localhost;User Id=sa;Password=YourStrong@Passw0rd;");
//Recreate database
DropDatabaseTask.DropIfExists(masterConnection, "demo");
CreateDatabaseTask.Create(masterConnection, "demo");
//Get connection manager to previously create database
var dbConnection = new SqlConnectionManager("Data Source=localhost;User Id=sa;Password=YourStrong@Passw0rd;Initial Catalog=demo;");
//Create destination table
CreateTableTask.Create(dbConnection, "Table1", new List <TableColumn>()
{
new TableColumn("ID", "int", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("Col1", "nvarchar(100)", allowNulls: true),
new TableColumn("Col2", "smallint", allowNulls: true)
});
//Create dataflow for loading data from csv into table
CsvSource <string[]> source = new CsvSource <string[]>("input.csv");
RowTransformation <string[], MyData> row = new RowTransformation <string[], MyData>(
input =>
new MyData()
{
Col1 = input[0], Col2 = input[1]
}
);
DbDestination <MyData> dest = new DbDestination <MyData>(dbConnection, "Table1");
//Link components & run data flow
source.LinkTo(row);
row.LinkTo(dest);
source.Execute();
dest.Wait();
//Check if data exists in destination
SqlTask.ExecuteReader(dbConnection, "Read all data from table1",
"select Col1, Col2 from Table1",
col1 => Console.WriteLine(col1.ToString() + ","),
col2 => Console.WriteLine(col2.ToString()));
Console.WriteLine("Press any key to continue...");
Console.ReadLine();
}
public void TestExecuteReaderMultiColumn()
{
List <ThreeInteger> asIsResult = new List <ThreeInteger>();
List <ThreeInteger> toBeResult = new List <ThreeInteger>()
{
new ThreeInteger(1, 2, 3), new ThreeInteger(4, 5, 6), new ThreeInteger(7, 8, 9)
};
ThreeInteger CurColumn = new ThreeInteger();
SqlTask.ExecuteReader("Test execute reader", "SELECT * FROM (VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9)) AS MyTable(a,b,c)"
, () => CurColumn = new ThreeInteger()
, () => asIsResult.Add(CurColumn)
, colA => CurColumn.A = (int)colA
, colB => CurColumn.B = (int)colB
, colC => CurColumn.C = (int)colC
);
CollectionAssert.AreEqual(asIsResult, toBeResult);
}
private static void GetColumnsFromSQLite(IConnectionManager connectionManager, ObjectNameDescriptor TN, List <TableColumn> columns)
{
TableColumn curCol = null;
var readMetaSql = new SqlTask($"Read column meta data for table {TN.ObjectName}",
$@"PRAGMA table_info(""{TN.UnquotatedFullName}"")"
, () => { curCol = new TableColumn(); }
, () => { columns.Add(curCol); }
, cid => {; }
, name => curCol.Name = name.ToString()
, type => curCol.DataType = type.ToString()
, notnull => curCol.AllowNulls = (long)notnull == 1 ? true : false
, dftl_value => curCol.DefaultValue = dftl_value?.ToString()
, pk => curCol.IsPrimaryKey = (long)pk >= 1 ? true : false
)
{
DisableLogging = true,
ConnectionManager = connectionManager
};
readMetaSql.ExecuteReader();
}
public void ExecuteReaderSingleColumn(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture tc = new TwoColumnsTableFixture(connection, "ExecuteReader");
tc.InsertTestData();
List <int> asIsResult = new List <int>();
List <int> toBeResult = new List <int>()
{
1, 2, 3
};
//Act
SqlTask.ExecuteReader(connection,
"Test execute reader",
$"SELECT {tc.QB}Col1{tc.QE} FROM {tc.QB}ExecuteReader{tc.QE}",
colA => asIsResult.Add(int.Parse(colA.ToString()))
);
//Assert
Assert.Equal(toBeResult, asIsResult);
}
private static TableDefinition ReadTableDefinitionFromSQLite(string tableName, IConnectionManager connection)
{
TableDefinition result = new TableDefinition(tableName);
TableColumn curCol = null;
var readMetaSql = new SqlTask($"Read column meta data for table {tableName}",
$@"PRAGMA table_info(""{tableName}"")"
, () => { curCol = new TableColumn(); }
, () => { result.Columns.Add(curCol); }
, cid => {; }
, name => curCol.Name = name.ToString()
, type => curCol.DataType = type.ToString()
, notnull => curCol.AllowNulls = (long)notnull == 1 ? true : false
, dftl_value => curCol.DefaultValue = dftl_value?.ToString()
, pk => curCol.IsPrimaryKey = (long)pk == 1 ? true : false
)
{
DisableLogging = true,
ConnectionManager = connection
};
readMetaSql.ExecuteReader();
return(result);
}
static void Main(string[] args)
{
var masterConnection = new SqlConnectionManager("Data Source=.;Integrated Security=false;User=sa;password=reallyStrongPwd123");
DropDatabaseTask.DropIfExists(masterConnection, "demo");
CreateDatabaseTask.Create(masterConnection, "demo");
var dbConnection = new SqlConnectionManager("Data Source=.;Initial Catalog=demo;Integrated Security=false;User=sa;password=reallyStrongPwd123");
CreateTableTask.Create(dbConnection, "Table1", new List <TableColumn>()
{
new TableColumn("ID", "int", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("Col1", "nvarchar(100)", allowNulls: true),
new TableColumn("Col2", "smallint", allowNulls: true)
});
CsvSource <string[]> source = new CsvSource <string[]>("input.csv");
RowTransformation <string[], MyData> row = new RowTransformation <string[], MyData>(
input => new MyData()
{
Col1 = input[0], Col2 = input[1]
});
DbDestination <MyData> dest = new DbDestination <MyData>(dbConnection, "Table1");
source.LinkTo(row);
row.LinkTo(dest);
source.Execute();
dest.Wait();
SqlTask.ExecuteReader(dbConnection, "Read all data from table1",
"select Col1, Col2 from Table1",
col1 => Console.WriteLine(col1.ToString() + ","),
col2 => Console.WriteLine(col2.ToString()));
Console.WriteLine("Press any key to continue...");
Console.ReadLine();
}
List <LoadProcess> ReadProcessInternal(long processId = 0, ReadOptions readOption = ReadOptions.ReadSingleProcess)
{
var allLoadProcesses = new List <LoadProcess>();
var loadProcess = new LoadProcess();
var sql = new SqlTask(this, Sql_Read(processId, readOption))
{
DisableLogging = true,
Actions = new List <Action <object> >()
{
col => loadProcess.Id = Convert.ToInt64(col),
col => loadProcess.StartDate = (DateTime)col,
col => loadProcess.EndDate = (DateTime?)col,
col => loadProcess.Source = (string)col,
col => { if (col == null)
{
loadProcess.SourceId = null;
}
else
{
loadProcess.SourceId = Convert.ToInt64(col);
} },
col => loadProcess.ProcessName = (string)col,
col => loadProcess.StartMessage = (string)col,
col => loadProcess.IsRunning = Convert.ToInt16(col) > 0 ? true : false,
col => loadProcess.EndMessage = (string)col,
col => loadProcess.WasSuccessful = Convert.ToInt16(col) > 0 ? true : false,
col => loadProcess.AbortMessage = (string)col,
col => loadProcess.WasAborted = Convert.ToInt16(col) > 0 ? true : false,
}
};
sql.BeforeRowReadAction = () => loadProcess = new LoadProcess();
sql.AfterRowReadAction = () => allLoadProcesses.Add(loadProcess);
sql.ExecuteReader();
return(allLoadProcesses);
}
private static TableDefinition ReadTableDefinitionFromSQLite(IConnectionManager connection, ObjectNameDescriptor TN)
{
TableDefinition result = new TableDefinition(TN.ObjectName);
TableColumn curCol = null;
var readMetaSql = new SqlTask(
$@"PRAGMA table_info(""{TN.UnquotatedFullName}"")"
, () => { curCol = new TableColumn(); }
, () => { result.Columns.Add(curCol); }
, cid => {; }
, name => curCol.Name = name.ToString()
, type => curCol.DataType = type.ToString()
, notnull => curCol.AllowNulls = (long)notnull == 0 ? true : false
, dftl_value => curCol.DefaultValue = TryRemoveSingleQuotes(dftl_value?.ToString())
, pk => curCol.IsPrimaryKey = (long)pk >= 1 ? true : false
)
{
DisableLogging = true,
ConnectionManager = connection,
TaskName = $"Read column meta data for table {TN.ObjectName}"
};
readMetaSql.ExecuteReader();
return(result);
}
public void ExecuteReaderWithParameter(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture twoColumns = new TwoColumnsTableFixture(connection, "ExecuteReaderWithPar");
twoColumns.InsertTestData();
List <int> asIsResult = new List <int>();
List <int> toBeResult = new List <int>()
{
2
};
List <QueryParameter> parameter = new List <QueryParameter>()
{
new QueryParameter("par1", "NVARCHAR(10)", "Test2")
};
//Act
SqlTask.ExecuteReader(connection, "Test execute reader",
"SELECT Col1 FROM ExecuteReaderWithPar WHERE Col2 = @par1", parameter,
colA => asIsResult.Add(int.Parse(colA.ToString())));
//Assert
Assert.Equal(toBeResult, asIsResult);
}
private static void GetColumnsFromPostgres(IConnectionManager connectionManager, ObjectNameDescriptor TN, List <TableColumn> columns)
{
TableColumn curCol = null;
var readMetaSql = new SqlTask($"Read column meta data for table {TN.ObjectName}",
$@"
SELECT cols.column_name
, cols.data_type AS ""internaldatatype""
,CASE
WHEN cols.domain_name IS NOT NULL THEN domain_name
WHEN cols.data_type='character varying' THEN
CASE WHEN character_maximum_length IS NULL
THEN 'varchar'
ELSE 'varchar('||character_maximum_length||')'
END
WHEN cols.data_type='character' THEN 'char('||character_maximum_length||')'
WHEN cols.data_type='numeric' THEN
CASE WHEN numeric_precision IS NULL
THEN 'numeric'
ELSE 'numeric('||numeric_precision||','||numeric_scale||')'
END
WHEN LEFT(cols.data_type,4) = 'time' THEN REPLACE(REPLACE(REPLACE(cols.data_type,'without time zone',''), 'with time zone', 'tz'),' ','')
ELSE cols.data_type
END AS ""datatype""
, CASE WHEN cols.is_nullable = 'NO' THEN 0 ELSE 1 END AS ""is_nullable""
, CASE WHEN cols.column_default IS NOT NULL AND substring(cols.column_default,0,8) = 'nextval' THEN 1 ELSE 0 END AS ""serial""
, CASE WHEN tccu.column_name IS NULL THEN 0 ELSE 1 END AS ""primary_key""
, cols.column_default
, cols.collation_name
, cols.generation_expression
FROM INFORMATION_SCHEMA.COLUMNS cols
INNER JOIN INFORMATION_SCHEMA.TABLES tbl
ON cols.table_name = tbl.table_name
AND cols.table_schema = tbl.table_schema
AND cols.table_catalog = tbl.table_catalog
LEFT JOIN INFORMATION_SCHEMA.table_constraints tc
ON cols.table_name = tc.table_name
AND cols.table_schema = tc.table_schema
AND cols.table_catalog = tc.table_catalog
AND tc.constraint_type = 'PRIMARY KEY'
LEFT JOIN information_schema.constraint_column_usage tccu
ON cols.table_name = tccu.table_name
AND cols.table_schema = tccu.table_schema
AND cols.table_catalog = tccu.table_catalog
AND tccu.constraint_name = tc.constraint_name
AND tccu.constraint_schema = tc.constraint_schema
AND tccu.constraint_catalog = tc.constraint_catalog
AND cols.column_name = tccu.column_name
WHERE(cols.table_name = '{TN.UnquotatedFullName}' OR CONCAT(cols.table_schema, '.', cols.table_name) = '{TN.UnquotatedFullName}')
AND cols.table_catalog = CURRENT_DATABASE()
ORDER BY cols.ordinal_position
"
, () => { curCol = new TableColumn(); }
, () => { columns.Add(curCol); }
, column_name => curCol.Name = column_name.ToString()
, internal_type_name => curCol.InternalDataType = internal_type_name.ToString()
, data_type => curCol.DataType = data_type.ToString()
, is_nullable => curCol.AllowNulls = (int)is_nullable == 1 ? true : false
, serial => curCol.IsIdentity = (int)serial == 1 ? true : false
, primary_key => curCol.IsPrimaryKey = (int)primary_key == 1 ? true : false
, column_default => curCol.DefaultValue = column_default?.ToString().ReplaceIgnoreCase("::character varying", "")
, collation_name => curCol.Collation = collation_name?.ToString()
, generation_expression => curCol.ComputedColumn = generation_expression?.ToString()
)
{
DisableLogging = true,
ConnectionManager = connectionManager
};
readMetaSql.ExecuteReader();
}
private static void GetColumnsFromSqlServer(IConnectionManager connectionManager, ObjectNameDescriptor TN, List <TableColumn> columns)
{
TableColumn curCol = null;
var readMetaSql = new SqlTask($"Read column meta data for table {TN.ObjectName}",
$@"
SELECT cols.name
, UPPER(tpes.name) AS type_name
, cols.is_nullable
, cols.is_identity
, ident.seed_value
, ident.increment_value
, CONVERT (BIT, CASE WHEN pkidxcols.index_column_id IS NOT NULL THEN 1 ELSE 0 END ) AS primary_key
, defconstr.definition AS default_value
, cols.collation_name
, compCol.definition AS computed_column_definition
FROM sys.columns cols
INNER JOIN (
SELECT name, type, object_id, schema_id FROM sys.tables
UNION
SELECT name, type, object_id, schema_id FROM sys.views
) tbl
ON cols.object_id = tbl.object_id
INNER JOIN sys.schemas sc
ON tbl.schema_id = sc.schema_id
INNER JOIN sys.systypes tpes
ON tpes.xtype = cols.system_type_id
LEFT JOIN sys.identity_columns ident
ON ident.object_id = cols.object_id
LEFT JOIN sys.indexes pkidx
ON pkidx.object_id = cols.object_id
AND pkidx.is_primary_key = 1
LEFT JOIN sys.index_columns pkidxcols
on pkidxcols.object_id = cols.object_id
AND pkidxcols.column_id = cols.column_id
AND pkidxcols.index_id = pkidx.index_id
LEFT JOIN sys.default_constraints defconstr
ON defconstr.parent_object_id = cols.object_id
AND defconstr.parent_column_id = cols.column_id
LEFT JOIN sys.computed_columns compCol
ON compCol.object_id = cols.object_id
WHERE ( CONCAt (sc.name,'.',tbl.name) ='{TN.UnquotatedFullName}' OR tbl.name = '{TN.UnquotatedFullName}' )
AND tbl.type IN ('U','V')
AND tpes.name <> 'sysname'
ORDER BY cols.column_id
"
, () => { curCol = new TableColumn(); }
, () => { columns.Add(curCol); }
, name => curCol.Name = name.ToString()
, type_name => curCol.DataType = type_name.ToString()
, is_nullable => curCol.AllowNulls = (bool)is_nullable
, is_identity => curCol.IsIdentity = (bool)is_identity
, seed_value => curCol.IdentitySeed = (int?)(Convert.ToInt32(seed_value))
, increment_value => curCol.IdentityIncrement = (int?)(Convert.ToInt32(increment_value))
, primary_key => curCol.IsPrimaryKey = (bool)primary_key
, default_value =>
curCol.DefaultValue = default_value?.ToString().Substring(2, (default_value.ToString().Length) - 4)
, collation_name => curCol.Collation = collation_name?.ToString()
, computed_column_definition => curCol.ComputedColumn = computed_column_definition?.ToString().Substring(1, (computed_column_definition.ToString().Length) - 2)
)
{
DisableLogging = true,
ConnectionManager = connectionManager
};
readMetaSql.ExecuteReader();
}
private static TableDefinition ReadTableDefinitionFromDb2(IConnectionManager connection, ObjectNameDescriptor TN)
{
TableDefinition result = new TableDefinition(TN.ObjectName);
TableColumn curCol = null;
string sql = $@"
SELECT c.colname AS column_name
, CASE WHEN c.typename
IN ('VARCHAR','CHARACTER','BINARY','VARBINARY','CLOB','BLOB','DBCLOB','GRAPHIC','VARGRAPHIC' )
THEN c.typename || '(' || c.length || ')'
WHEN c.typename
IN ('DECIMAL','NUMERIC','DECFLOAT','REAL','DOUBLE')
THEN c.typename || '(' || c.length ||',' || c.scale || ')'
ELSE c.typename
END AS data_type
, CASE WHEN c.nulls = 'Y' THEN 1 ELSE 0 END AS nullable
, CASE WHEN c.identity ='Y' THEN 1 ELSE 0 END AS is_identity
, CASE WHEN i.uniquerule ='P' THEN 1 ELSE 0 END AS is_primary
, c.default AS default_value
, c.collationname AS collation
--, c.generated as generation_expression
, c.text as computed_formula
, CASE WHEN i.uniquerule ='U' THEN 1 ELSE 0 END AS is_unique
, c.remarks as description
, CASE WHEN i.uniquerule ='P' THEN i.indname ELSE NULL END AS pk_name
, CASE WHEN i.uniquerule ='U' THEN i.indname ELSE NULL END AS uk_name
FROM syscat.columns c
INNER JOIN syscat.tables t on
t.tabschema = c.tabschema and t.tabname = c.tabname
LEFT JOIN (
SELECT ix.uniquerule, ix.tabschema, ix.tabname, idxu.colname, ix.indname
FROM syscat.indexes ix
INNER JOIN syscat.indexcoluse idxu
ON idxu.indname = ix.indname
AND idxu.indschema = ix.indschema
) i
ON i.tabschema = c.tabschema
AND i.tabname = c.tabname
AND i.colname = c.colname
WHERE t.type IN ('V','T')
AND ( t.tabname = '{TN.UnquotatedFullName}'
OR ( TRIM(t.tabschema) || '.' || t.tabname = '{TN.UnquotatedFullName}' )
)
ORDER BY c.colno;
";
var readMetaSql = new SqlTask(
sql
, () => { curCol = new TableColumn(); }
, () => { result.Columns.Add(curCol); }
, column_name => curCol.Name = column_name.ToString()
, data_type => curCol.DataType = data_type.ToString()
, is_nullable => curCol.AllowNulls = (int)is_nullable == 1 ? true : false
, is_identity => curCol.IsIdentity = (int)is_identity == 1 ? true : false
, is_primary => curCol.IsPrimaryKey = (int)is_primary == 1 ? true : false
, default_value => curCol.DefaultValue = TryRemoveSingleQuotes(default_value?.ToString())
, collation => curCol.Collation = collation?.ToString()
, computed_formula => curCol.ComputedColumn = computed_formula?.ToString()
, is_unique => curCol.IsUnique = (int)is_unique == 1 ? true : false
, remarks => curCol.Comment = remarks?.ToString()
, pk_name => result.PrimaryKeyConstraintName = String.IsNullOrWhiteSpace(pk_name?.ToString()) ? result.PrimaryKeyConstraintName : pk_name.ToString()
, uq_name => result.UniqueKeyConstraintName = String.IsNullOrWhiteSpace(uq_name?.ToString()) ? result.UniqueKeyConstraintName : uq_name.ToString()
)
{
DisableLogging = true,
ConnectionManager = connection,
TaskName = $"Read column meta data for table {TN.ObjectName}"
};
readMetaSql.ExecuteReader();
return(result);
}
//private static TableDefinition ReadTableDefinitionFromDataTable(string tableName, IConnectionManager connection)
//{
// connection.Open();
// var command = connection.CreateCommand($"SELECT * FROM {tableName} WHERE 1=2", null);
// var reader = command.ExecuteReader(CommandBehavior.SingleRow);
// DataTable dt = new DataTable();
// dt.Load(reader);
// connection.Close();
// return null;
//}
private static TableDefinition ReadTableDefinitionFromSqlServer(string tableName, IConnectionManager connection)
{
TableDefinition result = new TableDefinition(tableName);
TableColumn curCol = null;
var readMetaSql = new SqlTask($"Read column meta data for table {tableName}",
$@"
SELECT cols.name
, UPPER(tpes.name) AS type_name
, cols.is_nullable
, cols.is_identity
, ident.seed_value
, ident.increment_value
, CONVERT (BIT, CASE WHEN pkconstr.type IS NULL THEN 0 ELSE 1 END ) AS primary_key
, defconstr.definition AS default_value
, cols.collation_name
, compCol.definition AS computed_column_definition
FROM sys.columns cols
INNER JOIN sys.tables tbl
ON cols.object_id = tbl.object_id
INNER JOIN sys.schemas sc
ON tbl.schema_id = sc.schema_id
INNER JOIN sys.systypes tpes
ON tpes.xtype = cols.system_type_id
LEFT JOIN sys.identity_columns ident
ON ident.object_id = cols.object_id
LEFT JOIN sys.key_constraints pkconstr
ON pkconstr.parent_object_id = cols.object_id
AND ISNULL(pkconstr.type,'') = 'PK'
LEFT JOIN sys.default_constraints defconstr
ON defconstr.parent_object_id = cols.object_id
AND defconstr.parent_column_id = cols.column_id
LEFT JOIN sys.computed_columns compCol
ON compCol.object_id = cols.object_id
WHERE ( CONCAt (sc.name,'.',tbl.name) ='{tableName}' OR tbl.name = '{tableName}' )
AND tbl.type = 'U'
AND tpes.name <> 'sysname'
ORDER BY cols.column_id
"
, () => { curCol = new TableColumn(); }
, () => { result.Columns.Add(curCol); }
, name => curCol.Name = name.ToString()
, type_name => curCol.DataType = type_name.ToString()
, is_nullable => curCol.AllowNulls = (bool)is_nullable
, is_identity => curCol.IsIdentity = (bool)is_identity
, seed_value => curCol.IdentitySeed = (int?)seed_value
, increment_value => curCol.IdentityIncrement = (int?)increment_value
, primary_key => curCol.IsPrimaryKey = (bool)primary_key
, default_value =>
curCol.DefaultValue = default_value?.ToString().Substring(2, (default_value.ToString().Length) - 4)
, collation_name => curCol.Collation = collation_name?.ToString()
, computed_column_definition => curCol.ComputedColumn = computed_column_definition?.ToString().Substring(1, (computed_column_definition.ToString().Length) - 2)
)
{
DisableLogging = true,
ConnectionManager = connection
};
readMetaSql.ExecuteReader();
return(result);
}
private static TableDefinition ReadTableDefinitionFromSqlServer(IConnectionManager connection, ObjectNameDescriptor TN)
{
TableDefinition result = new TableDefinition(TN.ObjectName);
TableColumn curCol = null;
var readMetaSql = new SqlTask(
$@"
SELECT cols.name
, CASE WHEN tpes.name IN ('varchar','char','binary','varbinary')
THEN CONCAT ( UPPER(tpes.name)
, '('
, IIF (cols.max_length = -1, 'MAX', CAST(cols.max_length as varchar(20)))
, ')'
)
WHEN tpes.name IN ('nvarchar','nchar')
THEN CONCAT ( UPPER(tpes.name)
, '('
, IIF (cols.max_length = -1, 'MAX', CAST( (cols.max_length/2) as varchar(20)))
, ')'
)
WHEN tpes.name IN ('decimal','numeric')
THEN CONCAT ( UPPER(tpes.name)
, '('
, cols.precision
,','
,cols.scale, ')'
)
ELSE UPPER(tpes.name)
END AS type_name
, cols.is_nullable
, cols.is_identity
, ident.seed_value
, ident.increment_value
, CONVERT (BIT, CASE WHEN pkidxcols.index_column_id IS NOT NULL THEN 1 ELSE 0 END ) AS primary_key
, defconstr.definition AS default_value
, cols.collation_name
, compCol.definition AS computed_column_definition
, CONVERT (BIT, CASE WHEN uqidxcols.index_column_id IS NOT NULL THEN 1 ELSE 0 END ) AS is_unique
, CASE WHEN pkidxcols.index_column_id IS NOT NULL THEN pkidx.name ELSE NULL END AS pkkey_name
, CASE WHEN uqidxcols.index_column_id IS NOT NULL THEN uqidx.name ELSE NULL END AS uqkey_name
FROM sys.columns cols
INNER JOIN (
SELECT name, type, object_id, schema_id FROM sys.tables
UNION
SELECT name, type, object_id, schema_id FROM sys.views
) tbl
ON cols.object_id = tbl.object_id
INNER JOIN sys.schemas sc
ON tbl.schema_id = sc.schema_id
INNER JOIN sys.systypes tpes
ON tpes.xtype = cols.system_type_id
LEFT JOIN sys.identity_columns ident
ON ident.object_id = cols.object_id
LEFT JOIN sys.indexes pkidx
ON pkidx.object_id = cols.object_id
AND pkidx.is_primary_key = 1
LEFT JOIN sys.index_columns pkidxcols
on pkidxcols.object_id = cols.object_id
AND pkidxcols.column_id = cols.column_id
AND pkidxcols.index_id = pkidx.index_id
LEFT JOIN sys.indexes uqidx
ON uqidx.object_id = cols.object_id
AND uqidx.is_unique_constraint = 1
LEFT JOIN sys.index_columns uqidxcols
on uqidxcols.object_id = cols.object_id
AND uqidxcols.column_id = cols.column_id
AND uqidxcols.index_id = uqidx.index_id
LEFT JOIN sys.default_constraints defconstr
ON defconstr.parent_object_id = cols.object_id
AND defconstr.parent_column_id = cols.column_id
LEFT JOIN sys.computed_columns compCol
ON compCol.object_id = cols.object_id
WHERE ( CONCAT (sc.name,'.',tbl.name) ='{TN.UnquotatedFullName}' OR tbl.name = '{TN.UnquotatedFullName}' )
AND tbl.type IN ('U','V')
AND tpes.name <> 'sysname'
ORDER BY cols.column_id
"
, () => { curCol = new TableColumn(); }
, () => { result.Columns.Add(curCol); }
, name => curCol.Name = name.ToString()
, type_name => curCol.DataType = type_name.ToString()
, is_nullable => curCol.AllowNulls = (bool)is_nullable
, is_identity => curCol.IsIdentity = (bool)is_identity
, seed_value => curCol.IdentitySeed = (int?)(Convert.ToInt32(seed_value))
, increment_value => curCol.IdentityIncrement = (int?)(Convert.ToInt32(increment_value))
, primary_key => curCol.IsPrimaryKey = (bool)primary_key
, default_value =>
curCol.DefaultValue = default_value?.ToString().Substring(2, (default_value.ToString().Length) - 4)
, collation_name => curCol.Collation = collation_name?.ToString()
, computed_column_definition => curCol.ComputedColumn = computed_column_definition?.ToString().Substring(1, (computed_column_definition.ToString().Length) - 2)
, uq_key => curCol.IsUnique = (bool)uq_key
, pk_name => result.PrimaryKeyConstraintName = String.IsNullOrWhiteSpace(pk_name?.ToString()) ? result.PrimaryKeyConstraintName : pk_name.ToString()
, uq_name => result.UniqueKeyConstraintName = String.IsNullOrWhiteSpace(uq_name?.ToString()) ? result.UniqueKeyConstraintName : uq_name.ToString()
)
{
DisableLogging = true,
ConnectionManager = connection,
TaskName = $"Read column meta data for table {TN.ObjectName}"
};
readMetaSql.ExecuteReader();
return(result);
}
private static TableDefinition ReadTableDefinitionFromOracle(IConnectionManager connection, ObjectNameDescriptor TN)
{
TableDefinition result = new TableDefinition(TN.ObjectName);
TableColumn curCol = null;
//Regarding default values: The issue is described partly here
//https://stackoverflow.com/questions/46991132/how-to-cast-long-to-varchar2-inline/47041776
string sql = $@"
SELECT cols.COLUMN_NAME
, CASE WHEN cols.DATA_TYPE
IN ('VARCHAR','CHAR', 'NCHAR', 'NVARCHAR', 'NVARCHAR2', 'NCHAR2', 'VARCHAR2', 'CHAR2' )
THEN cols.DATA_TYPE || '(' || cols.CHAR_LENGTH || ')'
WHEN cols.DATA_TYPE
IN ('NUMBER')
THEN cols.DATA_TYPE || '(' ||cols.DATA_LENGTH ||',' ||
CASE WHEN cols.DATA_SCALE IS NULL THEN 127 ELSE cols.DATA_SCALE END
|| ')'
ELSE cols.DATA_TYPE
END AS data_type
, cols.NULLABLE
, cols.IDENTITY_COLUMN
, CASE WHEN cons.CONSTRAINT_TYPE = 'P' THEN 'ENABLED' ELSE NULL END as primary_key
, cols.DATA_DEFAULT --not working, see restriction above
, cols.COLLATION
, cols.DATA_DEFAULT AS generation_expression
, CASE WHEN cons.CONSTRAINT_TYPE = 'U' THEN 'ENABLED' ELSE NULL END as unique_key
FROM ALL_TAB_COLUMNS cols
LEFT JOIN (
SELECT acols.table_name, acols.column_name, acols.position, acons.status, acons.owner, acons.constraint_type
FROM ALL_CONSTRAINTS acons, ALL_CONS_COLUMNS acols
WHERE acons.CONSTRAINT_TYPE IN ('U','P')
AND acons.CONSTRAINT_NAME = acols.CONSTRAINT_NAME
AND acons.OWNER = acols.OWNER
) cons
ON cons.TABLE_NAME = cols.TABLE_NAME
AND cons.OWNER = cols.OWNER
--AND cons.position = cols.COLUMN_ID
AND cons.column_name = cols.COLUMN_NAME
WHERE
cols.TABLE_NAME NOT LIKE 'BIN$%'
AND cols.OWNER NOT IN ('SYS', 'SYSMAN', 'CTXSYS', 'MDSYS', 'OLAPSYS', 'ORDSYS', 'OUTLN', 'WKSYS', 'WMSYS', 'XDB', 'ORDPLUGINS', 'SYSTEM')
AND ( cols.TABLE_NAME = '{TN.UnquotatedFullName}'
OR (cols.OWNER || '.' || cols.TABLE_NAME ) = '{TN.UnquotatedFullName}'
)
ORDER BY cols.COLUMN_ID
";
var readMetaSql = new SqlTask(
sql
, () => { curCol = new TableColumn(); }
, () => { result.Columns.Add(curCol); }
, column_name => curCol.Name = column_name.ToString()
, data_type => curCol.DataType = data_type.ToString()
, nullable => curCol.AllowNulls = nullable.ToString() == "Y" ? true : false
, identity_column => curCol.IsIdentity = identity_column?.ToString() == "YES" ? true : false
, primary_key => curCol.IsPrimaryKey = primary_key?.ToString() == "ENABLED" ? true : false
, data_default => curCol.DefaultValue = data_default?.ToString()
, collation => curCol.Collation = collation?.ToString()
, generation_expression => curCol.ComputedColumn = generation_expression?.ToString()
, uq_key => curCol.IsUnique = uq_key?.ToString() == "ENABLED" ? true : false
)
{
DisableLogging = true,
ConnectionManager = connection,
TaskName = $"Read column meta data for table {TN.ObjectName}"
};
readMetaSql.ExecuteReader();
return(result);
}