Bulk Insert into SQL Server with ASP.NET …

As title is self explanatory … lets image you have some web application (that maintains an inventory of goods for example), and one day you need to change price of goods you have due to any reason (tax hike or change in commission or simple b’coz of price raise … I am a developer not business man, so I can’t think of all genuine reasons ) … and so now you need to change price of all those goods … how you will do that ?? well, it can be done by manually changing price of each item, and its easy if you have only few items (but if this number is even >100 you will soon find your self typing lots) … or you can create a script which updates price of all stuffs all together … second approach is more feasible if you have higher number of items in inventory. But again this will need manual intervention + expertise in SQL (assuming your backend is SQL Server ofcourse ) … but there is 3rd approach, as a developer I have already included an upload feature in the application which can be used in this circumstance to bulk update the table and all you need is a csv or text file !!!

About test application, I am using ASP.NET (.NET framework 4), IIS 7.5 (I am using Window 7), and ofcourse SQL Server 2008 dev. edition … I won’t be explaining whole thing but I will write about major parts. I have created sample database in sql server,

CREATE TABLE [dbo].[collect](
    [id] [varchar](20) NULL,
    [pwd] [varchar](20) NULL
)

Then I created new blank web site using Visual Studio 2010, in which as a storage location I choose “Local IIS” so now I have my locally hosted web application ready (which actually have only web.config file initially). Now advantage of using web.config file is, almost all of your site settings are hidden from user … for example database connection parameters (which is an essential security feature). Then, I created basic form for our experiment using two buttons, a file upload utility, a label and a gridview (to view results) … (I know without CSS and more HTML, page really looks dead but its just a test application )

Create proc [bulkUpdate] @path varchar(1000)
as
declare @root varchar(1000)
set @root = @path
declare @sql varchar(2000)
set @sql='BULK INSERT loginTest.dbo.collect FROM '''+@root+''' WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'')'
EXEC (@sql)

Above is another important player, it’s a stored procedure which is basically accepting path for csv or txt file and bulk inserts into given table. (like you can see that if use have more than one table we can change our dynamic sql to accept table name as well, but it will complicate things more so lets stick with simple problem for now)

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["userDB"].ToString());
            SqlCommand cmd = new SqlCommand();
            SqlParameter sp1 = new SqlParameter();
            conn.Open();
            cmd.Connection = conn;

Above is the connection string, and as you can see all I have to do it to just give “USERDB” a connection string name from web.config file.

cmd.Parameters.Add("@path", SqlDbType.VarChar).Value = test;
                cmd.CommandText = "bulkUpdate";  // Name of SP
                cmd.CommandType = CommandType.StoredProcedure;

Is how we pass parameter to stored procedure. Now, major advantage of using stored procedure instead of Dynamic SQL is, it is less susceptible to something called “SQL Injection Attack”, in which hacker can manipulate and get access to database mainly because of dynamic sql.

File.Delete(del);

this is something really interesting… when at first I tried to use only file upload control no matter how much I tried, my uploaded file always shows me wrong path (for example, I was uploading my file from desktop but still it was showing that my file is at inetpub folder which was actually application root folder) … so I used save option to save that file in root of application and then try to work on that file .. so now when ever I upload a file, that file gets copied in root so I am deleting it after each reading cycle.

[NOTE: I don’t know what is wrong with file upload utility (my understanding of it ) but when ever I hard code path of file in application, it worked just fine but when I try to use file upload control I always encounter above said problem … may be I was/am doing something wrong]

I have uploaded this sample application for download in My Sample Page …

If you have any doubts or suggestions please drop me a comment here. As you know its just a test application but it gives good idea @ use of Stored Procedure, File Upload Control and Bulk Insert in SQL Server.

That’s all for today….

It’s Just a thought …

<?php similar_posts(); ?>