ذخیره فایل در پایگاه داده SQL Server با استفاده از کنترل FileUpload

دراین مقاله نحوه ذخیره مستقیم فایل در پایگاه داده با استفاده از کنترل FileUpload در ASP.NET شرح داده می شود .

طراحی پایگاه داده

من یک پایگاه داده با نام dbFiles که دارای یک جدول با نام dbFiles ایجاد کردم . در جدول ۴ فیلد وجود دارد. نمای کامل در تصویر زیر :

همانطور که در بالا مشاهده می کنید Identity Specification در فیلد id به مقدار true تنظیم شده است به این معنی که به صورت خودکار خودش افزایش پیدا کند.

فیلد	توضیحات
id	شناسه
Name	نام فایل
Content Type	نوع محتوای فایل
Data	فایل ذخیره شده بر اساس داده های باینری

ارتباط با پایگاه داده ( Connection String)

Connection String جهت ارتباط با پایگاه داده در زیر آمده است با توجه به نیازتان آن را تغییر دهید

<connectionStrings>
<add name="conString" connectionString="Data Source=.\SQLEXPRESS;database=dbFiles; Integrated Security=true"/>
</connectionStrings >

</connectionStrings >

برای شروع یک کنترل FileUpload ، یک دکمه و یک Lable برای نمایش پیام اضافه کنید

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
<br />
<asp:Label ID="lblMessage" runat="server" Text="" Font-Names = "Arial"></asp:Label>

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

<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />

<br />

<asp:Label ID="lblMessage" runat="server" Text="" Font-Names = "Arial"></asp:Label>

کد های زیر برای صدا زدن رویداد کلیک در دکمه آپلود است

C#

protected void btnUpload_Click(object sender, EventArgs e)

{
    // Read the file and convert it to Byte Array
    string filePath = FileUpload1.PostedFile.FileName;  
    string filename = Path.GetFileName(filePath);
    string ext = Path.GetExtension(filename);
    string contenttype = String.Empty;
 
    //Set the contenttype based on File Extension
    switch(ext)
    {
        case ".doc":
            contenttype = "application/vnd.ms-word";
            break;
        case ".docx":
            contenttype = "application/vnd.ms-word";
            break;
        case ".xls":
            contenttype = "application/vnd.ms-excel";
            break;
        case ".xlsx":
            contenttype = "application/vnd.ms-excel";
            break;
        case ".jpg":
            contenttype = "image/jpg";
            break;
        case ".png":
            contenttype = "image/png";
            break;
        case ".gif":
            contenttype = "image/gif";
            break;
        case ".pdf":
            contenttype = "application/pdf";
            break;
    }
    if (contenttype != String.Empty)
    {
 
        Stream fs = FileUpload1.PostedFile.InputStream;
        BinaryReader br = new BinaryReader(fs);
        Byte[] bytes = br.ReadBytes((Int32)fs.Length);
 
        //insert the file into database
        string strQuery = "insert into tblFiles(Name, ContentType, Data)" + " values (@Name, @ContentType, @Data)";
        SqlCommand cmd = new SqlCommand(strQuery);
        cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename;
        cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = contenttype;
        cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;
        InsertUpdateData(cmd);
        lblMessage.ForeColor = System.Drawing.Color.Green;  
        lblMessage.Text = "File Uploaded Successfully";
    }
    else
    {
        lblMessage.ForeColor = System.Drawing.Color.Red;   
        lblMessage.Text = "File format not recognised." +
          " Upload Image/Word/PDF/Excel formats";
    }
}

protected void btnUpload_Click(object sender, EventArgs e)

{

// Read the file and convert it to Byte Array

string filePath = FileUpload1.PostedFile.FileName;

string filename = Path.GetFileName(filePath);

string ext = Path.GetExtension(filename);

string contenttype = String.Empty;

//Set the contenttype based on File Extension

switch(ext)

{

case ".doc":

contenttype = "application/vnd.ms-word";

break;

case ".docx":

contenttype = "application/vnd.ms-word";

break;

case ".xls":

contenttype = "application/vnd.ms-excel";

break;

case ".xlsx":

contenttype = "application/vnd.ms-excel";

break;

case ".jpg":

contenttype = "image/jpg";

break;

case ".png":

contenttype = "image/png";

break;

case ".gif":

contenttype = "image/gif";

break;

case ".pdf":

contenttype = "application/pdf";

break;

}

if (contenttype != String.Empty)

{

Stream fs = FileUpload1.PostedFile.InputStream;

BinaryReader br = new BinaryReader(fs);

Byte[] bytes = br.ReadBytes((Int32)fs.Length);

//insert the file into database

string strQuery = "insert into tblFiles(Name, ContentType, Data)" + " values (@Name, @ContentType, @Data)";

SqlCommand cmd = new SqlCommand(strQuery);

cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename;

cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = contenttype;

cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;

InsertUpdateData(cmd);

lblMessage.ForeColor = System.Drawing.Color.Green;

lblMessage.Text = "File Uploaded Successfully";

}

else

{

lblMessage.ForeColor = System.Drawing.Color.Red;

lblMessage.Text = "File format not recognised." +

" Upload Image/Word/PDF/Excel formats";

}

VB.Net

Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As EventArgs)
  ' Read the file and convert it to Byte Array
  Dim filePath As String = FileUpload1.PostedFile.FileName
  Dim filename As String = Path.GetFileName(filePath)
  Dim ext As String = Path.GetExtension(filename)
  Dim contenttype As String = String.Empty
 
  'Set the contenttype based on File Extension
  Select Case ext
    Case ".doc"
      contenttype = "application/vnd.ms-word"
      Exit Select
    Case ".docx"
      contenttype = "application/vnd.ms-word"
      Exit Select
    Case ".xls"
      contenttype = "application/vnd.ms-excel"
      Exit Select
    Case ".xlsx"
      contenttype = "application/vnd.ms-excel"
      Exit Select
    Case ".jpg"
      contenttype = "image/jpg"
      Exit Select
    Case ".png"
      contenttype = "image/png"
      Exit Select
    Case ".gif"
      contenttype = "image/gif"
      Exit Select
    Case ".pdf"
      contenttype = "application/pdf"
      Exit Select
    End Select
    If contenttype <> String.Empty Then
      Dim fs As Stream = FileUpload1.PostedFile.InputStream
      Dim br As New BinaryReader(fs)
      Dim bytes As Byte() = br.ReadBytes(fs.Length)
 
      'insert the file into database
       Dim strQuery As String = "insert into tblFiles" _
       & "(Name, ContentType, Data)" _
       & " values (@Name, @ContentType, @Data)"
       Dim cmd As New SqlCommand(strQuery)
       cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename
       cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value _
       = contenttype
       cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes
       InsertUpdateData(cmd)
       lblMessage.ForeColor = System.Drawing.Color.Green
       lblMessage.Text = "File Uploaded Successfully"
     Else
       lblMessage.ForeColor = System.Drawing.Color.Red
       lblMessage.Text = "File format not recognised." _
       & " Upload Image/Word/PDF/Excel formats"
     End If
  End Sub

Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As EventArgs)

' Read the file and convert it to Byte Array

Dim filePath As String = FileUpload1.PostedFile.FileName

Dim filename As String = Path.GetFileName(filePath)

Dim ext As String = Path.GetExtension(filename)

Dim contenttype As String = String.Empty

'Set the contenttype based on File Extension

Select Case ext

Case ".doc"

contenttype = "application/vnd.ms-word"

Exit Select

Case ".docx"

contenttype = "application/vnd.ms-word"

Exit Select

Case ".xls"

contenttype = "application/vnd.ms-excel"

Exit Select

Case ".xlsx"

contenttype = "application/vnd.ms-excel"

Exit Select

Case ".jpg"

contenttype = "image/jpg"

Exit Select

Case ".png"

contenttype = "image/png"

Exit Select

Case ".gif"

contenttype = "image/gif"

Exit Select

Case ".pdf"

contenttype = "application/pdf"

Exit Select

End Select

If contenttype <> String.Empty Then

Dim fs As Stream = FileUpload1.PostedFile.InputStream

Dim br As New BinaryReader(fs)

Dim bytes As Byte() = br.ReadBytes(fs.Length)

'insert the file into database

Dim strQuery As String = "insert into tblFiles" _

& "(Name, ContentType, Data)" _

& " values (@Name, @ContentType, @Data)"

Dim cmd As New SqlCommand(strQuery)

cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename

cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value _

= contenttype

cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes

InsertUpdateData(cmd)

lblMessage.ForeColor = System.Drawing.Color.Green

lblMessage.Text = "File Uploaded Successfully"

Else

lblMessage.ForeColor = System.Drawing.Color.Red

lblMessage.Text = "File format not recognised." _

& " Upload Image/Word/PDF/Excel formats"

End If

End Sub

در کد های بالا فایل آپلود شده ابتدا به صورت Stream (جریان) خوانده شد و بعد تبدیل به آرایه از بایت ها با استفاده از Binary Reader شد و در نهایت آرایه بایت ها در پایگاه داده با استفاده از متد InsertUpdateData ذخیره شد.

تابع InsertUpdateData در زیر آمده است

private Boolean InsertUpdateData(SqlCommand cmd)
{
    String strConnString = System.Configuration.ConfigurationManager
    .ConnectionStrings["conString"].ConnectionString;
    SqlConnection con = new SqlConnection(strConnString);
    cmd.CommandType = CommandType.Text;
    cmd.Connection = con;
    try
    {
        con.Open();
        cmd.ExecuteNonQuery();
        return true;
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
        return false;
    }
    finally
    {
        con.Close();
        con.Dispose();
    }
}

private Boolean InsertUpdateData(SqlCommand cmd)

{

String strConnString = System.Configuration.ConfigurationManager

.ConnectionStrings["conString"].ConnectionString;

SqlConnection con = new SqlConnection(strConnString);

cmd.CommandType = CommandType.Text;

cmd.Connection = con;

try

{

con.Open();

cmd.ExecuteNonQuery();

return true;

}

catch (Exception ex)

{

Response.Write(ex.Message);

return false;

}

finally

{

con.Close();

con.Dispose();

}

VB.NET

Public Function InsertUpdateData(ByVal cmd As SqlCommand) As Boolean
    Dim strConnString As String = System.Configuration.
    ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Dim con As New SqlConnection(strConnString)
    cmd.CommandType = CommandType.Text
    cmd.Connection = con
    Try
      con.Open()
      cmd.ExecuteNonQuery()
      Return True
    Catch ex As Exception
      Response.Write(ex.Message)
      Return False
    Finally
      con.Close()
      con.Dispose()
    End Try
End Function

Public Function InsertUpdateData(ByVal cmd As SqlCommand) As Boolean

Dim strConnString As String = System.Configuration.

ConfigurationManager.ConnectionStrings("conString").ConnectionString

Dim con As New SqlConnection(strConnString)

cmd.CommandType = CommandType.Text

cmd.Connection = con

Try

con.Open()

cmd.ExecuteNonQuery()

Return True

Catch ex As Exception

Response.Write(ex.Message)

Return False

Finally

con.Close()

con.Dispose()

End Try

End Function

شما می توانید کد های مربوطه (VB و #C) را از لینک زیر دانلود کنید .

دانلود کدها

حتما نگاهی به این مقالات بیندازید

۹۷/۰۵/۲۱

mohamad azari

آکادمی برنامه نویسان مشهد

آکادمی برنامه نویسان مشهد

دوره های آموزش برنامه نویسی و طراحی سایت در مشهد