// Insert a new Contact
public bool InsertContact(string firstName, string lastName, int groupId, string phone, string email, string address, MemoryStream picture, int userId)
{
using (SqlCommand command = new SqlCommand("InserContact", db.GetConnection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 50));
command.Parameters["@FirstName"].Value = firstName;
command.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 50));
command.Parameters["@LastName"].Value = lastName;
command.Parameters.Add(new SqlParameter("@GroupId", SqlDbType.Int));
command.Parameters["@GroupId"].Value = groupId;
command.Parameters.Add(new SqlParameter("@Phone", SqlDbType.VarChar, 20));
command.Parameters["@Phone"].Value = phone;
command.Parameters.Add(new SqlParameter("@Email", SqlDbType.VarChar, 30));
command.Parameters["@Email"].Value = email;
command.Parameters.Add(new SqlParameter("@Address", SqlDbType.Text));
command.Parameters["@Address"].Value = address;
command.Parameters.Add(new SqlParameter("@Picture", SqlDbType.VarBinary));
command.Parameters["@Picture"].Value = picture.ToArray();
command.Parameters.Add(new SqlParameter("@UserId", SqlDbType.Int));
command.Parameters["@UserId"].Value = userId;
try
{
db.OpenConnection();
if (command.ExecuteNonQuery() == 1)
{
return(true);
}
return(false);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
finally
{
db.CloseConnection();
}
}
}
// Function to add a group to the logged in user
public bool InsertGroup(string groupName, int userId)
{
using (SqlCommand command = new SqlCommand("InsertNewGroup", db.GetConnection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@GroupName", SqlDbType.VarChar, 100));
command.Parameters["@GroupName"].Value = groupName;
command.Parameters.Add(new SqlParameter("@UserId", SqlDbType.Int));
command.Parameters["@UserId"].Value = userId;
try
{
db.OpenConnection();
if (command.ExecuteNonQuery() == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
finally
{
db.CloseConnection();
}
}
}
// Function to check if the Username Exists while Register new User
public bool DoesUsernameExists(string username)
{
using (SqlCommand sqlCommand = new SqlCommand("uspSelectCurrentUsername", db.GetConnection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add(new SqlParameter("@Username", SqlDbType.VarChar, 100));
sqlCommand.Parameters["@Username"].Value = username;
using (SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand))
{
try
{
db.OpenConnection();
DataTable table = new DataTable();
adapter.Fill(table);
// If the user exists return true
if (table.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(false);
}
finally
{
db.CloseConnection();
}
}
}
}
// Display the logged in user image and username
internal void GetImageAndUsername()
{
using (SqlCommand command = new SqlCommand("uspSelectUsernameAndPicture", myDB.GetConnection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@UserId", SqlDbType.Int));
command.Parameters["@UserId"].Value = Globals.GlobalUserId;
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
DataTable table = new DataTable();
try
{
myDB.OpenConnection();
adapter.Fill(table);
if (table.Rows.Count > 0)
{
// Display the user image
byte[] pic = (byte[])table.Rows[0]["Picture"];
MemoryStream picture = new MemoryStream(pic);
this.pictureBoxUserPicture.Image = Image.FromStream(picture);
//display the user username
if (this.LabelUsername.Text == "(username)")
{
this.LabelUsername.Text = this.LabelUsername.Text.Replace("(username)", table.Rows[0]["Username"].ToString());
}
else
{
this.LabelUsername.Text = table.Rows[0]["Username"].ToString();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
myDB.CloseConnection();
}
}
}
}
// Login button
private void Button_Login_Click(object sender, EventArgs e)
{
ManageContacts_DB db = new ManageContacts_DB();
DataTable table = new DataTable();
using (SqlCommand command = new SqlCommand("uspLogin", db.GetConnection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@Username", SqlDbType.VarChar, 100));
command.Parameters["@Username"].Value = this.textBoxUsername.Text;
command.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar, 20));
command.Parameters["@Password"].Value = this.textBoxPassword.Text;
using (SqlDataAdapter adapter = new SqlDataAdapter())
{
try
{
db.OpenConnection();
adapter.SelectCommand = command;
adapter.Fill(table);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
db.CloseConnection();
}
}
}
// Check for empty fields
if (VerifyFields("login"))
{
// Check if this user exists
if (table.Rows.Count > 0)
{
// Get user Id
int userId = Convert.ToInt32(table.Rows[0][0].ToString());
Globals.SetGlobalUserId(userId);
// Show the Main App From
this.DialogResult = DialogResult.OK;
}
else
{
// Show an Error Message
this.DialogResult = MessageBox.Show("Invalid Username or Password",
"Login Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
this.DialogResult = DialogResult.None;
}
}
else
{
// Show an Error Message
this.DialogResult = MessageBox.Show("Empty Username or Password",
"Login Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
this.DialogResult = DialogResult.None;
}
}
