Howdy, Stranger!

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

Categories

Filestreaming reloads all docs, just want to load one doc

sharoncsharonc Posts: 11Member
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-PCGEOINT;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-PCGEOINT;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: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);
fs2.Flush();
fs2.Close();
}

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

}
}
Sign In or Register to comment.