Geek Answers Handbook

Upload / download file with SQL Server 2005/2008 from WinForms C # application?

I use DocX to create .docx files. Instead of storing them on your hard drive, Share or SharePoint, I prefer to store them in an SQL database. So my questions are:

  • How to write the correct code to save the file in this database?
  • How to write the correct code to extract the file to this database?
  • What data type should I set for a table for storing docx files? Is this an image?

Concerning,

Madboy

PS. I would prefer the correct code in terms of usage. Using the old "school" method:

using (var varConnection = Locale.sqlConnectOneTime(Locale.sqlDataConnectionDetailsDepozyt)) using (var sqlQuery = new SqlCommand(preparedCommand, varConnection)) using (var sqlQueryResult = sqlQuery.ExecuteReader()) while (sqlQueryResult != null && sqlQueryResult.Read())
+7
c # sql-server-2008 sql-server-2005
source share
2 answers

A docx file is like a zip file, which is a collection of several files. How about converting to binary and then saving to DB

Something how it should 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 shown below.

  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 a look at the following articles for reference and details.

Preservation

Retreival :

+6
source share

I know that this is not with the help of readers, but here is a sample for querying and saving to disk. To return it, it will be just an insert or update passing in 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();

maybe something like this

 while (myReader.Read()) { byte[] file = myReader.GetBytes(0)); // might be file = (byte[])GetValue(0); }
+2
source share

More articles:


All Articles