Solved

reading excel file in asp.net and inserting record in sql server

Posted on 2013-06-15
4
964 Views
Last Modified: 2013-06-17
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
0
Comment
Question by:ronipats
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 8

Accepted Solution

by:
rpkhare earned 500 total points
ID: 39250159
(1) You first read the Excel file and convert it to XML. To do this refer this link:

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
0
 
LVL 36

Expert Comment

by:Miguel Oz
ID: 39250701
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.
0
 

Author Comment

by:ronipats
ID: 39252766
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
0
 
LVL 8

Expert Comment

by:rpkhare
ID: 39253906
Please try this and let me know:
CREATE PROCEDURE demoProc 
(
      -- Add the parameters for the stored procedure here
      @tbl_demotbl demotbl readonly
)
AS
BEGIN

Open in new window


Seems to be a case of missing ")".
0

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question