public void Update(AbstractConnection connection, AbstractTransaction transaction, UPrintDataSet dataSet)
{
adapter = new NpgsqlDataAdapter();
adapter.SelectCommand = new NpgsqlCommand(selection, connection.GetConnection(), transaction.GetTransaction());
adapter.Fill(dataSet, "printer");
adapter.InsertCommand = new NpgsqlCommand(insertion, connection.GetConnection(), transaction.GetTransaction());
adapter.InsertCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "id", ParameterName = "@id"
});
adapter.InsertCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "name", ParameterName = "@name"
});
adapter.InsertCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "status", ParameterName = "@status"
});
adapter.InsertCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "active", ParameterName = "@active"
});
adapter.UpdateCommand = new NpgsqlCommand(updation, connection.GetConnection(), transaction.GetTransaction());
adapter.UpdateCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "id", ParameterName = "@id"
});
adapter.UpdateCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "name", ParameterName = "@name"
});
adapter.UpdateCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "status", ParameterName = "@status"
});
adapter.UpdateCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "active", ParameterName = "@active"
});
adapter.DeleteCommand = new NpgsqlCommand(deletion, connection.GetConnection(), transaction.GetTransaction());
adapter.DeleteCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "id", ParameterName = "@id"
});
NpgsqlCommandBuilder commandBuilder = new NpgsqlCommandBuilder(adapter);
adapter.Update(dataSet, "printer");
}
public bool Exists(AbstractConnection connection, Entity entity) {
using (var cn = connection.GetConnection()) {
cn.Open();
var table = cn.Query<string>(SQL, new { name = entity.OutputName() }).DefaultIfEmpty(string.Empty).FirstOrDefault();
return !string.IsNullOrEmpty(table);
}
}
public static IDbConnection Connection(AbstractConnection connection)
{
var cn = connection.GetConnection();
cn.Open();
return(cn);
}
public Fields Read(AbstractConnection connection, string process, string prefix, string name, string schema, bool isMaster = false) {
var fields = new Fields();
using (var cn = connection.GetConnection()) {
cn.Open();
var sql = PrepareSql();
connection.Logger.EntityDebug(name, sql);
var results = cn.Query(sql, new { name, schema });
foreach (var result in results) {
var columnName = result.COLUMN_NAME;
var type = GetSystemType(result.DATA_TYPE);
var length = result.CHARACTER_MAXIMUM_LENGTH == "0" || result.CHARACTER_MAXIMUM_LENGTH == "-1" ? "64" : result.CHARACTER_MAXIMUM_LENGTH;
var fieldType = (bool)result.IS_PRIMARY_KEY ? (isMaster ? FieldType.MasterKey : FieldType.PrimaryKey) : FieldType.NonKey;
var field = new Field(type, length, fieldType, true, string.Empty) {
Name = columnName,
Entity = name,
Process = process,
Index = Convert.ToInt16(result.ORDINAL_POSITION - 1),
Schema = schema,
Input = true,
Precision = result.NUMERIC_PRECISION,
Scale = result.NUMERIC_SCALE,
Alias = prefix + columnName
};
fields.Add(field);
}
}
return fields;
}
public bool Exists(AbstractConnection connection, Entity entity)
{
using (var cn = connection.GetConnection()) {
cn.Open();
return(cn.Query <bool>(SQL, new { name = entity.OutputName() }).DefaultIfEmpty(false).FirstOrDefault());
}
}
public RunOperation(string inKey, AbstractConnection connection, int timeOut)
: base(inKey, string.Empty)
{
_timeOut = timeOut;
_connection = connection.GetConnection();
_logger = connection.Logger;
}
public Fields Read(AbstractConnection connection, string process, string prefix, string name, string schema, bool isMaster = false)
{
var fields = new Fields();
using (var cn = connection.GetConnection()) {
cn.Open();
var sql = PrepareSql();
connection.Logger.EntityDebug(name, sql);
var results = cn.Query(sql, new { name, schema });
foreach (var result in results)
{
var columnName = result.COLUMN_NAME;
var type = GetSystemType(result.DATA_TYPE);
var length = result.CHARACTER_MAXIMUM_LENGTH == "0" || result.CHARACTER_MAXIMUM_LENGTH == "-1" ? "64" : result.CHARACTER_MAXIMUM_LENGTH;
var fieldType = (bool)result.IS_PRIMARY_KEY ? (isMaster ? FieldType.MasterKey : FieldType.PrimaryKey) : FieldType.NonKey;
var field = new Field(type, length, fieldType, true, string.Empty)
{
Name = columnName,
Entity = name,
Process = process,
Index = Convert.ToInt16(result.ORDINAL_POSITION - 1),
Schema = schema,
Input = true,
Precision = result.NUMERIC_PRECISION,
Scale = result.NUMERIC_SCALE,
Alias = prefix + columnName
};
fields.Add(field);
}
}
return(fields);
}
public bool Exists(AbstractConnection connection, Entity entity)
{
using (var cn = connection.GetConnection()) {
cn.Open();
var table = cn.Query <string>(SQL, new { name = entity.OutputName() }).DefaultIfEmpty(string.Empty).FirstOrDefault();
return(!string.IsNullOrEmpty(table));
}
}
public override void Create(AbstractConnection connection, Process process, Entity entity)
{
if (EntityExists != null && EntityExists.Exists(connection, entity))
{
process.Logger.EntityWarn(entity.Name, "Trying to create entity that already exists! {0}", entity.Name);
return;
}
var keyType = entity.IsMaster() ? FieldType.MasterKey : FieldType.PrimaryKey;
var writer = process.StarEnabled && keyType == FieldType.MasterKey ?
new FieldSqlWriter(entity.Fields, entity.CalculatedFields, process.CalculatedFields, GetRelationshipFields(process.Relationships, entity)) :
new FieldSqlWriter(entity.Fields, entity.CalculatedFields);
var primaryKey = writer.FieldType(keyType).Alias(connection.L, connection.R).Asc().Values();
var defs = new List <string>();
defs.AddRange(writer
.Reload()
.AddBatchId(entity.Index)
.AddDeleted(entity)
.AddSurrogateKey(entity.Index)
.Output()
.Alias(connection.L, connection.R)
.DataType(new SqlServerDataTypeService())
.AppendIf(" NOT NULL", keyType)
.Values());
var rowVersion = entity.Fields.WithSimpleType("rowversion").WithoutInput().WithoutOutput();
if (rowVersion.Any())
{
var alias = rowVersion.First().Alias;
defs.Add(connection.Enclose(alias) + " [ROWVERSION] NOT NULL");
}
var createSql = connection.TableQueryWriter.CreateTable(entity.OutputName(), defs);
_logger.EntityDebug(entity.Name, createSql);
var indexSql = connection.TableQueryWriter.AddUniqueClusteredIndex(entity.OutputName());
_logger.EntityDebug(entity.Name, indexSql);
var keySql = connection.TableQueryWriter.AddPrimaryKey(entity.OutputName(), primaryKey);
_logger.EntityDebug(entity.Name, keySql);
using (var cn = connection.GetConnection()) {
cn.Open();
cn.Execute(createSql);
cn.Execute(indexSql);
cn.Execute(keySql);
_logger.EntityInfo(entity.Name, "Initialized {0} in {1} on {2}.", entity.OutputName(), connection.Database, connection.Server);
}
}
private static void Execute(AbstractConnection connection, string sqlFormat, params object[] values) {
var sql = values.Length > 0 ? string.Format(sqlFormat, values) : sqlFormat;
using (var cn = connection.GetConnection()) {
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
private static void Execute(AbstractConnection connection, string sqlFormat, params object[] values)
{
var sql = values.Length > 0 ? string.Format(sqlFormat, values) : sqlFormat;
using (var cn = connection.GetConnection()) {
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
public int Count(AbstractConnection connection, Entity entity, bool useAlias) {
if (_connectionChecker == null || _connectionChecker.Check(connection)) {
if (_entityExists.Exists(connection, entity)) {
using (var cn = connection.GetConnection()) {
cn.Open();
var sql = string.Format("SELECT COUNT(*) FROM [{0}].[{1}] WITH (NOLOCK);", entity.Schema, entity.OutputName());
var cmd = cn.CreateCommand();
cmd.CommandText = sql;
return (int)cmd.ExecuteScalar();
}
}
}
return 0;
}
public override void Create(AbstractConnection connection, Process process, Entity entity) {
if (EntityExists != null && EntityExists.Exists(connection, entity)) {
process.Logger.EntityWarn(entity.Name,"Trying to create entity that already exists! {0}", entity.Name);
return;
}
var keyType = entity.IsMaster() ? FieldType.MasterKey : FieldType.PrimaryKey;
var writer = process.StarEnabled && keyType == FieldType.MasterKey ?
new FieldSqlWriter(entity.Fields, entity.CalculatedFields, process.CalculatedFields, GetRelationshipFields(process.Relationships, entity)) :
new FieldSqlWriter(entity.Fields, entity.CalculatedFields);
var primaryKey = writer.FieldType(keyType).Alias(connection.L, connection.R).Asc().Values();
var defs = new List<string>();
defs.AddRange(writer
.Reload()
.AddBatchId(entity.Index)
.AddDeleted(entity)
.AddSurrogateKey(entity.Index)
.Output()
.Alias(connection.L, connection.R)
.DataType(new SqlServerDataTypeService())
.AppendIf(" NOT NULL", keyType)
.Values());
var rowVersion = entity.Fields.WithSimpleType("rowversion").WithoutInput().WithoutOutput();
if (rowVersion.Any()) {
var alias = rowVersion.First().Alias;
defs.Add(connection.Enclose(alias) + " [ROWVERSION] NOT NULL");
}
var createSql = connection.TableQueryWriter.CreateTable(entity.OutputName(), defs);
_logger.EntityDebug(entity.Name, createSql);
var indexSql = connection.TableQueryWriter.AddUniqueClusteredIndex(entity.OutputName());
_logger.EntityDebug(entity.Name, indexSql);
var keySql = connection.TableQueryWriter.AddPrimaryKey(entity.OutputName(), primaryKey);
_logger.EntityDebug(entity.Name, keySql);
using (var cn = connection.GetConnection()) {
cn.Open();
cn.Execute(createSql);
cn.Execute(indexSql);
cn.Execute(keySql);
_logger.EntityInfo(entity.Name, "Initialized {0} in {1} on {2}.", entity.OutputName(), connection.Database, connection.Server);
}
}
public bool RecordsExist(AbstractConnection connection, Entity entity) {
if (EntityExists.Exists(connection, entity)) {
using (var cn = connection.GetConnection()) {
cn.Open();
var sql = $@"SELECT TOP(1) [{entity.PrimaryKey.First().Alias}] FROM [{entity.OutputName()}];";
var cmd = cn.CreateCommand();
cmd.CommandText = sql;
using (var reader = cmd.ExecuteReader()) {
return reader.Read();
}
}
}
return false;
}
public bool RecordsExist(AbstractConnection connection, Entity entity)
{
if (EntityExists.Exists(connection, entity))
{
using (var cn = connection.GetConnection()) {
cn.Open();
var sql = string.Format(@"SELECT TOP(1) [{0}] FROM [{1}];", entity.PrimaryKey.First().Alias, entity.OutputName());
var cmd = cn.CreateCommand();
cmd.CommandText = sql;
using (var reader = cmd.ExecuteReader()) {
return(reader.Read());
}
}
}
return(false);
}
public Fields Read(string name, string schema)
{
var result = new Fields();
using (var cn = _connection.GetConnection()) {
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText = string.Format("select * from {0}{1} where 1=2;", schema.Equals(string.Empty) ? string.Empty : _connection.Enclose(schema) + ".", _connection.Enclose(name));
var reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly);
var table = reader.GetSchemaTable();
if (table != null)
{
var keys = table.PrimaryKey.Any() ? table.PrimaryKey.Select(c => c.ColumnName).ToArray() : Enumerable.Empty <string>().ToArray();
foreach (DataRow row in table.Rows)
{
var columnName = row["ColumnName"].ToString();
var field = new Field(keys.Contains(columnName) ? FieldType.PrimaryKey : FieldType.NonKey)
{
Name = columnName,
Type = Common.ToSimpleType(row["DataType"].ToString())
};
if (field.Type.Equals("string"))
{
field.Length = row["ColumnSize"].ToString();
}
else
{
field.Precision = Convert.ToInt32(row["NumericPrecision"]);
field.Scale = Convert.ToInt32(row["NumericScale"]);
}
if (Convert.ToBoolean(row["IsRowVersion"]))
{
field.Length = "8";
field.Type = "rowversion";
}
result.Add(field);
}
}
};
return(result);
}
public bool RecordsExist(AbstractConnection connection, Entity entity) {
if (!EntityExists.Exists(connection, entity))
return false;
using (var cn = connection.GetConnection()) {
cn.Open();
var records = cn.Query<string>(string.Format(@"
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT `{0}`
FROM `{1}`
LIMIT 1;
COMMIT;
", entity.PrimaryKey.First().Alias, entity.OutputName()));
return records != null && records.Any();
}
}
public bool RecordsExist(AbstractConnection connection, Entity entity) {
if (!EntityExists.Exists(connection, entity))
return false;
using (var cn = connection.GetConnection()) {
cn.Open();
var exists = cn.Query<bool>(string.Format(@"
SELECT EXISTS(
SELECT ""{0}""
FROM ""{1}""
LIMIT 1
);
", entity.PrimaryKey.First().Alias, entity.OutputName())).DefaultIfEmpty(false).First();
return exists;
}
}
public int Count(AbstractConnection connection, Entity entity, bool useAlias)
{
if (_connectionChecker == null || _connectionChecker.Check(connection))
{
if (_entityExists.Exists(connection, entity))
{
using (var cn = connection.GetConnection()) {
cn.Open();
var sql = string.Format("SELECT COUNT(*) FROM [{0}].[{1}] WITH (NOLOCK);", entity.Schema, entity.OutputName());
var cmd = cn.CreateCommand();
cmd.CommandText = sql;
return((int)cmd.ExecuteScalar());
}
}
}
return(0);
}
public bool OutputExists(string name)
{
var sql = string.Format(@"
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '{0}';" , name)
;
using (var cn = _connection.GetConnection()) {
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader()) {
return(reader.Read());
}
}
}
public override IEnumerable <Row> Execute(IEnumerable <Row> rows)
{
using (var cn = _connection.GetConnection()) {
cn.Open();
var transaction = cn.BeginTransaction();
try {
foreach (var group in rows.Partition(_connection.BatchSize))
{
cn.Execute(_sql, group.Select(ToExpandoObject), transaction, 0);
}
transaction.Commit();
} catch (Exception ex) {
transaction.Rollback();
throw new TransformalizeException(_connection.Logger, EntityName, "The bulk update operation failed. {0}", ex.Message);
}
}
yield break;
}
public bool RecordsExist(AbstractConnection connection, Entity entity)
{
if (!EntityExists.Exists(connection, entity))
{
return(false);
}
using (var cn = connection.GetConnection()) {
cn.Open();
var records = cn.Query <string>(string.Format(@"
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT `{0}`
FROM `{1}`
LIMIT 1;
COMMIT;
", entity.PrimaryKey.First().Alias, entity.OutputName()));
return(records != null && records.Any());
}
}
public bool RecordsExist(AbstractConnection connection, Entity entity)
{
if (!EntityExists.Exists(connection, entity))
{
return(false);
}
using (var cn = connection.GetConnection()) {
cn.Open();
var exists = cn.Query <bool>(string.Format(@"
SELECT EXISTS(
SELECT ""{0}""
FROM ""{1}""
LIMIT 1
);
", entity.PrimaryKey.First().Alias, entity.OutputName())).DefaultIfEmpty(false).First();
return(exists);
}
}
public Map Read()
{
var map = new Map();
using (var cn = _connection.GetConnection()) {
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText = _sql;
var reader = cmd.ExecuteReader();
if (reader != null)
{
while (reader.Read())
{
map[reader.GetValue(0).ToString()] = new Item(reader.GetValue(1));
}
}
}
return(map);
}
//private static NLog.Logger logger = NLog.LogManager.GetCurrentClassLogger();
public void Read(AbstractConnection connection, AbstractTransaction transaction, UPrintDataSet dataSet)
{
adapter = new NpgsqlDataAdapter();
adapter.SelectCommand = new NpgsqlCommand(selection, connection.GetConnection(), transaction.GetTransaction());
adapter.Fill(dataSet, "person");
}
public void Update(AbstractConnection connection, AbstractTransaction transaction, UPrintDataSet dataSet)
{
adapter = new NpgsqlDataAdapter();
//adapter.SelectCommand = new NpgsqlCommand(selection, connection.GetConnection(), transaction.GetTransaction());
//adapter.Fill(dataSet, "job");
adapter.InsertCommand = new NpgsqlCommand(insertion, connection.GetConnection(), transaction.GetTransaction());
adapter.InsertCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "id", ParameterName = "@id"
});
adapter.InsertCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "name", ParameterName = "@name"
});
adapter.InsertCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "date_add", ParameterName = "@date_add"
});
adapter.InsertCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "description", ParameterName = "@description"
});
adapter.InsertCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "person", ParameterName = "@person"
});
adapter.InsertCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "model", ParameterName = "@model"
});
adapter.UpdateCommand = new NpgsqlCommand(updation, connection.GetConnection(), transaction.GetTransaction());
adapter.UpdateCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "id", ParameterName = "@id"
});
adapter.UpdateCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "name", ParameterName = "@name"
});
adapter.UpdateCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "date_add", ParameterName = "@date_add"
});
adapter.UpdateCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "description", ParameterName = "@description"
});
adapter.UpdateCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "person", ParameterName = "@person"
});
adapter.UpdateCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "model", ParameterName = "@model"
});
adapter.DeleteCommand = new NpgsqlCommand(deletion, connection.GetConnection(), transaction.GetTransaction());
adapter.DeleteCommand.Parameters.Add(new NpgsqlParameter()
{
SourceColumn = "id", ParameterName = "@id"
});
NpgsqlCommandBuilder commandBuilder = new NpgsqlCommandBuilder(adapter);
adapter.Update(dataSet, "job");
}
public bool Exists(AbstractConnection connection, Entity entity) {
using (var cn = connection.GetConnection()) {
cn.Open();
return cn.Query<bool>(SQL, new { name = entity.OutputName() }).DefaultIfEmpty(false).FirstOrDefault();
}
}
