CRUD In C# With SQL Using Stored Procedure | Insert Delete Update Search in SQL using linq C#

hello viewers welcome to programming geek i am here with a new tutorial that is complete good operation and insert delete update charts using stored procedure in c sharp link you [Music] okay start visual studio now go to the file menu click on new project okay now select windows and name the project project name is crude using sps minus for stored procedure select the location where the project will be created okay so share link you select the folder okay now click on ok this is windows form application now my project is creating yes the project is created go to the toolbox and next all in this form now design this form with label control text box and button control okay this level control will use as a title of this form text is product set of underscore product setup using sp as means stored procedure okay font size is 16 title is uh this is title so font size isn't big i got okay one bold true colors burn piece now change the back color of this form [Music] change the back color back color is yes now this is looking nice okay now i'm gonna add on the level control for id since the font size font size is 12 and text is product id okay now just copy and take the more label control for name and color okay now this is the second level this for item name and this one for color and this one for expiry date okay now the text box control yes this text box control allows id is textbox one and it's uh use uh for product id and textbooks two for item name and color from combo box so at the combo box from toolbox yes font size is 12 okay now add combo box item red green and black click on ok yes now add a date and figure control from the toolbox just drag the date and figure control and drop in the expedite okay now this is long format i'll make it a short format first of all font size is 12 yes and format long format to select format long to short yes now this is short format looking nice okay now at button control this button control i'll use to insert a record in the sql server database by using astro procedure font size is 12 font bold true and four color is button paste button highlight okay and back color purple okay now button text is insert okay now add a daily grid view just drag the data keyboard from the toolbox and drop into this from application research this form and research this data grid view okay now with the button click event in this button click when i write code to insert records and specific database using installed procedure so start sql server 2014 this is server name authentication exclusive authentication your login name is essay and password is exclusive notification password connect yes it's connecting yes it's connected explain the database now i'm gonna create a new database right in the database folder and yes database name name the database then my name is my task db [Music] okay now click on ok yes the database is created next to the table there is no table now i'm going to create a new table [Music] column name is predict id and data type is integer and second column is item name data type is n barca and third one is color colors also and worker and expiry expired date is day time the table table name is product info underscore tab click on ok yes the table is created i replace the table folder yes now there is no records in the database table [Music] okay [Music] now i'm gonna create a stored procedure now there is no stored procedure right click and click on stored procedure this is default text the stream of this text and create right to create a stored procedure create pros sp underscore product insert this is procedure name and a parameter at the top product id this is the data type is integer this is same as my table name table column okay [Music] 50 and then color color also and worker 50 and finally date expiry date expiry date is daytime okay then as begin [Music] i'm just writing the insert command to insert records in the database table using stored procedure insert into product info underscore tab this is very easy to to insert records in the database table using a stored processing system from application product info tab this is product id the database column name product id item name product info database table name and product id is column name item name color and expiration column name values values you get from the m parameter either to product id or that item name and add that of color editor to expiry date as i mentioned parameter okay color and then a third of expiry date yes now that completed just a foot end if you started begin you must end with inch command successful i mean the store procedure is created successful layers replace this programmability folder and like from the stored procedure yes this first procedure is created perfectly okay now just test this stored procedure to test just to write exact debut dot dps schema you must put schema and sp product insert this is the store procedure name and the parameters are producted item name color and now there's no records in the database table now i'm going to insert our records product id 101 you can see then item name is laptop then color so color is black yes this is an worker so you should put this color in single quotation and expiry date for the date noted that the date is always month day year format so this is the month day 10 and may 2020 okay just execute yes that was affected successfully let's check yes the record is inserted in the exclusive database table successfully in the install procedure okay now [Music] i'm gonna write insert records from textbooks one two and combo box and then expired it now i'm gonna add a link you file to connect sql server database with a visual studio so data select the link into sql class and name the dbml file my dbml file name is [Music] sv tutorial [Music] class okay click on add yes the dbml file is added now go to the server explorer and add connection right click on add connection for the server name and server name is your option type in pc as close authentication is an essay and password is sql certification password select the ddvis and database name is my task db click on ok yes it's connected now expand the database expand the stored procedure just drag this to procedure dropped in the dbml file okay it's added now save and then to connect with the uh cs file just to put the name as we tutorial class data context doctor that again as we detail class data context db equal to new usb d2l class data context then i will use just db so first of all bar vs equal to db dot s2 procedure name sp product insert and then parameter integer dot parse product id is in data type so uh integer dot festivals one dot text express one for product id i added in the windows form application you can see you already seen okay start with the break [Music] start with the first break then techniques tool for item name and then color color is from combo box so put combo box one dot com works on.text okay then daytime datum figure one dot text my dating figure is dating figure one dot text and uh you should convert it to date time so for datetime.parse [Music] okay now let's debug start debugging yes now let's check the records in the database table right click the table and click on show table data there is only single one records in there that will be stable now i'm going to insert was one more records product id102 item name mouse expiry date is 18 december okay it's okay color is red insert yes it's not displaying any message now i'm gonna adding a message after executing the operation it will list my message successfully saved [Music] [Applause] okay now i'm gonna create the second stored procedure this is to procedure [Music] to display data from sql server database to data grid view so change the store procedure name sp product list okay and now no need parameter [Music] okay then value no need to just change the sql command as mysql command is to select all the records from the sql database 2 uh data grid view so uh write the statement select star from product info underscore type this is table name okay now select and execute yes commands completed successfully now let's check the stored procedure so to check this procedure just write exact sp product list you should add schema to execute okay it's uh okay now now go to the dbml file and drag the store procedure and drop it in the dml file okay now add add method now create a method to load data in datagridview so why it load and now i'll just call this a stored procedure so her sd list equal to db dot master procedure name is just sp underscore product list okay finally delegate view under dedicates equal to st list okay now call this method in the button click event and form load even double click to go to the form load event yes and call this method in the form load event as well okay let's debug [Music] yes you can see the records are displaying the datagridview now i'm gonna insert one more record the record is a one zero three item name is keyboard and color is red this is the expert did just insert yes successfully inserted and the records are displaying the daily grid instantly okay and let's check the sql server table yes it's inserted successfully now add button control this button controller used to perform update operation so change the text of this button update double click to go to the button click event now i'm gonna creating and store procedure to perform update operation just copy the insert procedure and paste to make it as updated version change the store procedure name sp underscore product update our same just change the sql command update product info underscore tab then set product id this is database column equal to parameter at the rate of product id [Music] yes then item name equal to item name then color color database uh table column name color color equal to that of color it's very difficult to experience okay okay it's done now remove this back first break expiry date equal to expiry date in okay now select and execute command completed successfully okay now replace yes the procedure created successfully now replace the stored procedure folder go to the dbml file and just to drag this asp product update procedure and drop it in the dbmo file okay now just copy the button one code and paste it in button two to perform update operation all are same okay now change the stored procedure name db dot sp product update okay it's done oh yes i have to add higher clause to update specific record if i update it now it will updated all the records in the database table so at higher close higher product id equal to add data product id okay it will update all the column item name color expiry date based on product id to change anything in the store procedure you should add alter instead of create yes now the procedure is altered and now replace yes go to the dbml file delete the existing product update procedure and place it just drag and drop it once again okay now it's done now call the first of all add message message box dot show successfully updated after executing the operation it will display the message messagebox.show successfully updated [Music] and call the method to get the result instantly in the degree view okay now debug let's debug [Music] yes now i'm gonna update uh record102 [Music] mouse and color it will update ram in a mouse and color red to green expiry date 28 december to 30 december just click on update successfully updated you can see the result also displaying in the daily grid instantly okay close this window and now i'm gonna add one more button control this button control i'll use to delete records from the sql server database to uh from the specified database uh using stored procedure [Music] okay first of all create a store procedure just copy the store procedure and paste this store procedure i will use to perform delete operation so create force sp product delete this is the store procedure name and parameter only product i will delete record based on productivity so parameter only product id as begin delete sql command is delete product info tab higher remove this set and just put higher product id equal to at the rate of product id okay it's done very easy select the stored procedure and execute yes it's created successfully replace this stored procedure yes you can see it's also added as we product delayed now go to the dbml file and go to the server explorer first of all replace yes it's edited now just to drag and drop it in the dvm file okay now [Music] just uh use this store procedure coffee and paste okay now change the messes it will display the message successfully delete it and no need to tag those two tool letting picker just add text box one because i have one parameter and stored procedure name is product delete sp underscore product delete okay [Music] yes there is only one single parameter i just put uh [Music] texas one dot text okay now i'm gonna delete uh records the records is a one zero two delete click on delete successfully delete it and uh it's also you can see the result in the dedicated units stanley it's successfully deleted okay now i'm going to insert one more record one through three and color green insert yes inserted successfully and if i insert 102 monitor color is uh green okay successfully inserted if i want to delete just click on delete it will delete successfully but till it is a bit confidential so i have to add confirm masses before performing delete operation so that avoiding mistake okay if message box dot show this is the confirmations are you sure to directly dispel the message are you sure to delete then you should confirm the delete record document name is delete record and message box button will display the two button uh yes no if you click yes then it will execute this command and dot yes no okay yes code is in the condition let's test okay now 1 03 click on delete are you sure to delete if i select yes it will delete if i select no it's not deleted in record if you select yes it's deleted 103 record successfully you can see the 103 is deleted and it's not f here in that dd grid view 102 no not deleted yes okay now [Music] okay now i'm gonna add one more button control this button control i will use to charge a specific record in the dedicated view okay now go to the button click event [Music] now go to the sql server and create a stored procedure to charge the result specific result so creating a stored procedure to get a specific record in dedicated view change the stored procedure name mr procedure name is sp product charts okay product id okay now sql command select star from product info underscore tab higher product id equal to the product id select the s2 procedure and execute yes it's created successfully replace the stored procedure folder as you can see the store procedure is created successfully now go to the [Music] dbmo file and sign the server explorer replace again just to drag this product chart system procedure and drop it in the dbmo file okay it's ready now bar st equal to db dot master procedure name is product sp underscore product charts and parameters single one parameter product id so integer dot first textbox one dot text for product id oh add black first break close the first break yes now data grid view one the data tools equal to sd okay it's done [Music] you can air condition if techniques one dot text not equal to blank then it will display the result in the ddg view else it's uh if it's blank it will display an array condition is uh i mean if text one dot text equal to blank then it will display a message mastery dot show the message will be food product id okay now let's debug yes it's debugging now food to a one zero two click on charts yes it's displaying only one zero two if for one zero one let's display only one zero one records in the data grid view and if it's blank then it display masses food product id thanks for watching this tutorial [Music] you

Channel: ProgrammingGeek

Views: 8,291

Keywords: programminggeek, crud in c# with sql using linq, crud in c# with sql stored procedure, stored procedure in sql, c# tutorial, .dbml insert update delete in c# with sql server, crud in c#.net, crud in c#, insert update delete and search in sql server using c# linq, insert in sql using c#, update in sql using c#, delete in sql c#, search data from sql using c#, insert, update, delete, view, visual studio code, virul, stored procedures sql, crud c#, linq to sql

Id: 8mEodz9HyNI

Length: 27min 49sec (1669 seconds)

Published: Tue Jan 12 2021