Filestreaming reloads all docs, just want to load one doc - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


Filestreaming reloads all docs, just want to load one doc

sharoncsharonc Posts: 11Member
I'm trying to use filestreaming (SQL 2008) with Visual Studio 2008 to load a file into a folder. I have a window that allows the user to select the file he wants to load into a SQL Server table called Available_files and then my code loads the document into a folder called DEV_GEOINT Filestream files, on NTFS. The file loads into the SQL Server table, Available_Files with no problem. The problem comes whem I load the file into the folder DEV_GEOINT Filestream files on NTFS. It reloads all the files that are in the NTFS folder. how do I just have it load the file the user just requested? I try to do a select statment, but it doesn't like me using fi.Name for my SQL where clause. Please help. Below is my code.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Runtime.InteropServices;
using Microsoft.Win32.SafeHandles;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace GEOINT
public partial class Form1 : Form
public Form1()

private void btnInsert_Click(object sender, EventArgs e)
OpenFileDialog openFileDlg = new OpenFileDialog();
openFileDlg.InitialDirectory = Directory.GetCurrentDirectory();
if (openFileDlg.ShowDialog() == DialogResult.OK)
FileInfo fi = new FileInfo(openFileDlg.FileName);
FileStream fs = new FileStream(fi.FullName, FileMode.Open, FileAccess.Read);
BinaryReader rdr = new BinaryReader(fs);
byte[] fileData = rdr.ReadBytes((int)fs.Length);

string cs = @"server=JITC-PCGEOINT;database=DEV_GEOINT;integrated security=SSPI";
using (SqlConnection con = new SqlConnection(cs))

string sql = "INSERT INTO Available_Files VALUES (@filestorage, @pathfilename, default)";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.Add("@filestorage", SqlDbType.Image, fileData.Length).Value = fileData;
cmd.Parameters.Add("@pathfilename", SqlDbType.NVarChar).Value = fi.Name;

MessageBox.Show(fi.FullName, "Document/Picture Inserted Into Sql Server Table!", MessageBoxButtons.OK, MessageBoxIcon.Information);

//string cs2 = @"server=JITC-PCGEOINT;database=DEV_GEOINT;integrated security=SSPI";
//using (SqlConnection con = new SqlConnection(cs2))

SqlTransaction txn = con.BeginTransaction();
string sql2 = "SELECT File_Storage.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT(), PathFilename FROM Available_Files WHERE PathFilename = @pathfilename";
SqlCommand cmd2 = new SqlCommand(sql2, con, txn);
SqlDataReader rdr2 = cmd2.ExecuteReader();
while (rdr2.Read())
string filePath = rdr2[0].ToString();
byte[] objContext = (byte[])rdr2[1];
string pathfilename = rdr2[2].ToString();

SqlFileStream sfs = new SqlFileStream(filePath, objContext, System.IO.FileAccess.Read);

byte[] buffer = new byte[(int)sfs.Length];
sfs.Read(buffer, 0, buffer.Length);

// Write files in the table to the a directory.

string filename = @"C:DevelopmentDEV_GEOINT FileStream Files" + pathfilename;

System.IO.FileStream fs2 = new System.IO.FileStream(filename, FileMode.Create, FileAccess.Write, FileShare.Write);
fs2.Write(buffer, 0, buffer.Length);
MessageBox.Show(filename, "Document/Picture Inserted Into NTFS!", MessageBoxButtons.OK, MessageBoxIcon.Information);

private void btnExit_Click(object sender, EventArgs e)

Sign In or Register to comment.