Imports System.IO
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class ucLetterView
Private kCS As String = My.Settings.SMSAConnectionString '"Data Source=(local)\SQL2008;Initial Catalog=TestFileStream;Integrated Security=True"
' Dim LeterViewTap As New DataSet1DocTableAdapters.NewTableTableAdapter
' Dim LeterViewDat As New DataSet1Doc.NewTableDataTable
Private Declare Function ShellEx Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hWnd As Integer, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Integer) As Integer
Dim connection As SqlConnection
Private Sub ucLetterView_Load(sender As Object, e As EventArgs) Handles Me.Load
' LeterViewTap.Fill(LeterViewDat)
' Dim qFile = (From k In LeterViewDat Select k.FileName, k.FileExtension).ToList
' DGV1.DataSource = qFile
'Add Button Starts
Dim dgButtonColumn As New DataGridViewButtonColumn
dgButtonColumn.HeaderText = ""
dgButtonColumn.UseColumnTextForButtonValue = True
dgButtonColumn.Text = "Open"
dgButtonColumn.Name = "OpenFile"
dgButtonColumn.ToolTipText = "View File"
dgButtonColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCellsExcep tHeader
dgButtonColumn.FlatStyle = FlatStyle.System
' dgButtonColumn.DefaultCellStyle.BackColor = Color.Gray
' dgButtonColumn.DefaultCellStyle.ForeColor = Color.White
DGV1.Columns.Add(dgButtonColumn)
End Sub
Private Sub btnRefreshList_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefreshList.Click
Dim dr As SqlDataReader = GetFileList()
Dim tb As DataTable = New DataTable("Files")
tb.Load(dr)
DGV1.DataSource = tb
End Sub
Private Function GetFileList() As SqlDataReader
Dim cn As New SqlConnection(kCS)
Dim cmd As New SqlCommand("SELECT INTID, [Name], FileType FROM Files", cn)
cn.Open()
Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
End Function
Private Sub btnBrowseUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowseUpload.Click
Dim dlg As New OpenFileDialog
With dlg
.Title = "Select a file to upload"
If .ShowDialog = Windows.Forms.DialogResult.OK Then
txtFileUpload.Text = .FileName
End If
.Dispose()
End With
End Sub
Private Sub btnBrowseDownload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowseDownload.Click
Dim dlg As New SaveFileDialog
With dlg
.Title = "Select a save location"
If .ShowDialog = Windows.Forms.DialogResult.OK Then
txtFileDownload.Text = .FileName
End If
.Dispose()
End With
End Sub
Private Sub btnUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpload.Click
'Validate that the file exist
If Not IO.File.Exists(txtFileUpload.Text) Then
MessageBox.Show("The file you selected is invalid")
Return
End If
AddNewFile(txtFileUpload.Text)
btnRefreshList.PerformClick()
End Sub
Private Function AddNewFile(ByVal pFile As String) As Guid
'fill some variables with values
Dim ID As Guid = Guid.NewGuid
Dim strFileName As String = IO.Path.GetFileName(pFile)
Dim FileExt As String = IO.Path.GetExtension(txtFileUpload.Text)
Dim FileExtentionLengh As SqlInt16 = Len(FileExt) - 1
Dim FileExtention As String = Microsoft.VisualBasic.Right(FileExt, FileExtentionLengh)
TextBox1.Text = FileExtention
'open a connection
Dim cn As New SqlConnection(kCS)
cn.Open()
'insert the new row
Dim cmd As New SqlCommand("INSERT INTO Files ([ID], [Name], FileType) VALUES (@ID, @Name, @Extention)", cn)
cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = ID
cmd.Parameters.Add("@Name", SqlDbType.VarChar, 20).Value = strFileName
cmd.Parameters.Add("@Extention", SqlDbType.VarChar, 6).Value = FileExtention
cmd.ExecuteNonQuery()
'retreive the context of the transaction and the pathname
Dim trx As SqlTransaction = cn.BeginTransaction
cmd = New SqlCommand("SELECT [Data].PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() " + _
"FROM Files " + _
"WHERE ID = @ID", cn)
cmd.Transaction = trx
cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = ID
Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleRow)
rdr.Read()
Dim strfilePath As String = rdr.GetString(0)
Dim trxID As Byte() = DirectCast(rdr(1), Byte())
rdr.Close()
'insert the file into SQL FileStream field
Using fs As IO.FileStream = IO.File.OpenRead(pFile)
Using sqlFS As New SqlTypes.SqlFileStream(strfilePath, trxID, IO.FileAccess.Write)
Dim buffer As Byte() = New Byte(512 * 1024) {}
Dim intPos As Integer = fs.Read(buffer, 0, buffer.Length)
Do While intPos > 0
sqlFS.Write(buffer, 0, intPos)
intPos = fs.Read(buffer, 0, buffer.Length)
Loop
End Using
End Using
trx.Commit()
cn.Close()
Return ID
End Function
Private Sub btnDownload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDownload.Click
If String.IsNullOrEmpty(txtFileDownload.Text) Then
MessageBox.Show("You first need to select an output location")
Return
End If
If String.IsNullOrEmpty(DGV1.Item("ID", DGV1.CurrentRow.Index).Value.ToString) Then
MessageBox.Show("You need to select a row")
Return
End If
Dim ID As Guid = New Guid(DGV1.Item("ID", DGV1.CurrentRow.Index).Value.ToString)
ExportFileToDisk(ID, txtFileDownload.Text)
MessageBox.Show("File downloaded from SQL")
End Sub
Private Sub ExportFileToDisk(ByVal pID As Guid, ByVal pFileName As String)
Dim cn As New SqlConnection(kCS)
cn.Open()
'retreive the context of the transaction and the pathname
Dim trx As SqlTransaction = cn.BeginTransaction()
Dim cmd As New SqlCommand("SELECT [Data].PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() " + _
"FROM Files " + _
"WHERE ID = @ID", cn)
cmd.Transaction = trx
cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier).Value = pID
Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleRow)
rdr.Read()
Dim strFilePath As String = rdr.GetString(0)
Dim trxID As Byte() = DirectCast(rdr(1), Byte())
rdr.Close()
'pull data down from the SQL into a buffer, then hand it off to the local file.
Using fs As IO.FileStream = IO.File.OpenWrite(pFileName)
Using sqlFS As New SqlTypes.SqlFileStream(strFilePath, trxID, IO.FileAccess.Read)
Dim buffer As Byte() = New Byte(512 * 1024) {}
Dim intPos As Integer = sqlFS.Read(buffer, 0, buffer.Length)
Do While intPos > 0
fs.Write(buffer, 0, intPos)
intPos = sqlFS.Read(buffer, 0, buffer.Length)
Loop
End Using
End Using
trx.Commit()
cn.Close()
End Sub
Private Sub DGV1_CellContentClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DGV1.CellContentClick
Dim strSql As String = ""
Dim connection As SqlConnection = New SqlConnection(kCS)
Try
Select Case e.ColumnIndex
Case Is > -1
If sender.Columns(e.ColumnIndex).Name = "OpenFile" Then
Dim FileExtensionDGV As String = DGV1.Rows(e.RowIndex).Cells("FileType").Value
Select Case DGV1.Rows(e.RowIndex).Cells("FileType").Value
Case FileExtensionDGV
downLoadFile(DGV1.Rows(e.RowIndex).Cells("INTID"). Value, DGV1.Rows(e.RowIndex).Cells("Name").Value, DGV1.Rows(e.RowIndex).Cells("FileType").Value)
End Select
End If
End Select
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
End Sub
Private Sub downLoadFile(ByVal iFileId As Int16, ByVal sFileName As String, ByVal sFileExtension As String)
Dim strSql As String
connection = New SqlConnection(kCS)
connection.Open()
'For Document
Try
'Get image data from gridview column.
strSql = "Select Data from Files WHERE INTID=" & iFileId
Dim sqlCmd As New SqlCommand(strSql, connection)
'Get image data from DB
Dim fileData As Byte() = DirectCast(sqlCmd.ExecuteScalar(), Byte())
Dim sTempFileName As String = Application.StartupPath & "\" & sFileName
If Not fileData Is Nothing Then
'Read image data into a file stream
Using fs As New FileStream(sFileName, FileMode.OpenOrCreate, FileAccess.Write)
fs.Write(fileData, 0, fileData.Length)
'Set image variable value using memory stream.
fs.Flush()
fs.Close()
End Using
'Open File
' 10 = SW_SHOWDEFAULT
ShellEx(Me.Handle, "Open", sFileName, "", "", 10)
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class
این یک مثال ساده است که تمام نیازهای برنامه نویسی شما را بر طرف می کنه و بنا به نیاز خودتون میتونید تغییرش بدید. در ضمن DGV هم همون دیتاگرید ویو ویژاول بیسیکه.
فقط به خاطر داشته باشید که قبلش باید در SQL فایل استریم را فعال کنید روش این کار را هم در اینترنت به راحتی می تونید پیدا کنید.
در ضمن همون طوری که قبلا گفتم فایل استریم از SQL2008 به بعد قابل استفاده هست.
موفق باشید
دسترسی به فایلهای یک پوشه شیر نشده در شبکه
پاسخ با نقل قول
نوشته شده توسط FastCode
لطفا یکی کمک کنه
