In this article I would like to share with you a sample program that shows how to create a CRUD application in C# and Microsoft SQL Server I called this program Address Book that save, update, delete and view user records using data view grid. I hope you will find my work useful I work hard in this sample program this is my first time to create my own database application using C# and Microsoft SQL Server 2008 R2. I am using Visual Studio 2012 in this application. Thank you.
Add me at Facebook my address is jakerpomperada@gmail.com and jakerpomperada@yahoo.com.
My email address are the following jakerpomperada@gmail.com and jakerpomperada@yahoo.com.
My mobile number here in the Philippines is 09173084360.
Sample Program Output and Procedures To Follow
Program Listing
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace addressbook
{
public partial class Form1 : Form
{
SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=user;Integrated Security=true;");
SqlCommand cmd;
SqlDataAdapter adapt;
int ID = 0;
public Form1()
{
InitializeComponent();
DisplayData();
}
private void DisplayData()
{
con.Open();
DataTable dt = new DataTable();
adapt = new SqlDataAdapter("SELECT * FROM tbl_users", con);
adapt.Fill(dt);
dataGridView1.DataSource = dt;
con.Close();
}
private void ClearData()
{
txt_lastname.Text = "";
txt_firstname.Text = "";
txt_home.Text = "";
txt_mobile.Text = "";
txt_telephone.Text = "";
txt_email.Text = "";
ID = 0;
}
private void btn_quit_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Do you want to exit?", "My Application",
MessageBoxButtons.YesNo, MessageBoxIcon.Question)
== DialogResult.Yes)
{
Application.Exit();
}
else
{
txt_lastname.Focus();
}
}
private void btn_about_Click(object sender, EventArgs e)
{
MessageBox.Show("Written By: Mr. Jake R. Pomperada, MAED-IT 2016",
"About This Program", MessageBoxButtons.OK,
MessageBoxIcon.Information);
txt_lastname.Focus();
}
private void btn_save_Click(object sender, EventArgs e)
{
if (txt_lastname.Text != "" && txt_firstname.Text != ""
&& txt_home.Text != "" && txt_mobile.Text != ""
&& txt_telephone.Text != "" && txt_email.Text != "")
{
cmd = new SqlCommand("INSERT INTO tbl_users(lastname,firstname,address,mobile,telephone,email) VALUES(@lastname,@firstname,@address,@mobile,@telephone,@email)", con);
con.Open();
cmd.Parameters.AddWithValue("@lastname", txt_lastname.Text.ToUpper());
cmd.Parameters.AddWithValue("@firstname", txt_firstname.Text.ToUpper());
cmd.Parameters.AddWithValue("@address", txt_home.Text.ToUpper());
cmd.Parameters.AddWithValue("@mobile", txt_mobile.Text.ToUpper());
cmd.Parameters.AddWithValue("@telephone", txt_telephone.Text.ToUpper());
cmd.Parameters.AddWithValue("@email", txt_email.Text.ToLower());
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Record Inserted Successfully");
DisplayData();
ClearData();
}
else
{
MessageBox.Show("Please Provide Details!");
}
}
private void btn_update_Click(object sender, EventArgs e)
{
if (txt_lastname.Text != "" && txt_firstname.Text != ""
&& txt_home.Text != "" && txt_mobile.Text != ""
&& txt_telephone.Text != "" && txt_email.Text != "")
{
cmd = new SqlCommand("UPDATE tbl_users SET lastname=@lastname, firstname=@firstname,address=@address,mobile=@mobile,telephone=@telephone,email=@email WHERE id=@id", con);
con.Open();
cmd.Parameters.AddWithValue("@id", ID);
cmd.Parameters.AddWithValue("@lastname", txt_lastname.Text.ToUpper());
cmd.Parameters.AddWithValue("@firstname", txt_firstname.Text.ToUpper());
cmd.Parameters.AddWithValue("@address", txt_home.Text.ToUpper());
cmd.Parameters.AddWithValue("@mobile", txt_mobile.Text.ToUpper());
cmd.Parameters.AddWithValue("@telephone", txt_telephone.Text.ToUpper());
cmd.Parameters.AddWithValue("@email", txt_email.Text.ToLower());
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Record Updated Successfully");
DisplayData();
ClearData();
}
else
{
MessageBox.Show("Please Provide Details!");
}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
ID = Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString());
txt_lastname.Text = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();
txt_firstname.Text = dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString();
txt_home.Text = dataGridView1.Rows[e.RowIndex].Cells[3].Value.ToString();
txt_mobile.Text = dataGridView1.Rows[e.RowIndex].Cells[4].Value.ToString();
txt_telephone.Text = dataGridView1.Rows[e.RowIndex].Cells[5].Value.ToString();
txt_email.Text = dataGridView1.Rows[e.RowIndex].Cells[6].Value.ToString();
}
private void btn_new_Click(object sender, EventArgs e)
{
txt_lastname.Text = "";
txt_firstname.Text = "";
txt_home.Text = "";
txt_mobile.Text = "";
txt_telephone.Text = "";
txt_email.Text = "";
txt_lastname.Focus();
}
private void btn_delete_Click(object sender, EventArgs e)
{
if (ID != 0)
{
cmd = new SqlCommand("DELETE tbl_users WHERE id=@id", con);
con.Open();
cmd.Parameters.AddWithValue("@id", ID);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Record Deleted Successfully!");
DisplayData();
ClearData();
}
else
{
MessageBox.Show("Please Select Record to Delete");
}
}
}
}
SQL Dumb File
USE [user]
GO
/****** Object: Table [dbo].[tbl_users] Script Date: 11/05/2016 15:49:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_users](
[id] [int] IDENTITY(1,1) NOT NULL,
[lastname] [nchar](100) NULL,
[firstname] [nchar](100) NULL,
[address] [nchar](200) NULL,
[mobile] [nchar](15) NULL,
[telephone] [nchar](15) NULL,
[email] [nchar](100) NULL,
CONSTRAINT [PK_tbl_users] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tbl_users] ON
INSERT [dbo].[tbl_users] ([id], [lastname], [firstname], [address], [mobile], [telephone], [email]) VALUES (4, N'POMPERADA ', N'JACOB ', N'ERORECO SUBD, BACOLOD CITY ', N'09173084360 ', N'4335675 ', N'jacob_pomperada@gmail.com ')
INSERT [dbo].[tbl_users] ([id], [lastname], [firstname], [address], [mobile], [telephone], [email]) VALUES (5, N'BRYANT ', N'KOBE ', N'LOS ANGELES CALIFORNIA, USA ', N'1234567890 ', N'4563423 ', N'kobe@la_lakers.com ')
INSERT [dbo].[tbl_users] ([id], [lastname], [firstname], [address], [mobile], [telephone], [email]) VALUES (6, N'POMPERADA ', N'ALLIE ', N'BACOLOD CITY ', N'0912345623 ', N'4335675 ', N'allie_pomperada@yahoo.com.ph ')
INSERT [dbo].[tbl_users] ([id], [lastname], [firstname], [address], [mobile], [telephone], [email]) VALUES (7, N'POMPERADA ', N'JULIANNA RAE ', N'BACOLOD CITY, NEGROS OCCIDENTAL ', N'121212121212 ', N'4335081 ', N'iya_pomperada@gmail.com ')
INSERT [dbo].[tbl_users] ([id], [lastname], [firstname], [address], [mobile], [telephone], [email]) VALUES (8, N'GATES ', N'BILL ', N'ONE MICROSOFT WAY, USA ', N'1212123434343 ', N'34343434 ', N'bill_gates@microsoft.com ')
INSERT [dbo].[tbl_users] ([id], [lastname], [firstname], [address], [mobile], [telephone], [email]) VALUES (10, N'POMPERADA ', N'JAKE ', N'ALIJIS BACOLOD CITY ', N'09173084360 ', N'4335675 ', N'jakerpomperada@yahoo.com ')
SET IDENTITY_INSERT [dbo].[tbl_users] OFF
No comments:
Post a Comment