Is it possible to save a document displayed in Word directly to a varbinary(max) column in SQL Server. Later I wish to recall the doc back into Word.
How would I accomplish this?
private void button1_Click(object sender, EventArgs e)
{
Document doc = new Document();
doc.LoadFromFile(@"test.docx");
MemoryStream ms = new MemoryStream();
doc.SaveToStream(ms, FileFormat.Docx);
ms.ToArray();
Byte[] byData = ms.ToArray();
string constr = "server=.;User Id=root;password=123;Database=mytest";
SqlConnection mycon = new SqlConnection(constr);
mycon.Open();
SqlCommand mycmd = new SqlCommand("insert into pro_table (pro_name,pro_file) values('testFile',@file)", mycon);
mycmd.Parameters.Add("@file", SqlDbType.Binary, byData.Length);
mycmd.Parameters["@file"].Value = byData;
mycmd.ExecuteNonQuery();
mycon.Close();
}
//get
private void button2_Click(object sender, EventArgs e)
{
string conn = "server=.;database=mytest;Uid=root;Pwd=123 ";
string str = "select pro_file from pro_table where pro_name='testFile' ";
SqlConnection myconn = new SqlConnection(conn);
SqlDataAdapter sda = new SqlDataAdapter(str, conn);
DataSet myds = new DataSet();
myconn.Open();
sda.Fill(myds);
myconn.Close();
Byte[] Files = (Byte[])myds.Tables[0].Rows[0]["pro_file"];
Document odoc = new Document();
MemoryStream oms = new MemoryStream(Files);
odoc.LoadFromStream(oms, FileFormat.Docx);
odoc.SaveToFile("get.docx", FileFormat.Docx);
}
Public Sub GrabDocx()
Dim objDataSet2 As New DataSet
Dim strDoc_Id As String = txtDoc_Id.Text
strSelect = "SELECT doc_id, doc_image FROM fpdocs " &
"WHERE doc_id = " & "'" & strDoc_Id & "'"
Dim objDataAdapter2 As New SqlDataAdapter(strSelect, objConnection)
'fill the dataSet object with data
objDataAdapter2.Fill(objDataSet2, "cldocs")
Dim objCommand As New SqlCommand(strSelect, objConnection)
'open connection
If objConnection.State = ConnectionState.Closed Then objConnection.Open()
Dim objSQLDataReader = objCommand.ExecuteReader
Dim objDataTable As New DataTable
objDataTable.Load(objSQLDataReader)
Dim row As DataRow = objDataTable.Rows(0)
Dim objDocument As New Document
If IsDBNull(row("doc_Image")) Then
'do not try display doc
Else
Dim objMemoryStream1 As New IO.MemoryStream(CType(row("doc_image"), Byte()))
Try
objDocument.LoadFromStream(objMemoryStream1, FileFormat.Docx)
Catch ExceptionErr As Exception
MessageBox.Show(ExceptionErr.Message)
End Try
'dispose of object memorystream
If Not IsNothing(objMemoryStream1) Then objMemoryStream1.Dispose()
End If
'close connection
If objConnection.State = ConnectionState.Open Then objConnection.Close()
'dispose of objects
If Not IsNothing(objCommand) Then objCommand.Dispose()
If Not IsNothing(objSQLDataReader) Then objSQLDataReader.Close()
If Not IsNothing(objDataTable) Then objDataTable.Dispose()
End Sub
Public Sub ImportDoc()
Dim Img As Image
Dim ImageList As New ImageList ' provide for example
Dim FileDiag As New OpenFileDialog
Dim strImagePath As String = ""
Dim blnHasRows As Boolean = False
Dim strSelect As String = ""
' Make sure a document format is selected
If Trim(cboDoc_Format.Text) = "" Then
MessageBox.Show("A doc format type must be selected.")
Exit Sub
ElseIf Trim(cboDoc_Format.Text) = "Docx" Then
'set provide image filter for filediag
FileDiag.Filter = "Image Files(*.docx;|*.docx;|All files (*.*)|*.*"
Dim intResult As DialogResult = FileDiag.ShowDialog
Dim strFileName As String = FileDiag.FileName
If intResult = Windows.Forms.DialogResult.OK Then
Dim objDocx As Byte() = File.ReadAllBytes(strFileName)
Dim objCommand As SqlCommand = New SqlCommand
Dim objMemoryStream As New IO.MemoryStream
'convert the stream to the byte array (Blob for SQL)
Dim b As Byte() = objMemoryStream.ToArray()
objCommand.CommandText = "UPDATE cldocs " &
"SET doc_image=@doc_image " &
"WHERE doc_id = " & "'" & strDoc_Id & "'"
'set the SQLCommand object properties
objCommand.Connection = objConnection
objCommand.CommandType = CommandType.Text
'paramters for columns
objCommand.Parameters.AddWithValue("@doc_image", b)
'open Connection
If objConnection.State = ConnectionState.Closed Then objConnection.Open()
Try
objCommand.ExecuteNonQuery()
Catch SqlExceptionErr As SqlException
MessageBox.Show(SqlExceptionErr.Message)
End Try
'close connection
If objConnection.State = ConnectionState.Open Then objConnection.Close()
'reset save document flag
blnSaveDoc = True
'dispose of objects
If Not IsNothing(objCommand) Then objCommand.Dispose()
If Not IsNothing(objCommand) Then objMemoryStream.Dispose()
End If
End If
byte[] objDocx = File.ReadAllBytes(strFileName);
objCommand.Parameters.AddWithValue("@doc_image", objDocx);
'save
Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles button1.Click
Dim doc As New Document()
doc.LoadFromFile("test.docx")
Dim ms As New MemoryStream()
doc.SaveToStream(ms, FileFormat.Docx)
ms.ToArray()
Dim byData() As Byte = ms.ToArray()
Dim constr As String = "server=.;User Id=root;password=123;Database=mytest"
Dim mycon As New SqlConnection(constr)
mycon.Open()
Dim mycmd As New SqlCommand("insert into pro_table (pro_name,pro_file) values('testFile',@file)", mycon)
mycmd.Parameters.Add("@file", SqlDbType.Binary, byData.Length)
mycmd.Parameters("@file").Value = byData
mycmd.ExecuteNonQuery()
mycon.Close()
End Sub
'get
Private Sub button2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles button2.Click
Dim conn As String = "server=.;database=mytest;Uid=root;Pwd=123 "
Dim str As String = "select pro_file from pro_table where pro_name='testFile' "
Dim myconn As New SqlConnection(conn)
Dim sda As New SqlDataAdapter(str, conn)
Dim myds As New DataSet()
myconn.Open()
sda.Fill(myds)
myconn.Close()
Dim Files() As Byte = CType(myds.Tables(0).Rows(0)("pro_file"), Byte())
'save to file
'BinaryWriter bw = new BinaryWriter(File.Open("D:\\result.docx", FileMode.OpenOrCreate));
'bw.Write(Files);
'bw.Close();
Dim odoc As New Document()
Dim oms As New MemoryStream(Files)
odoc.LoadFromStream(oms, FileFormat.Docx)
odoc.SaveToFile("get.docx", FileFormat.Docx)
End Sub
mycmd.Parameters.Add("@file", SqlDbType.Binary, byData.Length)
mycmd.Parameters("@file").Value = byData
'1. Retrieve document from SQL Server and load it into Word.
Dim doc As New Document()
doc.LoadFromStream(MS, FileFormat.Docx)
'2. Make changes to the document.
Dim resultMs As New IO.MemoryStream()
'save to stream
doc.SaveToStream(resultMs, FileFormat.Docx2010)
Dim resultData() As Byte = resultMs.ToArray()
'save bytes back to SQL Server