Simple Contact Management System In C# and VB .Net

create database ContactManagement

use ContactManagement

create table ContactGroups(GroupId int primary key, GroupName varchar(40))

select * from ContactGroups

insert into ContactGroups values (1, 'Students')
insert into ContactGroups values (2, 'Relatives')

create table Contacts (ContactId int primary key, GroupId int references ContactGroups(GroupId), ContactName varchar(40), Phone varchar(15), Email varchar(40))

insert into Contacts values (1, 1, 'Sumesh S', '9961321010', 'mail4sumo@yahoo.co.in')

insert into Contacts values (2, 1, 'Nithin T M', '9446737393', 'nithintm@gmail.com')

insert into Contacts values (3, 2, 'Shalvin', '998765778', 'shalvin@gmail.com')

select * from Contacts

select G.GroupName, C.ContactName, C.Phone, C.Email from
ContactGroups G, Contacts C where G.GroupId = C.GroupId

I am adding a class called DbConnect which is to contain Database Connectivity and retrieval code.

using System.Data;
using System.Data.SqlClient;
namespace ContactManagementSystem
{

class DbConnect
{
public SqlConnection cnn;
public void Open()
{
cnn = new SqlConnection("Integrated Security=sspi;Initial Catalog=ContactManagement");
cnn.Open();
}

public DataTable GetGroups()
{
Open();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select * from ContactGroups", cnn);
da.Fill(ds, "Grp");
return ds.Tables["Grp"];
}
}

frmGroups
























Please visit my blog Windows Forms Input Validation if your are not familiar with Validation event and ErrorProvider Control.


using System.Data.SqlClient;
DbConnect dbc = new DbConnect();
Boolean b = false;
int intMax = 0;
SqlCommand cmd;

private void frmGroups_Load(object sender, EventArgs e)
{
dbc.Open();
cmd = new SqlCommand("select Max(GroupId) from ContactGroups", dbc.cnn);
b = Int32.TryParse(cmd.ExecuteScalar().ToString(), out intMax);
intMax += 1;

ShowData();
}

private void ShowData()
{
dgvGroups.DataSource = dbc.GetGroups();

}


private void btnAdd_Click(object sender, EventArgs e)
{
cmd = new SqlCommand("insert into ContactGroups values (" + intMax.ToString() + ", '" + txtContactGroup.Text + "')", dbc.cnn);
cmd.ExecuteNonQuery();
MessageBox.Show("Record Saved");
ShowData();
}


private void txtContactGroup_Validating(object sender, CancelEventArgs e)
{
if (txtContactGroup.Text == "")
{
errorProvider1.SetError(txtContactGroup, "Group Name cannot be blank");
txtContactGroup.Focus();
}
else
{
errorProvider1.SetError(txtContactGroup, "");
}
}

Obviously creating an sql statement for passing to SqlCommand is not the right approach. But let me keep it simple and short and not end up in feature creep. I will take up the better approach in a later blog.

frmContact
























Now let's come to the child table data insertion.

using System.Data.SqlClient;

DbConnect dbc = new DbConnect();
SqlCommand cmd;
Boolean b = false;
int intMax = 0;

private void frmContacts_Load(object sender, EventArgs e)
{
dbc.Open();
cboGroup.DataSource = dbc.GetGroups();
cboGroup.DisplayMember = "GroupName";
cboGroup.ValueMember = "GroupId";
}


private void btnSave_Click(object sender, EventArgs e)
{
dbc.Open();
cmd = new SqlCommand("select Max(ContactId) from Contacts", dbc.cnn);
b = Int32.TryParse(cmd.ExecuteScalar().ToString(), out intMax);
intMax += 1;

cmd = new SqlCommand("insert into Contacts values (" + intMax.ToString() + ", " + cboGroup.SelectedValue.ToString() + ", '" + txtContact.Text + "', '" + txtPhone.Text + "', '" + txtEmail.Text + "')", dbc.cnn);
cmd.ExecuteNonQuery();
MessageBox.Show("Record Saved");
}


VB .Net Code

'DbConnect - Class Module

Imports System.Data.SqlClient
Public Class DbConnect
Public cnn As SqlConnection

Public Sub Open()
cnn = New SqlConnection("Integrated Security=sspi;Initial Catalog=ContactManagement")
cnn.Open()
End Sub

Public Function GetGroups() As DataTable
Open()
Dim da As New SqlDataAdapter("select * from ContactGroups", cnn)
Dim ds As New DataSet
da.Fill(ds, "Grp")
Return ds.Tables("Grp")
End Function

Public Function GetContacts() As DataTable
Open()
Dim strSql As String
strSql = "select G.GroupName, C.ContactName, C.Phone, C.Email " strSql += "from ContactGroups G, Contacts C where G.GroupId = C.GroupId"
Dim da As New SqlDataAdapter(strSql, cnn)
Dim ds As New DataSet
da.Fill(ds, "Grp")
Return ds.Tables("Grp")
End Function

End Class

'frmMain

Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
Close()
End Sub

Private Sub GroupsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupsToolStripMenuItem.Click
Dim fg As New frmGroups
fg.MdiParent = Me fg.Show()
End Sub

Private Sub ContactsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ContactsToolStripMenuItem.Click
Dim fc As New frmContacts
fc.MdiParent = Me fc.Show()
End Sub


'frmContactGroups

Imports System.Data.SqlClient

Public Class frmGroups

Dim dbc As New DbConnect
Dim cmd As SqlCommand
Dim intMax As Integer
Dim b As Boolean
Dim be As Boolean = False
Private Sub frmGroups_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load dbc.Open()
IncrementId()
FillGroups()
End Sub

Private Sub IncrementId()
cmd = New SqlCommand("select Max(GroupId) from ContactGroups", dbc.cnn)
b = Int32.TryParse(cmd.ExecuteScalar().ToString(), intMax)
intMax += 1
End Sub

Private Sub FillGroups()
lstGroups.DataSource = dbc.GetGroups
lstGroups.DisplayMember = "GroupName"
End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
If be = True Then IncrementId()
End If
cmd = New SqlCommand("insert into ContactGroups values (" + intMax.ToString + ", '" + txtGroup.Text + "')", dbc.cnn)
cmd.ExecuteNonQuery()
MessageBox.Show("Record Saved")
FillGroups()
BlankControls()
be = True
End Sub

Private Sub BlankControls()
txtGroup.Text = "" txtGroup.Focus()
End Sub
End Class


'frmContacts
Imports System.Data.SqlClient

Public Class frmContacts

Dim dbc As New DbConnect
Dim cmd As SqlCommand
Dim intMax As Integer
Dim b As Boolean
Dim be As Boolean = False

Private Sub frmContacts_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
FillGroupsCombo()
IncrementId()
FillContactsGrid()
End Sub

Private Sub FillGroupsCombo()
cboGroups.DataSource = dbc.GetGroups
cboGroups.DisplayMember = "GroupName"
cboGroups.ValueMember = "GroupId"
End Sub

Private Sub IncrementId()
cmd = New SqlCommand("select Max(ContactId) from Contacts", dbc.cnn)
b = Int32.TryParse(cmd.ExecuteScalar().ToString(), intMax)
intMax += 1
End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
If be = True Then IncrementId()
End If
cmd = New SqlCommand("insert into Contacts values (" + intMax.ToString() + ", " + cboGroups.SelectedValue.ToString + ", '" + txtContact.Text + "', '" + txtPhone.Text + "', '" + txtEmail.Text + "')", dbc.cnn)
cmd.ExecuteNonQuery()
MessageBox.Show("Record Saved")
FillContactsGrid()
be = True
End Sub

Private Sub FillContactsGrid()
DataGridView1.DataSource = dbc.GetContacts
End Sub

Related Blog
Contact Management System VB .Net Windows Generated Code

Oracle Connectivity from C# Windows Forms

Inserting Values from Front End

System.Data.OleDb namespace contains classes required for interacting with any RDBMS including Oracle.

OleDbConnection class is used for establishing a live session between front end and back end. In this example we are connecting to Oracle with scott as username, tiger as password and hostname is Shalvin.

using System.Data.OleDb;

OleDbConnection cnn;
OleDbCommand cmd;
private void Form1_Load(object sender, EventArgs e)
{
cnn = new OleDbConnection("provider=msdaora.1;user id=scott;password=tiger;Data Source=Cargomar");
cnn.Open();
}
private void btnInsert_Click(object sender, EventArgs e)
{
cmd = new OleDbCommand("insert into Categories values (5, 'Mouse', 'Computer Mouse')", cnn);
cmd.ExecuteNonQuery();
MessageBox.Show("Record Saved");
}

Filling TextBox based on Selection from ComboBox

A common programming task is obtaining the details of a record based on a selection. Here Initially I am filling a combo box with Category Names. Based on a selection from Combo Box the the details of the Category will be displayed on the text boxes.










using System.Data.OleDb;

OleDbConnection cnn;
OleDbDataAdapter da;
DataSet ds = new DataSet();

bool b = false;

OleDbCommand cmd;
OleDbDataReader dr;

private void Form1_Load(object sender, EventArgs e)
{
cnn = new OleDbConnection("Provider=msdaora;Data Source=Cargomar;user id=scott;password=tiger");

cnn.Open();
da = new OleDbDataAdapter("select * from Categories", cnn);
da.Fill(ds, "Cat");
cboCategoryName.DataSource = ds.Tables["Cat"];
cboCategoryName.DisplayMember = "CategoryName";
cboCategoryName.ValueMember = "CategoryId";
b = true;
}

private void cboCategoryName_SelectedIndexChanged(object sender, EventArgs e)
{
if (b)
{
cmd = new OleDbCommand("select * from Categories where categoryId = " + cboCategoryName.SelectedValue, cnn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
txtCategoryId.Text = dr["CategoryId"].ToString();
txtDescription.Text = dr["Description"].ToString();
}
}

Deleting Record
private void btnDelete_Click(object sender, EventArgs e)
{
cmd = new OleDbCommand("delete from Categories where CategoryId = " + cboCategoryName.SelectedValue, cnn);
cmd.ExecuteReader();
MessageBox.Show("Record Deleted");
}

Editing Record

private void btnEdit_Click(object sender, EventArgs e)
{
txtCategoryId.Enabled = false;
cmd = new OleDbCommand("Update Categories set Description = '" + txtDescription.Text + "' where CategoryId = " + cboCategoryName.SelectedValue, cnn);
cmd.ExecuteNonQuery();
MessageBox.Show("Record Edited successfully");
}

VB.Net

Imports System.Data.SqlClient

Dim cnn As SqlConnection
Dim da As SqlDataAdapter
Dim ds As New DataSet

Dim b As Boolean = False
Dim cmd As SqlCommand
Dim dr As SqlDataReader

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cnn = New SqlConnection("Integrated Security=sspi;Initial Catalog=Northwind")
cnn.Open()
da = New SqlDataAdapter("select * from Categories", cnn)
da.Fill(ds, "Cat")
cboCategories.DataSource = ds.Tables("Cat")
cboCategories.DisplayMember = "CategoryName"


cboCategories.ValueMember = "CategoryId"

b = True
End Sub

Private Sub cboCategories_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboCategories.SelectedIndexChanged
If b = True Then

Dim strSql As String
strSql = "select * from Categories where CategoryId = " + cboCategories.SelectedValue.ToString()
cmd = New SqlCommand(strSql, cnn)
dr = cmd.ExecuteReader
While (dr.Read())
txtCategoryId.Text = dr("CategoryId")
txtDescription.Text = dr("description")
End While
dr.Close()
End If

End Sub