Data base Connectivity using C Sharp
Create table in SQL Server
create database gla123;
use gla123;
create table student(
id int identity primary key,
rollno int,
name varchar(100),
marks int
);
insert into students(rollno,name,marks)
values(101,'Ajay',67);
insert into students(rollno,name,marks)
values(102,'Vijay',87);
insert into students(rollno,name,marks)
values(111,'Rohit',91);
Select * from students;
// Programs in C# for database connectivity
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace WindowsFormsApp2ADO1
{
public partial class Form12 : Form
{
public Form12()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//// Select * from student
//
string connstr = @"Data Source=DESKTOP-PE7EI8M\SQLEXPRESS;Initial
Catalog=mydb1;Integrated Security=True";
String query = "select * from student";
SqlDataAdapter sqd = new SqlDataAdapter(query, connstr);
DataSet dataset = new DataSet();
sqd.Fill(dataset, "student");
dataGridView1.DataSource = dataset.Tables["student"];
private void button2_Click(object sender, EventArgs e)
{
// Insert Row
string connstr = @"Data Source=DESKTOP-PE7EI8M\SQLEXPRESS;Initial
Catalog=mydb1;Integrated Security=True";
string insert_query = "";
insert_query += "insert into student(rollno,name,marks) values(";
insert_query += textBox1.Text + ",";
insert_query += "'" + textBox2.Text + "'," + textBox3.Text + ")";
textBox4.Text = insert_query;
SqlConnection con = new SqlConnection(connstr);
SqlCommand cm = new SqlCommand(insert_query, con);
con.Open();
int n = cm.ExecuteNonQuery();
if (n == 1)
MessageBox.Show("Row Inserted Successfully");
else
MessageBox.Show("Problem in insert query");
}
private void button3_Click(object sender, EventArgs e)
{
// Delete button
try
{
string connstr = @"Data Source=DESKTOP-PE7EI8M\SQLEXPRESS;Initial
Catalog=mydb1;Integrated Security=True";
string delete_query = "delete from student where rollno = ";
delete_query += textBox1.Text;
textBox4.Text = delete_query;
// connection
SqlConnection con = new SqlConnection(connstr);
con.Open();
SqlCommand cmd = new SqlCommand(delete_query, con);
int row_deleted = cmd.ExecuteNonQuery();
if (row_deleted == 0)
MessageBox.Show("No row deleted ");
else
MessageBox.Show(row_deleted + " Rows deleted successuflly");
}
catch (Exception e1) { MessageBox.Show(e1.ToString()); }
}
private void button4_Click(object sender, EventArgs e)
{
// Search button
try
{
string connstr = @"Data Source=DESKTOP-PE7EI8M\SQLEXPRESS;Initial
Catalog=mydb1;Integrated Security=True";
string search_query = "select * from student where rollno = ";
search_query += textBox1.Text;
SqlConnection con = new SqlConnection(connstr);
con.Open();
SqlCommand cmd = new SqlCommand(search_query, con);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
textBox1.Text = reader["rollno"].ToString();
textBox2.Text = reader["name"].ToString();
textBox3.Text = reader["marks"].ToString();
}
}
catch (Exception e1) { MessageBox.Show(e1.ToString()); }
}
private void button5_Click(object sender, EventArgs e)
{
// Update button code
try
{
string connstr = "Data Source=DESKTOP-PE7EI8M\\SQLEXPRESS;Initial
Catalog=mydb1;Integrated Security=True;";
string update_query = "update student set rollno = ";
update_query += textBox1.Text;
update_query += ", name = '" + textBox2.Text + "' , ";
update_query += " marks = " + textBox3.Text;
update_query += " where rollno = " + textBox1.Text;
textBox4.Text = update_query;a
SqlConnection con = new SqlConnection(connstr);
con.Open();
SqlCommand cmd = new SqlCommand(update_query, con);
int n = cmd.ExecuteNonQuery();
if (n >= 1)
MessageBox.Show("Row Updated");
} catch(Exception e1) { MessageBox.Show(e1.ToString()); }
private void Form12_Load(object sender, EventArgs e)
{
}
}
}
