CRUD Operations using ASP NET Web API and SQL Server | Database Connection Using ODBC

in the first part of this video I will show you how to perform crude operations using asp.net web API and SQL Server database so we will use SQL client namespace and SQL queries in the second part of the video I will show you how to connect to a database using odbc drivers so the advantage of odbc is that it allows a single application to access different database Management Systems using the same source code using odbc we can create an asp.net application that has a connection string that contains the driver parameter so in this example we have driver equal to SQL Server driver this means that the application will connect to the SQL Server driver which connects the application to the SQL Server database and to connect the application to post degree SQL we need just to update the connection string so here the driver is now equal to post degree SQL driver so the application will connect to this driver which connects the application to post degree SQL database and to connect our application to any other database system we need just to update the connection string let's create a new project then in this window we can select C sharp and web and here we need to select asp.net core web API then next let's call this project web app the next in this window we can accept this default configuration then let's click on create then let's delete this default controller Also let's delete this model now let's create a new database and a new table so let's go to server Explorer let's create a new connection so if you don't have Microsoft SQL Server then you can click on change and then we need to select Microsoft SQL Server then ok then let's provide the server name so it is available on my computer so let's write dot then backslash then the instance name which is SQL Express let's use the default authentication method and here we need to create a new database and we can call it web app then ok so this database does not exist and we need to create it so let's click on yes so this database is empty for the moment and we need to create a new table we can create a new table using an SQL query so let's make a right click on this connection then new query then let's write the SQL query that allows us to create a new table so we will create a new table called products it contains the ID which is the primary key and also it is auto incremental we have the name of type and worker the brand the category the price which is of type decimal then we have the description also we have created a two chains of type date type and it has a default value which is the current timestamp now to execute this query we can use this button and here we can see that the query has been executed correctly now to find the table we need to refresh the connection so we need to select this connection then refresh and here we can see that we have this new table we can expand it and these are The Columns of this table now we need the connection string that allows us to connect to this database so we can make a right click on this connection then properties and here we have the connection string so let's copy it then let's go to our project and let's open this file up settings.jsort then we need to create a new key in this file to save the connection string so let's add comma then let's create a new key so we have connection strings already suggested let's select it and it is equal to an object so inside this object we can Define any number of connection strings so let's create our first connection string we can call it SQL Server DB and you can paste the connection string just here now let's save this file now we need to create a new API controller that allows us to perform crude operations on products so we can create it in the controllers folder we can make a right click then add then controller then let's select API controller then API controller empty then add and let's call it products controller so this controller will be accessible at this URL now we need to read the connection string from app settings so we need to create the Constructor and you need to request an object of type I configuration from the service container let's call it configuration then let's create a field of type string where we will save the connection string let's call it connection string then let's initialize this variable in the Constructor so it is equal to this configuration and between brackets we have to provide the path of the connection string which is available in up settings so here we need first the name of this key let's copy it and here between double quotes we can paste the name of the first key then column then we need the second key let's paste it so here we have a warning because this configuration can return null but we have a string that cannot be null so we can use the W question mark operator then a default value this means that if configuration is not null then connection string will be equal to the value that is available in up settings otherwise connection string will be equal to this empty string now we can use this connection string to connect to the database now we need to create two models the first model allows us to read the product details from the client and the second model allows us to read the product details from the database so let's create a new folder then add the new folder and let's call it models then let's create the first model so let's add a new class and this model will be used to receive the data from the client so we can call it product dto then let's add the properties that we should receive from the client so here we have the name the brand the category the price which is decimal and the description so all of these properties are required and we can decorate them with the required attribute and let's create a new model let's call it product this model will be used to read the data from the database so we need to create properties that correspond to the different Columns of the products table in the database so here we have the same properties of product DTU in addition to the product ID and the database and created at let's save this file now let's create the action method that allows us to create products so it will be accessible using the HTTP post method we need to decorate it with HTTP post then let's create a method called create product and is a parameter this method requires an object of type product DTU let's call it product D2 then let's return a success response now we need to connect to the database so let's add track h block in the catch we have an error so we can add an error to the model State and we can return a bed request so here we add another to the model state which is related to product and the error message is sorry but we have an exception then we return a bad request that contains this model state in the try we need to connect to the database so let's create an SQL connection and we need to provide the Constructor with the connection string so here we can see that we have an error and to fix it we need to install a new package so we can either install system.data.sql client or microsoft.data.sql client in my case I will install this package so let's click on find and install latest version now we can see that the error is fixed and if we scroll up we can see that we have this new namespace now let's open the connection then let's create the SQL query that allows us to create a new product so here we will insert a new product into the products table and we will insert the name the brand the category the price and the description then the values will be these parameters so now we need to replace these parameters by the corresponding values that are available in product DTU so let's create an SQL command so we can create this SQL command using this Constructor that receives the SQL query that we need to execute and the SQL connection which is this connection now let's replace these parameters by their values so we will replace the name which is this parameter by the name that is available in product dto then we will replace the brand then the category then the price and finally the description now we need to execute this SQL query so we can use this statement command dot execute non-query Let's test the application so we have this warning because we are using https with a self-assigned certificate so let's click on advanced then accept the risk and continue and here we can see that we have this controller with this endpoint let's expand it then try it out then let's fill this object then execute and here we can see that we have a success response now let's take a look on the database so let's make a right click on products then show table date and here we can see that we have this product now let's create a new action method that allows us to read the products it will be accessible using the HTTP get method so we need to decorate it with HTTP get and let's create a method called get products so this method does not require any parameter and it returns a list of product objects so here let's create a list of products let's call it products and let's initialize it then let's return a success response that contains this list so the type that we have here is this product model that we created now we need to connect to the database so let's add try catch in the catch we have an exception so we can return a bed request so let's copy these two statements and let's paste them just here in the try we need to connect to the database so let's create an SQL connection then let's open the connection then let's create a SQL query that allows us to read the products from the database so here we will read all the columns from the products table now we need the SQL command that allows us to execute this query then we need the SQL data reader that allows us to read the data from the database so here we will create a reader using command.execute reader and then we need a while loop to read the available rows and the products table so here it will be while reader.read now for every product that we have in the database we need to create an object of type product and we need to add it to this list so we need to read the ID the name the brand the category the price the description and created at so the ID is an integer so to read it we need to use get in 32 and 0 here is the index of the ID and the products table so the ID is the First Column so its index is zero and the name is the second column so its index is one so the name is of type string and to read it we have to use get string then here to read the price we use in get decimal and to read created at we use get date type now let's add this product to our list Let's test the application then let's use this endpoint so here we have a successor response that contains a list of products and we have only one product now let's create a new product let's fill this object then execute and here we have a success response let's see the available products so now we have two products now let's create a new action method that allows us to read a product by its ID so this method will be accessible using the HTTP get method so let's decorate it with HTTP get also we need to add the product ID to the route so here let's add parenthesis then double quotes and then we need to add brackets and then we need to add the parameter ID so now we need to read this ID in order to read the product having this ID so let's create a new action method we can call it get product it requires a parameter which is the product ID that we have in the route and then let's create a product and let's return it so here we return a success response that contains this product now we need to connect to the database to read the product having this ID so here let's add try catch in the catch we have an error so we can return bad request so let's copy these statements and let's paste them just here in the try we need to connect to the database so we need to create an SQL connection then let's open the connection then let's write the SQL query that allows us to read the product having this ID so we will edit all the columns from the products table where the product ID is equal to this parameter now we need to replace this parameter by the value of this variable so let's create an SQL command then let's replace this parameter by this variable then let's execute this SQL query and let's create an SQL data reader to read the result so here we are reading products by ID this means that we have at most one product so we don't need a while loop but we need an if condition so if we found a product having this ID then we will save the product details into this object so we will save the product ID the name the brand the category the price the description and created at but if we did not find any product in this case we can return not found so let's add else return not found Let's test the application then let's test this endpoint so let's read the details of the product to the id1 and here we have the product details now let's read the details of the product to the id2 and we have the product details now let's provide an ID that does not exist 20 for example and this time we have not found now let's create a new action method that allows us to update products so it will be accessible using the HTTP put method and we need to add the product ID to the route then let's create a method and let's call it update product it requires two parameters the product ID which is available in the route and an object of type product DTU then let's return a success response now we need to connect to the database so let's create a try catcher block in the catch you can return a bad request so we can copy these statements and let's add them just here in the try we need to create an SQL connection then let's open the connection then let's add the SQL query that allows us to update the product so we will update the products table and we will set the name the brand the category the price the description where the product ID is equal to this parameter now we need to replace these parameters by their values which are available in this product dto object so let's create an SQL command then let's replace these parameters by their values so we will replace the name the brand the category the price the description and the ID now let's execute this SQL query so we need to write command dot execute non-query now let's test the application and here we can see that we have this new endpoint which is accessible using the put method first let's take a look on the available products and let's update this product with the ID3 so here we have to provide the product ID and then let's fill this object then execute and here we have a success response with no data now let's take a look on the available products so let's click on execute here and now here we can see that we have the updated data now let's create a new action method that allows us to delete a product it will be accessible using the HTTP delete method so let's decorate it with HTTP delete and let's add the product ID to the route then let's create a new action method called delete product it requires the product ID as a parameter and let's return a success response then let's connect to the database so let's add Ray catch block in the catch we have an error so we can return a better request so let's copy these statements and let's paste them just here in the try let's connect to the database so let's create an SQL connection then let's open the connection then let's write the SQL query that allows us to delete a product having this ID so we will delete a product from the products table where the product ID is equal to this parameter now we need to replace this parameter by this variable so let's create the SQL command then let's replace this parameter by this variable then let's execute this query so we have to add command dot execute non-query now let's test the application now we have this new endpoint that allows us to delete a product by ID so first let's create a new product then execute then let's see the available products so we have this new product and let's delete it it has the id4 so here let's provide the ID which is for then execute and here we have a successor response with no data now let's take a look on the available products and this time we can see that we have a list that contains only three products so the product with id4 has been deleted in this video I will show you how to connect to different database systems using the var2dbc drivers these drivers are available at this page and you can find this link in the description of the video so here we can see that the Varto DBC drivers are fast and widely used and we can use them for free during 30 days also we can see that they have a large number of odbc drivers and to download the drivers we can click on this download button in this page we can see that they have 65 odbc drivers and you can scroll down to see the available drivers in this video I will show you how to use two drivers the odbc driver for SQL server and odbc driver for post degree SQL so we can download the odbc driver for SQL Server from this page you can find this URL and the description of the video so here we can see that the diverto DBC driver for SQL Server is a high performance data connector and it uses a direct connection to increase the data transmission speed also we can download the odbc driver for post degree SQL from this page and you can find this URL in the description of the video here we can see that the diverto DPC driver for post degree SQL is a high performance connectivity tool it uses a direct connection to increase the data transmission speed now let's create a new API controller that connects to the database using odbc so we can copy this controller and we can paste it inside the same folder now let's rename it and let's call it products or DBC controller then let's rename this class Also let's rename the Constructor and to use odbc we have to replace this Constructor with odbc connection so let's delete SQL and let's write odbc so we have an error this is because we need to install a new package so here let's select install package system.data.odbc then find and install latest version now we don't have the error anymore so also we need to replace SQL command with odbc command let's do the same thing with get products and with get product then let's do the same thing in update product now let's update delete product so odbc connections do not support these parameters and we have to replace them with question mark so here let's delete these parameters and let's add question mark instead foreign [Music] let's do the same thing here and also let's update this action method now we need the connection string that allows this controller to connect to a database using anodbc driver so I already installed these two drivers this is divert to DBC for SQL server and this is divert to DBC for post degree SQL so now let's make a search and let's type odbc then let's select odbc data source for 64-bit systems then let's go to drivers and we can see that I have the devarto DBC driver for post degree SQL and also the driver for SQL Server now let's click on systemdsn then add and here let's create a connection for the SQL Server so let's select SQL Server then finish then let's provide a name for this data source then we don't need the description and here let's write dot backslash then SQL Express let's select Windows Authentication and for the database I want to connect to the database that I have created which is web app Let's test the connection and here we have an error this is because SQL Server by default does not enable TCP IP so we need to enable TCP let's click on OK then let's click on search then let's write SQL Server configuration manager so in my case I have SQL Server 2016 let's select this option then in this window we have to expand this node SQL Server network configuration then let's select protocols for SQL Express and we can see that tcpip is disabled so let's enable it let's make a right click then enable then ok then let's see the properties of this connection so let's make a right click then properties let's go to IP addresses and here we have to keep this field empty and for the TCP Port we can use this default port number which is 1433 then ok and now we need to restart the SQL Server so let's go to SQL Server services then let's make a right click on SQL Server then restart now let's close this window and let's test the connection again so this time we have a successful connection so let's click on OK and to find the connections the ring we can click on this button so this is the connection string that we have to use let's copy it then close then here let's click on OK so we have a new DSN now we can click on OK let's go back to visual studio and let's create a new connection string let's call it odbc SQL Server DB and let's paste the connection string just here now let's copy the name of this connection string and let's use it and protect a DBC controller so just here we have to replace this connection string by the new one let's save the files and let's test the application so now we have this new controller so let's take a look on the available products and here we can see that we are able to access to the SQL Server database using the diverto DBC driver let's create a new product then execute so we have a success response now let's take a look on the available products again and we have this product with the id5 now let's connect our application to a post degree SQL database so I already created this database called web app using post degree SQL and it contains the products table so here we have the same Columns of the products table and the SQL Server database let's click on search and here let's type odbc [Music] then let's select setup odbc data source for 64-bit systems now let's click on systemdsn then add and this time we need the connection string to the odbc driver for post degree SQL so here let's select the Varto DBC driver for post degree SQL then finish then let's provide a name to this data source so the server is localhost and this is the poor number of post-degree SQL now let's provide the user ID and the password then let's provide the name of the database which is web app and the schema is public Let's test the connection so here we have a successful connection then let's click on this button and let's copy the connection string let's click on OK then ok now let's create a new connection string let's call it odbc post degree SQL DB then let's paste the connection string now let's copy the name of this connection string and let's use it in products odbc controller let's save the files and let's test the application now let's take a look on the available products so this time we have an empty list of products let's create a new product then execute and we have a success response let's take a look on the available products so this time we have a product let's take a look on the products in the post degree SQL database so let's make a right click on products then view edit date then all rows and this time we can see that we have this product

Channel: BoostMyTool

Views: 6,405

Keywords:

Id: cVz5oWRGbYA

Length: 38min 31sec (2311 seconds)

Published: Mon Jun 12 2023