How to Import Data from Excel File to Database Table Using ASP.NET Core and Entity Framework

hello in this video we will see how to read and upload Excel file in Microsoft SQL Server we will be using asp.net core and Entity framework this is the same Excel file we used in the last video in which we read and displayed the content of the file using asp.net core someone requested to create a similar tutorial but instead of displaying the content of Excel file he requested to insert all data in the database let's begin the tutorial first open visual Studios and click create a new project button these are all project templates you can use this search box to find Project template or use this filter controls to filter the template list let's search for asp.net core web app and select the template with c-sharp and MVC option press the next button now name the project and select the path where you want to save the project once done press the next button in this step we will choose the latest.net 7 framework and leave the authentication type as none we will go with HTTP and leave the docker support unchecked now press the create button to create the project after the project is created select IIs Express from the drop down here then click the play button to run the project there is no error in the project now we will install some packages for database connectivity to install a package open solution Explorer right click on the project name and select manage nuget packages in the manage nuget packages window click on the browse tab here we will search for the first package which is Microsoft Entity framework core this package provides support for Entity framework code now select the package and click on install button press ok press I accept after the package is installed search Microsoft Entity framework core dot SQL Server package this package enables us to use Microsoft SQL server in Entity framework core click on install button press OK button press I accept next install Microsoft dot Entity framework core dot tools package this package helps us to run the migrations follow the same steps to install the package lastly we need to install the design package repeat the same steps this package is used for scaffolding database related code in Entity framework code now that we have installed all four packages let's verify all four packages are installed and they are of the same version to do this we will click on install tab in the manage nuget packages window clear the filter to view all installed packages all packages are installed of the same version let's move forward let's close this window next open app settings.json file from the solution Explorer this file contains configuration settings of the application inside the app settings.json file we will create a new section called connection strings in this section we will Define our database connection string let's define our database connection string data source is equal to this is the server address in my case it will be localhost as my database is on the local system next is initial catalog which will be the database name next will be user ID of the database next database password persist security info and the last one is trust server certificate all right let's create a folder named data within the project now add a class in data folder and name it application DP context this class will handle the communication with the database we will inherit this class from DP context class put the cursor on DP context and press Ctrl plus period button click on using Microsoft Entity framework core next Define The Constructor to complete the code you can press the tab button okay now we have to create a model as we will be using Code first approach we will come back to this file after creating model open solution Explorer now right click on the model folder go to add and select class name the class student we will be having three members in this class first will be the primary key ID next name of student this will be string last one will be marks and it will be of type end make the default value of name String dot empty now go back to application DB context class at the end of this file add a DP set of student class if we do not Define this DB set we will not be able to get this model in the database again click on the error and press Ctrl plus period button include this class now open program.cs file from your solution Explorer here we will Define a variable named connection string we need to initialize this variable from the connection string in app settings copy this default connection from App setting and paste it here now we will check if connection string is null or empty then we will throw an exception next we will configure application DP context this allows us to easily access the database within our application also specify that application DB context should use SQL Server as a database provider and we will pass the connection string variable here next we will enable runtime compilation for razor Pages which means we can make changes to our Razer files and without requiring a rebuild of the entire application the changes will be reflected in the browser this feature speeds up the development and debugging process the server is coming because this feature requires another package now again open nuget Package manager Now search for Microsoft dot asp.netcore.mvc dot Razer dot runtime compilation install this package now the package is installed and you can see now the error is gone next open SQL Server management studio and connect the database using Windows Authentication expand the database folder here we do not have student DB that we mentioned in the connection string this database will be created using a command that we will run in some time and before that we will create a user that we defined in the connection string to create a user expand this security folder right click on the logins folder and click on new login select SQL Server option write the login name enter the password and confirm password uncheck enforce password policy go to server role now make this user system administrator go to status and make sure all permissions are granted and user is enabled press the OK button now in the object Explorer click the connect button connect the localhost server using SQL Server Authentication enter the new user credentials we just created click on connect the database is connected successfully so this verifies that the user is working now let's go back to visual Studios open Package manager console if you do not see this window go to view click on other windows and select package manager console now right command add migration and give migration a name after the migration is run a script file will be generated this script represents the changes that will be applied to the database however executing the migration script alone will not create the database let's verify this go to SQL Server management Studio refresh this database section and see no database is created to apply these migration changes to the database we need to run update database command now again open Package manager console and run this command now open SQL Server management studio and refresh the database section here we see that student DB database has been created the students table is also created right now the table is empty all the columns are created with ID as primary key column now open underscore layout file located inside the shared folder in views these are menu items copy and paste this menu item name the menu item upload Excel file next write its section and name it upload Excel now we need to Define this action inside the home controller this section will be called when the menu item is clicked now open the home controller write the action this section will simply return a view currently this action has no View to create a view right click on the action and select add View select empty Reserve View press add button name this view same as action name delete this code now create a form with type equals post and encoding type will be multi-part form data as this form will post files next add a button to browse file add another button to upload the content of Excel file in the database this button will call upload Excel action of Home controller and submit the form now come back to controller and write the action with HTTP post this section will be called on button click file data will be available in this iPhone file parameter first we will check if file is not null and its length is greater than 0 then we will continue with the upload and read process now we will Define a variable in which upload path will be saved next check if the folder does not exist then create the folder now combine upload folder path with the file name in the end we will write the file to the path and return The View till now file upload is done let's run and test the file upload code click upload Excel file choose file click upload Excel file button file is uploaded now go to solution Explorer inside ww root folder expand uploads folder here is the uploaded file so the file is successfully uploaded next we will use a nuget package that will help us read Excel file open nuget Package manager and search for Excel data reader install this package now open the official link of this package here you will find the code to read Excel file copy this script and paste it right after the upload code remove this error by adding file.io before file now we can read Excel file with any one method of the given two methods we will be using method 1 as it reads Excel file line by line let's delete all extra code and comments so in this while loop the reader will be reading each row of Excel file let's open the Excel file so here we have three columns we only need column number two that contains name and column number three that contains marks we will skip column number one also we need to skip the header row now come back to visual Studios inside this while loop we will get complete Row from Excel now we will create a student object here this will store student information in each row of excel s dot name is equal to reader dot get value from index number one s dot marks is equal to convert the marks to integer reader dot get value from index number two now we need to insert this object in the database go on top of the controller here we will Define a variable application DB context underscore context pass the variable here Now set underscore context is equal to context now come back to where we created the student object here write underscore context dot add student object s then await underscore context dot save changes async and this will save the record in the database now we have a last simple task we need to skip the header row Define a boole variable here and set it to false now we will check if the variable is false then make it true and continue the loop so now the first row will be skipped let's run the code choose file press the upload button this is the same error we faced in the last video let's Google it again open the stack Overflow link just copy and paste this line at start of the action run the project again choose file click on upload button OK the file is uploaded let's open SQL Server run this query okay we have successfully inserted Excel data in SQL Server next we will show a success message after the file gets uploaded right now we do not get any notification of success so we will simply return a message in view bag and after the if return view back Dot message is equal to empty now open The View here create an if condition that if you back Dot message is not null or empty and message equals success then show an alert here add message all record inserted successfully copy and paste this replace the success with empty here it will be a warning message write the message here file is empty run the project let's first delete the records okay the records are deleted browse the file click on upload button here is the success message so we have successfully uploaded the Excel file in the database thank you very much for watching this video please do support me by buying me a coffee the link is in description also I have uploaded the code of the video on GitHub its link is also in the description please do like my video if you learn anything from it and subscribe to my channel this will give me motivation to create more videos you can also give your feedback in the comments section see you next time with some other tutorial

Channel: Dex Code Lab

Views: 620

Keywords: how to import data from excel to sql server, import data from, import data from excel, import data from excel to sql server, excel to sql server, excel to database, import excel to database, import excel using asp.net, Read and Save data of Excel into SQL Server Database, excel read and save asp.net core, save excel data asp.net core, read save excel asp.net core, import data from excel file to database table in asp.net core

Id: 9JnaiQPbMG4

Length: 25min 23sec (1523 seconds)

Published: Tue Sep 19 2023