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); } }