Guardar Documento de Word en SQL Server

hola,

lo puedes guardar como binariote creas un campo para una imagen y lo
alamcenas alli, el problema es recuperarlo, ya que debes descargarlo como
archivo en tu pc , luego hacerle las ediciones y volver a montar la copia en
la base de datos.si me equivoco les agradeceria me dijeran otra forma.

"miguel" escribió en el mensaje
news:

Hola a todos,
Tengo un archivo combinado de word generado desde vb.net, pero ahora
necesito guardarlo en una base de datos SQL Server, ¿alguna idea?

Saludos!

Resources When Writing BLOB Values to SQL Server [Visual Basic]

You can write a binary large object (BLOB) to a database by inserting or
updating a field with a string value or byte array, depending on the type of
field in your database (see Writing BLOB Values to a Database). However, a
BLOB may be quite large and thus may consume extensive system memory when
written as a single value, resulting in decreased application performance.

A common practice to reduce the amount of memory used when writing a BLOB
value is to write the BLOB to the database in "chunks". The process of
writing a BLOB to a database in this way depends on the capabilities of your
database.

The following sample demonstrates how to write a BLOB in chunks to SQL
Server. The sample adds a new record to the Employees table of the Northwind
database, including an image of the employee, which is a BLOB. The sample
uses the UPDATETEXT function of SQL Server to write the image of the newly
added employee to the Photo field in chunks of a specified size.

The UPDATETEXT function requires a pointer to the BLOB field being updated.
In this sample, when the new employee record is added, the SQL Server
TEXTPTR function is called to return a pointer to the Photo field of the new
record. The returned pointer value is passed back as an output parameter.
The code in the sample retains this pointer and passes it to UPDATETEXT when
appending the chunks of data.

The Transact-SQL used to insert the new employee record and retain the
pointer to the Photo field is shown in the following example (where
@Identity and @Pointer are identified as output parameters for the
SqlCommand).

INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo,
Photo)
Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0)
SELECT @Identity = SCOPE_IDENTITY()
SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity
Note that an initial value of 0x0 (null) is inserted into the Photo field.
This ensures that a pointer value can be retrieved for the Photo field of
the newly inserted record. However, the null value will not affect the
appended chunks of data.

Having retained a pointer to the Photo field in the newly inserted record,
the sample can then append chunks of data to the BLOB field using the
UPDATETEXT function of SQL Server. The UPDATETEXT function takes as input
the field identifier (Employees.Photo), the pointer to the BLOB field, an
offset value that represents the location in the BLOB where the current
chunk will be written, and the chunk of data to append. The following code
example shows the syntax for the UPDATETEXT function (where @Pointer,
@Offset, and @Bytes are identified as input parameters for the SqlCommand).

UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes
The offset value is determined by the size of the memory buffer, which you
determine based on the needs of your application. A large buffer size will
write the BLOB faster, but will use more system memory. This sample uses a
rather small buffer size of 128 bytes. The offset value starts at 0 for the
first chunk of data, and is incremented by the size of the buffer for each
consecutive chunk.

This sample retrieves the employee photo, from a supplied file path, in
chunks. Each chunk is read into a byte array per the specified buffer size.
The byte array is then set as the value of the @Bytes input parameter of the
SqlCommand. The @Offset parameter value is updated and the SqlCommand is
executed, which appends the current chunk of bytes to the Photo field of the
employee record.

[Visual Basic]
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Public Class EmployeeData

Public Shared Sub Main()
Dim hireDate As DateTime = DateTime.Parse("4/27/98")
Dim newID As Integer = _
AddEmployee("Smith", "John", "Sales Representative", hireDate, 5,
"smith.bmp")
Console.WriteLine("New Employee added. EmployeeID = " & newID)
End Sub

Public Shared Function AddEmployee(lastName As String, firstName As
String, title As String, hireDate As DateTime, _
reportsTo As Integer, photoFilePath As String) As
Integer

Dim nwindConn As SqlConnection = New SqlConnection("Data
Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;")

Dim addEmp As SqlCommand = New SqlCommand("INSERT INTO Employees
(LastName, FirstName, Title, HireDate, ReportsTo, Photo) " & _
"Values(@LastName, @FirstName,
@Title, @HireDate, @ReportsTo, 0x0);" & _
"SELECT @Identity =
SCOPE_IDENTITY();" & _
"SELECT @Pointer =
TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity", nwindConn)

addEmp.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value =
lastName
addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value =
firstName
addEmp.Parameters.Add("@Title", SqlDbType.NVarChar, 30).Value =
title
addEmp.Parameters.Add("@HireDate", SqlDbType.DateTime).Value =
hireDate
addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value =
reportsTo

Dim idParm As SqlParameter = addEmp.Parameters.Add("@Identity",
SqlDbType.Int)
idParm.Direction = ParameterDirection.Output
Dim ptrParm As SqlParameter = addEmp.Parameters.Add("@Pointer",
SqlDbType.Binary, 16)
ptrParm.Direction = ParameterDirection.Output

nwindConn.Open()

addEmp.ExecuteNonQuery()

Dim newEmpID As Integer = CType(idParm.Value, Integer)

StorePhoto(photoFilePath, ptrParm.Value, nwindConn)

nwindConn.Close()

Return newEmpID
End Function

Public Shared Sub StorePhoto(fileName As String, pointer As Byte(),
nwindConn As SqlConnection)

Dim bufferLen As Integer = 128 ' The size of the "chunks" of the
image.

Dim appendToPhoto As SqlCommand = New SqlCommand("UPDATETEXT
Employees.Photo @Pointer @Offset 0 @Bytes", nwindConn)

Dim ptrParm As SqlParameter = appendToPhoto.Parameters.Add("@Pointer",
SqlDbType.Binary, 16)
ptrParm.Value = pointer
Dim photoParm As SqlParameter = appendToPhoto.Parameters.Add("@Bytes",
SqlDbType.Image, bufferLen)
Dim offsetParm As SqlParameter = appendToPhoto.Parameters.Add("@Offset",
SqlDbType.Int)
offsetParm.Value = 0

''''''''''''''''''''''''''''''''''''
'' Read the image in and write it to the database 128 (bufferLen) bytes
at a time.
'' Tune bufferLen for best performance. Larger values write faster, but
'' use more system resources.


Dim fs As FileStream = New FileStream(fileName, FileMode.Open,
FileAccess.Read)
Dim br As BinaryReader = New BinaryReader(fs)

Dim buffer() As Byte = br.ReadBytes(bufferLen)
Dim offset_ctr As Integer = 0

Do While buffer.Length > 0
photoParm.Value = buffer
appendToPhoto.ExecuteNonQuery()
offset_ctr += bufferLen
offsetParm.Value = offset_ctr
buffer = br.ReadBytes(bufferLen)
Loop

br.Close()
fs.Close()
End Sub

End Class
[C#]
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

public class EmployeeData
{
public static void Main()
{
DateTime hireDate = DateTime.Parse("4/27/98");
int newID = AddEmployee("Smith", "John", "Sales Representative",
hireDate, 5, "smith.bmp");
Console.WriteLine("New Employee added. EmployeeID = " + newID);
}

public static int AddEmployee(string lastName, string firstName, string
title, DateTime hireDate , int reportsTo, string photoFilePath)
{
SqlConnection nwindConn = new SqlConnection("Data
Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;");

SqlCommand addEmp = new SqlCommand("INSERT INTO Employees (LastName,
FirstName, Title, HireDate, ReportsTo, Photo) " +
"Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0);" +
"SELECT @Identity = SCOPE_IDENTITY();" +
"SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID =
@Identity", nwindConn);

addEmp.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value =
lastName;
addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value =
firstName;
addEmp.Parameters.Add("@Title", SqlDbType.NVarChar, 30).Value =
title;
addEmp.Parameters.Add("@HireDate", SqlDbType.DateTime).Value =
hireDate;
addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value =
reportsTo;

SqlParameter idParm = addEmp.Parameters.Add("@Identity", SqlDbType.Int);
idParm.Direction = ParameterDirection.Output;
SqlParameter ptrParm = addEmp.Parameters.Add("@Pointer",
SqlDbType.Binary, 16);
ptrParm.Direction = ParameterDirection.Output;

nwindConn.Open();

addEmp.ExecuteNonQuery();

int newEmpID = (int)idParm.Value;

StorePhoto(photoFilePath, (byte[])ptrParm.Value, nwindConn);

nwindConn.Close();

return newEmpID;
}

public static void StorePhoto(string fileName, byte[] pointer,
SqlConnection nwindConn)
{
int bufferLen = 128; // The size of the "chunks" of the image.

SqlCommand appendToPhoto = new SqlCommand("UPDATETEXT Employees.Photo
@Pointer @Offset 0 @Bytes", nwindConn);

SqlParameter ptrParm = appendToPhoto.Parameters.Add("@Pointer",
SqlDbType.Binary, 16);
ptrParm.Value = pointer;
SqlParameter photoParm = appendToPhoto.Parameters.Add("@Bytes",
SqlDbType.Image, bufferLen);
SqlParameter offsetParm = appendToPhoto.Parameters.Add("@Offset",
SqlDbType.Int);
offsetParm.Value = 0;

//''''''''''''''''''''''''''''''''''
// Read the image in and write it to the database 128 (bufferLen) bytes
at a time.
// Tune bufferLen for best performance. Larger values write faster, but
// use more system resources.

FileStream fs = new FileStream(fileName, FileMode.Open,
FileAccess.Read);
BinaryReader br = new BinaryReader(fs);

byte[] buffer = br.ReadBytes(bufferLen);
int offset_ctr = 0;

while (buffer.Length > 0)
{
photoParm.Value = buffer;
appendToPhoto.ExecuteNonQuery();
offset_ctr += bufferLen;
offsetParm.Value = offset_ctr;
buffer = br.ReadBytes(bufferLen);
}

br.Close();
fs.Close();
}
}
See Also
Writing BLOB Values to a Database | Sample ADO.NET Scenarios | Accessing
Data with ADO.NET | Using .NET Framework Data Providers to Access Data