Upload / Download file from SQL Server 2005/2008 from WinForms C# app?
Solution 1
A docx file is like a zip file which is collection of several files. What about converting into binary and then saving into DB
Something as followswill work
To save
string filePath = "";
string connectionString = "";
FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
BinaryReader reader = new BinaryReader(stream);
byte[] file = reader.ReadBytes((int)stream.Length);
reader.Close();
stream.Close();
SqlCommand command;
SqlConnection connection = new SqlConnection(connectionString);
command = new SqlCommand("INSERT INTO FileTable (File) Values(@File)", connection);
command.Parameters.Add("@File", SqlDbType.Binary, file.Length).Value = file;
connection.Open();
command.ExecuteNonQuery();
Retrieval is a bit complicated process as follows
SqlConnection connection = new SqlConnection("");
string query =
@" SELECT File FROM FileTable where FileID =" + 125;
SqlCommand command = new SqlCommand(query, connection);
FileStream stream;
BinaryWriter writer;
int bufferSize = 100;
byte[] outByte = new byte[bufferSize];
long retval;
long startIndex = 0;
string pubID = "";
connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.Default);
while (reader.Read())
{
pubID = reader.GetString(0);
stream =
new FileStream("abc.docx", FileMode.OpenOrCreate, FileAccess.Write);
writer = new BinaryWriter(stream);
startIndex = 0;
retval = reader.GetBytes(1, startIndex, outByte, 0, bufferSize);
while (retval == bufferSize)
{
writer.Write(outByte);
writer.Flush();
startIndex += bufferSize;
retval = reader.GetBytes(1, startIndex, outByte, 0, bufferSize);
}
writer.Write(outByte, 0, (int)retval - 1);
writer.Flush();
writer.Close();
stream.Close();
} reader.Close();
connection.Close();
Have look at following articles for help and details
Saving:
Retreival :
Solution 2
I know it's not using readers, but here is a sample to query it and save to disk. To put it back it would just be an insert or update passing in a byte[]. The data type would be varbinary max in sql.
SqlConnection connection = new SqlConnection ("...");
connection.Open ();
SqlCommand command = new
SqlCommand ("select...e", connection);
byte[] buffer = (byte[]) command.ExecuteScalar ();
connection.Close();
FileStream fs = new FileStream(@"C:\test.pdf", FileMode.Create);
fs.Write(buffer, 0, buffer.Length);
fs.Close();
reader might be something like this
while (myReader.Read())
{
byte[] file = myReader.GetBytes(0));
// might be
file = (byte[])GetValue(0);
}
MadBoy
CEO of Evotec. I have a blog that has lots of technical articles and nice PowerShell based modules. I also have polish version of my website although blog content/technical stuff is in English. If you fancy PowerShell you can have a look at my GitHub projects. Whether you just want to use those or help you're very welcome! If you want to contact me visit my website and all the information you need is there.
Updated on June 09, 2022Comments
-
MadBoy almost 2 years
I'm using DocX to create .docx files. Instead of storing them on hard drive, Share or SharePoint i would prefer to store them inside SQL Database. So my questions are:
- How to write proper code to save file to that database?
- How to write proper code to retrive file to that database?
- What kind of datatype should i set for a table to hold the docx files? Is it Image?
With regards,
MadBoy
PS. I would prefer a proper code in terms of using Using the old 'school' way:
using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetailsDepozyt)) using (var sqlQuery = new SqlCommand(preparedCommand, varConnection)) using (var sqlQueryResult = sqlQuery.ExecuteReader()) while (sqlQueryResult != null && sqlQueryResult.Read())