saving doc file in sql database

[deepas]

i need to save doc file in sql database but have no idea to choose the field data type, i think it'll require binary data type. and how to save selected doc file from window based application using c#.net
,please share any solution regarding this....

[DdoubleD]

Try using a BLOB for DB Field and use a MemoryStream for IO file transfer.

[sknake]

SQL 2005 has deprecated old binary image storage types so your best bet is using a varchar(max) in SQL 2005 or later, in SQL2000 you can use the image data type.

Here is an ASP.NET page that retrieves an image and uploads it. You can ignore the ASP.NEt specific stuff and just look at the upload/download aspects:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;

namespace daniweb.web
{
  public partial class PagePost : System.Web.UI.Page
  {
    protected void Page_Load(object sender, EventArgs e)
    {
      return;
      //Getting the main page
      if (Request.QueryString["image"] == null)
      {
        Image1.ImageUrl = "PagePost.aspx?image=blah.bmp";
        //Make sure it exists before we link to it
        //This is inefficient because it calls the database twice for one image
        byte[] picture = GetImage();
        if (picture == null)
        {
          Image1.Visible = false;
        }
      }
      else
      {
        string imageStr = Convert.ToString(Request.QueryString["image"]);
        if (string.Compare(imageStr, "blah.bmp", true) == 0)
        {
          byte[] picture = GetImage();
          Response.ContentType = "image/bmp";
          Response.BinaryWrite(picture);
        }
      }
    }

    
    private static byte[] GetImage()
    {
      byte[] result = null;

      const string query = @"Select Top 1 Picture From Picture";
      using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs")))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            using (DataTable dt = new DataTable())
            {
              dt.Load(dr);
              if (dt.Rows.Count > 0)
              {
                DataRow row = dt.Rows[0];
                if (row["Picture"] != DBNull.Value)
                {
                  try
                  {
                    result = (byte[])row["Picture"];
                  }
                  catch { }
                }
              }
            }
          }
        }
      }
      return result;
    }
    private static string BuildSqlNativeConnStr(string server, string database)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
      byte[] bytes = File.ReadAllBytes(@"C:\picture.bmp");
      const string query = @"Insert Into Picture (Picture) Values (@Picture)";
      using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs")))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          SqlParameter parm = new SqlParameter("@Picture", SqlDbType.Image);
          parm.Value = bytes;
          cmd.Parameters.Add(parm);
          cmd.ExecuteNonQuery();
        }
      }
    }
  }
}