Howdy, Stranger!

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

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.