Click here to Skip to main content
Click here to Skip to main content

Saving Files Into Database Using .NET Web Services

, 3 Oct 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
In this article, I will show you how to save big files into database using .NET Web Service and how to monitor transfer status.

Introduction

A few months ago, my boss asked me to create an Outlook Add-In which could save email attachments on SQL Server database. The main problem was that users could not connect to SQL Server directly, because instance of SQL Server was behind a firewall. The first version of file transferring module transferred file as a whole without cutting it into smaller parts. I realized that could be confusing for users, when they transfer big files. I have decided to add ProgressBar that allows users to see percentage status of file transfer. And here is a second problem. How to solve this? I had an idea that a good solution (not the only one) could be to parse this file into separate parts and send parts separately on a server, so I have created .NET Web Service that allows users to transfer files in parts. In this article, I will show you how it works and I will provide you with a simple project.

Background

One of the options, how to store big files into the database using .NET Web Service is to parse the file into a few smaller parts and then transfer this part separately on the server into a temporary file. After upload, you can save this file into a database. This could be done in a few steps:

  • Load file you want to transfer
  • Divide it into a smaller parts
  • Send these parts on a server
  • Append parts to the temporary file
  • Save temporary file into a database
  • Dispose of temporary file

When you load file and divide it into smaller parts, which could be transferred on server separately, you can easy monitor status of transfer. For example, when you want to transfer 1MB, you can divide it into 10 smaller parts. After transfer of one part, you can update ProgressBar. I will describe it later, how it works.

Database

Here, you can ask, why I use database and not just have the files within a folder. Storing files in database has several reasons, why it might be a good solution. If files are stored in a database table, when you back database up, you also back up all files. This could be an efficient way, when files are not too large. I am not saying this is the only one way how to solve the problem, it is simply one of the ways and for my purposes, this solution is sufficient.

Now, let’s take a look, how database looks like. In this example, I have created a database called FileStorage with only one table called File.

CREATE TABLE [dbo].[File](
	[FileID] [int] IDENTITY(1,1) NOT NULL,
	[Filename] [varchar](100) NULL,
	[Content] [varbinary](max) NULL,
 CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED 
(
	[FileID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, _
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

As you can see, it is a very simple table with 3 columns. We store primary key (FileID) as an int column, name of the file (Filename) as varchar and file data (Content) as varbinary column.

One main question is how do we get data in this table? For this purpose, I have actually written one stored procedure (spFile_Add). This is one of the options. This could be actually done with standard SQL command. It’s only up to you, which way you choose.

spFile_Add

CREATE PROCEDURE [dbo].[spFile_Add] 
(
	@Filename varchar(100),
	@Content varbinary(max)
)
AS
BEGIN
	INSERT INTO [File]([Filename],[Content]) VALUES (@Filename, @Content)
END

This stored procedure inserts a new file into the File table using the provided parameters. @Filename parameter is expected to be a varchar data type and describes a name of saved file. The second parameter @Content is varbinary data type and serves for saving binary content of file.

Creating a Web Service

When database is created, we can start writing Web Service. Web Services are very useful, when SQL Server (and other resources) is not directly accessible. That was my case.

With Visual Studio 2008, we can create Web Service using ASP.NET Web Service Application template. For this example, I have created a project called FileTransferWebService with Web Service FileTransfer.asmx. By default, another 2 files have been created:

AssemblyInfo.cs contains version and configuration information for your assembly
Web.config defines how your application will run (debug options, the use of cookies, etc). It also includes connectionStrings section, where you can put connection string to your database

In this case, I have created a connection string called ftConnString:

<connectionStrings>
    <add name="ftConnString"  connectionString="user id=@USER;pwd=@PASSWORD;
	data source=@DBSERVER;persist security info=False;
	initial catalog=FileStorage;" providerName="System.Data.SqlClient" />
  </connectionStrings> 

Don’t forget to set a valid user id, pwd and data source.

Every method exposed by Web Service has WebMethod attribute. This attribute in front of the method specifies that the method is accessible as a Web Service method. In this example, I have created 3 methods exposed by Web Service.

[WebMethod]
public string GetTempFilename()
{
    try
    {
        Guid g = Guid.NewGuid();
        //This method creates a temporary file on disk and 
        //returns name of temporary file. 
        //In this case it will be new Guid.
        FileStream fs = new FileStream(Server.MapPath(g.ToString()), FileMode.Create);
        fs.Close();
        return g.ToString();
    }
    catch (Exception err)
    {
        throw new Exception("GetTempFilename: " + err.Message);
    }
}

Method GetTempFilename() has to be the first method you call. This method creates a temporary file on server in which you will later save file parts.

[WebMethod]
public void AppendToTempFile(string tempFilename, byte[] data)
{
    try
    {
        //This method appends block of data to the end of the temporary file.
        FileStream fs = new FileStream(Server.MapPath(tempFilename), FileMode.Append);
        BinaryWriter bw = new BinaryWriter(fs);
        bw.Write(data);
        bw.Close();
        fs.Close();
    }
    catch (Exception err)
    {
        throw new Exception("AppendToTempFile: " + err.Message);
    }
}

This method appends part of file to the temporary file previously created by GetTempFilename() method. Parameter tempFilename identifies a temporary file for append.

[WebMethod]
public void SaveFileIntoDatabase(string filename, string tempFilename)
{
    SqlDatabase sqlDatabase = new SqlDatabase
	(ConfigurationManager.ConnectionStrings["ftConnString"].ConnectionString);
    //Now we must get all bytes from file and put them into fileData byte array.
    //This byte array we later save on database into File table.
    
    byte[] fileData = GetBytes(tempFilename);
    
    string sql = "spFile_Add";
    
    SqlCommand sqlCommand = sqlDatabase.GetStoredProcCommand(sql) as SqlCommand;
    try
    {
        sqlDatabase.AddInParameter(sqlCommand, "@Filename", SqlDbType.VarChar, filename);
        sqlDatabase.AddInParameter(sqlCommand, "@Content", SqlDbType.VarBinary, fileData);
        sqlDatabase.ExecuteNonQuery(sqlCommand);
    }
    catch (Exception err)
    {
        throw new Exception("SaveFileIntoDatabase: " + err.Message);
    }
}

public byte[] GetBytes(string path)
{
    FileStream fs=null;
    BinaryReader br=null;
    try
    {
        byte[] buffer = null;
        fs = new FileStream(Server.MapPath(path), FileMode.Open, FileAccess.Read);
        br = new BinaryReader(fs);
        long numBytes = new FileInfo(Server.MapPath(path)).Length;
        buffer = br.ReadBytes((int)numBytes);
        br.Close();
        fs.Close();
        return buffer;
    }
    catch (Exception err)
    {
        throw new Exception("SaveFileIntoDatabase: " + err.Message);
    }
}

SaveFileIntoDatabase(string filename, string tempFilename) method saves temporary file into database. The first parameter filename specifies original name of the file under which will be temporary file (tempFilename) saved into database table. Content of temporary file is loaded into byte array fileData by method GetBytes(string path). Path parameter specifies physical path of temporary file.

Connection to the database is provided by SqlDatabase class. Before declaration of instance of this class, you must include namespace Microsoft.Practices.EnterpriseLibrary.Data.Sql. When you don’t have Microsoft Enterprise Library installed on machine, where is your Web Service, you can download it from the following page: http://msdn.microsoft.com/en-us/library/ff648951.aspx.

You have to use SqlCommand to execute stored procedure spFile_Add with 2 parameters. The first parameter @Filename specifies name of the file. The second parameter @Content specifies binary data of file. When file is successfully uploaded, then you can delete a temporary file using File.Delete() method.

Adding a Web Service

When you want to consume a Web Service, you must first tell the application, where to find it. Visual Studio 2008 has a good wizard that helps you to add a Web Service Reference.

In solution explorer, right click on you project and then select "Add Service Reference…".

Add_Service_Reference.jpg

Then click on "Advanced..." button.

Service_Reference_Settings.jpg

In "Service Reference Settings", click on "Add Web Reference…" button.

Add_Web_Reference.jpg

In the address line, type the URL of the Web Service. In my example, the URL was: http://localhost/FileTransferWebService/FileTransfer.asmx. Before you add Web Reference, It is good to write Web reference name. I choose FileTransferWebService. After that, you have to click on "Add Reference" button.

Consuming a Web Service

Since the Web Reference is added, you can start writing a code. In my example, I have created one form named frmMain with some objects:

File_Transfer.jpg

Name Description
txtFile stores path to the file to be uploaded
cmdSelectFile shows OpenFileDialog
pbTansferStatus shows status of transfer
cmdSave starts transferring operation

Before saving file into database, you must select this file. By clicking on cmdSelect button, you will get OpenFileDialog window, where you can navigate through file system and select file. When you select this file, in text box txtFile, you will see full path to the file. Later, we will use this file for FileStream. Instance of FileStream class called fs is used as parameter of BinaryReader which reads bytes and stores bytes into byte array called b. ReadBytes function reads the specified number of bytes from the current stream into a byte array and advances the current position by that number of bytes. When we have part of file in byte array, then we can transfer it on server using AppendToTempFile() method. This method has 2 parameters. The first parameter is the name of a temporary file and the second are data to be appended to temporary file. This method is provided by FileTransfer class.

private void cmdSelectFile_Click(object sender, EventArgs e)
{
    //Using OpenFileDialog we can browse through file system
    //and select a file, we want to save into to database.
    OpenFileDialog dialog = new OpenFileDialog();
    dialog.Filter = "All files (*.*)|*.*";
    dialog.Title = "Select a file";
    txtFile.Text= (dialog.ShowDialog() == DialogResult.OK)
       ? dialog.FileName : null;
}

private void cmdSave_Click(object sender, EventArgs e)
{
    try
    {
        //now we have to set value of pbTransferSatatus to 0.
        pbTransferStatus.Value = 0;
        //Before saving we must create an instance of FileTransfer class, 
        //which allows us use 3 methods mentioned earlier.
        FileTransferWebService.FileTransfer ft =
            new FileTransfer.FileTransferWebService.FileTransfer();
            
        //GetTemFilename() method creates temporary file on server and returns it's name.
        string tempFileName = ft.GetTempFilename();
        FileInfo fi = new FileInfo(txtFile.Text);
        using (FileStream fs = new FileStream(txtFile.Text,FileMode.Open,FileAccess.Read))
        {
            //using BinaryReader we will read blocks of data from file
            //and append then to the temporary file.
            using (BinaryReader br = new BinaryReader(fs))
            {
                fs.Position = 0;
                int numBytesToRead = (int)fs.Length;
                int numBytesRead = 0;
                while (numBytesToRead > 0)
                {
                    //I choose 100 000 bytes long blocks.
                    byte[] b = br.ReadBytes(100000);
                    ft.AppendToTempFile(tempFileName, b);
                    if (b.Length == 0)
                    {
                        break;
                    }
                    numBytesRead += b.Length;
                    numBytesToRead -= b.Length;
                    //When block is saved, than we can update pbTransferStatus value
                    int progressStatus = (int)((double)100 / 
			(double)fs.Length * (double)numBytesRead);
                    pbTransferStatus.Value = progressStatus;
                }
                ft.SaveFileIntoDatabase(Path.GetFileName(txtFile.Text), tempFileName);
                MessageBox.Show("File has been saved into database", 
		"Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
    }
    catch (Exception err)
    {
        MessageBox.Show
	(err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Conclusion

In this article, I have described how to store big files into a database table and how to monitor status of transfer. For this purpose, .NET Web Service has been written. Writing Web Services is pretty easy. Visual Studio wizard makes writing services very fast. In this example, using Web Service is not the only one solution, it is one of the solutions you can choose.

History

  • 2 Oct 2010 - Original version posted
  • 3 Oct 2010 - Updated version posted

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Kanasz Robert
Architect The Staffing Edge & Marwin Cassovia Soft
Slovakia Slovakia
My name is Robert Kanasz and I have been working with ASP.NET, WinForms and C# for several years.
MCSD - Web Applications
MCSE - Data Platform
MCPD - ASP.NET Developer 3.5
- Web Developer 4
MCITP - Database Administrator 2008
- Database Developer 2008
MCSA - SQL Server 2012
MCTS - .NET Framework 3.5, ASP.NET Applications
- SQL Server 2008, Database Development
- SQL Server 2008, Implementation and Maintenance
- .NET Framework 4, Data Access
- .NET Framework 4, Service Communication Applications
- .NET Framework 4, Web Applications
MS - Programming in HTML5 with JavaScript and CSS3 Specialist
 
Open source projects: DBScripter - Library for scripting SQL Server database objects
 

Please, do not forget vote

Comments and Discussions

 
Questionplz PinmemberMember 1006397530-Jun-13 21:14 
Questionquery PinmemberMember 1006397530-Jun-13 21:13 
QuestionGood Article Pinmemberxmaster123_213-Mar-13 0:53 
AnswerRe: Good Article PinmvpKanasz Robert13-Mar-13 4:14 
GeneralMy vote of 5 PinmemberFernandoUY15-Feb-13 5:36 
GeneralRe: My vote of 5 PinmvpKanasz Robert12-Mar-13 3:43 
Questionnice Pinmemberstrucker_luc18-Nov-12 4:12 
AnswerRe: nice PinmvpKanasz Robert18-Nov-12 4:22 
QuestionInteresting article and very helpful Pinmemberkr1234564-Nov-12 4:54 
AnswerRe: Interesting article and very helpful PinmvpKanasz Robert4-Nov-12 5:04 
Questionhelpful PinmembersuperdevX151-Nov-12 7:48 
AnswerRe: helpful PinmvpKanasz Robert1-Nov-12 7:56 
Questiongot my 5 Pinmemberhakon12331-Oct-12 6:27 
AnswerRe: got my 5 PinmvpKanasz Robert31-Oct-12 6:38 
QuestionExcellent Pinmembermemlon mulas29-Oct-12 6:12 
AnswerRe: Excellent PinmvpKanasz Robert29-Oct-12 6:20 
Questiongood and well written article Pinmemberjackhoal27-Oct-12 4:53 
AnswerRe: good and well written article PinmvpKanasz Robert27-Oct-12 5:01 
Questionoutstanding Pinmemberrobkaan27-Oct-12 4:24 
AnswerRe: outstanding PinmvpKanasz Robert27-Oct-12 4:32 
Questionnice Pinmemberwindevvv21-Oct-12 7:45 
AnswerRe: nice PinmvpKanasz Robert21-Oct-12 8:01 
QuestionExcellent Pinmemberkaslaninovic2-Oct-12 23:44 
AnswerRe: Excellent PinmvpKanasz Robert3-Oct-12 7:52 
thank you
QuestionGood Pinmemberdeveloper88123-Sep-12 3:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411023.1 | Last Updated 3 Oct 2010
Article Copyright 2010 by Kanasz Robert
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid