We help IT Professionals succeed at work.

bulk insert from asp.net textarea

mattegol
mattegol asked
on
Medium Priority
598 Views
Last Modified: 2012-05-07
Is there a way to do a bulk insert from a asp.net textarea instead of a file?
Comment
Watch Question

Assuming you are using Oracle as database you can define the field as a clob in back end
Here is an hands on on inserting data to CLOB

http://www.codeproject.com/KB/database/C__and_Oracle.aspx

Author

Commented:
I'm using mssql 2000

Author

Commented:
To explain it better:

I was thinking to provide a textarea for the user to copy and paste data from a text file and then my web page will read line by line to store the data into columns firtsname, lastname, email in the database

Example data:
John,Smith,john@email.com
Clark,Cent,clark@email.com
Jessica,Anderson,jessica@email.com

What is the data size.
Normally varchar can store upto 8000 char

Author

Commented:
The data size will be different each time, if the datasize is more then 8000 chars then they should be able to do a second insert right?...

Lets say the most common size is less then 8000.
Here is a storedprocedure to help this
http://itknowledgeexchange.techtarget.com/itanswers/string-value-to-table-column/
What you need to do is
once user pastes the data,read that in c# and look for carriage returns as first delimiter
Once you have that then create a similar procedure as in the example to insert one record at a time.
The other way to do is to create an XML out of the data and send that XML as a input to the sql procedure

Step 1 Get the value in string builder object
Step 2 parse for carriage returns and store that substrings in a string array with "," separated.
Step 3 Pass that array elements one by one as input to the procedure

Author

Commented:
Will look that up tomorrow, it's late in the evening here. Thanks

Author

Commented:
I think I will try to create a XML of the data and send that XML as a input to the proc. Would'nt that be a more simple solution?

Is it hard to convert a excel file to XML and then send the XML to the proc?

I'm gonna increase the points to 500
Converting to xml is indeed a simple solution.
I will attach a sample procedure for that.

From excel, load a dataset with excel as the source and then use ds.writexml method to get the xml from it.
Then send this to procedure
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
ty
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.