Database Connectivity using asp.net(Delete Record and Search)

 Database Connectivity using asp.net(Delete Record  and Search)

A) Create Database and Table (Create Table Code ):-  ( Click Here )

USE [student_info]

GO

 

/****** Object:  Table [dbo].[StudentTable]    Script Date: 10/17/2020 04:51:07 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

 

CREATE TABLE [dbo].[StudentTable](

      [StudentId] [int] IDENTITY(1,1) NOT NULL,

      [NameofStudent] [varchar](50) NOT NULL,

      [CourseName] [varchar](50) NOT NULL,

      [Address] [varchar](50) NOT NULL,

      [ContactNumber] [varchar](50) NOT NULL,

 CONSTRAINT [PK_UserTable] PRIMARY KEY CLUSTERED

(

      [StudentId] 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 ANSI_PADDING OFF

GO

B) Create New Project in Visual Studio  and  Design Form :-

C) Create Connection ( Click Here )

D) Add App_Code folder and class  ( Click Here )

E) Class Code ( DatabaseConnectivityClass.cs)

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data.SqlClient; // Add Namespace

using System.Configuration;

using System.Data; // Add Namespace

 

/// <summary>

/// Summary description for DatabaseConnectivityClass

/// </summary>

public class DatabaseConnectivityClass

{

   

    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MSQL"].ToString());

 

    public int ForQuery(String sqlcmd)

    {

        con.Open();

        SqlCommand cmd = new SqlCommand(sqlcmd, con);

        int x = cmd.ExecuteNonQuery();

        con.Close();

        return x;

    }

     public DataSet DataSetReturn(String strcmd)

    {

        con.Open();

        // OleDbDataAdapter da = new OleDbDataAdapter(strcmd, con);

        SqlDataAdapter da = new SqlDataAdapter(strcmd, con);

        DataSet ds = new DataSet();

        da.Fill(ds);

        con.Close();

        return ds;

    }

 }

F) Search Button Code:- 

protected void Button1_Click(object sender, EventArgs e)

    {

     

        DatabaseConnectivityClass obj = new DatabaseConnectivityClass();

        DataSet ds = new DataSet();

        ds = obj.DataSetReturn("Select *from StudentTable where StudentId=" + TextBox5.Text);

 

        if (ds.Tables[0].Rows.Count >= 1)

        {

            Label1.Text = "Record Found";

            TextBox1.Text = ds.Tables[0].Rows[0]["NameofStudent"].ToString();

            TextBox2.Text = ds.Tables[0].Rows[0]["CourseName"].ToString();

            TextBox3.Text = ds.Tables[0].Rows[0]["Address"].ToString();

            TextBox4.Text = ds.Tables[0].Rows[0]["ContactNumber"].ToString();

            Button2.Enabled = true;

        }

        else

        {

            Label1.Text = "Record Not Found";

            TextBox1.Text = "";

            TextBox2.Text = "";

            TextBox3.Text = "";

            TextBox4.Text = "";

            Button2.Enabled = false;

 

        }

    }

G) Delete Button Code:-

protected void Button2_Click(object sender, EventArgs e)

    {

        DatabaseConnectivityClass obj = new DatabaseConnectivityClass();

        int x = obj.ForQuery("delete from StudentTable where StudentId=" + TextBox5.Text);

        Response.Write("One Record Deleted Successfully"+ x);

        Button2.Enabled = false;

        TextBox1.Text = "";

        TextBox2.Text = "";

        TextBox3.Text = "";

        TextBox4.Text = "";

    }

H) Page Load code:-

 protected void Page_Load(object sender, EventArgs e)

    {

        Button2.Enabled = false;

    }