Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.
reading excel file in asp.net and inserting record in sql server
I have written one application in ASP.Net (C#).
I want to read a excel file and insert all record from excel file to sql server table.
Say for example i have two column in excel file (student_id and student_name). I have 100 rows in this file. Now i want to read all record from this file and insert it into a table in sql server.
For inserting a record i want to write a stored procedure. Currently i am inserting record for executing for loop. so in this case, i am first reading file and populating data to data table variable. then for iterating through each record in table and calling stored procedure to insert that row.
is there any way where by i can send data table to sp as parameter and insert all record from datatable to database.
thanks
I want to read a excel file and insert all record from excel file to sql server table.
Say for example i have two column in excel file (student_id and student_name). I have 100 rows in this file. Now i want to read all record from this file and insert it into a table in sql server.
For inserting a record i want to write a stored procedure. Currently i am inserting record for executing for loop. so in this case, i am first reading file and populating data to data table variable. then for iterating through each record in table and calling stored procedure to insert that row.
is there any way where by i can send data table to sp as parameter and insert all record from datatable to database.
thanks
Asked:
2
1 Solution
If you are using SQL server 2008, you can send the datatable as parameter as shown in the link below: (You have to create a table type in SQL Server)
http://msdn.microsoft.com/en-us/library/bb675163.aspx
More examples are shown here:
http://stackoverflow.com/questions/2784851/passing-a-datatable-into-a-stored-procedure-is-there-a-better-way
http://www.codeproject.com/Articles/412802/Sending-a-DataTable-to-a-Stored-Procedure
Note: There is no need to use XML conversion.
http://msdn.microsoft.com/en-us/library/bb675163.aspx
More examples are shown here:
http://stackoverflow.com/questions/2784851/passing-a-datatable-into-a-stored-procedure-is-there-a-better-way
http://www.codeproject.com/Articles/412802/Sending-a-DataTable-to-a-Stored-Procedure
Note: There is no need to use XML conversion.
i have created one user defined tabletype in sql server 2008.
Then after i have created on stored procedure in which i have declared one parameter of new created type as
ex.
this is how i created new table type
USE examnewdb
GO
-- Create the data type
CREATE TYPE demotbl AS TABLE
(
course_name varchar(100),
course_code varchar(50)
)
GO
this is how i created stored procedure
CREATE PROCEDURE demoProc
-- Add the parameters for the stored procedure here
@tbl_demotbl demotbl readonly
AS
BEGIN
It is showing me error as "the parameter @tbl_demotbl has an invalid type. It cannot be declared READONLY since it is not a table valued parameter."
what should i do to resolve this error
Then after i have created on stored procedure in which i have declared one parameter of new created type as
ex.
this is how i created new table type
USE examnewdb
GO
-- Create the data type
CREATE TYPE demotbl AS TABLE
(
course_name varchar(100),
course_code varchar(50)
)
GO
this is how i created stored procedure
CREATE PROCEDURE demoProc
-- Add the parameters for the stored procedure here
@tbl_demotbl demotbl readonly
AS
BEGIN
It is showing me error as "the parameter @tbl_demotbl has an invalid type. It cannot be declared READONLY since it is not a table valued parameter."
what should i do to resolve this error
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
How to Convert Excel to XML in C#.NET
(2) Now take an input parameter in your stored procedure of type XML.
(3) Pass XML to stored procedure.
(4) In stored procedure, read this XML file as mentioned in the solution below:
Convert Xml to Table SQL Server