Connectivity ADO.NET with Connected Mode (for begainer Student...)

This code for Connectivity with DataBase (Ado.NET with Sql Server ) in Connected Mode.........

//create  table  Customer  in Database which name is Test  ........................

use test

Create table Customer(SrNo int primary key,FirstName nvarchar(50),LastName nvarchar(50),Dob DateTime)

select * from Customer

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

//using this namespace  for Connectivity  with ADO.NET........................

using System.Data.SqlClient;

namespace ConnectivityWithSql

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

//Code For Save data in DataBase .......................

private void btnsave_Click(object sender, EventArgs e)

{

try

{

//create  object  of Connection Class..................

SqlConnection con = new SqlConnection();

//Set Connection String property of Connection object..................

con.ConnectionString = @"Data Source=KUSH-PC\KUSH;Initial Catalog=test;Integrated Security=True";

//Open Connection..................

con.Open();

//Create object of Command Class................

SqlCommand cmd = new SqlCommand();

//set Connection Property  of  Command object.............

cmd.Connection = con;

//Set Command type of command object

//1.StoredProcedure

//2.TableDirect

//3.Text   (By Default)

cmd.CommandType = CommandType.Text;

//Set Command text Property of command object.........

cmd.CommandText = "insert into Customer(SrNo,FirstName,LastName,Dob) values(" + Convert.ToInt32(txtcustomerid.Text) + ",'" + txtfname.Text + "','" + txtlname.Text + "','" +Convert.ToDateTime(txtdob.Text) + "')";

//Execute command by calling following method................

   1.ExecuteNonQuery()

       It  query using for  insert,delete,update command...........

 2.ExecuteScalar()

     It  query return a single value and insert all record...................(using select,insert command)

//   3.ExecuteReader()

//      It  query return one or more than one record....................................

cmd.ExecuteNonQuery();

MessageBox.Show("Data Saved");

TextBoxClear();

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

}

//Code For Serach data From DataBase with SrNo

private void btnsearch_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection();

con.ConnectionString = "Data Source=KUSH-PC\\KUSH;Initial Catalog=test;Integrated Security=True";

con.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "select * from Customer where SrNo='" + Convert.ToInt32(txtcustomerid.Text) + "'";

cmd.Connection = con;

SqlDataReader dr = cmd.ExecuteReader();

if (dr.Read())

{

txtfname.Text = dr["FirstName"].ToString();

txtlname.Text = dr[2].ToString();

txtdob.Text = dr.GetDateTime(3).ToShortDateString();

}

else

MessageBox.Show("Record not found", "No record", MessageBoxButtons.OK, MessageBoxIcon.Information);

con.Close();

}

//Code For Delete data From DataBase with SrNo

private void btndelete_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection();

con.ConnectionString = "Data Source=KUSH-PC\\KUSH;Initial Catalog=test;Integrated Security=True";

con.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "Delete from Customer where SrNo='" + Convert.ToInt32(txtcustomerid.Text) + "'";

cmd.Connection = con;

int i=cmd.ExecuteNonQuery();

if (i > 0)

{

MessageBox.Show("Data delete Successfully for SrNo" + txtcustomerid.Text);

TextBoxClear();

}

else

{

MessageBox.Show("Record not found", "No record", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

con.Close();

}

//Code For Update data From DataBase with SrNo

private void btnupdate_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection();

con.ConnectionString = "Data Source=KUSH-PC\\KUSH;Initial Catalog=test;Integrated Security=True";

con.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "Update Customer set  FirstName='" + txtfname.Text + "',LastName='" + txtlname.Text + "',Dob='" +Convert.ToDateTime(txtdob.Text) + "' where SrNo='"+Convert.ToInt32(txtcustomerid.Text)+"'";

cmd.Connection = con;

int i = cmd.ExecuteNonQuery();

if (i > 0)

{

MessageBox.Show("Data updated Successfully for SrNo" + txtcustomerid.Text);

TextBoxClear();

}

else

{

MessageBox.Show("Record not found", "No record", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

con.Close();

}

//Code For Searching First  Row From DataBase

private void btnfirst_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection();

con.ConnectionString = "Data Source=KUSH-PC\\KUSH;Initial Catalog=test;Integrated Security=True";

con.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "select * from Customer";

cmd.Connection = con;

SqlDataReader dr = cmd.ExecuteReader();

if (dr.HasRows)

{

dr.Read();

txtcustomerid.Text = dr.GetInt32(0).ToString();

txtfname.Text = dr["FirstName"].ToString();

txtlname.Text = dr.GetString(2);

txtdob.Text = dr.GetDateTime(3).ToShortDateString();

}

else

{

MessageBox.Show("Record not found", "No record", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

con.Close();

}

//Code For Searching Last  Row From DataBase

private void btnlast_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection();

con.ConnectionString = "Data Source=KUSH-PC\\KUSH;Initial Catalog=test;Integrated Security=True";

con.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "select * from Customer";

cmd.Connection = con;

SqlDataReader dr = cmd.ExecuteReader();

while(dr.Read())

{

txtcustomerid.Text = dr.GetInt32(0).ToString();

txtfname.Text = dr["FirstName"].ToString();

txtlname.Text = dr.GetString(2);

txtdob.Text = dr.GetDateTime(3).ToShortDateString();

}

con.Close();

}

//Code For Searching Previouse  Row From DataBase

private void btnprevious_Click(object sender, EventArgs e)

{

string a = txtcustomerid.Text;

SqlConnection con = new SqlConnection();

con.ConnectionString = "Data Source=KUSH-PC\\KUSH;Initial Catalog=test;Integrated Security=True";

con.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "select * from Customer";

cmd.Connection = con;

SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

if (a == dr[0].ToString())

{

return;

}

txtcustomerid.Text = dr.GetInt32(0).ToString();

txtfname.Text = dr["FirstName"].ToString();

txtlname.Text = dr.GetString(2);

txtdob.Text = dr.GetDateTime(3).ToShortDateString();

}

con.Close();

}

//Code For Searching Next  Row From DataBase

private void btnnext_Click(object sender, EventArgs e)

{

string a = (txtcustomerid.Text);

SqlConnection con = new SqlConnection();

con.ConnectionString = "Data Source=KUSH-PC\\KUSH;Initial Catalog=test;Integrated Security=True";

con.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "select * from Customer";

cmd.Connection = con;

SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

if (a == dr[0].ToString())

{

dr.Read();

txtcustomerid.Text = dr.GetInt32(0).ToString();

txtfname.Text = dr["FirstName"].ToString();

txtlname.Text = dr.GetString(2);

txtdob.Text = dr.GetDateTime(3).ToShortDateString();

return;

}

}

con.Close();

}

//Blank All TextBox

private void TextBoxClear()

{

// different method for blank  TextBox Text.......................

txtcustomerid.Text = "";

txtfname.Clear();

txtlname.Text = String.Empty;

txtdob.Text = "";

}

private void btnClear_Click(object sender, EventArgs e)

{

TextBoxClear();

}

private void btnExit_Click(object sender, EventArgs e)

{

this.Close();

}

}

}