sharon.chapman7 -1 Newbie Poster

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 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()
        {
            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();
        }

    } 
}
mani-hellboy commented: your code not clear -1