示例#1
0
        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);
     }
 }
示例#3
0
        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;
        }
示例#5
0
 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());
     }
 }
示例#6
0
 public RunOperation(string inKey, AbstractConnection connection, int timeOut)
     : base(inKey, string.Empty)
 {
     _timeOut    = timeOut;
     _connection = connection.GetConnection();
     _logger     = connection.Logger;
 }
示例#7
0
        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);
        }
示例#8
0
 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();
            }
        }
示例#11
0
        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;
        }
示例#15
0
 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;
            }
        }
示例#19
0
 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);
 }
示例#20
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);
            }
        }
示例#24
0
        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);
        }
示例#25
0
        //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");
        }
示例#26
0
        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();
     }
 }