Build a Web-based Briefcase: Part I

Managing Files and Folders

CodeTalk

LANGUAGES: C#

ASP.NET VERSIONS: 2.0

 

Build a Web-based Briefcase: Part I

Managing Files and Folders

 

By Bipin Joshi

 

Web sites often store files directly in some folder of the file system. However, this may not always be the most suitable way to handle things. Many of you might have used Yahoo Briefcase, which allows you to store any file on the server so you can access it from anywhere. If you want to provide such a facility in your Web site, then file-system-based storage and retrieval is tedious. Enforcing security and managing storage space quota becomes difficult. A more elegant solution can be developed by storing files in a database. This way you have full control over who is storing and retrieving files, storage space, and usage statistics. In this two-part article we are going to develop a Web-based briefcase application using ASP.NET 2.0 and SQL Server 2005. We ll kick things off by creating database tables, stored procedures, and classes to manage files and folders.

 

Functionality

Before we begin the development, let s set the functionality expected from the briefcase application:

  • The application should allow us to manage files and folders via a Web-based interface.
  • The application should allow us to create folders and subfolders.
  • There should not be any limitation on the nesting levels of the folders.
  • The application should allow us to delete and rename the folders.
  • The application should allow us to upload files to a specific folder.
  • The file can be downloaded at any time by navigating to that folder.
  • We should be able to delete or rename the file.
  • The creation date and size of the file must be tracked.
  • If a folder is deleted then its subfolders and files should also be deleted.
  • The application must ensure there are no duplicate file or folder names in a given scope.

 

Software and Technology Features Used

We ll use the following software to develop our briefcase application:

  • ASP.NET 2.0
  • TreeView and GridView Web server controls
  • ADO.NET 2.0
  • Visual Studio 2005 (VWD Express Edition can also be used)
  • SQL Server 2005 with Management Studio

 

Database Design

To begin, let s define the structure of our database. Open SQL Server 2005 Management Studio and create a new database named BriefcaseDb. Figure 1 shows the New Database dialog box of SQL Server 2005 Management Studio.

 


Figure 1: Creating a new database.

 

The database needs to have two tables: Folders and Files. These tables will store folders and files, respectively. Figure 2 shows these two tables. These tables need certain unique keys. Figure 3 gives the complete T-SQL script to create these tables.

 


Figure 2: The Folders and Files tables.

 

CREATE TABLE [dbo].[Folders]

(

  [Id] [int] IDENTITY(1,1) NOT NULL,

[FolderName] [varchar](255)

 COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ParentFolderId] [int] NULL CONSTRAINT

  [DF_Folders_ParentFolderId]  DEFAULT ((0)),

  [DateCreated] [datetime] NULL,

     CONSTRAINT [PK_Folders] PRIMARY KEY CLUSTERED

     (

    [Id] ASC

    )

     WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],

  

     CONSTRAINT [IX_Folders] UNIQUE NONCLUSTERED

     (

    [ParentFolderId] ASC,

    [FolderName] ASC

 )

 WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

)

ON [PRIMARY]

CREATE TABLE [dbo].[Files]

(

  [Id] [int] IDENTITY(1,1) NOT NULL,

[FileName] [varchar](255)

 COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

  [FileData] [image] NULL,

  [FileSize] [int] NULL,

  [FolderId] [int] NULL,

  [DateCreated] [datetime] NULL,

    

     CONSTRAINT [PK_Files] PRIMARY KEY CLUSTERED

      (

    [Id] ASC

     )

     WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],

     CONSTRAINT [IX_Files] UNIQUE NONCLUSTERED

      (

       [FileName] ASC,

       [FolderId] ASC

     )

     WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

)

ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Figure 3: T-SQL script to create required tables.

 

Note the script in Figure 3 carefully. In addition to primary key constraints it has certain unique key constraints. The first unique constraint is on the FolderName and ParentFolderId columns of the Folders table. This constraint ensures that subfolder names are unique in one folder. Similarly, the other unique constraint is on the FileName and FolderId columns of the Files table. This unique constraint ensures that file names are unique in a given folder. The columns and their significance of both the tables are summarized in Figure 4.

 

Table Name

Column Name

Column Specifications

Description

Folders

Id

Int, Identity

Represents a unique identifier of a folder.

FolderName

Varchar (255)

Name of the folder. Maximum length is 255, but can be changed as per your requirement.

ParentFolderId

Int

This column contains 0 then it is a root folder else subfolder of the folder whose Id is equal to ParentFolderId.

DateCreated

DateTime

Date and time the folder is created.

Files

Id

Int, Identity

Represents a unique identifier of a file.

FileName

Varchar (255)

Name of the file. Maximum length is 255, but can be changed as per your requirement.

FileData

Image

The contents of the file.

FileSize

Int

Size of the file in bytes.

FolderId

Int

Id of the folder in which the file is stored.

DateCreated

DateTime

Date and time the file is created.

Figure 4: Table schema.

 

Creating Stored Procedures

Now we ll create some stored procedures for getting the data in and out of the database tables we just created. Figure 5 lists the required stored procedures and a brief description of each.

 

Stored Procedure Name

Description

Files_Create

This stored procedure adds a new file to the Files table.

Files_GetFile

This stored procedure returns details about a file from the Files table based on its Id.

Files_GetFromFolder

This stored procedure returns a list of files belonging to a particular folder from the Files table.

Files_DeleteFromFolder

This stored procedure removes all the files from a specified folder. This is accomplished by deleting all the records from the Files table for that folder.

Files_Delete

This stored procedure removes a file based on its Id by deleting its records from the Files table.

Files_Rename

This stored procedures renames a file by updating its record in the Files table.

Folders_Create

This stored procedure creates a new folder by inserting a record in the Folders table.

Folders_GetSubFolders

This stored procedure returns all the folders that are subfolders of a specified folder. This is accomplished by returning all the records from the Folders table for that matching ParentFolderId.

Folders_Delete

This stored procedure deletes a specified folder by deleting its record from the Folders table.

Folders_DeleteSubFolders

This stored procedure deletes all the subfolders of a specified folder by deleting all the records matching the ParentFolderId.

Folders_Rename

This stored procedure renames a folder by updating its record in the Folders table.

Figure 5: List of stored procedures.

 

The stored procedures listed in Figure 5 affect the Files and Folders tables from the BriefcaseDb database. The stored procedures having a prefix of Files_ affect the Files table; the stored procedures having a prefix of Folders_ affect the Folders table. The complete T-SQL script to create the stored procedures as listed in Figure 5 is included with the download files accompanying this article (see end of article for download details). The following sections provide an overview of the logic involved in creating, renaming, deleting, and retrieving files and folders.

 

Stored procedures for creating files and folders. Stored procedures that create files and folders simply add a new record in Files and Folders, respectively. The Files and Folders tables contain an identity column Id. This way each newly created file or folder has a unique identifier. This identifier is used later when renaming or deleting records.

 

Stored procedures for renaming files and folders. Renaming a file or folder involves updating the corresponding record from the Files and Folders tables, respectively. The record for a file or folder is updated based on its Id column. For renaming a file the FileName column of the Files table is updated with the new file name; for renaming a folder the FolderName column of the Folders table is updated with the new folder name.

 

Stored procedures for deleting files and folders. Deleting files or folders involves deleting records from the Files and Folders tables, respectively. The record is deleted on the basis of the Id column. When a folder is deleted, all the files belonging to it must also be deleted. This is done by deleting all the records from the Files table where the FolderId column value is the same as the Id of the folder being deleted. Furthermore, when a folder is deleted, all its subfolders must also be deleted. This is accomplished by deleting all the records from the Folders table where the ParentFolderId column value is the same as the Id of the folder being deleted.

 

Stored procedures for retrieving files and folders. Retrieving files or folders calls for executing a SELECT statement against the Files or Folders tables, respectively. A single file or folder can be retrieved based on its Id column value. All the files belonging to a folder can be retrieved by matching the FolderId column value with the Id of the folder. Similarly, all the subfolders of a folder can be retrieved by matching the ParentFolderId column value with the Id of the folder.

 

Creating a Data Access Class

We need to frequently get the data in and out of the database tables; hence, we ll create a data access class. To code the data access class, create a new Web site with C# as the language. Figure 6 shows the New Web Site dialog box of Visual Studio.

 


Figure 6: Creating a new Web site.

 

Next, right-click on the Web site and choose Add ASP.NET Folder from the shortcut menu. Then select App_Code to add the App_Code folder to your Web site. All our classes will reside in this folder. Now add to the App_Code folder a new class named SqlHelper. The SqlHelper class will have the four methods shown in Figure 7; Figure 8 shows SqlHelper s complete code.

 

Method

Description

ExecuteNonQuery(string)

This method accepts the name of the stored procedure to execute and returns the number of records affected by the query.

ExecuteNonQuery(string,SqlParameter[])

This method accepts the name of the stored procedure to execute and the array of SqlParameter objects. It returns the number of records affected by the query.

ExecuteDataSet(string)

This method accepts the name of the stored procedure to execute and returns the results as a DataSet.

ExecuteDataSet(string,SqlParameter[])

This method accepts the name of the stored procedure to execute and the array of SqlParameter objects. It returns a DataSet filled with the results of the query.

Figure 7: Methods of the SqlHelper class.

 

namespace Briefcase

{

public static class SqlHelper

{

 private static string strConn;

 static SqlHelper()

 {

  strConn = ConfigurationManager.ConnectionStrings

            ["connectionstring"].ConnectionString;

 }

 public static int ExecuteNonQuery(string sql)

 {

  return ExecuteNonQuery(sql, null);

 }

 public static int ExecuteNonQuery

  (string sql, SqlParameter[] p)

 {

  SqlConnection cnn = new SqlConnection(strConn);

  cnn.Open();

  SqlCommand cmd = new SqlCommand(sql,cnn);

  cmd.CommandType = CommandType.StoredProcedure;

  if (p != null)

  {

   for (int i = 0; i < p.Length; i++)

   {

    cmd.Parameters.Add(p[i]);

   }

 }

 int retval = cmd.ExecuteNonQuery();

 cnn.Close();

 return retval;

 }

 public static DataSet ExecuteDataSet(string sql)

 {

  return ExecuteDataSet(sql, null);

 }

 public static DataSet ExecuteDataSet

  (string sql, SqlParameter[] p)

 {

  SqlConnection cnn = new SqlConnection(strConn);

  cnn.Open();

  SqlCommand cmd = new SqlCommand(sql, cnn);

  cmd.CommandType = CommandType.StoredProcedure;

  if (p != null)

  {

   for (int i = 0; i < p.Length; i++)

   {

    cmd.Parameters.Add(p[i]);

   }

  }

  SqlDataAdapter da = new SqlDataAdapter();

  da.SelectCommand = cmd;

  DataSet ds = new DataSet();

  da.Fill(ds);

  cnn.Close();

  return ds;

 }

 }

}

Figure 8: The SqlHelper class.

 

As you can see from Figure 8, the SqlHelper class picks up the database connection string stored in the Web.config file and stores it in a static variable. Other static methods such as ExecuteNonQuery and ExecuteDataSet use this variable. Figure 9 shows the section of the Web.config file that stores the database connection string.

 

 connectionString="Data Source=.;initial

 catalog=briefcasedb;user id=some_user;

 password=some_password" providerName=

 "System.Data.SqlClient"/>

Figure 9: Storing the database connection string in the Web.config file.

 

SqlHelper is fairly simple, so we won t discuss it further. However, notice that the CommandType property of the SqlCommand class is set to CommandType.StoredProecedure because all our data access is happening via stored procedures.

 

Managing Folders

Now that we ve created the SqlHelper class, let s create a class for managing folders. We need to perform the following operations on the folders:

  • Create folders
  • Rename folders
  • Delete folders
  • Retrieve all the subfolders of a folder

 

We ll create a class named Folders and add the static methods to it, as shown in Figure 10. Each method of the Folders class is discussed next.

 

Method Name

Description

Create

This method accepts a folder name, parent Id (if any), and time stamp, and adds a new folder entry in the Folders table.

Rename

This method accepts the Id of the folder to be renamed and its new name, and changes the old name to the new one in the Folders table.

Delete

This method accepts the Id of the folder to be deleted and deletes its entry from the Folders table.

DeleteSubFolders

This method accepts the Id of a folder and deletes all its subfolders.

GetSubFolders

This method accepts the Id of a folder and returns all its subfolders.

Figure 10: The methods of the Folders class.

 

Create a new folder. Create a new folder by calling the Create method of the Folders class (see Figure 11). The Create method accepts a folder name, the Id of its parent folder, and a time stamp. If the new folder is supposed to be at the top level, the parent Id must be passed as 0. It then invokes the ExecuteNonQuery method of the SqlHelper class. Note that the code is calling the Folders_Create stored procedure we created earlier. The Folders table has a UNIQUE constraint that ensures that folder names are unique under a given scope. The Create method will throw an exception if the folder name is duplicated in a given context.

 

public static int Create

(string FolderName,int ParentFolderId,DateTime DateCreated)

{

 SqlParameter[] p=new SqlParameter[3];

 p[0]=new SqlParameter("@FolderName",FolderName);

 p[1]=new SqlParameter("@ParentFolderId",ParentFolderId);

 p[2]=new SqlParameter("@DateCreated",DateCreated);

 return SqlHelper.ExecuteNonQuery("Folders_Create",p);

}

Figure 11: Creating folders.

 

Renaming a folder. To rename a folder we use the Rename method of the Folders class (see Figure 12). The Rename method accepts the Id of a folder that is to be renamed and the new name of the folder. Inside it calls the Folders_Rename stored procedure via the ExecuteNonQuery method of the SqlHelper class. Just like the Create method, the Rename method also will throw an exception if there is a folder with the same name.

 

public static int Rename(int Id, string foldername)

{

 SqlParameter[] p = new SqlParameter[2];

 p[0] = new SqlParameter("@id", Id);

 p[1] = new SqlParameter("@foldername", foldername);

 return SqlHelper.ExecuteNonQuery("Folders_Rename", p);

}

Figure 12: Renaming a folder.

 

Deleting folders. There are two possibilities as far as folder deletion is concerned. First, we may delete a folder that doesn t have any subfolders. Second, the folder being deleted may contain subfolders. The Delete and DeleteSubFolders methods perform these respective operations (see Figure 13).

 

public static int Delete(int Id)

{

 SqlParameter[] p=new SqlParameter[1];

 p[0]=new SqlParameter("@Id",Id);

 return SqlHelper.ExecuteNonQuery("Folders_Delete",p);

}

public static int DeleteSubFolders(int ParentFolderId)

{

 SqlParameter[] p = new SqlParameter[1];

 p[0] = new SqlParameter("@Id", ParentFolderId);

 return SqlHelper.ExecuteNonQuery

("Folders_DeleteSubFolders", p);

}

Figure 13: Deleting folders.

 

The Delete method accepts the folder Id to be deleted. It then calls the ExecuteNonQuery method of the SqlHelper class and executes the Folders_Delete stored procedure. The DeleteSubFolders method accepts the Id of a folder whose subfolders are to be deleted. It then executes the Folders_DeleteSubFolders stored procedure by calling the ExecuteNonQuery method of the SqlHelper class.

 

Retrieving a list of subfolders. We need to display the subfolders of a folder on the Web-based user interface that we ll build later. Hence, we need a method that will return a list of all the subfolders belonging to a specific folder. The GetSubFolders method does just that (see Figure 14).

 

public static DataTable GetSubFolders(int Id)

{

 SqlParameter[] p = new SqlParameter[1];

 p[0] = new SqlParameter("@ParentId", Id);

 DataSet ds =

 SqlHelper.ExecuteDataSet("Folders_GetSubFolders", p);

 return ds.Tables[0];

}

Figure 14: Retrieving a list of subfolders.

 

The GetSubFolders method accepts the Id of a folder whose subfolders are to be retrieved and returns a DataTable containing the subfolder information. An Id parameter passed as 0 indicates that all the subfolders at the top-most level are to be retrieved. Internally, the GetSubFolders method executes the Folders_GetSubFolders stored procedure by calling the ExecuteDataSet method of the SqlHelper class. The 0th DataTable from the returned DataSet is then returned to the caller.

 

Managing Files

The way we created a class for managing folders also is how we create a class named Files for managing files. The File class should allow us to do the following operations:

  • Add a new file to a folder.
  • Delete a single file.
  • Delete all the files from a specified folder.
  • Rename a file.
  • Get all the files belonging to a specific folder.

 

To accomplish these tasks we need the methods of the Files class (see Figure 15).

 

Method Name

Description

Create

The Create method creates a new file in the Files table.

Delete

The Delete method deletes a single file with the specified Id.

DeleteFromFolder

The DeleteFromFolder method deletes all the files from a specified folder.

Rename

The Rename method renames a file.

GetFilesFromFolder

The GetFilesFromFolder method returns a list of all the files from a given folder.

GetFile

The GetFile method returns information and data about a single file.

Figure 15: Methods of the Files class.

 

Creating a file. The Create method of the Files class creates a new file under a specified folder. The Create method is shown in Figure 16.

 

public static int Create(string FileName,byte[]

 FileData,int FileSize,int FolderId,DateTime DateCreated)

{

SqlParameter[] p=new SqlParameter[5];

p[0]=new SqlParameter("@FileName",FileName);

p[1]=new SqlParameter("@FileData",FileData);

p[2]=new SqlParameter("@FileSize",FileSize);

p[3]=new SqlParameter("@FolderId",FolderId);

p[4]=new SqlParameter("@DateCreated",DateCreated);

return SqlHelper.ExecuteNonQuery("Files_Create",p);

}

Figure 16: Creating a new file.

 

The Create method accepts file name, contents of the file, size of the file in bytes, folder Id in which the file is to be created, and time stamp. Notice that contents of the file are supplied as byte array. The code then executes the Files_Create stored procedure by calling the ExecuteNonQuery method of the SqlHelper class. Recollect that the UNIQUE constraints of the Files table ensure that duplicate file names are not stored under a folder.

 

Renaming a file. The Rename method renames an existing file (see Figure 17). The Rename method accepts the Id of the file that is to be renamed and the new file name. It then executes the Files_Rename stored procedure by calling the ExecuteNonQuery method of the SqlHelper class.

 

public static int Rename(int Id,string filename)

{

 SqlParameter[] p = new SqlParameter[2];

 p[0] = new SqlParameter("@id", Id);

 p[1] = new SqlParameter("@filename", filename);

 return SqlHelper.ExecuteNonQuery("Files_Rename", p);

}

Figure 17: Renaming a file.

 

Deleting files. There are two possibilities as far as file deletion is concerned. We may delete a single file or we may delete the folder. In the former case we need to delete just a single file; the Delete method does that job. In the latter case we need to delete all the files from a specified folder; the DeleteFromFolder method does that. Both are displayed in Figure 18.

 

public static int Delete(int Id)

{

 SqlParameter[] p=new SqlParameter[1];

 p[0]=new SqlParameter("@Id",Id);

 return SqlHelper.ExecuteNonQuery("Files_Delete", p);

}

public static int DeleteFromFolder(int folderid)

{

 SqlParameter[] p = new SqlParameter[1];

 p[0] = new SqlParameter("@folderid", folderid);

 return SqlHelper.ExecuteNonQuery("Files_DeleteFromFolder", p);

}

Figure 18: Deleting files.

 

The Delete method accepts the Id of the file that is to be deleted. It then executes the Files_Delete stored procedure by calling the ExecuteNonQuery method of the SqlHelper class. The DeleteFromFolder method accepts the folder Id from which the files are to be deleted. It then executes the Files_DeleteFromFolder stored procedure by calling the ExecuteNonQuery method of the SqlHelper class.

 

Retrieving files. We need to retrieve files for two purposes. Firstly, when we wish to download a file we need to get its contents from the Files table. Secondly, when we navigate to a folder we need to display all the files from that folder. The GetFile and GetFilesFromFolder methods accomplish these tasks (see Figure 19).

 

public static DataTable GetFile(int Id)

{

 SqlParameter[] p = new SqlParameter[1];

 p[0] = new SqlParameter("@Id", Id);

 DataSet ds = SqlHelper.ExecuteDataSet("Files_GetFile", p);

 return ds.Tables[0];

}

public static DataTable GetFilesFromFolder(int FolderId)

{

 SqlParameter[] p = new SqlParameter[1];

 p[0] = new SqlParameter("@FolderId", FolderId);

 DataSet ds =

   SqlHelper.ExecuteDataSet("Files_GetFromFolder", p);

 return ds.Tables[0];

}

Figure 19: Retrieving files.

 

The GetFile method accepts the Id of a file and returns a DataTable containing details of that file. It retrieves a record for that file from the Files table by executing the Files_GetFile stored procedure. The GetFilesFromFolder method accepts the folder Id whose files are to be retrieved. It then executes the Files_GetFromFolder stored procedure by calling the ExecuteDataSet method of the SqlHelper class. The 0th DataTable from the returned DataSet is returned to the caller.

 

Conclusion

Storing files directly in the file system may not be the best way in all situations. In this article we started developing a Web-based briefcase application that allows you to create a database-driven file system and store files therein. We created database tables and stored procedures that get the data in and out of the tables. We also created three classes: SqlHelper, Files, and Folders. The SqlHelper class is a generic data access layer and encapsulates all the commonly used database operations. The Files and Folders classes internally use SqlHelper and allow you to manage files and folders, respectively. In Part II we ll complete our briefcase by developing a Web-based user interface.

 

The sample code for this series is available for download.

 

Bipin Joshi is the founder and owner of BinaryIntellect Consulting (http://www.binaryintellect.com), where he conducts professional training programs on .NET technologies. He is the author of Developer s Guide to ASP.NET 2.0 (http://www.binaryintellect.com/books) and co-author of three Wrox books on .NET 1.x. He writes regularly for http://www.DotNetBips.com, a community Web site he founded in the early days of .NET. He is a Microsoft MVP, MCAD, MCT, and member of ASPInsiders. He jots down his thoughts about .NET, life, and Yoga at http://www.bipinjoshi.com. He also conducts workshops on Yoga and Meditation, where he helps IT professionals develop a positive personality. You can contact him at mailto:[email protected].

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish