C# Asp.Net-Insert Update Delete and View With Sql Server Database

hi or welcome to dotnet mo in this video tutorial we will learn how to create c-sharp asp.net web application with operations like insert update delete and view so let get started this is my Visual Studio 2013 in order to create asp.net web application go to file then new then project then select visual studio 2012 under web then select asp.net web application the name your application here then select the location where you want to save this application then click on ok so here we have a brand new asp.net web application in this application we will deal with contact details of persons like mobile number name and address so we will do CR UD operations on this actually CI u V stands for create retrieve update and delete now let me do the DB part first after that we get back to this application this is my SQL Server management studio first of all we have to create a database for that right click on databases the new database then name your database here I will name it as SP c ru b SP code then click on OK under databases you can see the newly created database SP code here now I want to create a new tab for condign for then right click on tables then tab first column will be contact ID that that type will be int then name work 50 then mobile 450 then address workout 250 then I want to make this contact ID s primary key column for that I click on it and set primary key then I will set identity column as contact ID so that we don't want to insert values into this contact ID equal SQL Server will take care of that it will start from 1 and incremented by 1 upon new recording session in order to save this table you can use the shortcut control s or you can click on the Save button here the name your table here contact click on OK in order to see the newly created table expand these tables then click on refresh here so that's it now back to visual studio now we need to create a verb form for corn dank for that right click on projects then add webform then name your form here I will name it as contact click on OK so here we have our contact SPX page now let's design the page for that first of all I will use a hidden field in order to save the contact ID in this form I use HF as a prefix for hidden fees so H of contact ID then I will use a table to design the remaining part so starting with table tag so we have two columns inside this table and each row contains a label text box where so there will be three rows for name mobile and address you so here we have three rows for our phase first label will be named corresponding text box will be txt name I use txt as a prefix for text boxes then next row mobile corresponding text box will be txt mobile then address corresponding text box txt address we want to make this address text box as multi-line for that we will add the text mode as multi-line now let me add our four buttons save update and delete for that I will copy the slow and pacing here pay pacing labels and text box with three buttons for that habit drag buttons from toe box like this we need two more buttons one for save one for delete one for clear BTN say VTN delete and BTN clear finally we will add two more rows in order to show the success message and error message for that I will copy this row and paste in here two times and with placing buttons with label so that let me drag a label from Kobach's I will name it as it will be a success message text will be empty for now copy reliable and pasting here and changing the ID as LBL error message finally we need a good view before then I will add a line break here let me drag grid view from tool box I will name it as GV contact inside that we need three columns for name mobile and address starting with columns tag inside that I will add 3 st boundaries you data field must be equal to the column name that is named mobile and address and header text is shown in the column at the part so first column will be name and the text name finally I would like to add item template for the link button to select arrow SP template field item template inside that I will drag Eldin button from the toolbox ID will be LNK view text will be view I want to add contact ID as command arguments for this link button for that command argument equal to inside single quotes we will pass our contact ID inside eval function let me switch to design mode here we want to disable auto-generated column in skewed view and we need to make this success message color as green and red for error message also in order to avoid order the rated column we can set it using the tag auto the red column equals false and we need to make this success message as green for color equal green and that of our message will be red that's it let me switch to design mode here now it is looking fine now now I am going to work with this player button here actually this for clearing form elements we can do that using jQuery or JavaScript itself but for simplicity I will be using server-side c-sharp code for that go to properties of pattern we go to events we can double click here or we can double click on this button here itself so here we have the Clear button click events outside the events I will define a function for resetting the congos clear and first of all I will reset the hidden field value to empty string then txt name dot text equals txt mobile dot text equals txt address dot text equals empty string and I want to set the Save button text as safe because it will change during the update operation finally I would like to disable the delete button and we will call this function inside the Clear button event inside the page load we need to disable this delete button for that let me copy this line and patients here and we want to execute this only when it is not a postback so then we can use an if block if not east post back because lines inside page-load will execute when there is a post back there will be a server-side push back for each server side events so we don't want to execute this all those time we want to execute this only for the first time or when user requests this space inside the clear function I forgot to reset error message and success message for that lvl success message dot text equals lvl error message dot text equals empty string so this is how the application looks like our grid view is there we can see that because we don't have any row in the grid beam now before getting into the further development of this application we have to create store procedure for create update DD and view for that let me Flip into management studio in order to create a new stored procedure click on this new query here before start writing the store procedure make sure that we have selected proper database from this drop down so first of all I am going to create a stored procedure for create or update for that create procedure procedure name will be contact create or update then we need to write the parameters for this stopa seizure contact ID in as meetings and so we will be using same store procedure for create and update based on this contact ID value we will decide whether it is for create or update if it is zero we will add newer code if it is not zero we will update the row with given contact ID if contact ID equal to zero begin and you and we will update the you in order to execute this query you can press f5 or you can click on this execute button here so we have a creator this stopa CJ contact create or update successfully in order to see the newly created store procedure expand this program ability then under store procedure you can see that if not you can click on this refresh button here now I want to create a new stored procedure for contact view or for that I will start like this great cross contact you all and we have no parameter for this stored procedure has begin and select star from contact click on this execute button here so we have created this to procedure now we want to create a new store procedure for contact delete by ID contact delete by ID and we have a parameter contact ID as inch as vision and delete from corn that were contact ID equal to given contact ID let me execute this we need one more stop procedure contact view by ID contact view by ID contact ID essence as begin and select star from contact the contact ID equal to p1 Eddie that's it let me ask you this so we have created store procedure for contact create or update contact delete by ID con lack V or con Lac view by 80 now back to the studio now we are going to do operations like insert update delete and view in this asp.net give you first of all I will start with save or insert for that double click on it so that we create a button click even in this code behind before that we have to create an SQL connection SQL connection and we have to use the namespace system dot that dot SQL client s for equal to new SQL connections inside that we need to pass our connection string you now back to the save button click even so suppose we check whether the SQL connection is open or not if it is closed we will open scale corn dot stay equals connection stay for that we have to use the namespace system got data dot closed if it is closed we will open it here using the function open and then we will create an object of SQL comments in order to call the stored procedure securely command SQL CMD equals new SQL commands inside this we will pass over stored procedure contact add or edit let me copy D stropas is your name and pasting here second parameter will be our SQL connection object SQL corn and we will specify command type as storko CJ now we need to pass record parameter values into this two procedure for that SQL CMD dot parameters dot add the value for this store procedure conduct create or update we have four parameters so I will copy this line and place it here three more times first parameter will be contact ID in case of contact ID we will pass value from our hidden field H of contact ID dot value if it is empty we will pass zero otherwise we will pass corresponding contact ID after converting it into in 32 then second a meter will be named the parameter mobile finally address and we will pass remaining store procedure value directly from our form element txt name dot text dot trim let me copy this here and replacing case T name with TC mobile txt address dot test or train sorry we need one more equal sign here in order to execute this procedure we will call the function execute non query finally we will close our scale connection and in order to clear the form elements we need for player function finally we will show our success message for that you will check whether the hidden field for conduct ID is empty or not if it is empty if you show success message as saved successfully else means show updated successfully now let me run this application for that click on the start button here now let me end some test data Smith address click on say now so it is saved successfully let me check our tab will contact for that right click on contact table then click on select mm rows so here we have our newly inserted over here next we are going to show the insert row in our asp.net give you for that I will create a function fill grid-view first of all we will check whether scale function is open or not if it is close we will open the connection for that I will copy this these two lines from here and pasting here now I will create an object of SQL data adapter SQL data adapter as SQL da equals new SQL data adapter inside that we will pass the store procedure contact view or let me copy the Stokoe caesar name from here and pasting here and sec parameter will be your conscionable corn now we will said SQL data add up the comment type SQL da dot select command dot command type equals command type dot stored procedure and I will create an object of data table as DTB l equals new data table for this store paucity we don't have any parameter so i will call the data adapter dot fill function inside that we will pass our data table inside this data table we have all those that we are inserted into contact tab finally I will close your skill conscient now we will set this data table into our grid view Givi conduct dot data source equals d TBL and finally we will call the data bind function for this kid view dot dot tab binds and i will call the fill get view function inside our page load and i will call the same function after saving error code or updating our code now let me run this application that's it now we have the infected Smith o in our get view if user click on this view link button here we will show the corresponding conduct informations in these form elements and user can update or delete deeper code so that I will create click even for this Bing button back to wishes to you let me add on click even for the link button all click equal to Ln K on click let me copy this in the code-behind we will write an even for this even protector voyaged then even name then object sender even arcs e inside this event we will retrieve the contact information based on the contact ID and we can get that contact ID from reading button command argument so that I will declare in contact ID equals sender as Center means our link button as link button dot command argument so this video our contact ID finally I want to convert that into change for that we use the function convert to in 32 and we will pass this contact ID into the stored procedure contact by ID and we will display corresponding contact information in our form for that I will copy these lines from fill grid View function and changing accordingly here we have opened the SQL function and we have created SQL data adapter instead of this procedure we need contact view by ID then after that we have to pass parameter value for this contact ID so that we can do something like this scale da dot select command or parameters dot add width value then parameter name is contact ID pasting here and corresponding parameter value will be this variable and then we have created a table object et BL and then result is feed into this data table and finally we have closed the SQL connection and now we need to show these values into our form first of all I will set the hidden field value with our contact ID then we have txt name dot txt equals d TB l dot rows rows first row then inside this we need to pass the column name so in this case it would be name to string I will copy this so and pasting here two times for mobile and address txt mobile and here it would be mobile txt address here column name is address and I will change the Save button text as update and I will enable the delete button enabled equals true let me run this application now let me click on this view button so here we have the details of Smith let me change Smith to Smith one click on update so it is updated now but there is a small issue guys instead of updated successfully it is showing same successfully let me check Save button click event here you can see we call player function and it will reset the hidden field for contact ID so it will be always empty string in order to solve this problem I will declare a string here string corn back ID then we will save this hidden field value in that and we will check this contact ID here now it should be fine let me run this application click on this view button and changing smith 1 back to Smith click on update so it is working fine now now let's look what we can do with this delete button here back to visual studio switch to design mode I want to add click even for this button for that double click on it so here we have the delete button click events first of all we will open the SQL connection for that I will copy this code from here and we want to create an object of SQL command Kuehl CMD equals new SQL CMD inside that we need to pass our stored procedure name in this case it will be corn back delete by ID it has only one parameter contact ID let me copy the store procedure name and paste in here second parameter will be SQL connection and game set SQL see in the command type as to procedure then let me pass the parameter value parameters dot add which value then parameter name let me copy from here placing here then we will pass the contact ID from hidden field H of contact ID dot value in order to execute this SQL commands we call the function execute non curry finally we call clear function and we call the function feel good view finally we will set the success message as deleted successfully before calling a function we will close the SQL connection click on got close let me run this application now I'm going to add new contact information for Sarah mobile number then address click on save so here we can see the euro for SAR click on view let me update Sarah - Sarah 1 then click on update so here you can see it is updated successfully in order to delete this click on View button here then click on delete here so it is deleted successfully it is gone from our get view all so that's it guys in this video we have discussed how to do operations like insert update delete and view in c-sharp asp.net web application you can download this project with SQL scripts from the link given below in video description thank you very much for watching this video tutorial all the way to the end please be subscribe to my channel dotnet mob for hours and videos like this don't forget to comment your feedbacks in the comment box below please like and share this video with your friends and colleagues so that they can benefit from this have a nice day bye

Channel: CodAffection

Views: 472,484

Rating: 4.8459153 out of 5

Keywords: C# Asp.Net Insert Update Delete and View, Asp.net Web Application For CRUD Operations, Asp.net- Insert Update Delete and View With Sql Server DataBase, asp.net grid view Insert Update Delete and View, Save Update Delete and View in Asp.net, with stored procedure, c# asp.net crud, asp.net tutorial for beginners, asp.net gridview, gridview crud operation, CodAffection

Id: CtDE9gTwmyo

Length: 36min 54sec (2214 seconds)

Published: Mon Apr 17 2017