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 User FormateUser( UserDTO packet, Contact contact )
{
return new User
{
Contact = contact,
Id = packet.Id,
ContactId=packet.ContactId,
Login = packet.Login,
Password = packet.Password,
IsAdmin=packet.IsAdmin
};
}
public void Save( Contact contact )
{
using ( connection = factory.CreateDbConnection () )
{
if ( isTableExists () == false )
{
createTable ();
}
if ( isContactExists ( contact ) == false )
{
insertContact ( contact );
}
}
}
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 );
}
}
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;
}
}