C# Insert Update Delete View and Search

keep demo of this video tutorial how to save record in c-sharp windows application using SQL Server view all set sale records update sale records and delete server codes hey guys welcome to Gordon mob myself Chauncey this is a c-sharp business video tutorial in this video we will learn how to create a Windows application with SQL Server for save update delete and view functions basically almost all applications should have these functions in common after creating the application you can download the project all source code from the link given below in video description in the last few videos we have created tutorials on login screen and samba calculator you can find their links in video description let's flip into visual studio now we need to create a Windows application for that click on new project then under which receive shop select Windows then windows form application select the location where you want to save the application then name your application let me name this application as contact app here we have our default Windows form that comes with every new Windows application in this application we are going to deal with a contact details of persons with details like name mobile number and address now it's time to insert some windows application controls for that go to view then select toolbox now we need controls like buttons then labels then text box then one more control data grid view like this we need two more label text box pair for that select both of down then right click then copy then paste then monoface arrange them we need three buttons copy page space now we need to change some of the attributes of these controls for that select the control then right click then go to properties otherwise you can use keyboard shortcut f4 now we need to change this level into name and name this textbox - txt name this level - mobile number corresponding text box - txt mobile number then address corresponding test box - tht address actually the address we need to change this text to a label text to address actually add this text box takes more than one line for that we need to change this single single line text box to multi-line so the beginning is the height of the text box we need to change the attributes of buttons changing button text to save then naming the button to be T and say changing this button to put a name to we TN d date but sorry button text to delete then this one for reset v TM reset then you pause the recording after rearranging the controls we start we will come back and then with rearranging and change background and foreground colors of controls change colors we can go to the properties of candles then you can see background and foreground colors there in order to change you have options here from the phone dialog box otherwise you can use hexadecimal or RGB colors usually I used to retrieve colors from website using color picker extension in my Chrome browser this one and using that you can pick hexadecimal or RGB colors from your favorite images and after copying the hexadecimal code you can directly place you it will directly convert into RGB colors using these three text boxes we will end the contact details like name mobile number and address and using these three buttons we need two operations like save update delete and reset controls and the saved contacts will be shown here in this data grid we control and they have renamed the control into d g/b contacts it shows save records like tablets here you can see the form title form 1 then you rename let me change the text to something meaningful contacts app know before start coding you need to create a table for saving the contact records for that go to view then select server Explorer then right click on the data connections then add connection then browse the location where you want to save the database name your DB contact baby click OK it is asking for confirmation click yes here we have our contact DB and we need to create a table to save this combined details for that right click on table then add new table then enter or columns contact ID add a type a singe then named Walker 50 then my Bailey number in work air 50 address in work 215 now these are our tablet columns now I added one extra column contact ID that will be used as primary key right-click on the column then click on set primary key you're going to set additi specification for this contact ID column for that select a column then go to identity specification then is identity equals yes by default identity column in SQL Server we start from 1 and while inserting new records it will be incremented by 1 and we don't need to insert values into this column and though by our primary key constraints will be satisfied throughout our application I will show how it works while we save our corn dog records now we need to save this table for that ctrl s the name your table something like JBL underscore corn back okay now we have now yet done with our tablet creation now we can start with our coding now I am going to save these control data into the tablet that we just created that is this TBL contact table using this Save button for that right click on the button then properties then you can see the pro attributes of this a button and events also here you can see a lot of events for this save function we are going to write code inside click event for that double click on that click now we can write code for save here start with try cash it is a best practice while we code inside our events in Windows applications in this cache block we will show error message as message box so that it will show messages as pop-up messages in this application you will be using store procedures to insert edit view or codes in this cue server dB now I am going to create a new store procedure for save function so in order to save a new stored procedure right click on this to a procedure then select a new store procedure it will open a new fresh troupis EG document and here we have to name our store procedure let it be contact add or editing the same stored procedure can be used for add or insert or edit functions here we need to specify the parameters that we used to pass from c-sharp in to SQL Server first one will be more just to indicate whether it is insert or edit and rest of the parameter will be D will be same as and rest of the parameter will be same as the table column for that just click on this column name then copy then PC now we need to add this at the rate symbol before every a parameter for doing that you can add at the rate symbol before every parameter otherwise you can use shortcut that is hold alt on your keyboard then drag this cursor along the right side of the document then press then add the red symbol that's it now specify the data type of parameters same as the quorum that you so now we have done with our parameters now we need to write the exact SQL code if the web mode is equal to add up here we need to add the code for in session for that you can write insert into TBL contact then specify the column names that you can be done by copying these column names remove this contact ID I already said that contact ID is a identity column for identity columns SQL will be inserted a sequence number based on the increment value then values will be add name and mobile number then address now we need to save this a document for edit purpose we will edit this tapas EJ while we doing code for a deeper purpose now in order to say this store procedure just click ctrl S on your keyboard now you can see the saved store procedure under this stored procedure now we can call this a stored procedure in our C shop code for that we need to import the corresponding namespace system note that I wrote SQL client then we need to create an SQL connection object skewering connection skewer Yukon Golds new SQL connection and inside this double quotes we need to pass our DB connection string in order to get the DB connection string right click on our DB then properties then copy this connection string paste it here remove extra cords now we can use this SQL connection object to do any operation in our SQL Server db2 I am going to save or insert that as into our table TBL contact first of all we need to check xqx on state if the connection stage is closed we need to open still connection before every excuse of operation you know see shop now we need to create an object of SQL command skew LC MD tell me English my zoom level so that you can see equal you SQL command first parameter will be our store procedure name that is con that add or edit second parameter is SKU L connection object SQL corn then we need to specify the command type SQL command skill CMD dot command type equals the main types dot store positive now we need to pass values into this store procedure parameters for that can I discuss him the door parameters dot ad width value then first parameter will be at the rate mode in case of save is ad like this we need to pass values for another four parameters for that let me copy this line that paste paste paste paste second parameter is conduct ID third parameter is mean fourth parameter is mobile number 15 is address connect ID will be 0 for say name for name we can pass this Xbox value that is txt name we can pass Xbox value as this box name that is txt name dot text dot trim trim function is used to replace or remove extra spaces from left and right side of our text box like this we can pass at the parameters values just replace this TXE name with mobile number then here txt address that's it you can execute this command by calling execute non-core e function now we have saved our record into a table now we can show a popup message showing save successfully here we have open our SQL connection so before leaving this event we need to close SQL connection for that we can write code inside our final block this write SQL korndog close let's see let me run our application just rendering some test data Sam mobile number address it goes here then saving yes this saved successfully let me look into our SQL Server table like the canova table then short table data yes you saved the now let's talk about this contact ID column when we created this tablet EBL connect I told you that this contact ID column is created as identity specified column in case of identity specified column that is contact ID we have not passed values into the contact ID column it will be automatically inserted by our secure server in case of our first row you can see it is insert at its value one like that it will be inserted one two three and so on now we have completed with our save or insert operation now I am going to show the saved records in our data grid we control as a tab for showing the saved records let me move this data grid way into the right side of the form in addition to showing the saved records I am going to add search operation also for that I need one test box and one button this text box can be used for txt search here we write the name of the person and using this button we can sit in the save records for the purpose of showing the records in this radical view och purpose for that right click on stored procedures then add new stored procedure name your stored procedure contact view Oh third we need one parameter and this contact name 10 walker 50 now in order to select all the records in the tab we can write select star from TBL contact that is the tablet name it will retain all the rows in the table TBI contact in our case we need to return the result based on our search text box if we type s here we need to retrieve result from this that starts with a contact name PI s so we have to use like operator in our store procedure for then we can write mu name like like any start with our past connect me then the poison dead character now I save this proposition so in this case we will pass the search text box value into this a parameter and we will retrieve the results from our TBL core like table that start with our set name now we can call the stored procedure inside our C shaft code for that let me create a new function we'll fill that a gridview now we need to check SQL connection stage if connection state is closed we need open scale corn dot copper in the end of the function we need to close the connection while inserting records into the table we used SQL command object in case of retrieving that as we need to use SQL data adapter SQL D a consume you SQL data adapter first parameter is the name of procedure second parameter will be the SQL connection object then we need to specify the command type SQL the end or SQL command goal command type equals command time plus two procedure here this skill select command is used because we have written a select query here so we need in case of the SQL data adapter we need to specify the type of procedure SQL command also and below that we can pass our parameter select command dot parameter both and width value and here the parameter name is a Derrick conduct now pasted here then pass the txt search test box value drink from both ends then we need to create a data table object in order to save the result that is returned from SQL server to fill this data table with result returned from this cue server we can call a scale da dot fill D TBL that's it now we have the final receptor codes in our c-sharp data table now we need to show this data table records in our data grid view for that we can write DG v contacts not datasource equals d TBL now we can call this field that I gave you function from our search button click event for that right click on our search button then go to properties then events double click on click event then we need to code for arrow handy we can copy this message box we can call fill that a good view here now let me run our application and you can check how it works in search operation now we have entered or one record already that is for Sam let me add two more records stiva mobile number press save see you successfully let me search all the records it is shown there now we need to add another code for atom not just to save it is save successfully let me search all of the records that we have saved okay and if I search for a course that is starting with S we've typed s here then search it shows Sam and Steven if I search for reports that are starting with a it shows Adam so our search function is set operation is what we find right now and here you can see contact ID in our case it is our primary key so we don't need to show primary keys in our that I could use in order to hide a column from that act we can write G G be gone back dot quorum then index since it is the first color index will be 0 then we Sybil equals false that's it let me check now yes she is not showing the now we have completed with our save search and view functions next we are going to do update operation for that we will be doing something like right now we are showing the save the course here for update user need to double click on this record it should be shown here in these - boxes and this say button - will be change it to update and while clicking on that the change it details should be updated for that record first of all we need to change that target view attribute or property with only to true so that user can change or record details from the now we need to write code inside the double click event of this data review for that try click on that edit view in properties then events then search for double click here it is now first of all we need to check whether the current row or click draw index is well it just sticking it is clicking inside the inside the that I can be rows now we can populate text boxes with a puppet or that asks from data grid view txt name dot text will be d GV current draw dot sirs index for name is one not value to string same for my number and address change text box name change index txt address index is 3 and we need to change it in save text to update after double clicking on D data grid new home and we need to enable the delete button by default it will be disabled and we need to store the this primary key column that is connect ID in a public variable that can be declared here in combat ID so we need to set the connect ID means we on the clicker draw connected index is zero we need to convert that into InDesign that we can write convert into two in 32 that's it let me check how it works setting or records lock clicking on Sam it is shown for updates now if we click on this updates should update the record for that we need to change the beat n Save click event now we need to update the or edit button click event of say button for update function or update operation for that go to button click event of this Save button then I am going to add if as there if BTN save dot text equals save do this else you can copy this there is only few changes cause propósito is same and more piece edit and contact ID is the a public variable value that will be contact ID and the message pop up message will be updated successfully now we need to edit this contact add or edit store procedure accordingly open the stored procedure then we need to write else part as if add more equals indeed do this update TBL contact set mmm name with at name number number and divide mobile number and address with add weight address were contact ID equals a direct contact ID saving and let me check how it works setting for all records Sam changing his mobile number to sequence of one dating updated successfully searching again this updated successfully now next we are going to do the reset operation for that we will be writing one function oh wait we sell in reset operation we will clear all control values and reset variables to the initial State for that we can write T XT in name dot text txt mobile number dot txt DX T address dot tex or two empty string then BT and Cu dot text equals save then contact ID equals zero BTN delete an ample equals false that's it then we will be calling this a function from our wizard button clickable for that double click on this reset button then call reset function here let me check up how it works bringing sample code for update if I click on reset to reset into the initial stage in case of for Windows form there is an event that is load event that will be fired when the forum is opened for the first time so in our application we can call the reset function and the function fulfilling the save records in the form load event for that we can double click on this Windows form then we can call those functions reset then fill that tag grid-view now when we given the application to reset the fall and it will fill the that I could be with save records and we need to call the these two functions after the save or update operation that can be done inside the button clicking and off say but that's it now delete operation is pending in case of delay to operation it can be done when their code is open for the update operation and for delete operation we need to write a new stored procedure and use two procedure then a procedure contact deletion only one parameter that is the primary key contact ID delete TBL contact will contact ID equal to a thread contact ID saving the stored procedure for delete operation you have to write code inside button click event of this button for that double click on the button then try-catch copy the message box for ever now we need to call the stored procedure contact deletion that is similar to our say or in certain set of insert or update operation so we can copy that code turn it for this if and instead of contact add or edit over to opposite your name is contact deletion only one parameter this is the conduct it'll be the contact ID variable and deleted successfully and we need to call the reset function and function for fill that I give you also let us check how it works we want to delete the sama core for that double click on this record then click on delete then it shows deleted successfully yes this one from our net a grid view that's it you have covered all D basic operations in all Windows applications please be subscribe to my channel dot add more for our soon videos like this please like and share the video for better visibility amongst eShop developers thanks for watching have a nice day bye

Channel: CodAffection

Views: 250,507

Keywords: C# insert update delete and search, c# insert save edit delete view, windows form application tutorials, insert update view and search in sql server, insert edit view and search in stored procedure, datagridview search, connect C# and sql server, C# datagridview in sql server, delete datagridview c#, SqlDataAdapter in C#, SqlCommand in C#, how to create a C# project, how to create and stored procedure in c#, using stored procedure and c#, add edit delete in c#, CodAffection

Id: -2UcDV4uUu8

Length: 49min 38sec (2978 seconds)

Published: Thu Jun 23 2016