Build a Web-based Briefcase: Part I

CodeTalk

LANGUAGES:C#

ASP.NETVERSIONS: 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 thefile system. However, this may not always be the most suitable way to handlethings. Many of you might have used Yahoo Briefcase, which allows you to storeany file on the server so you can access it from anywhere. If you want toprovide such a facility in your Web site, then file-system-based storage andretrieval is tedious. Enforcing security and managing storage space quotabecomes difficult. A more elegant solution can be developed by storing files ina database. This way you have full control over who is storing and retrievingfiles, storage space, and usage statistics. In this two-part article we aregoing to develop a Web-based briefcase application using ASP.NET 2.0 and SQLServer 2005. We ll kick things off by creating database tables, storedprocedures, and classes to manage files and folders.

Functionality

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

Software and Technology Features Used

We ll use thefollowing software to develop our briefcase application:

Database Design

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

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 thesetwo tables. These tables need certain unique keys. Figure 3 gives the completeT-SQL script to create these tables.

Figure 2: The Folders and Filestables.

CREATE TABLE [dbo].[Folders]

(

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

[FolderName] [varchar](255)

 COLLATESQL_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_ASNULL,

  [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 scriptto create required tables.

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

Figure 4: Tableschema.

Creating Stored Procedures

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

Figure 5: List ofstored procedures.

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

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

Stored proceduresfor renaming files and folders. Renaming a file or folder involves updatingthe corresponding record from the Files and Folders tables, respectively. Therecord for a file or folder is updated based on its Id column. For renaming afile 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 updatedwith the new folder name.

Stored proceduresfor deleting files and folders. Deleting files or folders involves deletingrecords from the Files and Folders tables, respectively. The record is deletedon the basis of the Id column. When a folder is deleted, all the filesbelonging to it must also be deleted. This is done by deleting all the recordsfrom the Files table where the FolderId column value is the same as the Id ofthe folder being deleted. Furthermore, when a folder is deleted, all itssubfolders must also be deleted. This is accomplished by deleting all therecords from the Folders table where the ParentFolderId column value is the sameas the Id of the folder being deleted.

Stored proceduresfor retrieving files and folders. Retrieving files or folders calls forexecuting 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 thefiles belonging to a folder can be retrieved by matching the FolderId columnvalue with the Id of the folder. Similarly, all the subfolders of a folder canbe retrieved by matching the ParentFolderId column value with the Id of thefolder.

Creating a Data Access Class

We need to frequently get the data in and out of thedatabase tables; hence, we ll create a data access class. To code the dataaccess class, create a new Web site with C# as the language. Figure 6 shows theNew 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.NETFolder from the shortcut menu. Then select App_Code to add the App_Code folderto your Web site. All our classes will reside in this folder. Now add to theApp_Code folder a new class named SqlHelper. The SqlHelper class will have thefour methods shown in Figure 7; Figure 8 shows SqlHelper s complete code.

Figure 7: Methodsof the SqlHelper class.

namespace Briefcase

{

public static class SqlHelper

{

 private static stringstrConn;

 static SqlHelper()

 {

  strConn =ConfigurationManager.ConnectionStrings

            ["connectionstring"].ConnectionString;

 }

 public static intExecuteNonQuery(string sql)

 {

  returnExecuteNonQuery(sql, null);

 }

 public static intExecuteNonQuery

  (string sql,SqlParameter[] p)

 {

  SqlConnection cnn = newSqlConnection(strConn);

  cnn.Open();

  SqlCommand cmd = newSqlCommand(sql,cnn);

  cmd.CommandType =CommandType.StoredProcedure;

  if (p != null)

  {

   for (int i = 0; i    {     cmd.Parameters.Add(p[i]);    }  }  int retval = cmd.ExecuteNonQuery(); cnn.Close(); return retval;  }  public static DataSetExecuteDataSet(string sql)  {   returnExecuteDataSet(sql, null);  }  public static DataSetExecuteDataSet  (string sql,SqlParameter[] p)  {   SqlConnection cnn = newSqlConnection(strConn);   cnn.Open();  SqlCommand cmd = newSqlCommand(sql, cnn);   cmd.CommandType =CommandType.StoredProcedure;   if (p != null)   {    for (int i = 0; i    {     cmd.Parameters.Add(p[i]);    }   }   SqlDataAdapter da = newSqlDataAdapter();  da.SelectCommand = cmd;   DataSet ds = newDataSet();  da.Fill(ds);   cnn.Close();  return ds;  }  } }Figure 8: TheSqlHelper class.   As you can see from Figure 8, the SqlHelper class picks upthe database connection string stored in the Web.config file and stores it in astatic variable. Other static methods such as ExecuteNonQuery andExecuteDataSet use this variable. Figure 9 shows the section of the Web.config file that stores the database connection string.    connectionString="DataSource=.;initial catalog=briefcasedb;userid=some_user;  password=some_password"providerName= "System.Data.SqlClient"/> Figure 9: Storing thedatabase 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 toCommandType.StoredProecedure because all our data access is happening viastored procedures.  Managing Folders Now that we ve created the SqlHelper class, let s create aclass for managing folders. We need to perform the following operations on thefolders: Create foldersRename foldersDelete foldersRetrieve all the subfolders of a folder   We ll create a class named Folders and add the staticmethods to it, as shown in Figure 10. Each method of the Folders class isdiscussed 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 methodsof the Folders class.   Create a new folder.Create a new folder by calling the Create method of the Folders class (seeFigure 11). The Create method accepts a folder name, the Id of its parentfolder, 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 methodof the SqlHelper class. Note that the code is calling the Folders_Create storedprocedure we created earlier. The Folders table has a UNIQUE constraint thatensures that folder names are unique under a given scope. The Create methodwill 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=newSqlParameter[3];  p[0]=newSqlParameter("@FolderName",FolderName);  p[1]=newSqlParameter("@ParentFolderId",ParentFolderId);  p[2]=newSqlParameter("@DateCreated",DateCreated);  returnSqlHelper.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 newname of the folder. Inside it calls the Folders_Rename stored procedure via theExecuteNonQuery method of the SqlHelper class. Just like the Create method, theRename method also will throw an exception if there is a folder with the samename.  public static int Rename(int Id, string foldername) { SqlParameter[] p = newSqlParameter[2];  p[0] = newSqlParameter("@id", Id);  p[1] = newSqlParameter("@foldername", foldername);  returnSqlHelper.ExecuteNonQuery("Folders_Rename", p); }Figure 12:Renaming a folder.   Deleting folders.There are two possibilities as far as folder deletion is concerned. First, wemay delete a folder that doesn t have any subfolders. Second, the folder beingdeleted may contain subfolders. The Delete and DeleteSubFolders methods performthese respective operations (see Figure 13).  public static int Delete(int Id) { SqlParameter[] p=newSqlParameter[1];  p[0]=newSqlParameter("@Id",Id);  returnSqlHelper.ExecuteNonQuery("Folders_Delete",p); }public static int DeleteSubFolders(int ParentFolderId) { SqlParameter[] p = newSqlParameter[1];  p[0] = newSqlParameter("@Id", ParentFolderId);  returnSqlHelper.ExecuteNonQuery ("Folders_DeleteSubFolders", p); }Figure 13:Deleting folders.   The Delete method accepts the folder Id to be deleted. Itthen calls the ExecuteNonQuery method of the SqlHelper class and executes the Folders_Deletestored procedure. The DeleteSubFolders method accepts the Id of a folder whosesubfolders are to be deleted. It then executes the Folders_DeleteSubFoldersstored procedure by calling the ExecuteNonQuery method of the SqlHelper class.   Retrieving a listof subfolders. We need to display the subfolders of a folder on the Web-baseduser interface that we ll build later. Hence, we need a method that will returna list of all the subfolders belonging to a specific folder. The GetSubFoldersmethod does just that (see Figure 14).  public static DataTable GetSubFolders(int Id) { SqlParameter[] p = newSqlParameter[1];  p[0] = newSqlParameter("@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 whosesubfolders are to be retrieved and returns a DataTable containing the subfolderinformation. An Id parameter passed as 0 indicates that all the subfolders atthe top-most level are to be retrieved. Internally, the GetSubFolders methodexecutes the Folders_GetSubFolders stored procedure by calling the ExecuteDataSetmethod of the SqlHelper class. The 0th DataTable from the returned DataSet isthen returned to the caller.  Managing Files The way we created a class for managing folders also ishow we create a class named Files for managing files. The File class shouldallow 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 Filesclass (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: Methodsof the Files class.   Creating a file.The Create method of the Files class creates a new file under a specifiedfolder. The Create method is shown in Figure 16.  public static int Create(string FileName,byte[] FileData,int FileSize,intFolderId,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, andtime stamp. Notice that contents of the file are supplied as byte array. Thecode then executes the Files_Create stored procedure by calling the ExecuteNonQuerymethod of the SqlHelper class. Recollect that the UNIQUE constraints of the Filestable 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 methodaccepts the Id of the file that is to be renamed and the new file name. It thenexecutes the Files_Rename stored procedure by calling the ExecuteNonQuerymethod of the SqlHelper class.  public static int Rename(int Id,string filename) { SqlParameter[] p = newSqlParameter[2];  p[0] = newSqlParameter("@id", Id);  p[1] = newSqlParameter("@filename", filename);  returnSqlHelper.ExecuteNonQuery("Files_Rename", p); }Figure 17:Renaming a file.   Deleting files.There are two possibilities as far as file deletion is concerned. We may deletea single file or we may delete the folder. In the former case we need to deletejust a single file; the Delete method does that job. In the latter case we needto delete all the files from a specified folder; the DeleteFromFolder methoddoes that. Both are displayed in Figure 18.  public static int Delete(int Id) { SqlParameter[] p=newSqlParameter[1];  p[0]=newSqlParameter("@Id",Id);  returnSqlHelper.ExecuteNonQuery("Files_Delete", p); }public static int DeleteFromFolder(int folderid) { SqlParameter[] p = newSqlParameter[1];  p[0] = newSqlParameter("@folderid", folderid);  returnSqlHelper.ExecuteNonQuery("Files_DeleteFromFolder", p); }Figure 18:Deleting files.   The Delete method accepts the Id of the file that is to bedeleted. It then executes the Files_Delete stored procedure by calling the ExecuteNonQuerymethod of the SqlHelper class. The DeleteFromFolder method accepts the folderId from which the files are to be deleted. It then executes theFiles_DeleteFromFolder stored procedure by calling the ExecuteNonQuery methodof the SqlHelper class.   Retrieving files.We need to retrieve files for two purposes. Firstly, when we wish to download afile we need to get its contents from the Files table. Secondly, when wenavigate to a folder we need to display all the files from that folder. TheGetFile and GetFilesFromFolder methods accomplish these tasks (see Figure 19).  public static DataTable GetFile(int Id) { SqlParameter[] p = newSqlParameter[1];  p[0] = newSqlParameter("@Id", Id);  DataSet ds =SqlHelper.ExecuteDataSet("Files_GetFile", p);  return ds.Tables[0]; }public static DataTable GetFilesFromFolder(int FolderId) { SqlParameter[] p = newSqlParameter[1];  p[0] = newSqlParameter("@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 aDataTable containing details of that file. It retrieves a record for that filefrom the Files table by executing the Files_GetFile stored procedure. TheGetFilesFromFolder method accepts the folder Id whose files are to beretrieved. It then executes the Files_GetFromFolder stored procedure by callingthe ExecuteDataSet method of the SqlHelper class. The 0th DataTable from thereturned DataSet is returned to the caller.  Conclusion Storing files directly in the file system may not be thebest way in all situations. In this article we started developing a Web-basedbriefcase application that allows you to create a database-driven file systemand store files therein. We created database tables and stored procedures thatget 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 andencapsulates all the commonly used database operations. The Files and Foldersclasses internally use SqlHelper and allow you to manage files and folders,respectively. In Part II we ll complete our briefcase by developing a Web-baseduser interface.   The sample code forthis series is available for download.  Bipin Joshi is thefounder and owner of BinaryIntellect Consulting (http://www.binaryintellect.com),where he conducts professional training programs on .NET technologies. He isthe 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 communityWeb 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, andYoga at http://www.bipinjoshi.com. Healso conducts workshops on Yoga and Meditation, where he helps IT professionalsdevelop a positive personality. You can contact him at mailto:[email protected].