Complete CRUD Operation in Asp.Net C# With SQL Server Step by Step
Summary
TLDRThis 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
👨💻 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.
🗃️ 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.
🖥️ 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.
🔄 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.
📊 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.
Mindmap
Keywords
💡CRUD Operation
💡ASP.NET
💡C#
💡SQL Server
💡Visual Studio
💡GridView
💡TextBox
💡DropDownList
💡Button
💡SQL Injection
💡Connection String
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
Ver Más Videos Relacionados
Asp.net sql server database connection tutorial
🔥📱 Flutter x Firebase CRUD Masterclass • Create / Read / Update / Delete
Dream Report: Acquiring Data from SQL Server
Generate C# Model from existing Microsoft SQL Server Database
Connect a PostgreSQL database to a Spring Boot Application Tutorial
Introdução a bancos de dados SQLite com Python e módulo sqlite3
5.0 / 5 (0 votes)