Insert Update and Delete in C# DataGridView

cube demo of this video tutorial this is a step-by-step tutorial on how to implement insert update and delete operation inside C job data grid view in order to insert a record you can do this in order to update a record you can directly edit the value inside this that I get view we have implemented insert and update operation inside the cell value change event of this data get view in order to delete a record we can select the row using this row header here then press Delete on your keyboard then it will ask for confirmation are you sure to delete this record or not click yes to delete the record so here we have deleted the record apart from these crude operations we have discussed how to bind SQL Server tabal into c-sharp data grid view and here we have populated a combo box inside this data grid view and finally we have discussed how to prevent non digit numbers inside this age column I hope you will find this tutorial helpful for your windows form applications so please watch till the end of this video tutorial what's up YouTube welcome to dotnet MO in this tutorial I will show you how to implement operations like insert update and delete in c-sharp data gate view using SQL Server database before starting this video tutorial I would like to ask you a favor if you found this video helpful please thumbs up this video if you are new here please be subscribe to this channel dotnet Mobe first and foremost we have to create a windows form application for that you can click on this new project here and I want to save this project inside this project folder so I will copy this folder path from File Explorer then back to visual studio then browse then paste the folder path here hit enter then click on this select folder here so here we have selected the location for saving this project now select windows under visual c shop then select windows form application i will name this project as data grid view app now click on ok so here we have created a brand new windows form application in c shop inside this application here we have the default form form 1 first of all i am going to change the title of this form for that right click on this form then click on properties and i want to change is text property here I will set it as insert update and delete in c-sharp that I give you that's it now we need a data grid we to demonstrate all these operations for that open toolbox then go to all windows forms then search for data grid view double click on it so here we have the data grid view let me resize the data grid view here okay so here we have done with designing our windows form application before continuing with this project we have to create our database for that I will use my management studio here is my management studio first of all we have to create a database for that right click on databases the new database I will name this database as data grid view DB in short I will name it as DG v DB click on OK inside this data grid view we want to show details of employees among these details we have position for position I will show a combo box I want to populate this combo box with predefined collection so first of all I am going to create a table inside this database for position collection so right click on tables then new table we have two columns position ID it softly type integer then we have position itself it's off the type waka 50 I want to make this position ID column as primary key in order to sell this table you can use the shortcut control s or you can click on this Save icon here I will name this table as position then click on OK if you refresh this tablished note here you can see the newly created table position here now right-click on edge click on edit top 200 rows now I am going to populate this position table with predefined collection so here we have 4 positions accountant software engineer says assistant system administrator now we need the main table for my further you can right-click on Tablas the new dabble first column will be employee ID it's of the type integer then we have a column name to store full name of employee it's of the type walk 100 after that we have position ID position ID it's of the type in the job so when we select a position from given combo box in this data grid view we store the corresponding position ID inside this column position ID after that we have obvious column it's of the type vodka 50 and finally we have a column for age it's of the type integer now I want to make this employee ID column as the primary key I want to make the same column employee ID as the identity specification for this table for that select the column then go to column properties then you want to set this identity specification to years so that we don't want to insert values into this employ ID column it will start from 1 and incremented by 1 upon new record in session these increments will be done by SQL Server itself so it will maintain its unique constraint by itself in order to save this table you can use the shortcut control s or you can click on this Save button here now I will name this table as employee click on OK so here we have done with database now back to the vicious studio here we have the data grid view I want to name this data grid view as dgv employee now we want to add that tacit view column corresponding to these SQL Server tablet columns here so first of all select the data grid view then click on this right arrow here and click on edit columns now we need to add columns for that you can click on this Add button here inside this textbox you can name this data grid view column I will name this as T X T em blowy ID and here we can provide the heritage I will set it as employee ID this column is for ID column normally we don't show ID columns inside that I get view so I want to make this column as hidden so I will uncheck this checkbox visible here now click on add so here we have added one column for employee ID and we want to add column for rest of these columns here now we have txt name its header text will be name itself now check this text box visible click on add next we have position ID for position ID we want to show a combo box so I will select the type from here that I get view combo box column I will name this column as CB X I use CB x as a prefix for combo box CB x position I want to show the header text as position click on add now we have one column for office txt office type will be textbox column header text will be office click on add now we have the last column age I will name this column as txt age and I will set the header text as age here now click on add close this window click on OK here so here we have added required columns into this data grid view as you can see here our data give you columns only takes half of the allotted width so in order to address there with click on this right arrow here then edit columns I want to make this name column to fit the remaining space for that we can select the name column we can set this Auto size property here as field so this column so name column will take the remaining free space we have to map these columns inside SQL server table inside these data grid view column here for that you can click on this right arrow here then edit columns so here we have employee ID here you can see a property here data-property-name for this data give you column a Skilsaw vertebral column will be employee ID employee ID and for name it will be name itself and for position you can see the data property name here I will set it as position ID then we have office corresponding data property name will be office then we have the final column it's data property will be age itself now click on OK so here we have done with designing windows form application and SQL Server database now we are going to populate this data could be using this SQL Server tableau CEO first of all I will populate this combo box position combo box with these columns inside this table position we will populate these rows inside this combo box as an item then we will populate this that I give you as a whole using this table here I am ROI table currently you can see that this employee table is empty for now once we populate this data get view with employ table we will make this control as editable data get view so that we can do operations like insert update and delete so first of all right click on this form here then view code so here we are going to define two functions to populate these controls first of all we have to define a string variable in order to store the connection string for this DB so I will define a string here with name connection screen for now I will paste the connection string here first of all we have datasource where we need to specify the SQL server instance name and we have initial catalog where we need to provide the DB name and we have integrated security is equal to true because we are connecting this DB using windows authentication we have already discussed how to generate c-sharp constant string for SQL Server DB's in another video I have given the video link in video description if you want to know more about connection string you can go through that video for now I am going to define a function to populate this combo box position combo box so function will be something like this void populate position combo box combo box you don't have any parameter for this function I'm going to start with an using statement as a resource I will use an object of SQL connection class connection for that we have to import this namespace here using system dot data or SQL client that's it connection name will be SQL corn is equal to new SQL connection inside this constructor we can pass this connection string variable so I will paste the variable here inside that we have to retrieve these holes inside this position double here for that I'm going to use SQL data adapter SQL that adapter school data adapter as SQL da is equal to new SQL data adapter as a first parameter I will pass the SQL query because it is simple we don't have to define a store procedure for this query so I will directly enter the SQL query here select star from position as a second parameter we have to pass this SQL connection object in a SQL corner before that we have to open this connection object by calling this function open in order to store position cross inside this shop code I will define a data table here data table data table d TBL is equal to q data table now we can execute this query for that we just need to do this SQL da dot X QLD a dot fill as a parameter for this function we just need to pass this data table object here so inside this data table object we have all position rows from this S Plus server table position now we have to populate or bind this data table into this combo box position combo box here we have named this position combo box as CB x position so we can do this CB x position dot data source is equal to d TBL so here we have set the data source before that we have to specify value member and display member for this combo box for that we can do this CB X dot value member is equal to is equal to position ID and then after that we have value member sorry display member this play member as a display member we will show the position column so when we select an item from this position combo boss we need corresponding item position ID because we need to save that position ID inside this employ table in this column position ID so we can get that position ID from the value member of the combo box okay finally I want to add an extra item into this combo box here often you might have seen had the option which shows select or none in many of the combo box so in this case I want to show a header item which shows select for that we can do this first of all we have defined a data object in order to store new row from this data table and we have set the columns values first column will be 0 that means position ID then position column value will be select and finally we have inserted that new row or header row as a first row inside this TT TBL finally we have said that that a double into this combo box cbx position now we can call this function inside the form load even for that right click on this form here then go to properties then look for event load double click on it or you can double click on this form itself so that it will create the form load event here okay we can call this function populate position combo box here okay now let me run this application here so here we have the position combo box it is populated with 4 rows from the SQL Server table position and here we have the extra header item which shows select now let me close this application now we need one more function to populate this data grid view for that I will define an extra function here void populate that good view okay inside this function we have to retrieve rows from this employee table so I will copy this using statement inside this combo box populate function okay we just need to edit that accordingly it is a best practice to use using statement in C sharp to interact with SQL Server DB because once we open this SQL connection here we don't want to cross this SQL connection at the end suppose if there is an error in between these lines of execution it will get out of this using statement and it will automatically close this SQL connection so we don't want to worry about releasing this SQL connection object using statement will do the job for us automatically so here we have to edit this core which is copy pasted from our combo box populate function we need to retrieve rows from android tablet so query will be something like this select star from employee and here we have the rows from employee table now we need to sign this dabble into the data grid view so I will get rid of this extra lines here now we can set this DD BL inside this data grid view for that we can do this d GV employee t GV employee dot data source is equal to dt BL that's it we can call this function here populate that I get view let me run this application here so here we have done with populating that I gave you and attack it will call them combo box now we have to make this data grid view as editable so that we can implement operations like insert update and delete so let's look how we can do that currently inside this delegate view we have a new row by default if you want to add a new employee record you can use that go here so I will type the employee name EMP one you can move forward using tab control hit tab so that it will move from left to right if you want to move backward you can use the shortcut shift tab now I have entered the employee name if you want to select a position hit tab so here we have focused this combo box if you want to change the position then press spacebar then use your upward and downward navigation arrows in order to select a position from this comma box then hit tab to end the office so I will type the office here than age so like this we can end our employ details inside this that I gave you once we start editing this new that I give you row here it will create a new data grid view row by default okay so these are the functionality is implemented inside data grid view by default now in order to implement insert an update operation we are going to make use of an event from this delegate view which is this one cell value change using this event we will implement insert and update operation this even will be triggered once we change a cell value inside with that I give you so I'm going to create this cell value change even for this data grid view inside this even we can implement operations insert and update first of all we will make sure that there is a current grow or focus that I could be wrong for that we can do this if the GV employee dot current draw current draw not equal to null and means that we have a focused that I give you wrong inside this if closed we can do operations insert and update I will start with using statement using statement as a resource I will create an object of SQL connection like we have done here and SQL Korn dot open and then I will declare a data grid view row object here data grid view row I will name this variable as DG v rho is equal to d GV employee dot current row that means we will save the current selected focused row into this variable d GV row here before continuing with this function we have to create required store procedures for insert update and delete we can do these operations like we have done using these direct SQL queries if we do these operations using direct query there might be a chance for SQL injection you know to avoid that we will use SQL store procedures for that back to management studio now in order to create a new stroke or seizure you can click on this new query here let make sure that we have selected the corresponding database name from this drop down in order to create a store procedure we can do this create rows store procedure name will be something like this M blowy add or edit so this is the store procedure for two operations insert and update we will do these two operations using a single store procedure employee add or edit whether we have to provide the parameters for this store procedure first of all we have employee ID it softly type in d'azyr then we have named it soft the type walk hundred then we have position IV its of the type integer then we have office its of the type walk fifty finally we have on parameter for age it's of the type in the jaw as here we need to provide the square query to do operations insert and update first of all we will check whether this employee ID parameter employee ID is zero or not if it is zero then we will do the insert operation insert into employee table here we have to list the employee columns for so far we have name we don't want to insert values into this employee ID column because it is identity specified column and we have position ID after that we have office : finally we have age column we have to provide the values for these columns in same order so we have name parameter then we have position ID after that we have office and age in the else part we will do the update operation update employee table set name is equal to acne then position ID is equal to add position ID then we have office office is equal to add office finally age is equal to @h for employee I ve is equal to you @m LOI ID in order to create this torque procedure you can click on this execute button here so here we have created this torpor cjm LOI add or edit now we have to create one more procedure for delete operation for that I will click on this new query button again create pros procedure name will be something employee delete by ID we have a parameter here sorry here we have misspelled create we have a single parameter employee ID it's of the type integer as delete from employee double whoa employee ID is equal to the given ID now to create the stored procedure you can click on this execute button only craters to opposite can be seen under this store procedure note here so here we have the store procedure for insert and update operation here we have this toe procedure for delete operation now back to the Windows application here currently we are inside this cell value change even inside that we will implement insert and update operation for that we will call this tour procedure here employee add or edit in order to call that store procedure I will declare a variable of SQL command SQL command as SQL CMD is equal to new SQL command as a first parameter I will pass the store procedure name so let me copy the store procedure named from here and pasting here so second parameter we have to pass this well connection object first of all I will set the command type here to a CMD dot command type is equal to command type dot store procedure first of all I will show you how to do insert operation after that we will do the update operation so now we can pass the parameters for these straw positive parameters here in total we have five parameters SQL cm d dot parameters dot advil you sorry ad width value first parameter is for employee ID at employee ID for that we will pass 0 for insert operation employee ID parameter value will be 0 like this we have to pass parameters for remaining 4 parameters so I will copy paste this or four times here we have named parameter we can pass this name parameter value from this second column here or first column will be the employee ID column which is already hidden so here we have the second column name from this column we can pass value for this parameter for that we can do this we will make use of this data grid view row here d GV v GV row door cells inside that we can pass the column name or index i will pass the column name here it was txt name dot value this property is of the type object if we does not enter anything inside this text box for name column then value inside this property will be null so we will check whether it is null or not so you can do this t be annulled door value first of all we will check whether additional or not if it is null we will pass an empty string otherwise we can pass the value directly from the property here after that we have to convert that into string like this now we have to pass value for this parameter position ID so I will change this employee ID to position ID we have to pass value for this position ID like we have done for this name column here so let me copy this and paste in here instead of zero instead of txt name we have CB x position okay first of all we will check whether it is null or not if it is null we will pass a zero here otherwise we will have the number string for the selected item from this combo box here here you can see that this position ID is of the type integer so we want to convert this string into integer so finally I will convert this into individual for that I will do this convert to in 32 function will be called after that we have this office parameter so instead of ID we have office for this parameter we have to pass the value like we have done for the name parameter so let me copy this and pasting here for position ID and age so this will be age okay so here we have txt office sorry we have cbx position and here we have txt office same will be here so we don't want to do any change for office it will be same as name column both of them are string or columns finally we have the age column corresponding column name will be txt age if cell value is null we will pass a 0 string otherwise we will pass the number string since we have declared this parameter as individual so we want to convert that into integer so we can call the function convert to in 30 to convert to in 32 finally we have to execute the store procedure with given parameter values for that we can do this SQL CMD got executed on curry so this will insert a new employee record into the employee table after that we have to refresh this data grid view by calling this function here populate that I can use so let me copy this and we have to call the same function here also now let me run this application here I'm going to enter a new employee name here Fiona gleen then press tab so it should be saved inside Android tablet let me check our table here let me execute this query again so here we have the new record for Fiona key after this insert operation we have refreshed this data grid view by fetching all these rows inside this employee table back to this that I could view now let's look how we can implement update operation here we have hidden employee ID column for new data grid view role like this employee ID will be not and for employer course which are already inserted into employee ID we'll have an employee ID value which is corresponding to the employee ID value inside this employee table now let's look what it makes the difference so here we have inside this cell value changed even here we have passed employee ID value as zero for insert operation okay now I'm going to make a few changes here first of all here I will add an if Clause here if you will check the employee ID column value here the Divi Road or cells inside that we have to pass the column name here for employee ID we have named the column as txt employee ID dot value if it is equal to null DB null we can say that this row is a new row so we have to do insert operation here okay this is for insert operation in the else part we can do the update operation for that we have to pass the corresponding employee ID from this cell okay so let me copy this line from here and pasting here and we have to pass the employee ID so I will do this convert to in 32 because employer ID is of the type in the jaw here sorry yeah here it is in the jaw so we have to pass the value as individual we can copy this from here and paste in here so for insert operation employee ID will be 0 and for update operation employee ID will be a non zero value based on that inside this store procedure employee add or edit we will do insert and update operation let me run this application again so here we have the record for Fionna green which is already added into the employee table now I am going to update this record for that I will change the position to software engineer I want to make office as London then edge will be something 24 hit enter now check our DB here let me execute this select statement again so here we have updated record for Fiona ghee so here we have done in third an update operation inside an editable that I could view like this we can insert as many as employees we want so I'm going to insert a new employee record here James Jones James Jones corresponding position will be accountant then office will be something or Sydney then age will be 33 now let's check our employee table here so here we have there a quote for James Jones we have a small problem regarding this age column we must prevent non digit number inside this age column so let's look how we can do that for that we will make use of an event from data grid view which is edit control showing that even will be triggered when we start to edit itself from that I could view let me close this application here now go to design right click on that I get View button properties then go to events then look for edit control showing even so here it is edit control showing double click on it before can be me with this edit control showing event we have to write a key press even for normal textbox so for now I will copy paste the event here so here we will allow only two types of keys first one is digit keys which is 0 1 2 up to 9 then we have control keys that means escape delete and backspace enter so these control keys are needed in order to edit a mistyped digit inside the text box if typed key is not from these two categories we will prevent the key by setting this property a dot handle to true first of all we will check the column or current cell index dgv Android or current cell door column index so we want to check the column index for this column age column we have employee ID as the first column so 0 1 2 3 4 so each column will have a column index 4 so if column index is 4 we want to bind this key press event hello numbers only into that cell so first of all we will do this eel dot control will have the textbox object txt age dot key press is equal to first of all we will avoid the already binder text box even key press event textbooks for that we can do this a low-key press okay after that we will add this key press even into this text box now let me run this application I'm going to add a new employ here Tony Abraham then I will select the position as sales assistant then office will be somewhere in India then we have age it will be 26 if i try to endure' something other than number can see that it won't work okay so here we have prevent that non digit numbers inside this age quo now let's discuss how to delete row in that I get view in c-sharp Windows application by default if you select a row from this data grid view using this row header here then hit delete button on your keyboard it will delete that row from your data grid view but it won't delete that draw from our SQL server DB in order to delete that from our DB permanently we have to write c-sharp code for that we will make use of this stored procedure that we have already written and blow it delete by ID we delete an employee record with given employee ID so let's look what are the things that we have to do to delete this row from SQL Server DB permanently for that I will make use of the event from data grid view which is this one user deleting raw event you can see the event here user deleting row I am going to implement this event here so double click on it first of all we will make sure that the row which is selected by user is already inserted into our DB for that we can do this if bgv employee dot current row dot cell you have to check the employee ID column txt employee ID dot value is not equal to n not so we can do this BB null dot value inside that we can do the delete operation first of all we will ask the user whether you are sure to delete this employee record or not because delete is a loss of data so we need confirmation from user side so we can do this message box door show we are asking are you sure sorry are you sure to believe this record ok as a second parameter we have to pass the title for this message box so I will type that grid-view third parameter we will pass the buttons for confirming this delete operation so message box buttons dot yes or no we need these two buttons yes or no if you sir confirm this operation that will retain dialogue result yes so we will come back this return the cell with dialogue result yes ok if it is yes we can go ahead with deleting this row so first of all I am starting with using statement beside that I will create an object of SQL connection SQL connection ask you and corn is equal to new SQL connection inside that we have to provide the connection string here first of all we will open this connection by calling this function open and then I will create an object of SQL command as QL CMD is equal to new SQL command inside that first of all we will pass this tour procedure name which is this one employee delete by ID let me copy this and pasting here as a second parameter will pass this connection object SQL corn below that we will specify the command type command type is equal to command type dot store procedure because we are using store procedure here ok and we have to pass the value for this parameter employee ID SQL CMD so is ql CMD load parameters dot ad with value parameter will be employee ID and value of this parameter should be passed as integer so I am going to convert the value to individual convert to in 32 then we will pass the value from the employee ID column so I will copy this and paste in here that's it now we can call this store procedure for that I will do this SQL CMD dot execute not query so here we have done with delete operation here we need to be conscious about the default functionality or feature of that agat view when you sir click delete button from there keyboard the same event will be triggered okay we have to prevent the default delete operation from the data grid view we will delete only when you saw confirm this operation using this message box here so in the else part in order to block the default delete operation we can do this II don't cancel is equal to two okay we have to do the same for this if close here first if close here we check whether the employee ID value is null or not if it is null or something we will prevent the default operation like this now let me run this application now I am going to delete this record for gemstones for that you can select the row using this row header here then press Delete on your keyboard so it will show this message box are you sure to delete this record if I press yes it will delete the corresponding record for the employee if I press no it won't do nothing so I click yes so here you can see that James John's record is deleted now let me check our database so I will expand establish node here right-click on employees tab then select top thousand rows so here we have deleted James John's record permanently from this employee tab now I would like to make one correction regarding this alone numbers key press event we have to move this line out of this if close here okay in order to remove the keepers event from other columns if you found this video helpful please thumbs up this video and for more hours and videos like this please please subscribe to this channel dotnet mob you can download this project source code and DB script from the link given below in video description please like and share this video with your friends so that they can benefit from this have a nice day bye

Channel: CodAffection

Views: 161,819

Keywords: Insert Update and Delete in C# DataGridView, C# DataGridView CRUD Using SQL Server, insert update delete in database from datagridview, how to update data in datagridview c#, how to delete row in datagridview in C# windows application, datagridview delete row, editable datagridview in c#, datagridview crud c#, Insert New row in datagridview, C# DataGridView CRUD With ComboBox, Windows Form Tutorial, C# DataGridView Insert Update Delete Using Stored Procedure, CodAffection

Id: cQQy_IfFddg

Length: 45min 27sec (2727 seconds)

Published: Mon Dec 04 2017

Reddit Comments

[u/[deleted]]

hasn't this been done with data adapters and data grids since the early 2000's?

edit: jumbled brain