File Upload Download to SQL Server using Dynamic Data Services

Tristanlim
Posted by in ASP.NET category on for Beginner level | Points: 250 | Views : 19851 red flag

Shows how to incorporate upload/download to SQL server function directly using Dynamic Data:
1 Create the Dynamic Data website
2 Create Custom Field Templates
3 Create the Custom Field Templates to implement the upload/download functionality


 Download source code for File Upload Download to SQL Server using Dynamic Data Services

This tutorial shows you how to create a basic website that uses ASP.NET Dynamic Data to create a data-driven website with minimal coding. It goes further to show you how to create and use custom field templates to customize the interactions for particular data fields. Finally, it shows you how to create a custom field template to upload and download files into SQL Server using Dynamic Data Entities.

The major aim of this tutorial is to show you how to create the custom field template to work around the limitations imposed by Dynamic Data and still offer CRUD for files.

 

Prerequisites

In order to complete this tutorial, you will need:

·         Microsoft Visual Studio 2010 or Visual Web Developer 2010 Express.

·         SQL Server 2008 (Any edition)

·         SQL Server Management Studio

Creating the Database

In order to work with the data, you will need to create a database named “Database1” with a table named “Document”, containing the following fields with characteristics as below.

Alternatively, you can either copy and paste or run the script attached below.

USE [master]

GO

 

/****** Object:  Database [Database1]    Script Date: 12/09/2010 16:17:01 ******/

CREATE DATABASE [Database1] ON  PRIMARY

( NAME = N'Database1', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Database1.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'Database1_log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Database1_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

 

ALTER DATABASE [Database1] SET COMPATIBILITY_LEVEL = 100

GO

 

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [Database1].[dbo].[sp_fulltext_database] @action = 'enable'

end

GO

Creating a Dynamic Data website

You can create the website in Visual Studio using the ASP.NET Dynamic Data Entities Web Site template. In this example, I chose to use the website template as the web application template currently has some bugs.

To create the Dynamic Data Web Site project

 .       Start your preferred IDE.

2.       In the File menu, click New Web Site.

3.       Under Installed Templates, choose either Visual Basic or Visual C#. The rest of the tutorial is in VB but if you prefer C#, simply convert the VB code with your favorite code converter.

4.       In the centre pane, choose ASP.NET Dynamic Data Entities Web Site. You can choose to use the ASP.NET Dynamic Data LINQ to SQL Web Site too, but that is not covered by my tutorial. You can refer to MSDN for details on how to do that.

5.       In the Web Location box, select File System and then enter the name of the folder where you want to keep the pages of the website.

Adding Data to the website

The next step is to add a database to the project. Later you will use it to create a data context (classes to represent database entities) and then register the data context for use by Dynamic Data. 

To create the data model using Entity Framework

1.       In the Solution Explorer, right-click the project, click Add, and then click New Item.

2.       Under Installed Templates, in the left pane, ensure that the same language is chosen, Visual Basic  or Visual C#.

3.       In the center pane, click ADO.NET Entity Data Model.

4.       In the Name box, enter a name for the database model. In this tutorial, I will use the default “Model.edmx”.

5.       Click Add.

6.       If the message box below shows up, just click Yes.

7.       The Entity Data Model Wizard window is displayed.

8.       Select Generate from database, and then click Next.

9.       Under Which data connection should your application use to connect to the database?, click New Connection.

10.   Select your server and database to create the connection.

11.   Click the Yes, include the sensitive data in the connection string and ensure that the Save entity connection settings in Web.Config as: is checked.

12.   Name your Entity Connection as you wish, but here I will use Database1Entities. The name here does not affect anything else as the generated code will automatically adjust.

13.   Next, in Choose Your Database Objects, choose the Document table and ensure that the Pluralize or singularize generated object names is unchecked.

14.   You can save your new model as DatabaseModel1. This name will be used throughout the codes later, hence you need to be careful about changing this. This name will be reflected in the Namespace.

15.    The ADO.NET Entity Data Model Designer will be displayed. Close it.

To register the data context

1.       Open the Global.asax file.

2.       Uncomment the line containing the DefaultModel.RegisterContext method.

3.       Set the context type and ScaffoldAllTables as:

DefaultModel.RegisterContext(GetType(Database1Model.Database1Entities), New ContextConfiguration() With {.ScaffoldAllTables = True})

 

Testing the Dynamic Data Web Site

You can now test the Dynamic Data website you have created.

To test the website

1.       Press F5 to debug the project.

2.       If the message box Debugging Not Enabled shows up, choose Modify the Web.Config file to enable debugging and click OK.

3.       You should see the following web page. 

4.       Click into the Document link and then click Insert New Item

5.       Congratulations! You have now succeeded in creating a generic Dynamic Data website.

6.       Next we will proceed to add the magic to customize the field templates. 

Creating Custom Field Templates

In this section, we will create a simple custom field template to get you familiarized with the concept and steps before going on to the more complicated upload and download templates. We will be using this simple template to prepare our Dynamic Data website to use the more complicated templates, so please do not skip this section.

Creating the ReadonlyText Template

This template will prevent the text displayed from being altered. We will use this to prevent alterations to the data fields containing the File Name and File Type.


To create the custom field template

1.       In Solution Explorer, expand the Dynamic Data\FieldTemplates folder, and make a copy the Text_Edit.ascx in the same folder. A copy and paste action will do it.

2.       Rename the Text_Edit.ascx to ReadonlyText_Edit.ascx.

3.       Double click the renamed file, switch to Split mode and change the ReadOnly attribute of TextBox1 to true.

4.       Save.

Creating Metadata Classes for the Data Model

In this section, you create a partial class. This class extends the data model and enables you to associate your custom field template with a specific data field. You will also create a class for you to perform searches for your DynamicData control so you can refer to and hence, manipulate the data within. This function is copied from Stephen Naughton’s blog on http://csharpbits.notaclue.net/. His website is chock full of goodness about Dynamic Data and helped me tremendously when I was struggling with the problem of field template interactions.

To create the class

1.       In Solution Explorer, right click the App_Code folder and click Add New Item.

2.       Under Installed Templates, in the left pane, ensure that you have selected the SAME programming language as your website. Discrepancies will ensure that your IDE will NOT compile your codes; you should only change languages within a SOLUTION and not within a PROJECT.

3.       In the center pane, click Class.

4.       In the Name box, enter whatever you want. This does not affect your code compilation as you can change the class name declaration within, but it is generally a good idea to put the same name as the class you want to create so it will be easier to find later.

5.       Click Add.

6.       If you did not right click the App_Code folder to add the new class file, you will get the following message box. Just click Yes.

7.       Copy and paste the following codes into the IDE. Merge them into the same file.

For a more in depth explanation of what the partial class and metadata class do, you can refer to http://msdn.microsoft.com/en-us/library/ee712708.aspx.

FindDynamicControlRecursive


Imports Microsoft.VisualBasic

Imports System.Web.DynamicData

Imports System.ComponentModel

Imports System.ComponentModel.DataAnnotations

 

Namespace Database1Model

    Public Class Class1

        '<summary>

        'Returns the DynamicControl by searching recursively for it by DataField.

        '</summary>

        '<param name="Root">The control to start the search at.</param>

        '<param name="Id">The DataField of the control to find</param>

        '<returns>The found control or NULL if not found</returns>

        'Copied from Stephen Naughton @ http://csharpbits.notaclue.net/

 

        Public Shared Function FindDynamicControlRecursive(ByRef root As ControlByVal dataField As StringAsControl

            Dim dc = TryCast(root, DynamicControl)

            'Category

            If dc IsNot Nothing Then

                If [String].Compare(dc.DataField, dataField, True) = 0 Then

                    Return dc

                End If

            End If

 

            For Each Ctl As Control In root.Controls

                Dim FoundCtl As Control = FindDynamicControlRecursive(Ctl, dataField)

 

                If FoundCtl IsNot Nothing Then

                    Return FoundCtl

                End If

            Next

            Return Nothing

        End Function

    End Class

End Namespace


 

Partial Class “Document” and “DocumentMetadata”

Imports System.Web.DynamicData

Imports System.ComponentModel

Imports System.ComponentModel.DataAnnotations

 

Namespace Database1Model

    <DisplayName("   Documents")>

       <MetadataType(GetType(DocumentMetadata))>

    Partial Public Class Document

 

    End Class

 

    Public Class DocumentMetadata

        <Display(Name:="File Name", Order:=1)> <UIHint("ReadonlyText")> Public DocumentName As Object

        <Display(Name:="File Type", Order:=2)> <UIHint("ReadonlyText")> Public DocumentExt As Object

        <Display(Name:="Content", Order:=3)> <UIHint("DocumentContent")> Public DocumentContent As Object

        <Display(Name:="Remarks", Order:=4)> Public Remarks As Object

        <Display(Name:="Last Modified", Order:=5)> Public ModifiedDate As Object

    End Class

End Namespace

 

Testing Data Field Customization

You can now test the custom template behavior:    Run the project and navigate to the Insert.aspx page for the Document table.    Try to type something in the File Name and File Type fields. You should not be able to do it.

·         Notice the field names are also changed as per the definitions in the DocumentMetadata class.

Congratulations! You have created the custom field template and associated it with the table fields successfully.

Creating the DocumentContent custom field templates

In this section, you will create 2 custom field templates to handle the file upload and download functions.

No doubt there are more efficient ways to perform the document handling in Dynamic Data but this method is simple and works well within its framework.

Create DocumentContent.ascx

DocumentContent.ascx will be used by the page templates to display the File Content data field in List.aspx and Detail.aspx. This control will allow the user to download the stored files by converting the stored Base64 string into a binary stream and also preparing the http response with the file name and type.

As we do not to overload the response of the List.aspx page (imagine filling up your List.aspx response with the contents of a large number of large files… L) So we are going to just display a simple label called “Binary Data” in that field while in List mode. When the user displays the individual record in Display mode, we allow the file contents to be sent and kept in readiness to download.

1.       In Solution Explorer, expand the Dynamic Data\FieldTemplates folder, and make a copy the Text.ascx in the same folder. A copy and paste action will do it.

2.       Rename the Text.ascx to DocumentContent.ascx.

3.       Double click the renamed file, switch to Split mode.

4.       Copy the contents below into the file. DO NOT overwrite the first line.

DocumentContent.ascx

<asp:Literal runat="server" ID="Literal1" Text="<%# FieldValueString %>" Visible="False" />

<asp:Label ID="lblBinaryData" runat="server" Text="Binary Data"></asp:Label>

<asp:Button ID="btnDownload" runat="server" Text="Download" />


5.       Save.

6.       Open the DocumentContent.ascx.vb file and past the following code into it.

DocumentContent.ascx.vb

Imports System.ComponentModel.DataAnnotations

Imports System.Web.DynamicData

Imports System.Web

Imports System.Data.SqlClient

Imports System.Data

 

Class TextField

    Inherits FieldTemplateUserControl

 

    Public Overrides ReadOnly Property FieldValueString As String

        Get

            Return MyBase.FieldValueString

        End Get

    End Property

 

    Public Overrides ReadOnly Property DataControl As Control

        Get

            Return Literal1

        End Get

    End Property

 

    Protected Sub btnDownload_Click(ByVal sender As ObjectByVal e As System.EventArgsHandlesbtnDownload.Click

        Dim DocExt As Literal = Database1Model.Class1.FindDynamicControlRecursive(Page,"DocumentExt").Controls(0).Controls(0)

        Select Case DocExt.Text

            Case "doc"

                Response.ContentType = "application/vnd.ms-word"

                Exit Select

            Case "docx"

                Response.ContentType = "application/vnd.ms-word"

                Exit Select

            Case "xls"

                Response.ContentType = "application/vnd.ms-excel"

                Exit Select

            Case "xlsx"

                Response.ContentType = "application/vnd.ms-excel"

                Exit Select

            Case "jpg"

                Response.ContentType = "image/jpg"

                Exit Select

            Case "png"

                Response.ContentType = "image/png"

                Exit Select

            Case "gif"

                Response.ContentType = "image/gif"

                Exit Select

            Case "pdf"

                Response.ContentType = "application/vnd.pdf"

                Exit Select

            Case Else

                Response.ContentType = "application/X-unknown"

 

        End Select

        'Send file as binary stream

        Response.Clear()

        Response.Buffer = True

        Response.Charset = "UTF-8"

 

        Dim DocName As Literal = Database1Model.Class1.FindDynamicControlRecursive(Page,"DocumentName").Controls(0).Controls(0)

        Response.AddHeader("content-disposition""attachment; filename=" +HttpUtility.UrlPathEncode(DocName.Text))

        Dim bytes() As Byte = Convert.FromBase64String(Literal1.Text)

        Response.BinaryWrite(bytes)

        Response.Flush()

        Response.End()

    End Sub

 

    Protected Sub Page_Load(ByVal sender As ObjectByVal e As System.EventArgsHandles Me.Load

        'If it is a list or a details request with no DocumentID as params, disable download button

        If ContainerType = ContainerType.List Or Request.Params("DocumentID") = 0 Then

            btnDownload.Visible = False

        Else

            lblBinaryData.Visible = False

        End If

    End Sub

End Class



Create DocumentContent_Edit.ascx

DocumentContent_Edit.ascx will be used by the page templates to display in Edit.aspx and Insert.aspx. We do not need to create a DocumentContent_Insert.ascx as the Dynamic Data Framework will automatically fall back to the _Edit template of the same name. This control will allow the user to upload the file, storing it as a Base64 string and updating the File Name and File Type data fields automatically. The actual record insertion still requires the user to click on the Insert link.

1.       In Solution Explorer, expand the Dynamic Data\FieldTemplates folder, and make a copy the Text_Edit.ascx in the same folder. A copy and paste action will do it.

2.       Rename the Text_Edit.ascx to DocumentContent_Edit.ascx.

3.       Double click the renamed file, switch to Split mode.

4.       Copy the contents below into the file. DO NOT overwrite the first line.

DocumentContent_Edit.ascx

<asp:TextBox ID="TextBox1" runat="server" Text='<%# FieldValueEditString %>'  CssClass="DDTextBox"Visible="False"></asp:TextBox>

<asp:FileUpload ID="FileUpload1" runat="server" />

<asp:Button ID="btnUpload" runat="server" CausesValidation="False" Text="Upload" UseSubmitBehavior="False" />

<br />

<asp:Label ID="lblStatus" runat="server" Font-Bold="False" Font-Size="Small" ForeColor="Red"></asp:Label>

<asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator1" CssClass="DDControl DDValidator"ControlToValidate="TextBox1" Display="Static" Enabled="false" />

<asp:RegularExpressionValidator runat="server" ID="RegularExpressionValidator1" CssClass="DDControl DDValidator"ControlToValidate="TextBox1" Display="Static" Enabled="false" />

<asp:DynamicValidator runat="server" ID="DynamicValidator1" CssClass="DDControl DDValidator"ControlToValidate="TextBox1" Display="Static" />


5.       Save.

6.       Open the DocumentContent_Edit.ascx.vb file and past the following code into it.

DocumentContent_Edit.ascx.vb

Imports System.ComponentModel.DataAnnotations

Imports System.Web.DynamicData

Imports System.Web

 

Class Text_EditField

    Inherits FieldTemplateUserControl

 

    Public Overrides ReadOnly Property DataControl As Control

        Get

            Return TextBox1

        End Get

    End Property

 

    Protected Sub Page_Load(ByVal sender As ObjectByVal e As EventArgs)

        If (Column.MaxLength < 20) Then

            TextBox1.Columns = Column.MaxLength

        End If

        TextBox1.ToolTip = Column.Description

        SetUpValidator(RequiredFieldValidator1)

        SetUpValidator(RegularExpressionValidator1)

        SetUpValidator(DynamicValidator1)

    End Sub

 

    Protected Overrides Sub OnDataBinding(ByVal e As EventArgs)

        MyBase.OnDataBinding(e)

        If (Column.MaxLength > 0) Then

            TextBox1.MaxLength = Math.Max(FieldValueEditString.Length, Column.MaxLength)

        End If

    End Sub

 

    Protected Overrides Sub ExtractValues(ByVal dictionary As IOrderedDictionary)

        dictionary(Column.Name) = ConvertEditedValue(TextBox1.Text)

    End Sub

 

    Protected Sub btnUpload_Click(ByVal sender As ObjectByVal e As System.EventArgsHandles btnUpload.Click

        If (FileUpload1.HasFile) Then

            Dim FileExtension As String = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower().Remove(0, 1)'Removes . from file extension

 

            Try

                Dim FileData(FileUpload1.PostedFile.ContentLength) As Byte

                FileUpload1.PostedFile.InputStream.Read(FileData, 0, FileUpload1.PostedFile.ContentLength)

 

                Dim DocName As TextBox = Database1Model.Class1.FindDynamicControlRecursive(Page,"DocumentName").Controls(0).Controls(0)

                DocName.Text = FileUpload1.FileName.ToLower()

 

                Dim DocExt As TextBox = Database1Model.Class1.FindDynamicControlRecursive(Page,"DocumentExt").Controls(0).Controls(0)

                DocExt.Text = FileExtension

 

                Dim DocContent As TextBox = Database1Model.Class1.FindDynamicControlRecursive(Page,"DocumentContent").Controls(0).Controls(0)

                DocContent.Text = Convert.ToBase64String(FileData)

 

                lblStatus.Text = "File uploaded. Please save this record when ready."

 

            Catch ex As Exception

 

                lblStatus.Text = "Error. File could not be uploaded. Please contact system admin for assistance."

            End Try

 

        Else

            lblStatus.Text = "Please select a file."

        End If

    End Sub

End Class


 

Testing Your Customization

You can now test the custom templates behavior:  Run the project and navigate to the Insert.aspx page for the Document table.  You should now see the new controls for the Content field.

·         Try to upload a file. Click Browse to select a file and then click Upload to store the data on the page.

·         Remember to click Insert to actually commit the whole record to the database.

·         Save the record and you will be redirected to the List page. You should see “Binary Data” in the Content column.

·         Now go to the Details page and verify the Download button is being displayed.

·         Try to download it by clicking the Download button.

 

Congratulations! You have completed this tutorial successfully.  

Further Enhancements

Some suggested enhancements for your further coding pleasure would be: 

  1.        File SIZE limitation through the Web.Config file settings and/or Upload function checking.
  2.         File TYPE limitation through Upload function checking.

Troubleshooting

If the website’s FileUpload component suddenly stops working. Do not panic and just add the following codes to the Details.aspx, Edit.aspx, Insert.aspx in the \DynamicData\PageTemplates folder.

    <asp:UpdatePanel ID="UpdatePanel1" runat="server">

        <Triggers>

            <asp:PostBackTrigger ControlID="FormView1" />

        </Triggers>

        <ContentTemplate>

 

This is because sometimes the Dynamic Data Template and Asynchronous Updates of the UpdatePanel component does not work well with the FileUpload component.

It simply tells the UpdatePanel to do a full PostBack whenever anything within the FormView1 instance triggers an event. This will force a Synchronous Update which is what the FileUpload component is designed for.

Acknowledgements

I’d like to thank MSDN for being so free with their info and I have obviously cribbed a lot of their formatting and style from their articles, to Stephen Naughton for his elegant FindDynamicControlRecursive function. Also, to all the great people out there who take the trouble to share their knowledge and help each other online.

Thanks and have a nice day!

Page copy protected against web site content infringement by Copyscape

About the Author

Tristanlim
Full Name: Tristan Lim
Member Level: Starter
Member Status: Member
Member Since: 12/17/2010 2:59:25 AM
Country: Singapore

http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)