CRUD operations in C# console app using SQL Server | INSERT | PART 1| C# | SQL

hello everyone welcome to the very first video of our channel in this video let's see how to insert data into a table in database using c sharp console application this insertion is a part of grid operations in credit operations this insertion comes under c creation we will see the remaining operations that is retrieval updation and deletion in future videos the creation of this app involves two steps first is to create a database and table in sql server management studio and then establish connection to the database from our visual studio program and then perform credit operations this is the order in which we are going to perform first we are going to establish connection and then in certain sorry insertion and then remaining all the operations and then we create a loop through this is the prop work we have to do the first thing is to create a db end table so let's head over to ssns once ssms is loaded i will connect to my server let's create a new database the query to create databases create database database name let's have it as codb that is credit operations db in short and execute the command so now our database is created next let's switch to that database if you see your databases changed so let's create a table so the command to create table is create table table name next comes the column name let's start with id let it be in and primary key let's make it as identity 1 comma 1 let me tell you what is identity identity is nothing but the auto increment of the id column and how do we increment it we start from 1 and keep on incremented by one by one i'll show you how it was done when we insert the data and make it as not null the next column shall be user name let it be 50 not null next let's have user age and let it be hint and not null so that's it this is the query for table creation let's execute this command so now it is created let's see what does the structure this is the command to see what is in the table let's execute and so this is a structure we have user id user name and then user age so now we have completed our first part and now let's go over to visual studio and create a console application always run visual studio in administrator mode so that we will have all the access right create a new project so now visual studio is loaded now let's go to console application click next let's name it as thread app click next now we have to select target framework i am selecting 5.0 create so now our application is successfully created so the first thing we need to do is create a connection to the server so for that let's open server explorer in server x explorer this was the old collection sorry connection let me remove it let's create a new connection here we need to write a server name my server name is by default dot so i am writing it here i will select my database codb is my database test connection succeeded click ok so we have a connection established here we will come to this later so let's start programming so in the prep work if we see what we need to do first we need to establish connection before for that we need to get connection string from database right so first let's create a variable called sql connection using which sorry this is a sql connection class using which we will connect to the database i am creating a variable called sql connection for this object class so it is an error because we haven't included it show potential fixes install system dot data dot sql client let's install that see we have system.data that is still length and the error is gone so now let's create a connection string string connection string is equal to let's add a connection string so that connection string we already have in our server explorer yeah when you go to the properties here the connection string is given just copy it completely and paste it inside the connection string so here what this means is data source is my server name my server is dot so that is here this is my database name so now what we are going to do is establish the connection before that let's create a try cache block because there are chances that exception might occur so when the exception occurs we can catch it and handle it gracefully instead of letting the application crash so that is the reason why we use try clash right catch block so this is the exception class and it is the object so let me console dot right line here i will show show my exception message so that's it so inside the stripe block let's try to establish the connection first so for that let's do sqlconnection object of this class is equal to new sql connection and the parameter for this sql connection is a connection stream so let's give a connection string here once this is done let's open the connection so sql connection dot open that is what we have to do this sql connection is the variable so the object connection dot open now our connection will be established so to confirm that let's write connection established successfully okay so let's run and see whether her connection is getting established successfully so if you see the connection is established successfully so let's close it and let's go further mode now we are going to do create path in grunt operations so let's do that so the first thing we have to do is write a query so let's write it string let's say insert query so what this insert query is nothing but a query that we write an sql but that we are going to write it in here and pass the object so we pass the variable from user okay so for that yeah before creating we need to get user name and user age if you remember from a database or for a table console so string username is equal to will be our output before that let's write console.writeline enter your name so that we will store a 10 here read line method so what we are going to do is console console.readline next we are going to get age so that will be in int so this user each is equal to before that let's create let's get give the command console dot right line enter your age so you will get the age what is the user name so in uh c sharp everything that we get from read line will be a string so we have when user enters their age for example 15 it will be treated as a string and after getting that we will convert into it so for that what we are going to do is in dot pause console dot read line so what this means is we get it using console.trade line and we convert it into it by using in dot pass method so now we have both our parameters for creating our ins for writing our insertion query so let's write our query so this is the query to insert into table insert into table name values before writing values we can give parameter also so that would be username comma user h values so here is the tricky part for the time being i remove this thing so first thing we are going to insert is a string right in a string whatever we do it will be like whatever we write it will be written as let me write insertion query here to make it clear insert into details user name comma user age values what would be this ring let's see my name is sri comma what is my age let's get let's say 10 for example so this is our values so so in sql we will use single quotes sorry i forgot that okay and so this is how our query that we write in visual studio will be internally turned into this query so before that so first we will have single quotes and then double quotes the variable and then again double quotes in single quotes so what next is if you notice here we will have comma so let me add comma here and then next thing that comes up comma and uh the double quotes starting for the next one what is the next thing that is coming is username right if you see here we have we don't have any codes so we can write it like exactly how we do usually so user age plus we will quote the double quote and then close this query and semicolon so that's it this is our query so if we look closely it is getting translated into insert into details username usage values this double quotes will be gone so what will be remaining is single quote username single quote comma user age and then we can execute this query so since now we have written the query now we have to create a class create an object of a class called sql command to execute the query so let me write sql command and giving it name as insert command which is equal to new sql command the parameters of this command are of the insert query comma what is the next parameter as a connection sql connection we have created right so that connection and then semicolon so we have created a command so now list here let's execute this command insert command dot execute non-query this execute non-query method is used to execute sql queries that doesn't return any data for example creation updation and dilation we cannot use this for retrieval because retrieval returns data right so we cannot use that so for a queries that doesn't return any data we use execute non-query so once this is successfully completed let's notify console.writeline data the successfully inserted into table okay i so now we have this okay so let's run and see first we have connection established successfully next enter your name for example yeah let's enter the name leonard age let's give 40 data is successfully inserted into table and it stops so let's go into database and see we got inserted so if you notice we didn't give our id but it is automatically created so this is what the first thing one comma one is here right it starts with one so our id is starting with one let's insert another record and see what happens now enter your name what is your name penny age 30 inserted so if you notice again we didn't give any id but we will have inserted penny and it is incremented as 1 2 like that so this is why we have used identity after doing all the database operations it is best practice to close the connection we have created so how do we do it as sql connection object let's use that sql connection dot close so this closes the connection we established in the beginning right we have created sql connection.open now we are closing this connection thank you for watching this video in next video let's see how to retrieve data and update data and deletion in future videos thank you

Channel: Sri Tharini

Views: 18,716

Keywords: CRUD operations, using C# and SQL, CRUD operations using C#, C# Tutorial, sql tutorial, crud c#, crud c# and sql, crud sql, crud C# sql, C# insert, C# update, C# delete, C# select, C# search, insert sql, update sql, delete sql, select sql, C# insert sql, C# update sql, C# delete sql, C# select sql, crud operation C# sql, sql and c# crud, C# and sql crud, #SQLServer, #CRUD, #c#, c#tutorial, #Howto, #programming, #sritharini, #stepbystep, #india, #study, #database

Id: 6hVp8NFcgdw

Length: 20min 23sec (1223 seconds)

Published: Sat Jul 17 2021