コード例 #1
0
        public void Save(UserRegistration user, OperationType operation, string cvr)
        {
            long user_id = 0;

            using (DbConnection connection = DaoUtil.GetConnection())
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        using (DbCommand command = DaoUtil.GetCommand(UserStatements.Insert, connection))
                        {
                            command.Transaction = transaction;

                            command.Parameters.Add(DaoUtil.GetParameter("@uuid", user.Uuid));
                            command.Parameters.Add(DaoUtil.GetParameter("@shortkey", user.ShortKey ?? (object)DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@user_id", user.UserId ?? (object)DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@phone_number", user.PhoneNumber ?? (object)DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@name", user.Person.Name ?? (object)DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@cpr", user.Person.Cpr ?? (object)DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@email", user.Email ?? (object)DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@racfid", user.RacfID ?? (object)DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@location", user.Location ?? (object)DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@cvr", cvr));
                            command.Parameters.Add(DaoUtil.GetParameter("@operation", operation.ToString()));

                            user_id = Convert.ToInt64(command.ExecuteScalar());
                        }

                        // insert positions
                        foreach (Position position in user.Positions ?? Enumerable.Empty <Position>())
                        {
                            using (DbCommand command = DaoUtil.GetCommand(UserStatements.InsertPositions, connection))
                            {
                                command.Transaction = transaction;

                                command.Parameters.Add(DaoUtil.GetParameter("@user_id", user_id));
                                command.Parameters.Add(DaoUtil.GetParameter("@name", position.Name));
                                command.Parameters.Add(DaoUtil.GetParameter("@orgunit_uuid", position.OrgUnitUuid));
                                command.ExecuteNonQuery();
                            }
                        }

                        transaction.Commit();
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();

                        throw;
                    }
                }
            }
        }
コード例 #2
0
        public void OnSuccess(long id)
        {
            using (DbConnection connection = DaoUtil.GetConnection())
            {
                connection.Open();

                using (DbCommand command = DaoUtil.GetCommand(UserStatements.Success, connection))
                {
                    command.Parameters.Add(DaoUtil.GetParameter("@id", id));
                    command.ExecuteNonQuery();
                }
            }
        }
コード例 #3
0
ファイル: OrgUnitDao.cs プロジェクト: OS2sync/OS2sync
        public void Delete(long id)
        {
            using (DbConnection connection = DaoUtil.GetConnection())
            {
                connection.Open();

                using (DbCommand command = DaoUtil.GetCommand(OrgUnitStatements.Delete, connection))
                {
                    command.Parameters.Add(DaoUtil.GetParameter("@id", id));
                    command.ExecuteNonQuery();
                }
            }
        }
コード例 #4
0
        public void OnFailure(long id, String errorMessage)
        {
            using (DbConnection connection = DaoUtil.GetConnection())
            {
                connection.Open();

                using (DbCommand command = DaoUtil.GetCommand(UserStatements.Failure, connection))
                {
                    command.Parameters.Add(DaoUtil.GetParameter("@id", id));
                    command.Parameters.Add(DaoUtil.GetParameter("@error", errorMessage));
                    command.ExecuteNonQuery();
                }
            }
        }
コード例 #5
0
ファイル: InitDB.cs プロジェクト: OS2sync/OS2sync
        public static void InitializeDatabase()
        {
            if (!string.IsNullOrEmpty(OrganisationRegistryProperties.GetInstance().DBConnectionString))
            {
                using (DbConnection connection = DaoUtil.GetConnection())
                {
                    try
                    {
                        string location = OrganisationRegistryProperties.GetInstance().MigrationScriptsPath;

                        if (OrganisationRegistryProperties.GetInstance().Database.Equals(DatabaseType.MSSQL))
                        {
                            var evolve = new Evolve.Evolve(connection, msg => log.Info(msg))
                            {
                                Locations       = new[] { location },
                                Schemas         = new[] { "dbo" }, // default schema can be NULL in SQL Server, which makes Evolve unhappy
                                IsEraseDisabled = true
                            };

                            evolve.Migrate();
                        }
                        else
                        {
                            var evolve = new Evolve.Evolve(connection, msg => log.Info(msg))
                            {
                                Locations       = new[] { location },
                                IsEraseDisabled = true
                            };

                            evolve.Migrate();
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }
コード例 #6
0
        public List <UserRegistrationExtended> Get4OldestEntries()
        {
            var users = new List <UserRegistrationExtended>();

            using (DbConnection connection = DaoUtil.GetConnection())
            {
                connection.Open();

                using (DbCommand command = DaoUtil.GetCommand(UserStatements.Select, connection))
                {
                    using (DbDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            UserRegistrationExtended user = new UserRegistrationExtended();
                            long user_id = (long)reader["id"];
                            user.Id = user_id;

                            user.PhoneNumber = GetValue(reader, "phone_number");
                            user.Email       = GetValue(reader, "email");
                            user.RacfID      = GetValue(reader, "racfid");
                            user.Location    = GetValue(reader, "location");

                            user.UserId   = GetValue(reader, "user_id");
                            user.Cvr      = GetValue(reader, "cvr");
                            user.Uuid     = GetValue(reader, "uuid");
                            user.ShortKey = GetValue(reader, "shortkey");

                            user.Person.Name = GetValue(reader, "name");
                            user.Person.Cpr  = GetValue(reader, "cpr");
                            user.Timestamp   = (DateTime)reader["timestamp"];
                            user.Operation   = (OperationType)Enum.Parse(typeof(OperationType), GetValue(reader, "operation"));

                            users.Add(user);
                        }
                    }
                }

                foreach (var user in users)
                {
                    // read positions
                    using (DbCommand command = DaoUtil.GetCommand(UserStatements.SelectPositions, connection))
                    {
                        command.Parameters.Add(DaoUtil.GetParameter("@id", user.Id));

                        using (DbDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Position position = new Position();
                                position.OrgUnitUuid = GetValue(reader, "orgunit_uuid");
                                position.Name        = GetValue(reader, "name");

                                user.Positions.Add(position);
                            }
                        }
                    }
                }
            }

            return(users);
        }
コード例 #7
0
ファイル: OrgUnitDao.cs プロジェクト: OS2sync/OS2sync
        public void Save(OrgUnitRegistration ou, OperationType operation, string cvr)
        {
            long orgunit_id = 0;

            using (DbConnection connection = DaoUtil.GetConnection())
            {
                connection.Open();

                using (DbTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        using (DbCommand command = DaoUtil.GetCommand(OrgUnitStatements.Insert, connection))
                        {
                            command.Transaction = transaction;

                            command.Parameters.Add(DaoUtil.GetParameter("@uuid", ou.Uuid));
                            command.Parameters.Add(DaoUtil.GetParameter("@shortkey", (object)ou.ShortKey ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@name", (object)ou.Name ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@parent_ou_uuid", (object)ou.ParentOrgUnitUuid ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@payout_ou_uuid", (object)ou.PayoutUnitUuid ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@cvr", cvr));
                            command.Parameters.Add(DaoUtil.GetParameter("@operation", operation.ToString()));

                            command.Parameters.Add(DaoUtil.GetParameter("@orgunit_type", ou.Type.ToString()));
                            command.Parameters.Add(DaoUtil.GetParameter("@los_shortname", (object)ou.LOSShortName ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@los_id", (object)ou.LOSId ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@phone_number", (object)ou.PhoneNumber ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@url", (object)ou.Url ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@landline", (object)ou.Landline ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@email", (object)ou.Email ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@location", (object)ou.Location ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@ean", (object)ou.Ean ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@contact_open_hours", (object)ou.ContactOpenHours ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@dtr_id", (object)ou.DtrId ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@email_remarks", (object)ou.EmailRemarks ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@contact", (object)ou.Contact ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@post_return", (object)ou.PostReturn ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@phone_open_hours", (object)ou.PhoneOpenHours ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@post", (object)ou.Post ?? DBNull.Value));
                            command.Parameters.Add(DaoUtil.GetParameter("@manager_uuid", (object)ou.ManagerUuid ?? DBNull.Value));

                            orgunit_id = Convert.ToInt64(command.ExecuteScalar());
                        }

                        // insert tasks
                        foreach (string task in ou.Tasks ?? Enumerable.Empty <string>())
                        {
                            using (DbCommand command = DaoUtil.GetCommand(OrgUnitStatements.InsertTasks, connection))
                            {
                                command.Transaction = transaction;

                                command.Parameters.Add(DaoUtil.GetParameter("@orgunit_id", orgunit_id));
                                command.Parameters.Add(DaoUtil.GetParameter("@task", task));

                                command.ExecuteNonQuery();
                            }
                        }

                        // insert contact for tasks
                        foreach (string task in ou.ContactForTasks ?? Enumerable.Empty <string>())
                        {
                            using (DbCommand command = DaoUtil.GetCommand(OrgUnitStatements.InsertContactForTasks, connection))
                            {
                                command.Transaction = transaction;

                                command.Parameters.Add(DaoUtil.GetParameter("@orgunit_id", orgunit_id));
                                command.Parameters.Add(DaoUtil.GetParameter("@task", task));

                                command.ExecuteNonQuery();
                            }
                        }

                        transaction.Commit();
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();

                        throw;
                    }
                }
            }
        }
コード例 #8
0
ファイル: OrgUnitDao.cs プロジェクト: OS2sync/OS2sync
        public List <OrgUnitRegistrationExtended> Get4OldestEntries()
        {
            List <OrgUnitRegistrationExtended> result = new List <OrgUnitRegistrationExtended>();

            using (DbConnection connection = DaoUtil.GetConnection())
            {
                connection.Open();

                using (DbCommand command = DaoUtil.GetCommand(OrgUnitStatements.Select, connection))
                {
                    using (DbDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            long orgUnitId = (long)reader["id"];

                            var orgUnit = new OrgUnitRegistrationExtended();
                            orgUnit.Id                = orgUnitId;
                            orgUnit.Uuid              = GetValue(reader, "uuid");
                            orgUnit.ShortKey          = GetValue(reader, "shortkey");
                            orgUnit.Name              = GetValue(reader, "name");
                            orgUnit.ParentOrgUnitUuid = GetValue(reader, "parent_ou_uuid");
                            orgUnit.PayoutUnitUuid    = GetValue(reader, "payout_ou_uuid");
                            orgUnit.ManagerUuid       = GetValue(reader, "manager_uuid");
                            orgUnit.LOSShortName      = GetValue(reader, "los_shortname");
                            orgUnit.LOSId             = GetValue(reader, "losid");
                            orgUnit.PhoneNumber       = GetValue(reader, "phone_number");
                            orgUnit.Email             = GetValue(reader, "email");
                            orgUnit.Landline          = GetValue(reader, "landline");
                            orgUnit.Url               = GetValue(reader, "url");
                            orgUnit.Location          = GetValue(reader, "location");
                            orgUnit.Ean               = GetValue(reader, "ean");
                            orgUnit.Post              = GetValue(reader, "post_address");
                            orgUnit.ContactOpenHours  = GetValue(reader, "contact_open_hours");
                            orgUnit.DtrId             = GetValue(reader, "dtr_id");
                            orgUnit.EmailRemarks      = GetValue(reader, "email_remarks");
                            orgUnit.Contact           = GetValue(reader, "contact");
                            orgUnit.PostReturn        = GetValue(reader, "post_return");
                            orgUnit.PhoneOpenHours    = GetValue(reader, "phone_open_hours");
                            orgUnit.Operation         = (OperationType)Enum.Parse(typeof(OperationType), GetValue(reader, "operation"));
                            orgUnit.Cvr               = GetValue(reader, "cvr");

                            // type can be null on DELETEs
                            string tmpType = GetValue(reader, "orgunit_type");
                            if (!string.IsNullOrEmpty(tmpType))
                            {
                                orgUnit.Type = (OrgUnitType)Enum.Parse(typeof(OrgUnitType), tmpType);
                            }
                            else
                            {
                                orgUnit.Type = OrgUnitType.DEPARTMENT;
                            }

                            result.Add(orgUnit);
                        }
                    }
                }

                foreach (var orgUnit in result)
                {
                    orgUnit.Tasks           = new List <string>();
                    orgUnit.ContactForTasks = new List <string>();

                    using (DbCommand command = DaoUtil.GetCommand(OrgUnitStatements.SelectTasks, connection))
                    {
                        command.Parameters.Add(DaoUtil.GetParameter("@id", orgUnit.Id));

                        using (DbDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                string task = GetValue(reader, "task");

                                orgUnit.Tasks.Add(task);
                            }
                        }
                    }

                    using (DbCommand command = DaoUtil.GetCommand(OrgUnitStatements.SelectContactForTasks, connection))
                    {
                        command.Parameters.Add(DaoUtil.GetParameter("@id", orgUnit.Id));

                        using (DbDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                string task = GetValue(reader, "task");

                                orgUnit.ContactForTasks.Add(task);
                            }
                        }
                    }
                }
            }

            return(result);
        }