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 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 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]=@paramFirstName,
[LastName]=@paramLastName,
[Address]=@paramAddress,
[ZipCode]=@paramZipCode,
[City]=@paramCity,
[Telephone]=@paramTelephone,
[Email]=@paramEmail,
[BirthDate]=@paramBirthDate
WHERE Id=@Id";
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 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;
}
