Filestreaming, loads fine into SQL Server 2008 R2, reloads all pictures NTFS

[sharon.chapman7@gmail.com]

Hi,
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 statement, but it doesn't like me using fi.Name for my SQL where clause. Please help, I'm new to C#. 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()
{
InitializeComponent();
}

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);
rdr.Close();
//fs.Close();

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

con.Open();
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;
cmd.ExecuteNonQuery();
//con.Close();

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

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

//con.Open();
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);
//sfs.Close();

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

string filename = @"C:\Development\DEV_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);
fs2.Flush();
fs2.Close();
}

}
}
private void btnExit_Click(object sender, EventArgs e)
{
this.Close();
}

}
}

 

[cjard]

Can you give a better description of the error than "it doesn't like me using fi name"
I'm not really sure I'd know what to do with a computer that told me it didn't like something

Ps, I don't recommend using an email address as a forum username.. Gmail is good at spam filtering but there's no point needlessly opening yourself up to being a spam target by posting your mail address hundreds of times in threads on the web

 

[sharon.chapman7@gmail.com]

Streaming problems in C#

Can you give a better description of the error than "it doesn't like me using fi name"
I'm not really sure I'd know what to do with a computer that told me it didn't like something

Ps, I don't recommend using an email address as a forum username.. Gmail is good at spam filtering but there's no point needlessly opening yourself up to being a spam target by posting your mail address hundreds of times in threads on the web

Thanks for your advice.

I want to load the document the user selects to a folder on my hard drive. When I try to just select that record, I get the error "Incorrect syntax near '='" at this point: SqlDataReader rdr2 = cmd2.ExecuteReader();

string sql2 = "SELECT File_Storage.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT(), PathFilename FROM Available_Files WHERE PathFilename = @pathfilename";

My question is: How do I load to a folder on my hard drive, just the document the user selected?

Thanks,
Sharon