CRUD without Entity Framework in ASP.NET Core MVC

what's up youtube welcome to coda friction in this tutorial we will discuss how to implement sp.net core mvc correct operations without using entity framework core we have connected to the sql server without ndd framework and we have implemented all of these operations using stored procedure previously i have already implemented core operations in asp.net and commvc with ndd framework co there will be an article on the same topic you could grab code from there as well all of the links will be there in video description you could check that from there without further ado let's get started with this topic here is my visual studio 2090 first of all we have to create an mvc project in asp.net core so click on this create new project here now let's create an asp.net core web application i will name this project as mvc cred without a ndd framework then select the location where you want to save this project click on create now select this mvc template here i will uncheck this option for https which is not required for development environment click on create so here is our brand new mvc application now let's look how to implement the correct operations without ndd framework inside this asp.net core application for that first of all let's create an sql server database here here is my management studio now let's create a new database to implement cred operations inside this application we'll be dealing with details of book like a book title or the price etc so i will name this db as books db so here is our new database inside that let's create a table for books so right click on it then double let's add required columns into this table book id which is of the type int title as work 100 then author which is also work 100 and finally we have the price column which is of the type integer now let's set this book id column as the primary key now we have to apply the identity specification for the same column book id so with this book id column selected go to column properties here then under identity specification set is identity as is so that we don't have to insert values into this book id column here sql server will take care of that it will start from 1 and increment it by 1 upon new record insertion to create this table click on this save icon here or you can use the shortcut ctrl s as books click on ok so here is our new table books now let's look how to implement operations like insert update delete or retrieve from our asp.net core application without any framework so back to the project here first of all i'm gonna create a view model for the table that we have just created so let me create a new class book view model now let's add properties corresponding to this table column here first of all we have the primary key which is book id then we have the title which is of the type string let me copy this and paste that below also and finally we have the price property so here we have a view model which is not a model that we used to have in our entity framework code we are not going to use entity framework core inside this application then why do we need this model clause here as per the naming convention itself view model classes are only used for designing view files inside this application with which we can send data from our action method into corresponding view files you could see that in a bit now let's create a corresponding controller inside this controllers folder here so right click on it then add controller for this mvc project we have three scaffolded item available here first of all we have this empty template it will just create a controller clause with the provided name with the second method here read or write action methods it will create a corresponding controller with action methods for all of secret operations but there won't be corresponding view files if you also want to include view files for the corresponding action methods you can use the second method here but there is a problem we have to be using entity framework if we use any of these first two methods here there should be a lot of work to be done the created action methods will be empty and there won't be corresponding view files all of them will be automatically added with this third scaffolded item here to avoid the extra works i will select this entity framework a scaffolded item in here we have to provide the model class for that we can pass this view model book view model here if you know how ndd framework works you know that data context class is a must intended framework but in this project we are not going to use entity framework code we are creating mvc controller using this entity framework scaffolded item to avoid extra works to achieve that we have to do some tricks here first of all the model class must have a primary key so first of all let's set the primary key for this book id here so here it is we just need to add this attribute key here we are just adding this attribute just for the sake of the scaffolded item template now let's create the controller add controller select this template for entity framework model will be book view model to create this controller with this cap folder template we'll create a demi data context for that you just need to click on this plus button here click on add let's name this controller as book controller click on add as you can see here it is currently installing gate packages for nad framework core we are just using that for creating the mvc controller easily finally here we have the book controller inside that we have all of the action methods for all the secret operations that we have to implement for the corresponding control action methods you could see the views folder here create delete details edit and index and the demi data context class is created inside this data folder here don't worry we are not going to use this data context class which is just added for the sake of scaffolding process if we were using these two first scaffolded methods here we have to create all of these views from scratch and also the controller action methods here will be empty now we just need to modify these action methods and view files as per our requirement as part of creating this controller there is one more modification inside the startup class here which is the dependency injection for the database context class you could see this function at db context which is not required when we are not using entity framework core so let me remove that from here now inside these views files here we don't need these two view files create and details let me remove that from here we will be retrieving all of your requests from books table and displaying that inside this index view here and inside this uh view edit actually i want to rename that like add or edit so inside this views file here we'll be designing a form for both insert and update operation and we will be displaying this delete view here just for confirming the delete operation just saying are you sure to delete this record to get the confirmation from user side we can discuss that in a bit now back to the controller here all of the action methods inside this controller are using entity framework for database interaction which we have to replace with the conventional ado.net interface as part of the entity framework here we have the constructor parameter for the dependency injection so let me remove that from here we don't need that for this application we need this index action method and since we are not using the entity framework we can avoid this async keyword from here and we don't need this wrapping with this task now we will just return the index view we don't need this details action method and also this action with this for create both get and post so let me remove all of them from here for this view here add or edit will be modifying this action method edit as add or edit let's remove these uh async keyword from here for now let me remove existing entity framework codes from here for now we will just return the view here now here we have the add or edit action method of the type post let's remove this i think keyword from here let's remove these entity framework codes let's keep this as simple as possible and finally here we have the action methods for delete both get and post okay let's remove existing entity framework codes we don't need this method here now let's remove this async keyword here so that's all about the action methods now let's implement asp.net corporate operations with stored procedures first of all i'm going to design a form for inserting a new book record for that we can make use of this action method and already of the type get for that we have to make the url like this book forward slash add or edit for an insert operation we just need this the url routing of this application is already mentioned inside the startup file here the first word after the uh domain name will be treated as the controller and the default value for the controller will be home after that we have the action method with the default value index value after this action method will be treated as the value of the id parameter so that's how this url works the controller name is book so book forward slash then the name of the action method add or edit in case of an update operation we'll be passing an id something like 5 we can access the same with this id parameter we will discuss that once insert operation is done for now let's look how we can return a fresh form for inserting a new book record inside the action method we have this return statement with the view function so it will look for a view file with the same name as that of the action method add or edit which is here first of all you could see that this view accept a model of the type book view model so from this action method first of all i'm going to create an object of book view model here as book view model is equal to new book view model now let's pass the same inside this view here so currently this object is a fresh instance of book view model with that we can design the form here without changing anything inside this view file here let me save all of these modifications here let's run this application without debug mode so go to debug and start without debugging so this is how the fresh mvc home controller looks like as per the default routing inside this application this home controller will be loaded with the index action method now let's look how this add already view looks like for that we have to use this url here for slash book add or edit boom so here we have a fresh form to insert new books into the application now let's start modifying this application as per our requirement inside this view file here we have a form along with two headings you could see the same here along with that you could see the number and the photo here now where does that come from it's already designed inside this global layout page inside this shared folder underscore layout.cshtml so all of these html declaration with now bar and footer will be the in all of the views throughout the under application inside this global layout you could see that this title is set from this view data with this property title we have passed the value for this title from each of these views files here you could see the same inside this add already here we have set that as edit since we are going to use the same view for both insert and update operation i want to change this title based on the context of operation so i will pass the value for this title property conditionally so we can do this models as you know inside this model object will be having this a model view object that is passed from this get action method here from that we can check this property book id if its value is zero so in that case i will be returning this create book else update book so this property title will be replaced in place of this view data title here so that is what we are seeing inside this tab title here i want to show the same property inside this h1 tag here so we could do this view data inside that we have this title property now we don't need this h4 element here let's build this project again after successful build back to the application reload the page now when we submit this form here it should be posted into this or action method add or edit off the type post we have to mention the same inside this form attribute sp action here currently it is edit instead of that we have to pass add or edit now when we submit this form it will reach up to this post action method add or edit inside this action method we have to implement insert or update operation based on the value of this id parameter here before that let's implement validation inside this form for that we can start with this view model here these two properties title and author are required properties inside this view model so let's specify that with this attribute required let me copy this and pasting here for also also don't be afraid that we are not using entity framework these attributes are related to the validation of model or viewmodel now we have to add one more validation for this price control here the value inside this control must be greater than or equal to one so we can specify the validation with this attribute range so the price should be in between one and the maximum allowed value for integer if there is an error while validating this property we can set the error message here should be greater than 1 or equal to 1 so here we have the if statement with which we will check whether this form is valid or not if it is not valid we will return the same view with the view that we have passed from the form okay now i want to change the form submit button text as submit instead of save let me save all of these modification and build this project here after successful build back to the application reload this page to check the validation let's submit this form without entering any of these fields here form is not valid because of these validation errors you could see the error messages beneath corresponding input controls here now as you enter values into these input controls here the validation errors vanishes it's because of the client side validation present inside our mvc application which is already though mentioned inside our view file add or edit here inside this section it loads this script here underscore validation scripts partial.csshtml inside that it loads jquery validate scripts from this www root file here so these scripts will be rendered in this section script section which is mentioned inside the global layout view here scripts so this section is not required for all of the views if there is a script section in child views like add or edit it will be loaded in place of this line here and thereby inside mvc forms like this we are able to implement client-side validation also so all of these validations will be checked from client side itself without posting this form into our server okay now form as a whole is valid now we can submit this form into corresponding post action method which is mentioned inside the form attribute asp action here currently it is edit we have to change that as add or edit so this form will be submitted into this action method add or edit offset type post now inside this http post action method add or edit we have to handle both insert and update operation based on the value of id the value for this id parameter will be same as that of the id that we have passed with this get action method here based on the value of this id we can decide whether we have an insert or update operation for that first of all we will be creating a stored procedure inside our sql server db here so here is my management studio in order to create a stored procedure let's expand this program ability here now right click on stored procedure click on stored procedure here so let's create the stored procedure for both insert and update operation to save the time i will be pasting the stored procedure assuming that you are pretty confident about your skills on writing stored procedure that's why you have chose this uh method or ndd framework so here is our stored procedure book add or edit here we have the parameter for this stored procedure all of these parameters values should be passed from our action method if book id is 0 we will insert a new record into books table here and inside the else part we will update the record with the given id with new updated details now to create this stored procedure click on execute here newly created stored procedure can be seen inside this stored procedures folder here so here it is book add or edit now before calling this stored procedure from our action method let's create all of the stored procedure related to books table here now let's create a stored procedure for retrieving all of the requests from this books table here so let me create a new stored procedure i have already prepared the scripts let me paste that here so here we have the stored procedure book view all inside that we have executed this sql query select start from books to create this third procedure click on execute now let's create another stored procedure for retrieving a given book record so here it is book view by id here we have this equal query to retrieve a given the code with the given id so far we have created three stored procedure now let's create the final one for deleting a record with a given id so here it is book delete by id here we have the corresponding sql script click on execute so that's it we have created all of this root procedure required for implementing correct operations inside our asp.net core mvc application now back to the visual studio here now inside this add or edit action method let's execute the stored procedure book add or edit for that first of all we have to configure the connection string inside this application it should be mentioned inside this app settings json file here here you could already see a connection string which is added as part of creating this book controller using the entity framework scaffolding item we are not going to use this connection string here instead of that we will add a new connection string here dev connection first of all we have to specify the instance name of the sql server and with this database we have to provide the corresponding database name which is books db since we are using local authentication we have set this trusted connection as true in order to execute multiple query batches we have set this property multiple active result sets as true now that's all about the db connection string now from this action method we have to execute the stored procedure book add or edit how can we do that when this connection string is specified inside app settings.json if we were using entity framework core we will be injecting the db context class into this controller and rest of the action method can make use of that to interact with the db there are different ways to access the connection string or the corresponding db provider interface in our controller here in this application we will be using this simple method by default inside this startup class here this i configuration is already added into this startup file here through this iconfiguration property we can access this connection string here so inside all of the mvc controllers we are going to inject that i configuration in our controller constructors so we just need to do this i configuration as configuration so whenever we make a request into mvc controllers corresponding controller clause instance will be created and the same time this constructor will be executed the value for this constructor parameter will be passed from this startup clause here now to save this configuration we are going to create a private property inside this controller for that you could use this shortcut just place the cursor on this i configuration here then you could see this light simple here expand it and select this option create and initialize field configuration so here we have the private property to save the injected configuration object for private properties i will be using this prefix underscore like this underscore configuration we will update the same inside this constructor also now rest of the action methods inside this controller can make use of this private property to access the configuration that we have saved here let's do that with this post action method here first of all we need an instance of sql connection class for that first of all we have this using statement here as a resource for this using statement or using block we have ql connection as sql connection and here we are creating a new instance of the clause sql connection into that we have to pass the corresponding db connection string and we can access from this private property configuration just call this function get connection string inside this function we just need to pass the name of the connection string that we have saved here dev connection okay you could execute sql queries or stored procedure without using this using block here but if we use using blocks for chop class or instances which has the interface i disposable we don't have to destroy that object after using the instance this using statement will take care of that we just need to open this connection here and we don't have to close the connection it will be automatically done by this using block here first of all let's open this connection sqlconnection.open function can be called now to execute the stored procedure we can create an instance of sql command here sqlcmd new sql command so that we have to provide the name of the stored procedure which is book add or edit as a second parameter we have to pass the sql connection object just below that we have to provide the type of the sql command which is stored procedure so command type dot store procedure now we have to pass values for the stored procedure parameters book id title author and price for that we could do this parameters dot add with value function can be called as a first parameter we have to pass the name of the um parameter which is book id without this at the rate simple just pass the rest of the name book id we could pass the value from this book view model object or parameter here now i have passed rest of the parameters values like this finally to execute the stored procedure we just need to call this function execute non-query so this single stored procedure will take care of both operations insert or update based on the id that we have passed here corresponding to this book id inside this uh add or edit view we have this hidden field here and rest of the property value is coming from the corresponding input controls after successful insert or update operation will be redirected to the index view and we have already discussed about this uh form validation if there is any error with the validation the same view will be returned with the given book view model if this operation is successful this action method will be redirected to this index action method here inside that we have returned this view index dot cshtml inside this view we have to retrieve all of the records from our tab this view is created with the entity framework scaffold item so we have to make few modifications inside this view here for now i will just remove all of the uh lines of code which may cause the problem so for now i will just uh comment this table here we can modify this view in a bit for now let me save all of these modifications here let's build this project again i'm sorry for interfering let's get straight to the point as a developer you already know big projects or lengthy tutorials like this requires a lot of work and time in making so here i'm asking for your little financial support it will definitely motivate me to bring more and more helpful and quality free tutorials like this in front of you hope we can learn and grow together like this please check out various payment methods for donation from video description or you could shop with our amazon affiliate links without being any extra penny back to the application reload this form in order to avoid this autocomplete or auto prediction from previously posted data we can set this up attribute for the form auto complete as of to test the insert operation let me add a new book into the table so which is code complete this is a must read book for anyone who code i have given links for such helpful books in all of my videos description and the most recommended books you could check that from video description now let's provide the author and finally the price now let's submit the form boom since we are redirected to index action method hope everything been successful let's check that inside our books table here let's execute this select star statement here so here we have the new book for code complete now let's look how to retrieve data from this table without using entity framework for that let's update this index action method here first of all we have to call this a stored procedure book view all okay for now i will just copy paste this using statement from add or edit instead of sql command we need sql data adapter as sql da and here we have to pass the name of the stored procedure which is view all book view all let's pass sql connection as a second parameter now we'll set the command type here sqlda since we have a select command we can set the command type with this property select command command type is equal to command type dot store procedure now to save your request retrieve from the stored procedure we need a data table so data table as dtbl is equal to new data table now to fill the retrieval course into this table we just need to call the function fill from sql data adapter now we will pass the same into our index view here now with this data table we have to display retrieve the course inside our index view here so first of all at the top we have to specify the model type here model as system dot data dot data table i will set this title as list of books i will set the same inside this h1 so i will paste that here now in order to add some styles to this header i will be using this uh class from bootstrap which is jumbotron let's enclose this header with this jumbotron div here to send that this header we can add this class text sender now let's uncomment this table here let's add one more class from bootstrap for this table table borders and we want to set a background for this double header so t head light can be applied here let's modify this table column header book title author and price inside this final column we'll be showing these action buttons for edit and delete and inside this header i want to show this add hyperlink here let me cut this and paste here let's get rid of this paragraph from here in order to insert a new record we will be using this add or edit so add or edit instead of these are four each we'll be using for loop so we want to i trade this for loop as much as records inside the table so model dot rows dot count now here we have to show the book title for that we can do this model dot rows and the row index can be accessed from this i here and then title property now let me copy this and pasting for rest of the columns here this is for author and price and here we have the final column for action buttons we don't need this details here for this attribute sp route id we have to pass the corresponding book id that we can access like this let me copy this and pasting here book id let's pass the same here for our delete button also let me save all of this modification and build this project again after successful build back to the view reload this page boom so here we have retrieved all of your requests from our skill server table through stored procedure in our asp.net core web application if you want to add a new book you can click on this create new button here it will open up a fresh form to insert the book record if you want to go back to the list you can click on this back to list here let me add one more book here this is another great book for developers from robert c martin clean code submit the form that's it here we have added our second book also now if you want to edit a record you can click on this edit hyperlink here with this hyperlink we will make the http request into this action method add or edit of the type get here we are already using this action method for returning a fresh form for insert operation now to return a form for update operation with the given id we have to make few more modifications here for that first of all we will be adding this if statement here besides that we will check whether this id is greater than zero or not if it is greater than zero we have to retain a form with a corresponding book details inside the form controls for that i'm gonna define a separate method here so here is the method fetch book by id for this function we need a single parameter id and from this function we just need to retain a book view model this function is not an action method so we have to mark that with this attribute known action first for inside this function i will create an object of the view model here inside this function we just need to call this stored procedure here book view by id for that first of all i will copy paste the using statement from this index action method here first of all i will be creating a data table here dtbl the name of the stored procedure is book view by id let me copy the same from here for this stored procedure we have a single parameter book id so let's pass the corresponding value here select command dot parameters dot add with value book id and let's pass the id here for the proper order i will just uh set the command type before passing the parameter value here so by calling this fill statement here the data table will be populated with the corresponding book record for that i will add this if statement here first of all we will check whether this data table has a single draw only so first of all we have the book id here we can access the same from the data table ddbl dot rows from the first row we can retrieve the book id like this we have to populate three more properties so let me copy paste this three times below this is for title author price sorry we forgot to call this tostring function here to convert the same into corresponding string representation since both of these book id and price are in integer we have to convert this string into integer so we can call this function convert to int 32 let's call the same function for this price property also and finally from this method we will return the book view model now we can call this function inside this add or edit action method here book view model is equal to fetch book by id into the function we just need to pass this id parameter here it will return the corresponding book view model for the given id and that object will be passed to this add or edit view here inside that we have already designed the form for both insert and update operation we don't have to make any change for this update operation inside the form now with this hyperlink here we have to make the http get request into this arrow edit action method here for that we have to update the corresponding hyperlink action method as add or edit here reload the page again now let's try to update this book record here boo we have shown this header conditionally and all of these form controls are populated with the corresponding record details now after making required changes inside these form controls user can submit this form this form will be submitted to the corresponding post action method add or edit here inside that we have already handled both insert and update operation with this stored procedure book and or edit let's try the update operation here in order to verify update operation is working for all of the columns inside the table i will be adding this uh suffix astic marks to string properties and the number of fields will end up with two zeros now let's submit the form boo so here we have implemented the update operation with stored procedure in this dotnet mvc application now let's implement the final correct operation delete here for that here we have the get action method delete with a parameter as id for now from this action method we will just return this delete view here now into this view we have to pass the book view model with the given id so we can do this we can call this function fetch book by id into that just pass this id here now let's pass the same object into the delete view here now let's look what we got inside this delete view here i will change this title to delete book i will show the same inside this h1 element here with this view we are just confirming this operation from user side are you sure to delete this record or not this view is created based on book view model object we have passed the same from this action method here also so we don't have to make any of the modifications for these properties here with this dl element here we are just showing all of the properties values inside the view if you so want to confirm the delete operation you just need to click on this delete button here let's look how this delete view looks like click on delete boom so here we have the view asking are you sure to delete this record or not if we click on this delete button here it will reach up to this post action method delete confirmed here the name of the action method is separately mentioned with this action name here so we can so we will be using delete as an action name for this method here here like you can see here action method is specified as delete now inside this action method we just need to execute this stored procedure here book delete by id for that i will copy paste this using statement from add or edit post action method here book delete by id we have only one parameter which is book id and we can pass that from this id parameter here we will execute the stored procedure with this function called here after successful delete operation the user will be redirected to this index action method here let's look whether it is working or not after successful build back to the application now once again back to the list click on delete confirm the operation by submitting this form with this delete button here boom here we have successfully deleted the corresponding book record so that's it guys in this project we have connected to sql server database without entity framework and we have implemented all of the correct operations using stored procedure in asp.net mvc application you could download this project source code from the github link given below in video description if you found this video helpful please thumbs up this video and for more awesome videos like this please please subscribe to our channel called affection please share this video with your friends and colleagues so that they can benefit from this have a nice day bye

Channel: CodAffection

Views: 39,286

Keywords: asp.net core mvc crud without entity framework, asp.net core mvc crud example, crud operations in mvc without using entity framework core, .net core crud with ADO.NET, retrieve data from database without entity framework, stored procedure in .net core without entity framework, connect to sql server without entity framework, execute stored procedure in asp.net core, CodAffection

Id: HupmPny23pE

Length: 44min 30sec (2670 seconds)

Published: Mon Nov 02 2020