Example : All Integrated Codes (Save, Edit, Delete, Search )in C#.
Table Creation Code
CREATE TABLE UserRegistration
(
Slno INT IDENTITY(1,1) PRIMARY KEY,
UserName VARCHAR(100) NOT NULL,
Password VARCHAR(255) NOT NULL,
Address VARCHAR(250),
MobileNo VARCHAR(15),
Email VARCHAR(100),
DOB DATE,
Gender VARCHAR(10),
NonMatric BIT DEFAULT 0,
Matric BIT DEFAULT 0,
Intermediate BIT DEFAULT 0,
GraduationPostGraduation BIT DEFAULT 0,
Nationality VARCHAR(50),
Remarks VARCHAR(500)
);
NB: Here, IDENTITY(1,1) automatically creates and increment value by 1 in SLno field of this table in the SQL Server database 2014.
============================================================================================================
using System;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
//connectivity code
SqlConnection con = new SqlConnection(
// @"Data Source=Servername;Initial Catalog=DatabaseName;Integrated Security=True");
//@"Data Source=.\SQLEXPRESS;Initial Catalog=DatabaseName;Integrated Security=True"
@"Data Source=RKM;Initial Catalog=CSharpDB;Integrated Security=True");
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
//Connectivity Confirmation Message Display
try
{
// Open SQL Server connection
con.Open();
MessageBox.Show(
"Database Connected Successfully.",
"Connection",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(
"Connection Failed:\n" + ex.Message,
"Database Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
finally
{
// Close connection
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
TxtPass.PasswordChar = '*';
TxtCPass.PasswordChar = '*';
//TxtPass.UseSystemPasswordChar = true;
//TxtCPass.UseSystemPasswordChar = true;
CmbNationality.Items.Add("Select One");
CmbNationality.Items.Add("India");
CmbNationality.Items.Add("USA");
CmbNationality.Items.Add("SriLanka");
CmbNationality.Items.Add("Bhutan");
CmbNationality.Items.Add("Nepal");
CmbNationality.Items.Add("Other");
// No item selected initially
//CmbNationality.SelectedIndex = -1;
// First item selected initially
CmbNationality.SelectedIndex = 0;
DtpDob.Format = DateTimePickerFormat.Custom;
DtpDob.CustomFormat = "dd MMM yyyy";
Clear();
}
private void UrBtnExit_Click(object sender, EventArgs e)
{
//this.Close();
DialogResult result;
result = MessageBox.Show(
"Do you want to exit?",
"Exit Confirmation",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
Application.Exit();
}
}
private void Clear()
{
TxtSlno.Text = "";
TxtUserId.Clear(); //TxtUserId.Text = "";
TxtAddress.Clear();
TxtPass.Clear();
TxtCPass.Clear();
TxtMob.Text = "";
DtpDob.Value = DateTime.Today;
RdbMale.Checked = false;
RdbFemale.Checked = false;
RdbOther.Checked = false;
ChkNonMatric.Checked = false;
ChkMatric.Checked = false;
ChkItermediate.Checked = false;
ChkGradPostG.Checked = false;
// First item selected initially
CmbNationality.SelectedIndex = 0;
// No item selected initially
//CmbNationality.SelectedIndex = -1;
// to remove typed text
//CmbNationality.Text = "";
RtbRemarks.Text = "N/A";
//RtbRemarks.Text = ""; //Rich text box
//RtbRemarks.Clear();
//listBox1.ClearSelected();
//listBox1.Items.Clear();
//pictureBox1.Image = null;
//label1.Text = "";
TxtSlno.Focus();
}
private void UrBtnClear_Click(object sender, EventArgs e)
{
Clear();
}
private void UrBtnSave_Click(object sender, EventArgs e)
{
try
{
// -----------------------------------------
// Validate User Name
// -----------------------------------------
if (TxtUserId.Text.Trim() == "")
{
MessageBox.Show("Please enter User Name/Id.",
"Validation",
MessageBoxButtons.OK,
MessageBoxIcon.Warning);
TxtUserId.Focus();
return;
}
// -----------------------------------------
// Validate Password
// -----------------------------------------
if (TxtPass.Text.Trim() == "")
{
MessageBox.Show("Please enter Password.",
"Validation",
MessageBoxButtons.OK,
MessageBoxIcon.Warning);
TxtPass.Focus();
return;
}
// -----------------------------------------
// Validate Confirm Password
// -----------------------------------------
if (TxtCPass.Text.Trim() == "")
{
MessageBox.Show("Please enter Confirm Password.",
"Validation",
MessageBoxButtons.OK,
MessageBoxIcon.Warning);
TxtCPass.Focus();
return;
}
// -----------------------------------------
// Compare Password and Confirm Password
// -----------------------------------------
if (TxtPass.Text != TxtCPass.Text)
{
MessageBox.Show("Password and Confirm Password do not match.",
"Validation",
MessageBoxButtons.OK,
MessageBoxIcon.Warning);
TxtCPass.Focus();
return;
}
// -----------------------------------------
// Validate Mobile Number
// -----------------------------------------
if (TxtMob.Text.Trim() == "")
{
MessageBox.Show("Please enter Mobile Number.");
TxtMob.Focus();
return;
}
// -----------------------------------------
// Validate Gender
// -----------------------------------------
if (RdbMale.Checked == false &&
RdbFemale.Checked == false &&
RdbOther.Checked == false)
{
MessageBox.Show("Please select Gender.");
return;
}
// -----------------------------------------
// Validate Nationality
// -----------------------------------------
if (CmbNationality.SelectedIndex == -1)
{
MessageBox.Show("Please select Nationality.");
CmbNationality.Focus();
return;
}
// -----------------------------------------
// Get Gender
// -----------------------------------------
string gender = "";
if (RdbMale.Checked == true)
{
gender = "Male";
}
else if (RdbFemale.Checked == true)
{
gender = "Female";
}
else if (RdbOther.Checked == true)
{
gender = "Other";
}
// -----------------------------------------
// SQL INSERT command
// -----------------------------------------
string sql = @"INSERT INTO UserRegistration
(
UserName,
Password,
Address,
MobileNo,
Email,
DOB,
Gender,
NonMatric,
Matric,
Intermediate,
GraduationPostGraduation,
Nationality,
Remarks
)
VALUES
(
@UserName,
@Password,
@Address,
@MobileNo,
@Email,
@DOB,
@Gender,
@NonMatric,
@Matric,
@Intermediate,
@Graduation,
@Nationality,
@Remarks
) ";
// -----------------------------------------
// Create SQL Command
// -----------------------------------------
SqlCommand cmd = new SqlCommand(sql, con);
// -----------------------------------------
// Pass TextBox values
// -----------------------------------------
cmd.Parameters.AddWithValue("@UserName",
TxtUserId.Text.Trim());
cmd.Parameters.AddWithValue("@Password",
TxtPass.Text);
cmd.Parameters.AddWithValue("@Address",
TxtAddress.Text.Trim());
cmd.Parameters.AddWithValue("@MobileNo",
TxtMob.Text.Trim());
cmd.Parameters.AddWithValue("@Email",
TxtEmail.Text.Trim());
// -----------------------------------------
// Pass DateTimePicker value
// -----------------------------------------
cmd.Parameters.AddWithValue("@DOB",
DtpDob.Value.Date);
// -----------------------------------------
// Pass RadioButton value
// -----------------------------------------
cmd.Parameters.AddWithValue("@Gender",
gender);
// -----------------------------------------
// Pass CheckBox values
// true = 1
// false = 0
// -----------------------------------------
cmd.Parameters.AddWithValue("@NonMatric",
ChkNonMatric.Checked);
cmd.Parameters.AddWithValue("@Matric",
ChkMatric.Checked);
cmd.Parameters.AddWithValue("@Intermediate",
ChkItermediate.Checked);
cmd.Parameters.AddWithValue("@Graduation",
ChkGradPostG.Checked);
// -----------------------------------------
// Pass ComboBox value
// -----------------------------------------
cmd.Parameters.AddWithValue("@Nationality",
CmbNationality.Text);
// -----------------------------------------
// Pass RichTextBox value
// -----------------------------------------
cmd.Parameters.AddWithValue("@Remarks",
RtbRemarks.Text.Trim());
// -----------------------------------------
// Open database connection
// -----------------------------------------
if (con.State == ConnectionState.Closed)
{
con.Open();
}
// -----------------------------------------
// Execute INSERT command
// -----------------------------------------
int result = cmd.ExecuteNonQuery();
// -----------------------------------------
// Check whether record was saved
// -----------------------------------------
if (result > 0)
{
MessageBox.Show("Record Saved Successfully.",
"Save",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
Clear();
}
else
{
MessageBox.Show("Record could not be saved.");
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message,
"Database Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
finally
{
// Close database connection
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
private void UrBtnDelete_Click(object sender, EventArgs e)
{
// Check Serial Number
if (TxtSlno.Text.Trim() == "")
{
MessageBox.Show("Please enter Serial Number to delete.", "Validation",MessageBoxButtons.OK,MessageBoxIcon.Warning);
TxtSlno.Focus();
return;
}
// Check whether Serial Number is numeric
int slno;
if (!int.TryParse(TxtSlno.Text.Trim(), out slno))
{
MessageBox.Show(
"Please enter a valid Serial Number.",
"Validation",
MessageBoxButtons.OK,
MessageBoxIcon.Warning);
TxtSlno.Focus();
return;
}
// Ask for confirmation
DialogResult result = MessageBox.Show(
"Are you sure you want to delete this record?",
"Delete Confirmation",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
if (result == DialogResult.No)
{
return;
}
try
{
// SQL DELETE statement
string sql = "DELETE FROM UserRegistration WHERE Slno = @Slno";
SqlCommand cmd = new SqlCommand(sql, con);
// Pass Serial Number
cmd.Parameters.AddWithValue("@Slno", slno);
// Open connection
if (con.State == ConnectionState.Closed)
{
con.Open();
}
// Execute DELETE command
int rows = cmd.ExecuteNonQuery();
// Check whether record was deleted
if (rows > 0)
{
MessageBox.Show(
"Record Deleted Successfully.",
"Delete",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
// Clear all controls
Clear();
}
else
{
MessageBox.Show(
"Record not found.",
"Delete",
MessageBoxButtons.OK,
MessageBoxIcon.Warning);
TxtSlno.Focus();
}
}
catch (Exception ex)
{
MessageBox.Show(
"Error: " + ex.Message,
"Database Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
finally
{
// Close connection
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
private void UrBtnSearch_Click(object sender, EventArgs e)
{
// Check whether Serial Number is entered
if (TxtSlno.Text.Trim() == "")
{
MessageBox.Show(
"Please enter Serial Number to search.",
"Validation",
MessageBoxButtons.OK,
MessageBoxIcon.Warning);
TxtSlno.Focus();
return;
}
// Check whether Serial Number is numeric
int slno;
if (!int.TryParse(TxtSlno.Text.Trim(), out slno))
{
MessageBox.Show(
"Please enter a valid numeric Serial Number.",
"Validation",
MessageBoxButtons.OK,
MessageBoxIcon.Warning);
TxtSlno.Focus();
return;
}
try
{
// SQL Search Query
string sql = @"SELECT *
FROM UserRegistration
WHERE Slno = @Slno";
SqlCommand cmd = new SqlCommand(sql, con);
// Pass Serial Number to query
cmd.Parameters.AddWithValue("@Slno", slno);
// Open database connection
if (con.State == ConnectionState.Closed)
{
con.Open();
}
// Execute query
SqlDataReader dr = cmd.ExecuteReader();
// Check whether record exists
if (dr.Read())
{
// -----------------------------------
// TEXTBOX DATA
// -----------------------------------
TxtSlno.Text =
dr["Slno"].ToString();
TxtUserId.Text =
dr["UserName"].ToString();
TxtPass.Text =
dr["Password"].ToString();
// Since Confirm Password is not stored
// in the database, show same password
TxtCPass.Text =
dr["Password"].ToString();
TxtAddress.Text =
dr["Address"].ToString();
TxtMob.Text =
dr["MobileNo"].ToString();
TxtEmail.Text =
dr["Email"].ToString();
// -----------------------------------
// DATE OF BIRTH
// -----------------------------------
if (dr["DOB"] != DBNull.Value)
{
DtpDob.Value =
Convert.ToDateTime(dr["DOB"]);
}
// -----------------------------------
// GENDER - RADIO BUTTON
// -----------------------------------
string gender =
dr["Gender"].ToString();
RdbMale.Checked =
gender == "Male";
RdbFemale.Checked =
gender == "Female";
RdbOther.Checked =
gender == "Other";
// -----------------------------------
// QUALIFICATION - CHECKBOXES
// -----------------------------------
ChkNonMatric.Checked =
Convert.ToBoolean(dr["NonMatric"]);
ChkMatric.Checked =
Convert.ToBoolean(dr["Matric"]);
ChkItermediate.Checked =
Convert.ToBoolean(dr["Intermediate"]);
ChkGradPostG.Checked =
Convert.ToBoolean(
dr["GraduationPostGraduation"]);
// -----------------------------------
// NATIONALITY - COMBOBOX
// -----------------------------------
CmbNationality.Text =
dr["Nationality"].ToString();
// -----------------------------------
// REMARKS - RICHTEXTBOX
// -----------------------------------
RtbRemarks.Text =
dr["Remarks"].ToString();
MessageBox.Show(
"Record Found Successfully.",
"Search",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
else
{
MessageBox.Show(
"Record Not Found.",
"Search",
MessageBoxButtons.OK,
MessageBoxIcon.Warning);
TxtSlno.Focus();
}
// Close DataReader
dr.Close();
}
catch (Exception ex)
{
MessageBox.Show(
"Error: " + ex.Message,
"Database Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
finally
{
// Close database connection
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
private void UrBtnEdit_Click(object sender, EventArgs e)
{
// -----------------------------------------
// CHECK SERIAL NUMBER
// -----------------------------------------
if (TxtSlno.Text.Trim() == "")
{
MessageBox.Show(
"Please enter Serial Number.",
"Validation",
MessageBoxButtons.OK,
MessageBoxIcon.Warning);
TxtSlno.Focus();
return;
}
int slno;
if (!int.TryParse(TxtSlno.Text.Trim(), out slno))
{
MessageBox.Show(
"Please enter a valid Serial Number.",
"Validation",
MessageBoxButtons.OK,
MessageBoxIcon.Warning);
TxtSlno.Focus();
return;
}
// -----------------------------------------
// CHECK USER NAME
// -----------------------------------------
if (TxtUserId.Text.Trim() == "")
{
MessageBox.Show("Please enter User Name.");
TxtUserId.Focus();
return;
}
// -----------------------------------------
// CHECK PASSWORD
// -----------------------------------------
if (TxtPass.Text.Trim() == "")
{
MessageBox.Show("Please enter Password.");
TxtPass.Focus();
return;
}
// -----------------------------------------
// CHECK CONFIRM PASSWORD
// -----------------------------------------
if (TxtCPass.Text.Trim() == "")
{
MessageBox.Show("Please enter Confirm Password.");
TxtCPass.Focus();
return;
}
// -----------------------------------------
// COMPARE PASSWORDS
// -----------------------------------------
if (TxtPass.Text != TxtCPass.Text)
{
MessageBox.Show(
"Password and Confirm Password do not match.");
TxtCPass.Focus();
return;
}
// -----------------------------------------
// CHECK GENDER
// -----------------------------------------
if (RdbMale.Checked == false &&
RdbFemale.Checked == false &&
RdbOther.Checked == false)
{
MessageBox.Show("Please select Gender.");
return;
}
// -----------------------------------------
// CHECK NATIONALITY
// Assuming index 0 = "Select One"
// -----------------------------------------
if (CmbNationality.SelectedIndex <= 0)
{
MessageBox.Show("Please select Nationality.");
CmbNationality.Focus();
return;
}
// -----------------------------------------
// GET SELECTED GENDER
// -----------------------------------------
string gender = "";
if (RdbMale.Checked)
{
gender = "Male";
}
else if (RdbFemale.Checked)
{
gender = "Female";
}
else if (RdbOther.Checked)
{
gender = "Other";
}
// -----------------------------------------
// CONFIRM UPDATE
// -----------------------------------------
DialogResult result = MessageBox.Show(
"Are you sure you want to update this record?",
"Update Confirmation",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
if (result == DialogResult.No)
{
return;
}
try
{
// -----------------------------------------
// SQL UPDATE QUERY
// -----------------------------------------
string sql = @"UPDATE UserRegistration SET
UserName = @UserName,
Password = @Password,
Address = @Address,
MobileNo = @MobileNo,
Email = @Email,
DOB = @DOB,
Gender = @Gender,
NonMatric = @NonMatric,
Matric = @Matric,
Intermediate = @Intermediate,
GraduationPostGraduation = @Graduation,
Nationality = @Nationality,
Remarks = @Remarks
WHERE Slno = @Slno";
// -----------------------------------------
// CREATE SQL COMMAND
// -----------------------------------------
SqlCommand cmd = new SqlCommand(sql, con);
// -----------------------------------------
// PASS VALUES TO PARAMETERS
// -----------------------------------------
// Serial Number
cmd.Parameters.AddWithValue(
"@Slno",
slno);
// User Name
cmd.Parameters.AddWithValue(
"@UserName",
TxtUserId.Text.Trim());
// Password
cmd.Parameters.AddWithValue(
"@Password",
TxtPass.Text);
// Address
cmd.Parameters.AddWithValue(
"@Address",
TxtAddress.Text.Trim());
// Mobile Number
cmd.Parameters.AddWithValue(
"@MobileNo",
TxtMob.Text.Trim());
// Email
cmd.Parameters.AddWithValue(
"@Email",
TxtEmail.Text.Trim());
// Date of Birth
cmd.Parameters.AddWithValue(
"@DOB",
DtpDob.Value.Date);
// Gender
cmd.Parameters.AddWithValue(
"@Gender",
gender);
// Qualification CheckBoxes
cmd.Parameters.AddWithValue(
"@NonMatric",
ChkNonMatric.Checked);
cmd.Parameters.AddWithValue(
"@Matric",
ChkMatric.Checked);
cmd.Parameters.AddWithValue(
"@Intermediate",
ChkItermediate.Checked);
cmd.Parameters.AddWithValue(
"@Graduation",
ChkGradPostG.Checked);
// Nationality
cmd.Parameters.AddWithValue(
"@Nationality",
CmbNationality.Text);
// Remarks
cmd.Parameters.AddWithValue(
"@Remarks",
RtbRemarks.Text.Trim());
// -----------------------------------------
// OPEN CONNECTION
// -----------------------------------------
if (con.State == ConnectionState.Closed)
{
con.Open();
}
// -----------------------------------------
// EXECUTE UPDATE
// -----------------------------------------
int rows = cmd.ExecuteNonQuery();
// -----------------------------------------
// CHECK UPDATE RESULT
// -----------------------------------------
if (rows > 0)
{
MessageBox.Show(
"Record Updated Successfully.",
"Update",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
Clear();
}
else
{
MessageBox.Show(
"Record Not Found.",
"Update",
MessageBoxButtons.OK,
MessageBoxIcon.Warning);
TxtSlno.Focus();
}
}
catch (Exception ex)
{
MessageBox.Show(
"Error: " + ex.Message,
"Database Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
finally
{
// -----------------------------------------
// CLOSE CONNECTION
// -----------------------------------------
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
}
}
0 Comments