public IList<ItemStatusEntity> GetAllItemStatuss()
        {
            List<ItemStatusEntity> itemStatusList = new List<ItemStatusEntity>();
            try
            {
                _conn = new SqlConnection(ConnString);
                _conn.Open();

                string sql = "SELECT itemStatusUd, status, updateTimestamp, updatePersonFK FROM DBO.itemStatus;";
                var cmd = new SqlCommand(sql, _conn);

                var rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    var itemStatus = new ItemStatusEntity()
                    {
                        itemStatusId = rdr.IsDBNull(rdr.GetOrdinal("itemStatusId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemStatusId")),
                        status = rdr.IsDBNull(rdr.GetOrdinal("status")) ? null : rdr.GetString(rdr.GetOrdinal("status")),
                        updateTimestamp = rdr.IsDBNull(rdr.GetOrdinal("updateTimestamp")) ? new DateTime() : rdr.GetDateTime(rdr.GetOrdinal("updateTimestamp")),
                        updatePersonFK = rdr.IsDBNull(rdr.GetOrdinal("updatePersonFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("updatePersonFK"))
                    };
                    itemStatusList.Add(itemStatus);
                }
            }
            finally
            {
                _conn?.Close();
            }
            return itemStatusList;
        }
Example #2
0
 /// <summary>
 /// Test if connection is OK
 /// </summary>
 /// <returns>Bool true, if it is ok</returns>
 public static void Test()
 {
     SqlConnection connection = null;
     try
     {
         connection = new SqlConnection(ConnectString);
         connection.Open();
     }
     catch
     {
         throw;
     }
     finally
     {
         connection?.Close();
     }
 }
        public bool ContactExists(string email)
        {
            try
            {
                const string sql = "SELECT COUNT([Id]) AS ContactsId FROM Contacts WHERE email = @Email;";

                _conn = new SqlConnection(ConnString);
                _conn.Open();

                var cmd = new SqlCommand(sql, _conn);
                cmd.Parameters.Add("@CEmail", SqlDbType.VarChar);
                cmd.Parameters["@Email"].Value = email;

                return ((int)cmd.ExecuteScalar() >= 1);
            }
            finally
            {
                _conn?.Close();
            }
        }
        public IList<Contact> GetAllContacts(string query, int page, int pageSize)
        {
            var contacts = new List<Contact>();
            try
            {
                _conn = new SqlConnection(ConnString);
                _conn.Open();

                var cmd = new SqlCommand(_selectSql, _conn);

                var paramQuery = new SqlParameter
                {
                    ParameterName = "@query",
                    Value = "%" + query + "%"
                };
                cmd.Parameters.Add(paramQuery);

                var paramStartRow = new SqlParameter
                {
                    ParameterName = "@StartRow",
                    Value = page * pageSize
                };
                cmd.Parameters.Add(paramStartRow);

                var paramEndRow = new SqlParameter
                {
                    ParameterName = "@EndRow",
                    Value = (page * pageSize) + pageSize
                };
                cmd.Parameters.Add(paramEndRow);

                var rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    var contact = new Contact()
                    {
                        Id = rdr.IsDBNull(rdr.GetOrdinal("Id")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("Id")),
                        FirstName = rdr.IsDBNull(rdr.GetOrdinal("FirstName")) ? null : rdr.GetString(rdr.GetOrdinal("FirstName")),
                        LastName = rdr.IsDBNull(rdr.GetOrdinal("LastName")) ? "" : rdr.GetString(rdr.GetOrdinal("LastName")),
                        Address = rdr.IsDBNull(rdr.GetOrdinal("Address")) ? null : rdr.GetString(rdr.GetOrdinal("Address")),
                        ZipCode = rdr.IsDBNull(rdr.GetOrdinal("ZipCode")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("ZipCode")),
                        City = rdr.IsDBNull(rdr.GetOrdinal("City")) ? null : rdr.GetString(rdr.GetOrdinal("City")),
                        Telephone = rdr.IsDBNull(rdr.GetOrdinal("Telephone")) ? null : rdr.GetString(rdr.GetOrdinal("Telephone")),
                        Email = rdr.IsDBNull(rdr.GetOrdinal("Email")) ? null : rdr.GetString(rdr.GetOrdinal("Email")),
                        BirthDate = rdr.IsDBNull(rdr.GetOrdinal("BirthDate")) ? null as DateTime? : rdr.GetDateTime(rdr.GetOrdinal("BirthDate"))
                    };
                    contacts.Add(contact);
                }
            }
            finally
            {
                _conn?.Close();
            }
            return contacts;
        }
        public void Update(int id, Contact contact)
        {
            var contactToUpdate = GetContactById(id);
            if (contactToUpdate == null)
            {
                throw new Exception("Contact does not exist in database");
            }

            try
            {
                _conn = new SqlConnection(ConnString);

                var cmd = _conn.CreateCommand();
                cmd.CommandText = @"UPDATE Contacts SET [FirstName][email protected],
                                                        [LastName][email protected],
                                                        [Address][email protected],
                                                        [ZipCode][email protected],
                                                        [City][email protected],
                                                        [Telephone][email protected],
                                                        [Email][email protected],
                                                        [BirthDate][email protected]
                                                        WHERE [email protected]";

                cmd.Parameters.Add("@Id", SqlDbType.Int);
                cmd.Parameters["@Id"].Value = id;

                cmd.Parameters.Add("@paramFirstName", SqlDbType.VarChar);
                cmd.Parameters["@paramFirstName"].Value = contact.FirstName;

                cmd.Parameters.Add("@paramLastName", SqlDbType.VarChar);
                cmd.Parameters["@paramLastName"].Value = contact.LastName;

                cmd.Parameters.Add("@paramAddress", SqlDbType.VarChar);
                cmd.Parameters["@paramAddress"].Value = contact.Address;

                cmd.Parameters.Add("@paramZipCode", SqlDbType.Int);
                cmd.Parameters["@paramZipCode"].Value = contact.ZipCode;

                cmd.Parameters.Add("@paramCity", SqlDbType.VarChar);
                cmd.Parameters["@paramCity"].Value = contact.City;

                cmd.Parameters.Add("@paramTelephone", SqlDbType.VarChar);
                cmd.Parameters["@paramTelephone"].Value = contact.Telephone;

                cmd.Parameters.Add("@paramEmail", SqlDbType.VarChar);
                cmd.Parameters["@paramEmail"].Value = contact.Email;

                cmd.Parameters.Add("@paramBirthDate", SqlDbType.DateTime);
                cmd.Parameters["@paramBirthDate"].Value = contact.BirthDate;

                _conn.Open();

                var number = cmd.ExecuteNonQuery();

                if (number != 1)
                {
                    throw new Exception($"No Contacts were updated with Id: {id}");
                }
            }
            finally
            {
                _conn?.Close();
            }
        }
        public long Insert(Contact contact)
        {
            try
            {
                var contactExists = GetContactByEmail(contact.Email);
                if (contactExists != null)
                {
                    throw new Exception($"Entity {contact.Email} already exists in database!");
                }
                _conn = new SqlConnection(ConnString);

                var cmd = _conn.CreateCommand();

                cmd.CommandText =
                    @"INSERT INTO[dbo].[Contacts] (FirstName, LastName, [Address], ZipCode, City, Telephone, Email, BirthDate)
                    VALUES(@FirstName, @LastName,  @Address, @ZipCode, @City, @Telephone, @Email, @BirthDate);SELECT CAST(scope_identity() AS int)";

                cmd.Parameters.Add("@FirstName", SqlDbType.VarChar);
                cmd.Parameters["@FirstName"].Value = contact.FirstName;

                cmd.Parameters.Add("@LastName", SqlDbType.VarChar);
                cmd.Parameters["@LastName"].Value = contact.LastName;

                cmd.Parameters.Add("@Address", SqlDbType.VarChar);
                cmd.Parameters["@Address"].Value = contact.Address;

                cmd.Parameters.Add("@ZipCode", SqlDbType.Int);
                cmd.Parameters["@ZipCode"].Value = contact.ZipCode;

                cmd.Parameters.Add("@City", SqlDbType.VarChar);
                cmd.Parameters["@City"].Value = contact.City;

                cmd.Parameters.Add("@Telephone", SqlDbType.VarChar);
                cmd.Parameters["@Telephone"].Value = contact.Telephone;

                cmd.Parameters.Add("@Email", SqlDbType.VarChar);
                cmd.Parameters["@Email"].Value = contact.Email;

                cmd.Parameters.Add("@BirthDate", SqlDbType.DateTime);
                cmd.Parameters["@BirthDate"].Value = contact.BirthDate;

                _conn.Open();

                try
                {
                    return  long.Parse(cmd.ExecuteScalar().ToString());
                }
                catch (Exception)
                {
                    throw new Exception($"Entity {contact.FirstName} {contact.LastName} not inserted in database!");
                }

            }
            finally
            {
                _conn?.Close();
            }
        }
        public long GetNumberOfContacts(string query)
        {
            try
            {
                const string sql = "SELECT COUNT(*) FROM Contacts where FirstName LIKE @query OR LastName LIKE @query";

                _conn = new SqlConnection(ConnString);
                _conn.Open();

                var cmd = new SqlCommand(sql, _conn);
                var paramQuery = new SqlParameter
                {
                    ParameterName = "@query",
                    Value = "%" + query + "%"
                };
                cmd.Parameters.Add(paramQuery);

                return long.Parse(cmd.ExecuteScalar().ToString());
            }
            finally
            {
                _conn?.Close();
            }
        }
        public Contact GetContactById(int id)
        {
            Contact contact = null;
            try
            {
                _conn = new SqlConnection(ConnString);
                _conn.Open();

                const string sql = "SELECT Id, FirstName, LastName, [Address], ZipCode, City, Telephone, Email, BirthDate FROM Contacts WHERE [Id] = @Id;";

                var cmd = new SqlCommand(sql, _conn);

                var paramId = new SqlParameter
                {
                    ParameterName = "@Id",
                    Value = id
                };
                cmd.Parameters.Add(paramId);

                var rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    contact = new Contact
                    {
                        Id = rdr.IsDBNull(rdr.GetOrdinal("Id")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("Id")),
                        FirstName = rdr.IsDBNull(rdr.GetOrdinal("FirstName")) ? null : rdr.GetString(rdr.GetOrdinal("FirstName")),
                        LastName = rdr.IsDBNull(rdr.GetOrdinal("LastName")) ? "" : rdr.GetString(rdr.GetOrdinal("LastName")),
                        Address = rdr.IsDBNull(rdr.GetOrdinal("Address")) ? null : rdr.GetString(rdr.GetOrdinal("Address")),
                        ZipCode = rdr.IsDBNull(rdr.GetOrdinal("ZipCode")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("ZipCode")),
                        City = rdr.IsDBNull(rdr.GetOrdinal("City")) ? null : rdr.GetString(rdr.GetOrdinal("City")),
                        Telephone = rdr.IsDBNull(rdr.GetOrdinal("Telephone")) ? null : rdr.GetString(rdr.GetOrdinal("Telephone")),
                        Email = rdr.IsDBNull(rdr.GetOrdinal("Email")) ? null : rdr.GetString(rdr.GetOrdinal("Email")),
                        BirthDate = rdr.IsDBNull(rdr.GetOrdinal("BirthDate")) ? null as DateTime? : rdr.GetDateTime(rdr.GetOrdinal("BirthDate"))
                    };
                }
            }
            finally
            {
                _conn?.Close();
            }
            return contact;
        }
Example #9
0
        private void BulkCopy_DoWork(object sender, DoWorkEventArgs e)
        {
            //TODO: use more usings!

            var sw = Stopwatch.StartNew();
            SqlConnection connSource = null;
            SqlConnection connDest = null;

            var result = new BulkCopyResult();
            e.Result = result;

            var parameters = (BulkCopyParameters)e.Argument;

            try {
                connSource = new SqlConnection(parameters.Source.ConnectionString);
                connDest = new SqlConnection(parameters.Destination.ConnectionString);

                connSource.Open();
                connDest.Open();

                SqlDataReader reader = null;
                foreach (var table in parameters.Tables.Values) {
                    if (worker.CancellationPending) {
                        e.Cancel = true;
                        return;
                    }

                    Log.Information("Copying rows to {0}", "[" + connDest.DataSource + "].[" + connDest.Database + "]." + table.FullName);

                    SqlTransaction transaction = null;
                    SqlBulkCopy bulkCopy = null;
                    try {
                        //Use Dictionary<,> so that destination column names can be case-insensitively located in their proper case because SqlBulkCopy mappings are case-sensitive!
                        var destSchema = connDest.GetSchema("Columns", new[] {connDest.Database, table.Schema, table.Name});
                        var destColumnsMap = destSchema.AsEnumerable().Select(x => x.Field<string>("COLUMN_NAME")).ToDictionary(k => k, v => v, StringComparer.OrdinalIgnoreCase);

                        transaction = connDest.BeginTransaction();

                        string query = String.Format(parameters.Query, table.FullName);
                        reader = new SqlCommand(query, connSource) { CommandTimeout = 9000 }.ExecuteReader();

                        //TODO: any FKs should be dropped and then recreated after truncating
                        try {
                            new SqlCommand(String.Format(Query_TruncateTable, table.FullName), connDest, transaction) { CommandTimeout = 120 }.ExecuteNonQuery();
                        } catch {
                            new SqlCommand(String.Format(Query_DeleteAllInTable, table.FullName), connDest, transaction) { CommandTimeout = 120 }.ExecuteNonQuery();
                        }

                        bulkCopy = new SqlBulkCopy(connDest, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls, transaction) {
                            BulkCopyTimeout = 9000,
                            BatchSize = 10000,
                            NotifyAfter = 10000,
                            DestinationTableName = table.FullName
                        };
                        bulkCopy.SqlRowsCopied += sbc_SqlRowsCopied;

                        //Iterate over the Reader to get source column names because they may be defined by query results rather than a table-schema
                        var sourceColumns = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
                        for (int i = 0; i < reader.FieldCount; i++) {
                            sourceColumns.Add(reader.GetName(i));
                        }

                        var mapColumns = bulkCopy.ColumnMappings;
                        foreach (var column in sourceColumns) {
                            if (destColumnsMap.ContainsKey(column)) {
                                mapColumns.Add(column, destColumnsMap[column]);
                            }
                        }

                        rowsInCurrentTable = table.RowCount;

                        //Make sure the progress indicators are updated immediately, so the correct progress details are shown
                        sbc_SqlRowsCopied(bulkCopy, new SqlRowsCopiedEventArgs(0));

                        bulkCopy.WriteToServer(reader);

                        transaction.Commit();

                        Log.Information("Copied approximately {0} rows to {1}", table.RowCount, "[" + connDest.DataSource + "].[" + connDest.Database + "]." + table.FullName);

                    } catch (Exception ex) {
                        result.FailedTables[table.FullName] = ex;
                        transaction?.Rollback();
                    } finally {
                        bulkCopy?.Close();
                        reader?.Close();
                    }

                    if (worker.CancellationPending) {
                        e.Cancel = true;
                        return;
                    }
                }

            } finally {
                connDest?.Close();
                connSource?.Close();
                sw.Stop();
                result.Elapsed = sw.Elapsed;
            }
        }
Example #10
0
        public ItemStatusEntity GetItemStatusById(int id)
        {
            List<ItemStatusEntity> itemStatusList = new List<ItemStatusEntity>();
            try
            {
                _conn = new SqlConnection(ConnString);
                _conn.Open();

                string sql = "SELECT itemStatusUd, status, updateTimestamp, updatePersonFK FROM DBO.itemStatus WHERE itemStatusId = @id;";
                var cmd = new SqlCommand(sql, _conn);

                var paramQuery = new SqlParameter
                {
                    ParameterName = "@id",
                    Value = "%" + id + "%"
                };
                cmd.Parameters.Add(paramQuery);

                var rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    var itemStatus = new ItemStatusEntity()
                    {
                        itemStatusId = rdr.IsDBNull(rdr.GetOrdinal("itemStatusId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemStatusId")),
                        status = rdr.IsDBNull(rdr.GetOrdinal("status")) ? null : rdr.GetString(rdr.GetOrdinal("status")),
                        updateTimestamp = rdr.IsDBNull(rdr.GetOrdinal("updateTimestamp")) ? new DateTime() : rdr.GetDateTime(rdr.GetOrdinal("updateTimestamp")),
                        updatePersonFK = rdr.IsDBNull(rdr.GetOrdinal("updatePersonFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("updatePersonFK"))
                    };
                    itemStatusList.Add(itemStatus);
                }
            }
            finally
            {
                _conn?.Close();
            }
            return itemStatusList.FirstOrDefault();
        }
Example #11
0
        public void Update(int id, ItemStatusEntity itemStatus)
        {
            CheckItemStatusForRequiredValues(itemStatus, RepositoryUtils.RepositoryAction.Update);

            var contactToUpdate = GetItemStatusById(id);
            if (contactToUpdate == null)
            {
                throw new Exception("ItemStatus does not exist in database");
            }

            try
            {
                _conn = new SqlConnection(ConnString);

                var cmd = _conn.CreateCommand();
                cmd.CommandText = @"UPDATE person SET [status][email protected],
                                                      [updateTimestamp]=getdate(),
                                                      [updatePersonFK][email protected]
                                                      WHERE [email protected]";

                cmd.Parameters.Add("@Id", SqlDbType.Int);
                cmd.Parameters["@Id"].Value = id;

                cmd.Parameters.Add("@status", SqlDbType.VarChar);
                cmd.Parameters["@status"].Value = itemStatus.status;

                cmd.Parameters.Add("@updatePersonFK", SqlDbType.Int);
                cmd.Parameters["@updatePersonFK"].Value = itemStatus.updatePersonFK;

                _conn.Open();

                var number = cmd.ExecuteNonQuery();

                if (number != 1)
                {
                    throw new Exception($"No Contacts were updated with Id: {id}");
                }
            }
            finally
            {
                _conn?.Close();
            }
        }
Example #12
0
        public bool ItemStatusExists(string status)
        {
            try
            {
                const string sql = "SELECT COUNT([itemStatusId]) FROM dbo.itemStatus WHERE status = @status;";

                _conn = new SqlConnection(ConnString);
                _conn.Open();

                var cmd = new SqlCommand(sql, _conn);
                cmd.Parameters.Add("@status", SqlDbType.VarChar);
                cmd.Parameters["@status"].Value = status;

                return ((int)cmd.ExecuteScalar() >= 1);
            }
            finally
            {
                _conn?.Close();
            }
        }
Example #13
0
        public long Insert(ItemStatusEntity itemStatus)
        {
            CheckItemStatusForRequiredValues(itemStatus, RepositoryUtils.RepositoryAction.Insert);
            try
            {
                var contactExists = GetItemStatusByStatusName(itemStatus.status);
                if (contactExists != null)
                {
                    throw new Exception($"Entity {itemStatus.status} already exists in database!");
                }
                _conn = new SqlConnection(ConnString);

                var cmd = _conn.CreateCommand();

                cmd.CommandText =
                    @"INSERT INTO[dbo].[itemStatus] (status, updateTimestamp, updatePersonFK)
                    VALUES(@status, getdate(), @updatePersonFK);SELECT CAST(scope_identity() AS int)";

                cmd.Parameters.Add("@status", SqlDbType.VarChar);
                cmd.Parameters["@status"].Value = itemStatus.status;

                cmd.Parameters.Add("@updatePersonFK", SqlDbType.Int);
                cmd.Parameters["@updatePersonFK"].Value = itemStatus.updatePersonFK;

                _conn.Open();

                try
                {
                    return int.Parse(cmd.ExecuteScalar().ToString());
                }
                catch (Exception)
                {
                    throw new Exception($"ItemStatus {itemStatus.status} not inserted in database!");
                }

            }
            finally
            {
                _conn?.Close();
            }
        }
Example #14
0
        public long GetNumberOfItemStatus()
        {
            try
            {
                _conn = new SqlConnection(ConnString);
                _conn.Open();

                string sql = "SELECT COUNT(itemStatusId) FROM dbo.itemStatus;";
                var cmd = new SqlCommand(sql, _conn);

                return Int32.Parse(cmd.ExecuteScalar().ToString());
            }
            finally
            {
                _conn?.Close();
            }
        }