ASP.NET - Reading a tab delimited file and update the database table

Hi,

I want to read the data from  tab delimited text file into variables and pass this data as variables to stored procedure to update a sql server database table.
I want this to be done from ASP.NET PAGE, when the user selects the the file to update and clciks on the update button,
the program should read the data from text file and update the table.

Can any one suggests me what is the best way to do this.

Thanks
tvenkat9Asked:
Who is Participating?
 
Melih SARICAOwnerCommented:
Here is an example of a stored procedure

------------CREATE Proc _sp_UpdateBDTOrders(
      @UserId varchar(35),
      @Dowhat int
--      @Dowhat Values
--      0 = Check Tables
--      1 = Copy Data To temp file
--      2 = delete tmp file
--      3 = Process Data
--      4 =Kontrol Data
)
as
if @Dowhat = 0
begin
      if  object_id('_BDTOrdersTaahhutTmp') is null
            begin
            Create Table _BDTOrdersTaahhutTmp
            (
                  T$Comp int,
                  t$orno int,
                  t$pono int,
                  t$ddat datetime,
                  t$dqua int,
                  Process_time datetime Default getdate(),
                  USER_GUID  varchar(40),
                  Copy_Complete_Time datetime Null)
            create clustered index _BDTOrdersTaahhutTmp_index1  on _BDTOrdersTaahhutTmp (t$comp,t$orno,t$pono)
      end
      if  object_id('_BDTOrdersTaahhutTmp') is null
            begin
                  Create Table _BDTTmp
                  (
                  T$Comp int,
                  t$orno int,
                  t$pono int,
                  t$ddat datetime,
                  t$dqua int
                  )
            end
end
if @Dowhat = 1
begin
       declare @SQLtext varchar(500)
      set @SQLtext = '
      bulk insert _BDTTmp from ''F:\imports\BDTOrders\'+@UserID+'.csv''
            WITH
                        (
                                FIELDTERMINATOR = '';'',
                                ROWTERMINATOR = ''\n'',
                         FIRSTROW  = 2
                        )'

      exec (@SQLtext)
      select 0 Stat
end
if @Dowhat = 2
begin
   delete from       _BDTTmp
end
if @dowhat = 3
begin
      insert into _BDTOrdersTaahhutTmp(T$Comp,t$orno,t$pono ,t$ddat ,t$dqua,USER_GUID)
             select *,@UserID from _BDTTmp

      drop table _BDTTmp

      update ecatalog.dbo.__Bakiye  
            set ecatalog.dbo.__Bakiye.T$ddat = ecatalog.dbo._BDTOrdersTaahhutTmp.t$ddat,
                  ecatalog.dbo.__Bakiye.t$dqua = ecatalog.dbo._BDTOrdersTaahhutTmp.t$dqua
            from ecatalog.dbo.__Bakiye  inner join      ecatalog.dbo._BDTOrdersTaahhutTmp
                  on ecatalog.dbo._BDTOrdersTaahhutTmp.t$comp = ecatalog.dbo.__Bakiye.t$comp and
                        ecatalog.dbo._BDTOrdersTaahhutTmp.t$orno = ecatalog.dbo.__Bakiye.t$orno and
                        ecatalog.dbo._BDTOrdersTaahhutTmp.t$pono = ecatalog.dbo.__Bakiye.t$pono
      Where ecatalog.dbo._BDTOrdersTaahhutTmp.User_GUID = @USERID
            and  ecatalog.dbo._BDTOrdersTaahhutTmp.Copy_Complete_Time  is null

      update _BDTOrdersTaahhutTmp set Copy_Complete_Time = getdate()
            where USER_GUID= @USerId and Copy_Complete_Time is null
end
if @dowhat =4
begin
      Select  _BDTTmp.* ,ecatalog.dbo.__Bakiye.t$ITEM,ecatalog.dbo.__Bakiye.T$CUNO from _BDTTmp left  join ecatalog.dbo.__Bakiye
                  on ecatalog.dbo._BDTTmp.t$comp = ecatalog.dbo.__Bakiye.t$comp and
                        ecatalog.dbo._BDTTmp.t$orno = ecatalog.dbo.__Bakiye.t$orno and
                        ecatalog.dbo._BDTTmp.t$pono = ecatalog.dbo.__Bakiye.t$pono
end
GO
-----------

Melih SARICA
0
 
nauman_ahmedCommented:
The following article will be helpfull:

Reading a Delimited File Using ASP.Net and VB.Net
http://www.devarticles.com/c/a/ASP.NET/Reading-a-Delimited-File-Using-ASP.Net-and-VB.Net/


Reading a Delimited File using ASP.NET and VB.NET
http://www.theukwebdesigncompany.com/articles/article.php?article=151

-Nauman.
0
 
SammyCommented:
You can read the textfile and bound the results from reading it to a dropdown list so users can select the option they want to update "Stored procedure" and you can do the databased updating based on their selection
take a look here to see how can a delimited textfile can be read in asp.net

http://www.devarticles.com/c/a/ASP.NET/Reading-a-Delimited-File-Using-ASP.Net-and-VB.Net/

Make sense?

Good luck
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Melih SARICAOwnerCommented:
if u ll work with SQL server database and with a huge text file , best way to use bulk insert method of SQL Server.
its faster then .NET technology.

if ur situation suits this scene .. i can explain how.

Melih SARICA
0
 
tvenkat9Author Commented:
I want to update the table from Asp.net application, user will not have access directly to the sql server database.
Can we use bulk insert method of SQL Server from web application?
0
 
Melih SARICAOwnerCommented:
yes .. bulk insert is an TSQL command on SQL server

But csv file must be located on SQL server drives.

Create a tmp table that has same column count with csv file
First load ur data in to a tmp table
check data in tmp table to be sure data is valid.
then copy to the original localtion





0
 
tvenkat9Author Commented:
Can you give some more details about bulk insert ,
How do I write code for bulk insert , How do I call the this function from asp.net (Can I create stored procedure?).

Thanks
0
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.

All Courses

From novice to tech pro — start learning today.