Save Binary Files in SQL
Uploaded by
ScribdTranslationsSave Binary Files in SQL
Uploaded by
ScribdTranslationsSave Binary Files in SQL Server
By bpbrainiak
ExtraI do ofPicacodes
Mon Feb 20 2006
In my current project, an ASP .NET application, the client expressed the desire to save in
database of letters, based on Word templates and generated through the application.
They want to store them in the database because, as official documents, they have to
can be reprinted or viewed exactly as they were created in their day.
Therefore, we need a method to store Word files in the database.
binary format, data stream and be able to recover them later for their
visualization.
Note: the purpose of this post is not the creation of Word files from C#. You can
use Word automation for this, or third-party components such as those published
because they are excellent.
We are going to create a simple table in SQL Server to host the documents. For this
for example, we will call it DocsBinarios, and it will have the following structure:
Field Type Nulls?
DocId Int (identity) No
Document Image No
DocumentName VarChar(100) No
It is quite self-explanatory: the field DocId is an auto-generated primary key field.
The fieldDocument is the one that will store the bit streams, that is, the ones themselves.
Word files in binary format. The field NameDoc will store the name that
originally provided to the document when it was generated.
Later we will create a stored procedure, UploadDocs, that will be used to save
records in this table:
CREATE PROCEDURE UploadDoc (@doc AS Image, @nombre AS VarChar(100))
AS
INSERT INTO BinaryDocs (Document, DocumentName) VALUES (@doc, @name)
GO
Colorized by:CarlosAg.CodeColorizer
It is also quite self-explanatory: a simple INSERT INTO that receives as
parameters unImage and unVarChar
And this is the method that saves the data in the database:
private void SaveFileToDatabase(string[] paths, string[] files)
{
We create a new FileStream object to read the file.
Word in binary mode
System.IO.FileStream fs = new FileStream(sPath + sFile,
System.IO.FileMode.Open);
//We create a byte array to store the data read by fs.
Byte[] data= new byte[fs.Length];
And we store the data in the array data
fs.Read(data, 0, Convert.ToInt32(fs.Length));
We open a connection. In this case, the data of the string
connection to the database is recovered from a section of the
//web.config file through ConfigurationSettings
SqlConnection cnn=
newSqlConnection(ConfigurationSettings.AppSettings["databaseConnection"]);
cnn.Open();
//We create a StoredProcedure type command to invoke
//UploadDocs
SqlCommand cmd = new SqlCommand("UploadDoc", cnn);
cmd.CommandType=CommandType.StoredProcedure;
We add the expected parameters and their corresponding values
cmd.Parameters.Add(“@doc”, data);//los datos del fichero Word
cmd.Parameters.Add("@nombre", sFichero);//and its name
We execute the stored procedure, which inserts a new
Register in DocsBinarios with the data we want to enter
cmd.ExecuteNonQuery();
We close the connection and the file
cnn.Close();
fs.Close();
}
Colorized by:CarlosAg.CodeColorizer
Now let's look at the method to retrieve that data from the database and display it as
a Word file:
private void ReadFromDB()
{
We open the connection, exactly like before.
SqlConnection cnn=
newSqlConnection(ConfigurationSettings.AppSettings["conexionBD"]);
cnn.Open();
This is the command that opens the log we want. For this
We always open the first record; it should be modified.
// this code for the method to receive as a parameter the
the record we want to open, of course.
SqlCommand comm = new SqlCommand("SELECT * FROM DocsBinarios")
" WHEREdocId =1", cnn);
comm.CommandType=CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds= newDataSet("Binaries");
da.Fill(ds);
We create an array of bytes that contains the stored bytes.
in the Document field of the table
byte[] bits = ((byte[])(ds.Tables[0].Rows[0].ItemArray[1]));
cnn.Close();
We are going to save that array of bytes as a file in the
hard drive, a temporary file that can be discarded later.
To avoid user concurrency issues,
//we generate a unique name for it
stringsFile=“tmp”+ GenerarNombreFichero() +“.doc”;
We create a new FileStream, which this time will serve to
create a file with the specified name
FileStream fs = new FileStream(Server.MapPath(".") +
@"\DocsGenerated\"+ sFile, FileMode.Create);
And we write to disk the array of bytes that make up
//the Word file
fs.Write(bits,0, Convert.ToInt32(bits.Length));
fs.Close();
To display the file, we use a function
//JavaScript llamada mostrarFichero (que lo único que
to load the specified file
And we make it run in a pop-up.
stringscript=“<script languaje=‟javascript‟> ”;
script += "showFile('DocsGenerated/' + sFile + '")";
</script>
Page.RegisterStartupScript("showFile", script);
}
Colorized by:CarlosAg.CodeColorizer
And that's it. Using this last method, the Word file is loaded from the database and is
show in a popup and in all its glory.
To have the complete code, this is the JavaScript function that loads the file
generated from database:
<script language="javascript">
function showFile(destination) {
window.open(destination,null,"directories=no,height=600,
width=800,left=0,top=0,location=no,menubar=yes,
status=no,toolbar=yes,resizable=yes
document.forms(0).submit();
}
</script>
Colorized by:CarlosAg.CodeColorizer
And this is the method GenerateFileName(), which uses the server's ticks account to
create quasi-unique file names.
private string GenerateFileName()
{
lastTick=0;
while(ultimoTick==Environment.TickCount)
{
System.Threading.Thread.Sleep(1);
}
lastTick=Environment.TickCount;
returnDateTime.Now.ToString(“yyyyMMddhhmmss”) +“.”+
ultimoTick.ToString();
}
