locked
How to store binary (file) in SQL server using Entity Framework? RRS feed

  • Question

  • Hi,

     

    Sorry for the newbie question. I want to store files in SQL server. I searche a lot but didn't found what I could use. I'd like to use Entity Framework and C# in Visual Studio 2010. Could somebody help me with a code snippet or pointing me to a good howTo where I could find and understand how to store files in SQL using Entity Framework 4 and C#?

     

    Thanks,

    G


    I'm not a development! I'm an infrastructure engineer! Please be patient on the development forums! :)
    • Moved by SamAgain Tuesday, November 2, 2010 5:50 AM better fit (From:.NET Base Class Library)
    Monday, November 1, 2010 2:53 PM

Answers

  • Hi Homecsiga,

     

    In order to store files in SQL Server via Entity Framework, we just have to retrieve this file from file system and store it in a byte array. Then we could store it to SQL Server. Please refer to the code attached to the end of this reply.

     

    If anything is unclear, please let me know.

     

    # Table Definition

     

    CREATE TABLE [dbo].[FileStoreDemo](

                    [id] [int] NOT NULL,

                    [name] [nvarchar](100) NOT NULL,

                    [content] [varbinary](max) NULL,

                    CONSTRAINT [pk_filestoredemo_id] PRIMARY KEY CLUSTERED ([id] ASC))

     

    # End Definition

     

    # Code

     

    string filename = @”<file path>”;

     

    using (fooEntities fe = new fooEntities())

    {

        FileStoreDemo fsd = fe.FileStoreDemoes.CreateObject();

     

        fsd.name = new FileInfo(filename).Name;

        fsd.content = File.ReadAllBytes(filename);

        fe.FileStoreDemoes.AddObject(fsd);

     

        fe.SaveChanges();

    }

     

    # End Code


    Regards,
    Tom Li
    • Marked as answer by Homecsiga Wednesday, November 3, 2010 9:05 PM
    Wednesday, November 3, 2010 7:00 AM

All replies

  • Hi,

    Thanks for your post. Just to store files in SQL server, a quick reflection leads to a blanket solution to store the files as BLOB(Binary Large Object).  Anyway, please consider the SQL forum as a better option.


    Please mark the right answer at the right time.
    Thanks,
    Sam
    Tuesday, November 2, 2010 5:49 AM
  • ' This is a very simple example on how to load binary file such a JPEG, Word
    ' document etc into SQL Server, and how to retrieve it. It's based on an
    ' article by Michael in SQL Server Magazine. The article is available at
    ' http://www.sqlmag.com and enter 92955 in the Instant DocID box. The article
    ' as such requires a subscription, but there is a zip file with the article
    ' you can download, and includes a Visual Studio project of a slightly versatile
    ' program than the code below. There is also an SQL script that shows to load
    ' a BLOB with the BULK rowset provider (SQL 2005 only).

    ' To run the program below as-is, create this table in the tempdb in your
    ' local server:
    '
    ' CREATE TABLE BLOBtable (filename nvarchar(255)  NOT NULL,
    '                         BLOB     varbinary(MAX) NOT NULL)
    '
    ' If you are using SQL 2000, change "varbinary(MAX)" to "image".
    '
    ' Compile the program from the .Net Framework command prompt with
    '    vbc blobload.txt
    ' You need .Net Framework 2.0 (which comes with SQL Server.)
    '
    ' To run it you need to provide a filename and a direction "in" or "out".
    ' Note that "out" does not write the file, it merely retrieves to get the
    ' size of the BLOB.
    '
    ' In practice you may want to change connection string, table, add write-to-file
    ' etc.

     

    Imports System.IO
    Imports System.Data
    Imports System.Data.SqlClient

    Module BLOB

       Dim ConnectionString As String = _
             "Data Source=(local);Trusted_Connection=yes;Initial Catalog=tempdb"

       Private Sub ImportBLOB(filename As String)

            Dim fs As New FileStream _
                (filename, FileMode.Open, FileAccess.Read)
            Dim bloblen As Integer = CInt(fs.Length)
            Console.Write ("BLOB size: " & bloblen.ToString() & vbCrLf)
            Dim BLOB(bloblen) As Byte
            fs.Read(BLOB, 0, bloblen)
            fs.Close()

            Dim cn As New SqlConnection(ConnectionString)

            Dim cmd As SqlCommand = New SqlCommand _
                       ("INSERT INTO BLOBtable (filename, BLOB) " & _
                             "VALUES(@filename, @blob_data)", cn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.Add("@filename", SqlDbType.VarChar)
            cmd.Parameters("@filename").Direction = ParameterDirection.Input
            cmd.Parameters.Add("@blob_data", SqlDbType.Image)
            cmd.Parameters("@blob_data").Direction = ParameterDirection.Input

            ' Store the file name
            cmd.Parameters("@filename").Value = filename
            ' Store the byte array in the image field
            cmd.Parameters("@blob_data").Value = BLOB

            cn.Open()
            Dim Start As Long = System.DateTime.Now.Ticks
            cmd.ExecuteNonQuery()
            Console.Write("BLOB stored in " & _
                         ((System.DateTime.Now.Ticks - Start) / 10000) & _
                         " ms." & vbCrLf)
            cn.Close()

        End Sub

        Sub RetrieveBLOB(filename As String)

            Dim cn As New SqlConnection(ConnectionString)
            cn.open()

            Dim cmd As New SqlCommand  _
               ("SELECT BLOB FROM BLOBtable WHERE filename = @filename", cn)
            cmd.Parameters.Add("@filename", SqlDbType.VarChar)
            cmd.Parameters("@filename").Direction = ParameterDirection.Input
            cmd.Parameters("@filename").Value = filename

            Dim BLOB As Byte()
            Dim Start As Long = System.DateTime.Now.Ticks
            BLOB = cmd.ExecuteScalar()
            Console.Write("BLOB retrieved in " & _
                         ((System.DateTime.Now.Ticks - Start) / 10000) & _
                         " ms." & vbCrLf)
            Console.Write("Size of retrieved BLOB: " & BLOB.Length() & vbCrLf)

            cn.Close()
            cn = Nothing
        End Sub


        Public Sub Main(ByVal args() AS String)
           If args.Length <> 2 Then
              MsgBox("USAGE: blobload filename in|out")
              Exit Sub
           End If

           If args(1).ToLower() = "in" Then
              Call ImportBLOB(args(0))
           Else If args(1).ToLower() = "out" Then
              Call RetrieveBLOB(args(0))
           Else
              MsgBox("USAGE: blobload filename in|out")
           End If
         End Sub

    End Module

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, November 2, 2010 5:58 AM
    Answerer
  • Hi Homecsiga,

     

    In order to store files in SQL Server via Entity Framework, we just have to retrieve this file from file system and store it in a byte array. Then we could store it to SQL Server. Please refer to the code attached to the end of this reply.

     

    If anything is unclear, please let me know.

     

    # Table Definition

     

    CREATE TABLE [dbo].[FileStoreDemo](

                    [id] [int] NOT NULL,

                    [name] [nvarchar](100) NOT NULL,

                    [content] [varbinary](max) NULL,

                    CONSTRAINT [pk_filestoredemo_id] PRIMARY KEY CLUSTERED ([id] ASC))

     

    # End Definition

     

    # Code

     

    string filename = @”<file path>”;

     

    using (fooEntities fe = new fooEntities())

    {

        FileStoreDemo fsd = fe.FileStoreDemoes.CreateObject();

     

        fsd.name = new FileInfo(filename).Name;

        fsd.content = File.ReadAllBytes(filename);

        fe.FileStoreDemoes.AddObject(fsd);

     

        fe.SaveChanges();

    }

     

    # End Code


    Regards,
    Tom Li
    • Marked as answer by Homecsiga Wednesday, November 3, 2010 9:05 PM
    Wednesday, November 3, 2010 7:00 AM
  • Thank you!
    I'm not a development! I'm an infrastructure engineer! Please be patient on the development forums! :)
    Wednesday, November 3, 2010 9:05 PM
  • Hi Homecsiga,

     

    In order to store files in SQL Server via Entity Framework, we just have to retrieve this file from file system and store it in a byte array. Then we could store it to SQL Server. Please refer to the code attached to the end of this reply.

     

    If anything is unclear, please let me know.

     

    # Table Definition

     

    CREATE TABLE [dbo].[FileStoreDemo](

                    [id] [int] NOT NULL,

                    [name] [nvarchar](100) NOT NULL,

                    [content] [varbinary](max) NULL,

                    CONSTRAINT [pk_filestoredemo_id] PRIMARY KEY CLUSTERED ([id] ASC))

     

    # End Definition

     

    # Code

     

    string filename = @”<file path>”;

     

    using (fooEntities fe = new fooEntities())

    {

        FileStoreDemo fsd = fe.FileStoreDemoes.CreateObject();

     

        fsd.name = new FileInfo(filename).Name;

        fsd.content = File.ReadAllBytes(filename);

        fe.FileStoreDemoes.AddObject(fsd);

     

        fe.SaveChanges();

    }

     

    # End Code


    Hi , I want to store binary files (blob)I searched many forums  but didn't found !
    My probleme is : how to store binary file (Doc , Pdf , txt..) type blob into database using Entity framework(edmx)
    please help !


    Monday, November 11, 2013 12:41 AM