Complete CRUD Operation in Asp.Net C# With SQL Server Step by Step

Summary

This tutorial guide walks viewers through creating a complete CRUD (Create, Read, Update, Delete) operation in ASP.NET using C# with SQL Server. Starting with setting up a new project, designing the webpage, and creating databases and tables, it progresses to connecting SQL Server with Visual Studio and coding for CRUD functionalities. The video demonstrates inserting records into a database, updating them, and retrieving data to display in a grid view, ensuring a hands-on understanding of database interactions in web development.

Takeaways

• 😀 The tutorial is focused on creating a CRUD (Create, Read, Update, Delete) operation in ASP.NET with C# and SQL Server.
• 🛠️ The process starts with creating a new project in Visual Studio and designing the webpage for the CRUD operations.
• 📝 The tutorial covers creating a database and table in SQL Server, including setting up the columns and data types for the 'student info' table.
• 🔗 It explains how to connect SQL Server to Visual Studio, which is essential for database operations within an ASP.NET application.
• 📑 The script details the steps to design the user interface with HTML elements like div, table, label, text box, combo box, and button.
• 🖱️ The video demonstrates adding event handlers for the 'Insert' button to perform data insertion into the SQL Server database.
• 🔍 The 'Load Records' method is introduced to fetch and display records from the database in a GridView control.
• 🛑 The tutorial includes implementing 'Update' and 'Delete' operations with corresponding button click events and SQL command executions.
• 🔄 The script also covers how to retrieve and populate data into text boxes and combo boxes based on selected student IDs.
• 🗑️ A confirmation message is added before performing delete operations to prevent accidental data loss.
• 🎉 The tutorial concludes with successfully demonstrating the CRUD operations and encourages viewers to subscribe for more content.

Q & A

What is the main topic of the tutorial video?

The main topic of the tutorial video is to demonstrate how to perform a complete CRUD (Create, Read, Update, Delete) operation in ASP.NET using C# and SQL Server.

What are the three main steps mentioned in the video for setting up a project in ASP.NET?

The three main steps mentioned are creating a new project, designing the webpage, and creating databases and tables.

How does one create a new project in Visual Studio according to the video?

To create a new project in Visual Studio, one should click on the file menu, select 'New Web Site', choose 'Visual C#', select 'ASP.NET Web Site', set the location to save the website, and provide a project name.

What is the purpose of the GridView control in the context of this tutorial?

The GridView control is used to display the inserted records from the SQL Server database table in the ASP.NET webpage.

What is the name of the database created in the video?

The name of the database created in the video is 'programming db'.

What are the data types of the columns in the 'student info' table?

The data types of the columns in the 'student info' table are Integer for 'student id', NVarChar(50) for 'student name', NVarChar(50) for 'address', and NVarChar(50) for 'contact'.

How is the connection to SQL Server established in the video?

The connection to SQL Server is established by adding a connection in the Server Explorer of Visual Studio, providing the server name, authentication details, and selecting the database 'programmingdb'.

What is the purpose of the 'SqlCommand' object in the video?

The 'SqlCommand' object is used to execute SQL commands, such as inserting records into the database.

How does the video demonstrate updating a record in the database?

The video demonstrates updating a record by using an UPDATE SQL command within a button click event, setting the values for the fields based on user input from the webpage controls.

What is the method used in the video to display records in the GridView instantly after an insert operation?

The method used to display records instantly is by calling a 'load records' method that fetches data from the database and binds it to the GridView control.

How does the video handle the deletion of records?

The video handles the deletion of records by using a DELETE SQL command within a button click event, specifying the record to delete based on the 'student id' from a textbox.

What additional feature is added to the delete button in the video to prevent accidental deletions?

A confirmation message box is added to the delete button using the 'confirm' JavaScript function, which prompts the user to confirm their intention to delete before executing the operation.

Outlines

00:00

👨‍💻 Introduction to CRUD Operations in ASP.NET

This paragraph introduces the tutorial on performing CRUD (Create, Read, Update, Delete) operations in ASP.NET using C# and SQL Server. The presenter guides viewers through setting up a new ASP.NET project in Visual Studio, creating a project structure, and designing the webpage layout. The focus is on creating a student management form with various controls such as labels, text boxes, combo boxes, and buttons to facilitate CRUD operations. The tutorial emphasizes styling elements and configuring the interface before diving into the coding process.

05:00

🗃️ Creating and Connecting to SQL Database

This section discusses the creation of a new SQL database called 'programmingdb' and the setup of a table named 'student_info_tab' to store student data. The table includes columns for student ID, name, address, age, and contact details. The paragraph also covers how to connect SQL Server to Visual Studio using the Server Explorer, setting up SQL connections in the ASP.NET project, and preparing SQL commands to insert records into the database. Emphasis is placed on proper data types and initializing the connection string for database interactions.

10:02

🖥️ Inserting and Displaying Records in GridView

In this paragraph, the tutorial demonstrates how to insert records into the SQL database and display them in a GridView on the ASP.NET webpage. It covers coding the button click event to execute SQL commands that insert student details from text boxes into the database. The presenter addresses common issues such as ensuring the SQL connection is open before executing queries and troubleshooting syntax errors in SQL commands. The section concludes by showing how to load records into the GridView after insertion to immediately reflect the changes on the webpage.

15:03

🔄 Updating and Deleting Records

This segment focuses on updating and deleting records in the SQL database through the ASP.NET interface. The tutorial explains how to set up buttons for updating and deleting records, and how to write SQL commands for each operation. It includes a step-by-step guide to modifying the student information in the database and reflecting these changes in the GridView. Additionally, the presenter introduces a method to confirm deletion actions through a client-side script, ensuring that users are prompted before permanently removing records from the database.

20:04

📊 Fetching and Displaying Specific Records

In the final part, the tutorial covers fetching specific records from the SQL database based on the student ID entered in a text box and displaying the results in the corresponding text boxes and combo boxes on the webpage. It explains how to use SQL data readers to retrieve data and bind it to the form controls. The paragraph also highlights the process of updating and deleting records with confirmation prompts, ensuring the application handles CRUD operations efficiently. The tutorial concludes with a summary of the operations performed and a call to action for viewers to subscribe to the channel.

Keywords

💡CRUD Operation

CRUD stands for Create, Read, Update, and Delete, which are the four basic functions of persistent storage. In the context of the video, CRUD operations are the primary focus, demonstrating how to implement these functionalities in an ASP.NET web application using C# and SQL Server. The script describes the process of creating a project, designing the user interface, and coding the backend logic to perform CRUD operations on a database.

💡ASP.NET

ASP.NET is a web application framework developed by Microsoft that allows developers to build dynamic web sites, applications, and services. In the video, ASP.NET is used as the platform to create a web application that performs CRUD operations. The script mentions creating a new ASP.NET web site project and using ASP.NET features such as GridView to display data.

💡C#

C# is a programming language developed by Microsoft as part of its .NET initiative. It is one of the main languages used in the video to write the code for the web application. The script includes examples of C# code for connecting to a SQL Server database, executing SQL commands, and handling events in the ASP.NET application.

💡SQL Server

SQL Server is a relational database management system developed by Microsoft. It is used in the video to store and manage the data for the web application. The script describes how to create a new database and table in SQL Server, as well as how to connect the database to the ASP.NET application.

💡Visual Studio

Visual Studio is an integrated development environment (IDE) from Microsoft that is used to develop applications for various platforms. In the video, Visual Studio is the tool used to create the ASP.NET project, design the user interface, and write the C# code for the application. The script mentions opening a new website project in Visual Studio and using its features like Server Explorer.

💡GridView

GridView is a server control in ASP.NET that is used to display and manipulate data in a tabular format. In the video, GridView is utilized to display the records inserted into the SQL Server database. The script describes adding a GridView control to the ASP.NET page and using it to show the results of CRUD operations.

💡TextBox

TextBox is a standard control in ASP.NET used to capture user input. In the script, TextBox controls are added to the web page to allow users to enter data such as student ID, name, address, and contact information, which will be used in CRUD operations.

💡DropDownList

DropDownList is another ASP.NET control that provides a list of options from which the user can select. In the video, a DropDownList is used to select an address, one of the fields in the student information form, providing a user-friendly way to input data into the application.

💡Button

Button is a control in ASP.NET used to initiate actions or events. The script describes adding buttons with different functionalities, such as 'Insert', 'Update', 'Delete', and 'Get', which correspond to the CRUD operations that can be performed on the database.

💡SQL Injection

SQL Injection is a security vulnerability where an attacker can insert or 'inject' malicious SQL statements into an input field for execution. Although not explicitly mentioned in the script, the concept is relevant when discussing database operations. The video demonstrates the importance of safely handling user input to prevent such vulnerabilities.

💡Connection String

A connection string is a sequence of characters that specifies information needed to connect to a database. In the video, the script describes setting up a connection string to establish a link between the ASP.NET application and the SQL Server database, which is essential for performing CRUD operations.

Highlights

Introduction to complete CRUD operations in ASP.NET with C# and SQL Server.

Steps overview: creating a new project, designing the workpiece, creating a database and table, and connecting SQL Server with Visual Studio.

Detailed walkthrough of creating a new ASP.NET web project in Visual Studio, including project setup and initial configurations.

Designing the form: Adding a table with labels, text boxes, combo box, and buttons to perform CRUD operations.

Setting up the SQL Server database, creating the database 'ProgrammingDB' and the table 'student_info_tab' with appropriate columns.

Connecting Visual Studio to SQL Server: Adding a SQL connection string and writing SQL commands to insert records into the database.

Implementing the insert operation: Writing C# code to insert student records into the SQL Server database.

Fixing common errors such as connection issues and SQL syntax errors during the insertion process.

Adding a GridView to display inserted records from the database in real-time within the ASP.NET application.

Implementing the update operation: Writing C# code to update student records based on student ID.

Implementing the delete operation: Writing C# code to delete student records and confirming the deletion with a client-side prompt.

Enhancing the user experience by adding a confirmation prompt before deleting records.

Implementing the search operation: Writing C# code to retrieve and display specific student records from the database.

Final testing: Inserting, updating, and deleting records, and confirming that all CRUD operations are functioning as intended.

Conclusion and user guidance: Final remarks on the tutorial and encouraging viewers to subscribe for more content.

Transcripts

hello viewers welcome to programming gig

i'm here with a new tutorial that is

complete crude operation in asp.net

c-sharp with sql server

[Music]

complete crude operation in asp.net

c-sharp with sql alpha

follow the system first one is create

new project second one is design the

workpiece

and third one is create ddbs and table

fourth one is connect sql server with

visual studio

it can be 10 or 15 or somewhat

for 510 is the right code too far from

crude operation in

asp.net with sql okay

so start visual studio

[Music]

yes it's starting click on file menu

open new

website yes select visual c sharp in the

left fan

and select asp.net web from site

select the location where you want to

save this website

web from site okay this is the location

project c

shop confolder and food backslash

and then for the project name my project

name is crude tutorial

click on ok

yes my project is created this is

default text in asp.net

so now i'll remove the default text and

i'll design

this page to perform complete crude

operation in c shop with asp.net okay

first of all deep

and in this tip i'm just adding div

style font size

font size is large xlrs because the this

div will use as title of the page

okay that is student info manage form

now make it align it center

align equal to center okay

now add a break then

i will add a table table row

six rows and column three columns

okay now

i'll add

[Music]

label and text box combo box and button

control to perform crude operation okay

so student id is to the name address ace

and contact

[Music]

okay now

go to the toolbox

and in the toolbox add a

text box control just drag and drop it

in the paste default aspect space

and since the font size

font size medium yes

resize this text box and just copy this

text box

for a student name and addresses

drop down list to add address

and ages attack 3 and contact is

textbook 4.

finally for tab control to

add one more row in the table

and button text is a insert

for color white

[Music]

font size on both true and font size is

medium

okay now the

back colors in the back oh this is

border color back color select back

color

yes the color is purple now this is

looking nice

now go to the button click event

add a grid view this grid view will be

used to display the inserted records in

the sql server database table

in asp.net okay

this is the grid view i just added

now so that this is the button click

even i will write code

to insert records and that's what i

wanted to be stable

so let's go to the sql server

this is sql server 2014

sa password transfer authentication

password database

new data create new database my database

name is programming db okay click on ok

yes the database is created you can see

the programming db is created

and there is no table yet now i'm going

to create a table

column name is student id data type is

integer

and the student name data type is and

worker

50 and then address agent contact okay

so insert more column address and worker

is ages plot and contact

contact is n worker plot means it will

allow

decimal set primary key in the student

id okay

name the table table name is student

info underscore tab

click on ok yes my table is created

replace this table folder and the server

explorer is the table is created

go to the server explorer in

your visual studio and right click add

connection

to connect the server server name

authentication username essay and

password just go through education

password database name is programmingdb

click on ok

yes let's add it now this is the s17 for

underscore tab you can see in the

uh server explorer so to connect to the

sql server first of all

uh add sql connection skill connection

con equal to new sql connection

mysql connection is that to use sql

construction you should add namespaces

in system.data.sql

client mysql connection is right click

property yes this is the connection

listing just double click to select copy

and paste in the double quotation

okay you can see id is essay and

okay now it's called command com equal

to

new sql command mysql command is to

insert records in the specifications

table

so insert into student info underscore

this is the table name in the sql server

values values restaurant id name

addresses below

i'll get from the control what i added

in the default.spx page like

textbooks12 and comma

drop down list okay so i just

put the syntax only now

student id next zero

first column is student id from text box

one

textbox one so and

student name tag goes to address drop

down list

add the drop down list item list item i

just uh

put three item usa canada and uk

okay the drop down list item is added

edge is staggering theory and contact is

tiger store so

just put the control name

a student id for

student id is integer type so it should

be converted to integer so integer dot

first type of sound or text

and then the student name is and worker

so just for

textbooks2.txt yes

and then address addresses

uh drop down list one dot selected value

this is also an worker so

just under download standard selected

value and is ages plot type so you

should convert it to double double dot

for

text titles three dot text

and finally contact contact is

textbooks4.txt

[Music]

okay it's done now initialize the

connection string

comma and con

okay now just uh to

first of all let's uh check the records

and the field yes see that age is plot

type

so i have converted to double

yes you can see double dot for text

three dot text

okay now

[Music]

com mysql command com dot execute

non-query

finally it will display the message

after completing the execution operation

successfully the message will be

successfully inserted to display the

messages in as

pop-up just to write this code discrete

manager dot register

starter script this this dot get type

script and then alert alert message will

be

successfully inserted it will display

only the message is successfully

inserted and then comma two

okay it's done

now the different viewing browser right

click and view in browser the

default.spx space

okay student id 101 student name john

usa

h25 contact oh it's displaying an

executive

requires an open and available

connection the connection system

currently

is closed so i have to open the

connection

just for con dot open and after

completing the execution operation just

for con dot close

[Music]

okay now reload space

and photo record once again

insert login failed for

sa let's check the connection string

these are these assets okay but password

is look like a star so you should

forward your original password

reload this page again

and correct syntax near the keyword into

let's check the command sql command

insert for t and check the

whole command from beginning to end oh

yes

there is a comma extra comma just remove

this comma

yes now

this page again and insert the record

once again

insert yes successfully inserted but

this result is not displaying in the

grid view instantly so

to display the result in the grid view

instantly

i should add a method but the records

is inserted successfully in the specific

database but not displaying the

grid view so to display the records in

the grid view

i'm gonna add a method

so void method name is load

record

[Music]

okay method name is load record now

place the connection listing out of

events so that

it can be used in any event

in this page so this is out of event

connection connect sql connection is in

out of event so

now just put command no need to write

sql connection

again in this method so my command is to

select all the records in the grid view

skill command comma continues in sql

command select star from

a student in for underscore tab the

table name

initialize the connection string con

yes this is the account connection

listing i can used

so now it's the date adapter d equal to

new sql data adapter

com

then a data table

data table dt equal to

new data table

[Music]

to add data table you should add numbers

using system.data

okay now d dot field dd

this is the actual data adapter and it

is predictable

finally you beyond the data source equal

to dt

and then we'll be under data bind

[Music]

okay now i'll call this method in the

uh click event all over all button click

event and

[Music]

as well as phase load event okay

to uh add method in the page dot event

you should add

not is false back if not is false back

then load records

yes the record is displaying in the grid

view perfectly

now i'm gonna insert one more record and

the result will be displayed in the grid

instantly

yes it's successfully inserted and

displaying the grid view instantly

okay

now add one more button just copy this

button

and paste yes this button controller

used to update

records based on student id text is

button text is update

and go to the events uh

property event remove this previous

event and

double click on click

yes the event is created now just copy

the

insert button code

yes now this is a

insert command now i'm gonna put update

command

just put update is 24 tab and then set

[Music]

set student name instant name is second

column

is to the name equal to for the syntax

single quotation double question and

then plus sign

okay name from techniques to dot text

and then address just copy

and paste yes this is for address

address equal to

drop down list one dot selected value

[Music]

okay then is aj's

float database data type is plot so you

should convert it to double

double dot first tag three dot tags

[Music]

okay now contact

contact equal to textbook four dot text

contact that from technical dot text

higher you should add ir student

id equal to its integer type so integer

dot first techniques one dot text i mean

if you

put id one zero one it will update one

zero one's

name address is and contact

okay initialize the connection string

con

okay now it's done com dot execute

non-query okay contact close

okay now finally message change the

message

message is successfully updated after

executing the operation it will display

message successfully updated

okay save and go to the browser

reload the space yes yes yes

the button is displaying now i'm gonna

add update

101 records now name is

john so i will update it

to john simon

and addresses usa i just put canada

is 25 to 30 and contact

contact uh two

successfully but you can see that the

student name addresses and contact is

updated successfully

okay now replace this

table yes you can see john simon cannon

that

the record is updated in the

copy specific button and face to far

from

delete operation the property

change the text text is

delete okay and go to the event

this is button to click events and now

i'm gonna create a button three click

event yes

this is the new button click event okay

uh just copy this code and

first in button 3 click event just uh

change this code this is this button

control i'll use to

perform delete operations so statement

is delete

student in four tab only higher

yes higher student id equal to textbox

one dot text

initializing connection string on ok uh

execution

click under close when message message

will be successfully

deleted okay it's done and just calling

the method load records

save this uh application okay

now i'm gonna delete one zero one record

just click on delete

yes successfully you can see in the data

again in the grid view the records is

also removed from the grid view

okay now

add in the button control to perform

charts operation

so change the text of this button

button is charts volume of the event it

will be the new click event

and the text will be charts

text is charts okay

now now we're gonna click yes

[Music]

okay now

you can only use the load record method

and if you use the load record method

it's displaying all the records

and now i'm going to show how to add

higher close and display the specific

records uh from the switzerland database

to the

grid view so my statement

is select star prom is turned in for tab

higher

student id just copy from the previous

button click event

student id equal to integer dot first

technics one dot text

yes it will display that records which

says student id is

based on the textbox wonder text

okay save and reload this page

first of all i'm inserting one more

records yes successfully inserted

now one more records

sarah yes successfully started now

uh charts 103 yes it's displaying only

103

foot one through two displaying 102

records only

okay now i'm gonna add one more

button this button controller used to

get data from exclusive database to text

box and combo box from xbox one

okay remove the button click event

yes now

[Music]

copy this sql command

and paste it in the button five click

event

yes select a start from a student in

front of a score tab it's not radical to

just type

1. text okay now open the connection

conduct

open and then sql dated after data

reader

sql data reader r equal to com dot

execute data

okay then file r dot read then

it should for just index tables two dot

text equal to

r dot get value that value index uh

name index is one dot twisting let's

check

the index in the switcher to be stable

you can see student id is index zero

student name index one is

address index two is index three and

contact is index four

so i will put index based on the column

take this to index one

and uh contour name is textures2

now just copy

and paste

address is uh from drop down list so

four to drop down list one dot selected

value equal to

address index is two get value two dot

twisting and age is still

three and index is three

and contact is four and index is four

okay now it's done save

all and then reload this page

[Music]

okay put one zero two just click on get

yes it's displaying all the records

based on product student id

i just update and

now if 101 yes john usa 25 e402

testing i can add 23 okay

it's working fine

[Music]

delete successfully deleted delete is

confidential if you delete the records

you cannot get back this record so i'm

gonna add confirm masses to execute

delayed operation so

go to the tools and just write some

codes

this is on client click event just

put on client click equal to

return confirm

then first break single quotation

are you sure to directly display my

access are you sure to delete

okay it's done just write this

uh code in the on client click then it

will display the message before

performing the executing operation

okay now i reload this page

and now just uh

photo102 and click on delete

so we'll get yes delete are you sure to

deal with this will match that issue to

delete if you

click cancel it will display nothing and

it will do nothing but if you click

ok then it will delete successfully

[Music]

yes it's working fine

now i'm gonna just update this energy

update updated successfully and the name

also updated

are you sure to delete cancel no yeah

okay

yes successfully deleted thanks for

watching this tutorial if you like this

video please subscribe my channel

[Music]

you