Solved

saving a blob file to mysql using asp.net

Posted on 2003-12-07
4
1,101 Views
Last Modified: 2008-02-01
hi! i am having a problem saving my word document/pdf files to mysql database using asp.net. it is my first time to use the longblob datatype that's why it would be better if you could provide me a detailed solution. hope you can help me solve the problem. thanks a lot!!!!
0
Comment
Question by:john_20
  • 2
4 Comments
 
LVL 28

Accepted Solution

by:
iboutchkine earned 250 total points
ID: 9892177
' WriteBLOB demonstrates how to write image data to a SQL
' Server table. This example assumes a database named "Test"
' on the local machine, and a table named "blobTest" with
' an identity column "id," a "fileName" column, and a column
' named "blobData" to store the image data.
Public Sub WriteBLOB(ByVal fileName As String)

    ' Create connection object to SQL Server.
    Dim cn As New System.Data.SqlClient.SqlConnection( _
        "Data Source=(local);Initial Catalog=Test;Integrated Security=SSPI;")

    ' Create the command object that implements the INSERT command.
    Dim cmd As New System.Data.SqlClient.SqlCommand( _
        "INSERT INTO blobTest (blobData, fileName) VALUES (@blobData, @fileName) ", cn)

    ' Open the specified image file from disk.
    Dim fs As New System.IO.FileStream( _
          fileName, System.IO.FileMode.Open, System.IO.FileAccess.Read)
          
    Dim b(CType(fs.Length, Integer)) As Byte
    fs.Read(b, 0, b.Length)
    fs.Close()

    ' Create the parameters for the INSERT statement.
    cmd.Parameters.Add("@fileName", fileName)
    cmd.Parameters("@fileName").Direction = System.Data.ParameterDirection.Input

    cmd.Parameters.Add("@blobData", System.Data.SqlDbType.Image)
    cmd.Parameters("@blobData").Direction = System.Data.ParameterDirection.Input
    cmd.Parameters("@blobData").Value = b

    ' Open the connection and insert the data.
    Try
        cn.Open()
        cmd.ExecuteNonQuery()
    Catch e As SqlClient.SqlException
        Throw e
    Finally
        cn.Close()
    End Try

End Sub
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 9892181
Here is another example to save Word doc to Access database

Imports System.Data.OleDb
Imports System.IO

Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "
#End Region
#Region " Write to DB "
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        'take this doc and save it to the TestDb  too the table BLOBTable
        WriteDocumentToDB("c:\temp\word.doc")
    End Sub
    Public Sub WriteDocumentToDB(ByVal sFile As String)
        Dim da As New OleDbDataAdapter(fsSQL, fsConn)
        Dim cbDocument As OleDbCommandBuilder = New OleDbCommandBuilder(da)
        Dim ds As New DataSet()


        da.MissingSchemaAction = MissingSchemaAction.AddWithKey

        Dim fs As New FileStream(sFile, FileMode.OpenOrCreate, FileAccess.Read)
        Dim MyData(fs.Length) As Byte

        fs.Read(MyData, 0, fs.Length)
        fs.Close()

        Try
            da.Fill(ds, "BLOBTable")
        Catch e As Exception
            MsgBox(Err.Description)
        End Try
        Dim myRow As DataRow

        myRow = ds.Tables("BLOBTable").NewRow()
        myRow("Description") = "This is description of inserted document"
        myRow("BLOBField") = MyData
        ds.Tables("BLOBTable").Rows.Add(myRow)
        da.Update(ds, "BLOBTable")
        fs = Nothing
        cbDocument = Nothing
        ds = Nothing
        da = Nothing

        MsgBox("File saved to database")

    End Sub
#End Region
#Region " Restore from DB "
    Private Sub btnRestore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRestore.Click
        'check it will restore from the Row 0 of the BLOBTable (change if you want to
        'restore from different rows)
        ReadDocumentFromDB("c:\a\Word.doc")
    End Sub
    Public Sub ReadDocumentFromDB(ByVal sOutFile As String)
        Dim da As New OleDbDataAdapter(fsSQL, fsConn)
        Dim cbDocument As OleDbCommandBuilder = New OleDbCommandBuilder(da)
        Dim ds As New DataSet()

        da.Fill(ds, "BLOBTable")

        Dim myRow As DataRow
        'retrieve from Row(0)
        myRow = ds.Tables("BLOBTable").Rows(0)

        Dim MyData() As Byte

        MyData = myRow("BLOBField")

        Dim K As Long

        K = UBound(MyData)

        Dim fs As New FileStream(sOutFile, FileMode.OpenOrCreate, FileAccess.Write)

        fs.Write(MyData, 0, K)
        fs.Close()
        fs = Nothing
        cbDocument = Nothing
        ds = Nothing
        da = Nothing

        MsgBox("File retrieved")

    End Sub
#End Region
End Class

0
 

Author Comment

by:john_20
ID: 9961632
thanks!
0
 
LVL 4

Expert Comment

by:Moizsaif123
ID: 13545363
I am trying to do the same as above...I posted my question below...


http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/Q_21351438.html
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question