locked
How to save a excel file in sql database? RRS feed

  • Question

  • Hi,

    i want to save one excel file in sql database in some column and same i want to retrive .

    for this i am using file upload control to upload a file.

    Plz tell me the code...


    Sharad Sharma Pursuit Technologies Bangalore
    Saturday, April 17, 2010 6:46 AM

Answers

All replies

  • Hi Sharad?

    Are you referring to a Windows Forms implementation or an ASP.NET implementation. Your question is not clear.

    Best Regards,

    Praneeth


    Praneeth Wickramasinghe (SCJP, SCWCD)
    Saturday, April 17, 2010 7:01 AM
  • You can Save/Retrieve the file in binary format. please see an implementation at http://www.databasejournal.com/features/mssql/article.php/1475641/Storing-and-Retrieving-Binary-Data-in-SQL-Server.htm (Storing and Retrieving Binary Data in SQL Server)
    Saturday, April 17, 2010 7:01 AM
  • asp.net...
    Sharad Sharma Pursuit Technologies Bangalore
    Saturday, April 17, 2010 7:07 AM
  • Hi Sharad,

    You could have a look at the following links to gain understanding on doing what you require and hope it helps.

    http://www.kodyaz.com/articles/file-upload-and-save-to-sql-server.aspx

    Best Regards,

    Praneeth


    Praneeth Wickramasinghe (SCJP, SCWCD)
    Saturday, April 17, 2010 7:19 AM

  • i defined one column in sql for storing the file with datatype. image

    when i am inserting a document in this column "<Binary data>" this value is showing.

    and when i trying to retrive this file.In the file "System.Byte[]" text is coming in a cell.

     

    I am storing excel file in database

    code to insert a Excel file in database:

    Int32 File1Length = this.FileUpload1.PostedFile.ContentLength;
            String File1Type = this.FileUpload1.PostedFile.ContentType;
            Stream File1Stream;
            File1Stream = this.FileUpload1.PostedFile.InputStream;
            String File1Name = this.FileUpload1.PostedFile.FileName;
            byte[] File1Content = new byte[File1Length];
            File1Stream.Read(File1Content, 0, File1Length);

            SqlConnection cn = new SqlConnection("connection string is here");
            cn.Open();
            string sql = "INSERT INTO Files(fileData) "
           + "VALUES ('" + File1Content + "')";
            SqlCommand cmd = new SqlCommand(sql, cn);
            cmd.ExecuteNonQuery();
            cn.Close();

     

    Code to read the file back from database:

     

            SqlConnection con = new SqlConnection("connection string is here");
          SqlDataAdapter da = new SqlDataAdapter("Select fileData from Files", con);
         SqlCommandBuilder MyCB = new SqlCommandBuilder(da);
         DataSet ds = new DataSet();
       
         con.Open();
         da.Fill(ds, "Files");
         DataRow myRow = default(DataRow);
         myRow = ds.Tables["Files"].Rows[0];
       
         byte[] MyData = null;
         MyData =(byte[]) myRow["fileData"];


         Response.ContentType = "application/vnd.ms-excel";
         Response.AddHeader("content-disposition", "attachment;filename=Tr.xls");
         Response.Buffer = true;
         Response.Charset = "";
         Response.Cache.SetCacheability(HttpCacheability.NoCache);
         //Response.BinaryWrite(reportFile);
         Response.BinaryWrite(MyData);
         Response.End();

         con.Close();


    Sharad Sharma Pursuit Technologies Bangalore
    Saturday, April 17, 2010 8:17 AM
  • Please repost at http://forums.asp.net/ where you will get better answers from experts there.

    Thank you.

    • Marked as answer by Figo Fei Monday, April 19, 2010 9:32 AM
    Saturday, April 17, 2010 8:32 AM