This article shows you how to insert, update, delete,search by name ,Roll no,marks and display data in Listbox
Introduction
Here I am using local databases.
Note: You need to include this assembly reference.
using MySql.Data.MySqlClient;
SQL Connection:
First, you need to establish Local database connection for databases
namespace SQL_Database
{
public partial class Form1 : Form
{// Delcare Sql class objects
SqlConnection cn;
SqlDataAdapter da;
DataSet ds;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
String con_str = @"Data Source=(local);Initial Catalog=BZU;Integrated Security=True";
cn = new SqlConnection(con_str);
try
{
cn.Open(); // open database connection
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
Insert Data
private void button1_Click(object sender, EventArgs e)
{
String query = "Insert into Student(RollNo, Name, Marks) Values(@RollNo, @Name, @Marks)";
SqlCommand cmd = new SqlCommand(query, cn);
cmd.Parameters.Add("@RollNo", SqlDbType.VarChar, 15);
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@Marks", SqlDbType.Int);
cmd.Parameters["@RollNo"].Value = textBox1.Text;
cmd.Parameters["@Name"].Value = textBox2.Text;
cmd.Parameters["@Marks"].Value = textBox3.Text;
try
{
int i = cmd.ExecuteNonQuery();
if (i == 0)
MessageBox.Show("No Record inserted");
if (i == 1)
MessageBox.Show("One Record inserted into database Successfully");
ClearFields();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
private void ClearFields()
{
textBox1.Clear();
textBox2.Clear();
textBox3.Clear();
}
Update Data
private void button2_Click(object sender, EventArgs e)
{
string query = "Update Student Set Name=@Name,Marks=@Marks where RollNo=@RollNo ";
SqlCommand cmd = new SqlCommand(query, cn);
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@Marks", SqlDbType.Int);
cmd.Parameters.Add("@RollNo", SqlDbType.VarChar, 15);
cmd.Parameters["@Name"].Value = textBox2.Text;
cmd.Parameters["@Marks"].Value = int.Parse(textBox3.Text);
cmd.Parameters["@RollNo"].Value = textBox1.Text;
try
{
int i = cmd.ExecuteNonQuery();
if (i == 0)
MessageBox.Show("Record not found");
if (i == 1)
MessageBox.Show("One Record updated successfully");
ClearFields();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
Delete Data
private void button3_Click(object sender, EventArgs e)
{
String query = "Delete from Student Where RollNo=@RollNo";
SqlCommand cmd = new SqlCommand(query, cn);
cmd.Parameters.Add("@RollNo", SqlDbType.VarChar, 10);
cmd.Parameters["@RollNo"].Value = textBox1.Text;
try
{
int i = cmd.ExecuteNonQuery();
if (i == 0)
MessageBox.Show("Record not found");
if (i == 1)
MessageBox.Show("One Record Deleted Successfully");
ClearFields();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
Display Data
private void button6_Click(object sender, EventArgs e)
{
try
{
String query = "Select * from Student";
da = new SqlDataAdapter(query, cn);
ds = new DataSet();
da.Fill(ds, "Students");
dgStudent.DataSource = ds;
dgStudent.DataMember = "Students";
dgStudent.Refresh();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
Search By Name
private void button5_Click(object sender, EventArgs e)
{
String query = "Select * from Student where Name = @Name";
SqlCommand cmd = new SqlCommand(query, cn);
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50);
cmd.Parameters["@Name"].Value = textBox2.Text;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
try
{
DataSet ds = new DataSet();
da.Fill(ds, "Students");
dgStudent.DataSource = ds;
dgStudent.DataMember = "Students";
dgStudent.Refresh();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
Search By RollNo
private void button4_Click(object sender, EventArgs e)
{
String query = "Select * from Student where RollNo = @RollNo";
SqlCommand cmd = new SqlCommand(query, cn);
cmd.Parameters.Add("@RollNo", SqlDbType.VarChar, 10);
cmd.Parameters["@RollNo"].Value = textBox1.Text;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
try
{
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count == 0)
MessageBox.Show("Record not found");
else
{
textBox2.Text = dt.Rows[0]["Name"].ToString();
textBox3.Text = dt.Rows[0]["Marks"].ToString();
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
Search By Marks
private void button7_Click(object sender, EventArgs e)
{
String query = "Select * from Student where Marks = @Marks";
SqlCommand cmd = new SqlCommand(query, cn);
cmd.Parameters.Add("@Marks", SqlDbType.Int);
cmd.Parameters["@Marks"].Value = Convert.ToInt32(textBox3.Text);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
try
{
DataSet ds = new DataSet();
da.Fill(ds, "Students");
dgStudent.DataSource = ds;
dgStudent.DataMember = "Students";
dgStudent.Refresh();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
