CODEWITHSUNDEEP
c#sqlvisual-studio-2013
anugrah s p
anugrah s p

Reputation: 17

How to insert word document into sql server and download it into my drive? (visual studio c# windows application)

can any one suggest a code to insert word document to SQL database and download it from SQL database.(code should be visual studio c# windows application)

Thank you

Upvotes: 0

Views: 5428

Answers (3)

yantaq
yantaq

Reputation: 4038

Assume you have this table schema and you are using local sql db:

CREATE TABLE [FileTable1]
(
    [filePath] VARCHAR(100),
    [data] VARBINARY(MAX)
)

Here is the code to Insert:

static void InsertFile(string filename)
{   
    SqlConnection conn = new SqlConnection("Server=(localdb)\\v11.0 ; Initial Catalog = {your db name}; Integrated Security = SSPI");

    SqlCommand cmd = new SqlCommand("INSERT INTO FileTable1 VALUES (@filePath, @data)", conn);

    cmd.Parameters.AddWithValue("@filePath", Path.GetFileName(filename));
    cmd.Parameters.Add("@data", SqlDbType.VarBinary, -1).Value = File.ReadAllBytes(filename);
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
}

here is the code to insert:

string filetoInsert = @"C:\Temp\Test1.docx"; 
InsertFile(filetoInsert);

you can then retrieve it from database, like so:

static byte[] ReadFile(string filename)
{   
     SqlConnection conn= new SqlConnection("Server=(localdb)\\v11.0 ; Initial Catalog = {your db name}; Integrated Security = SSPI");

    SqlCommand cmd = new SqlCommand("SELECT * FROM FileTable1 WHERE filePath=@filePath", conn);

    cmd.Parameters.AddWithValue("@filePath", filename);
    conn.Open();
    SqlDataReader rdr = cmd.ExecuteReader();
    rdr.Read();
    MemoryStream ms = new MemoryStream();

    long startIndex= 0;
    const int readSize= 256;
    while (true)
    {
        byte[] buffer = new byte[readSize];
        long bytesRead= rdr.GetBytes(1, startIndex, buffer, 0, readSize);
        ms.Write(buffer, 0, (int)bytesRead);
        startIndex += bytesRead;
        if (bytesRead != readSize) break;
    }
    conn.Close();
    byte[] byteArr = ms.ToArray();
    ms.Dispose();

    return byteArr;
}

once you retrieve the data from db, you can save it somewhere in a temp location or in your designated location.

    string fileToRetrieve = @"Test1.docx"; 
    var fileRetrieved = RetrieveFile(fileToRetrieve);
    string tempFile = @"C:\file\path\Retrived.docx";
    File.WriteAllBytes(tempFile, fileRetrieved);

Upvotes: 1

Grantly
Grantly

Reputation: 2556

Open the file as a MemoryStream (of bytes). Save the memorystream to your SQL table - to a column defined as VARBINARY(MAX). to restore the file, do the same in reverse. Plenty of info for these steps on the internet

Upvotes: 0

user5161537
user5161537

Reputation:

Read the word document as byte array and store that as blob data in database. For downloading read the byte array and stream the output as file with .doc extension

Upvotes: 1

Related Questions