private bool isContactExists(Contact contact)
{
try
{
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT COUNT( *) from [" + TABLENAME + @"] where
[Address] like @address AND
[Phone] like @phone";
FactoryUtility.AddParameterWithValue(cmd, "@address", contact.Address);
FactoryUtility.AddParameterWithValue(cmd, "@phone", contact.Phone);
cmd.Connection = connection;
int addressCount = ( int )cmd.ExecuteScalar();
if (addressCount > 0)
{
contact.Id = getExistingId(contact);
return(true);
}
return(false);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(true);
}
}
private void insertContact(Contact contact)
{
try
{
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = @"INSERT INTO [" + TABLENAME + @"]
( [Address], [Phone])
VALUES (@country, @Phone)";
cmd.Connection = connection;
FactoryUtility.AddParameterWithValue(cmd, "@country", contact.Address);
FactoryUtility.AddParameterWithValue(cmd, "@phone", contact.Phone);
cmd.ExecuteNonQuery();
contact.Id = getLastId();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void Delete(int IdToDelete)
{
using (connection = factory.CreateDbConnection())
{
try
{
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = "DELETE FROM [" + TABLENAME + @"]
WHERE [Id] like @contactId";
FactoryUtility.AddParameterWithValue(cmd, "@contactId", IdToDelete);
cmd.Connection = connection;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
public int Update(User user)
{
using (connection = factory.CreateDbConnection())
{
try
{
if (isContactExists(user.Contact) == false)
{
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = "UPDATE [" + TABLENAME + @"]
SET [Address] = @address ,
[Phone] = @phone
WHERE [Id] = @Id";
FactoryUtility.AddParameterWithValue(cmd, "@Id", user.Contact.Id);
FactoryUtility.AddParameterWithValue(cmd, "@address", user.Contact.Address);
FactoryUtility.AddParameterWithValue(cmd, "@phone", user.Contact.Phone);
cmd.Connection = connection;
cmd.ExecuteNonQuery();
return(-1);
}
else
{
if (user.ContactId != user.Contact.Id)
{
return(user.ContactId);
}
else
{
return(-1);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(-1);
}
}
}
public void Delete(Contact contact)
{
using (connection = factory.CreateDbConnection())
{
try
{
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = "DELETE FROM [" + TABLENAME + @"]
WHERE [Address] like @address AND
[Phone] like @phone";
FactoryUtility.AddParameterWithValue(cmd, "@address", contact.Address);
FactoryUtility.AddParameterWithValue(cmd, "@phone", contact.Phone);
cmd.Connection = connection;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private bool isTableExists()
{
bool result = false;
try
{
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = @"IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME = @tableName)
SELECT 1 AS res ELSE SELECT 0 AS res; ";
cmd.Connection = connection;
FactoryUtility.AddParameterWithValue(cmd, "@tableName", TABLENAME);
result = ( int )cmd.ExecuteScalar() == 1;
return(result);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(result);
}
}
private int getExistingId(Contact contact)
{
try
{
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT [ID] from [" + TABLENAME + @"] where
[Address] like @address AND
[Phone] like @phone";
FactoryUtility.AddParameterWithValue(cmd, "@address", contact.Address);
FactoryUtility.AddParameterWithValue(cmd, "@phone", contact.Phone);
cmd.Connection = connection;
int addressID = ( int )cmd.ExecuteScalar();
if (addressID > 0)
{
return(addressID);
}
return(-1);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(-1);
}
}