How to import uploaded database into SQL

[blenderdude]

Perhaps someone has an idea on how to go about this. I want an easy
way for the end user to take list of email addresses and upload them
to my sql server. The user should be able to choose between two or
three database formats.(ms access, csv, etc).

So I would have a web form with a file upload control and hopefully I
could import the data from the file stream in memory without actually
having to physically write the file to disk.

Any ideas where I should start with this?

Are there security risks when you allow people to upload an access
database and then try to open it? I notice that Exchange seems to be
pretty paranoid about access databases and will block any mdb
attachments, so it would seem that there is an implied vulnerability
here?

Thanks for any suggestions....

Mike Hanewinckel

 

[Cowboy \(Gregory A. Beamer\)]

Without some serious bit slinging, you will not get this done with Access;
it can be done with a .csv file, however. As far as the Access security
risk, there are risks when opening the file in Access, but none I know of
with querying the data. This is due to the fact that Access is well suited
for creating a form on launch that runs code outside of Access (a virus?).
You could pull from Access separately and have it temporarily save to disk.

Here is an option:
1. Set up an FTP site
2. Set up a file watcher component (or have an executable set up as a task
on the server -- using AT command)

You can still have a form to upload to the FTP site, but FTP also allows
someone to simply drop the file. The process that handles the file works
like so:

1. Open file (if you stick to csv, excel, access, all can be opened with
ADO.NET)
2. Read data and store in your RDBMS
3. Delete or archive the file

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
Author: ADO.NET and XML: ASP.NET on the Edge

****************************************************************************
****
Think Outside the Box!
****************************************************************************
****

 

[blenderdude]

Without some serious bit slinging, you will not get this done with Access;
it can be done with a .csv file, however. As far as the Access security
risk, there are risks when opening the file in Access, but none I know of
with querying the data. This is due to the fact that Access is well suited
for creating a form on launch that runs code outside of Access (a virus?).
You could pull from Access separately and have it temporarily save to disk.

Ok. That makes sense.

Here is an option:
1. Set up an FTP site
2. Set up a file watcher component (or have an executable set up as a task
on the server -- using AT command)

You can still have a form to upload to the FTP site, but FTP also allows
someone to simply drop the file. The process that handles the file works
like so:
1. Open file (if you stick to csv, excel, access, all can be opened with
ADO.NET)
2. Read data and store in your RDBMS
3. Delete or archive the file

I dont think the ftp thing is going to be a good option here, but I
have worked with the fileupload component in asp.net and it is not too
hard. One of the options that I like is how you can access the file
contents without having to save the file. That way you don't have to
give worry about getting your web host to set write permissions on
that folder.

I was hoping there was some easy way to set a connection to the file
in memory. But if I am following you, this is no minor task with an
mdb file. Anyway, It would not be worth losing support for mdb
uploads.

So I could use ado.net to parse any of those aformentioned database
formats. I could write one function for importing the database and
reuse it for any of the file formats changing only the connection
object.

Thanks,
Mike

 

[Cowboy \(Gregory A. Beamer\)]

It comes to an understanding of the stream object. The basic idea is you
have two ends with a stream in the middle. One of the ends, however, can be
in memory, rather than a typical end location. The basic flow is something
like this:

1. Get file with a reader
2. Feed reader to string
3. Output file with writer

But, let's say that the writer writes to a StringBuilder (from code I wrote
for a VB.NET based book):

Dim objStringBuilder As New SysText.StringBuilder()
Dim objStringWriter As New SysIO.StringWriter(objStringBuilder)
Dim strOutput As String

' This is an XSL tranformation that outputs to the writer
objXSLTransform.Transform(objXML, Nothing, objStringWriter)
strOutput = objStringBuilder.ToString()

You now have the info stored in a string. Downside is string parsing is not
as friendly a manner to parse data. For a CSV, you could then split on end
of line and further on the comma. THe example above is a streamwriter off a
transform, but most of the stream objects will either let you write to a
Stringbuilder, a byte array or a writer of some sort which allows you to
access the string.

Another option is to save to disk and process. In many instances, it might
be a better option, as you have the ability to use ADO.NET and have
Microsoft responsible for your class model. Yes, you use the disk for a time
(perhaps a very short time), but it gives you some other artifacts that are
not that bad. If the processing fails, you have the raw file to search for
exceptions (the answer may still be "you guys need to follow our format").
This is highly beneficial in many situations like Extranets. You mention
this is probably not an option.

There is the option to use a RAM disk (scratch place in memory, as if it
were a disk), but this option will also require the ISP to help you out
here. It might be easier to allow them to let you access your own folder
programatically (the ISP can set it so you only have access to your website,
as far as disk goes. This is quite easy for the ISP with .NET.

Now, to the final question. You can use the OLEDB objects for Access, Excel,
CSV and even a more generic text file. Technically, you could also have
people upload FoxPro/Paradox/DBase (any that use the DBase file format), et
al, and consume them. You will have to get the actual path, but you can pull
this dynamically from the page that does the processing of the file
(HttpRequestObject.PhysicalPath or use the ServerVariables collection). This
is where you point the file path portion of your connection string, the
provider will be the type of file you are consuming.

One more thing: Make sure you have exception handling in your code, as it is
likely someone will break your rules and cause an exception to fire. Rule 1
is "never trust user input."

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
Author: ADO.NET and XML: ASP.NET on the Edge

****************************************************************************
****
Think Outside the Box!
****************************************************************************
****