CODEWITHSUNDEEP
c#asp.net.netsqlsql-server-2008
SamuraiJack
SamuraiJack

Reputation: 5539

What will be the best approach for uploading multiple files to database with single execution of stored procedure

I have 5 FileUpload controls on a webform.. I have two sql server tables...

Tbl_ClaimDetails

SrNo | Remark | BrMkrdt

Tbl_ClaimImages

SrNo | Img | Id | ImgName

The first file upload control is compulsory. For instance if a user selects 3 files to be uploaded then the first image file is used to insert data into Tbl_ClaimDetails through stored procedure (SrNo in Tbl_ClaimDetails is identity) i am returning the last srno using SCOPE_IDENTITY(). I am storing this SCOPE_IDENTITY() value in a variable lastid

Now i am using another stored procedure to insert data in Tbl_ClaimImages of this first image along with lastid..

if (file1.ContentLength > 0 && (u1 != null || u1 != 0))
{
                Stream fs = file1.InputStream;
                BinaryReader br = new BinaryReader(fs);
                Byte[] bytes = br.ReadBytes((Int32)fs.Length);

//store procedure inserts data in tbl_claimdetails and return SCOPE_IDENTITY()
                lastid = dbo.ExecProc1(claim.Remark,  claim.BrMkrdt);
//another stored procedure inserts data in tbl_ClaimImages
                dbo.insert(bytes, lastid, file1.FileName);    
             count++;
}

if (file2.ContentLength > 0 && (u1 != null || u1 != 0))
{
                Stream fs = file2.InputStream;
                BinaryReader br = new BinaryReader(fs);
                Byte[] bytes = br.ReadBytes((Int32)fs.Length);                
                dbo.insert(bytes, lastid, file1.FileName);    
             count++;
}// and so on till file5

This approach is working fine. I do not have any errors or problem BUT i want to know if there is a better approach than this? As you can see i am executing two different stored procedures for inserting values in the given to tables. The stored procedure which inserts values in Tbl_ClaimDetails gets executed just once at the beginning and then i am executing another stored procedure again and again to insert values in Tbl_ClaimImages depending on number of images user is uploading. Is it possible all this can be done using one single stored procedure in one single execution? Or is there better approach?

Upvotes: 1

Views: 423

Answers (1)

Hi10
Hi10

Reputation: 541

To reduce the number of calls and to get the things in one go, I think only one approach with assuming you have database of MS-SQL Server 2008+ and no constraint with using ADO.Net SQL Client.

  1. Create two TableTypes in SQL Server, with same schema of two tables i.e. Tbl_ClaimDetails and Tbl_ClaimImages
  2. Create one Stored Proc having two parameters i.e. TableTypes you have created.
  3. Store Procedure can have Merge statement which can help you to insert in parent child tables easily.
  4. Create two DataTable in .Net which should have same structure of your underlying db tables, for file you can use byte[] [give a try].
  5. Use Sql.Structured data type and pass these two data tables to created stored procedure.

Also you can find sample code of How to use table type in SQL from code project.

With this approach you need only one call to SQL with complete data structures. I hope this will help you to meet what you are looking at.

Upvotes: 1

Related Questions