Embedding Documents in Data Fields

  • Thread starter Thread starter Les Nessman
  • Start date Start date
L

Les Nessman

I am trying to embed various documents in a database field (currently
access, but moving to sql server later). I can find no documentation to show
me how to do this. The document type might vary (excel, word, pdf), but I
need to be able to create records with an embedded field, and also allow a
user to edit or view the document in the associated program. If changes are
made, they should be saved back to the DB. Any help (url, code, whatever)
would be most appreciated.
 
Tips, try to avoid using BLOBS if you can. Becuase it will
affect the performance of you application.

I would suggest you to add the document to the disc and
add the path to the documents into the field instead.

/Fredrik Normén NSQUARED
 
Hi, Les Nessman!
I am trying to embed various documents in a database field (currently
access, but moving to sql server later).
Click to expand...

///
/// How to store a file image to the MSSQL server:
///

System.Data.DataSet ds;
System.Data.SqlClient.SqlConnection cn;
System.Data.SqlClient.SqlDataAdapter da;
System.Data.SqlClient.SqlCommandBuilder cb;

string FileName = "Disk:\\Path\\FileName.FileExt";
string connectionString = "Server=(local);Database=MyDatabase;User
ID=user;Password=password;";
string selectCommandText = "select [id], [image_data] from [dbo].[mytable]";

// connect server and fill DataSet from
cn = new System.Data.SqlClient.SqlConnection(connectionString);
ds = new System.Data.DataSet();
da = new System.Data.SqlClient.SqlDataAdapter(selectCommandText, cn);
da.Fill(ds);

// Load file image from source
byte[] arr = null;
using(System.IO.FileStream fs = new System.IO.FileStream(FileName,
System.IO.FileMode.Open, System.IO.FileAccess.Read,
System.IO.FileShare.Read))
{
arr = new byte[fs.Length];
fs.Read(arr, 0, (int)fs.Length);
}

ds.Tables[0].Rows[0]["image_data"] = arr;

// update server source

da.UpdateCommand = cb.GetUpdateCommand();
da.InsertCommand = cb.GetInsertCommand();
da.DeleteCommand = cb.GetDeleteCommand();
da.Update(ds);


///
/// sample to load file image to MSSQL server:
///

System.Data.DataSet ds;
System.Data.SqlClient.SqlConnection cn;
System.Data.SqlClient.SqlDataAdapter da;

string connectionString = "Server=(local);Database=MyDatabase;User
ID=user;Password=password;";
string selectCommandText = "select [id], [image_data] from [dbo].[mytable]";

// connect server and fill DataSet from
cn = new System.Data.SqlClient.SqlConnection(connectionString);
ds = new System.Data.DataSet();
da = new System.Data.SqlClient.SqlDataAdapter(selectCommandText, cn);
da.Fill(ds);

byte[] arr = ds.Tables[0].Rows[0]["image_data"];
// ^^^^^^^ must output to your file


// If you want to update the view based on a source table, then create this
view with VIEW_METADATA, SCHEMABINDING options:

CREATE VIEW dbo.mytable
WITH VIEW_METADATA, SCHEMABINDING
AS
select
[id]=[source_table].[id],
[image_data]= [source_table].[image_data]
from
dbo.source
 
Back
Top