locked
Retrive datatable from SQl Server Varbinary field using c# RRS feed

  • Question

  • I have success fully saved datatable in varbinary field in sql server by following code

                        object qFileObj = dt;
                        MemoryStream memStream = new MemoryStream();
                        StreamWriter sw = new StreamWriter(memStream);
                        sw.Write(qFileObj);

                        cmd = new SqlCommand("INSERT INTO TBL_QPR(FK_Candidate,SetFile) VALUES                                     

                        (@FK_Candidate,@SetFile)", con);
                        cmd.Parameters.Add("@FK_Candidate", SqlDbType.Int, Int32.MaxValue);  
                        cmd.Parameters.Add("@SetFile", SqlDbType.VarBinary, Int32.MaxValue);
                        cmd.Parameters["@FK_Candidate"].Value = rd["ID"].ToString();
                        cmd.Parameters["@SetFile"].Value = memStream.GetBuffer();
                        cmd.ExecuteNonQuery(); 

    Now , i want to get back this datatable.


    Wednesday, July 25, 2012 9:37 AM

Answers

  • Finally i got the solution of my problem. I was wrong here to save Data table object in SQl Server . You can do it by Serializing Data table into file then save it into Varbinary field of sql server. By the reverse process of it you can get Data table again.

    thanks jtorrecilla for your great support.

    Wednesday, July 25, 2012 1:20 PM

All replies

  • To get the data back out of database, you need to write SELECT sql statement query:

    1. To get all columns of all rows: "SELECT * FROM TableName";

    2. To get all columns from particular row: "SELECT * FROM TableName WHERE ColumnID = '1'"; // from row with id = 1

    2. To get particular column(s) from particular row: "SELECT Column1, Column2, Column 3 FROM TableName WHERE ColumnID = '1'";

    --

     So based on these types of queries, you then decide which classes to use to retreive data.

    But based on your INSERT query, I would assume you want all to get the data inserted, so only of particular row (of FK column):

    //...
    string setFile = ""; //
    
    cmd = new SqlComamnd("SELECT * FROM TBL_QPR WHERE FK_Candidate = @param", conn);   
    cmd.Parameters.Add("@FK_Candidate", SqlDbType.Int, Int32.MaxValue).Value = 1; //put some id here as condition
    conn.Open();
    SqlDataReader reader = cmd.ExecuteReader();
    if(reader.Read())
    {
        setFile = reader[0].ToString();
    }
    



    Mitja

    Wednesday, July 25, 2012 10:28 AM
  • The return value is a string while  i want to get Datatable as i have already saved it in SetFile field Which is VARBINARY(MAX).
    Wednesday, July 25, 2012 10:39 AM
  • Please try read the following URL. I hope the follwing URL will be sloved yopur problem

    http://stackoverflow.com/questions/1787077/datatable-not-accepting-the-value-of-varbinary

    http://stackoverflow.com/questions/1098022/retrieve-varbinary-datafield-from-sqldatabase


    With Thanks and Regards
    Sambath Raj.C
    click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you
    Happy Programming!

    Wednesday, July 25, 2012 11:31 AM
  • I have already followed these links but could not get solution.

    Wednesday, July 25, 2012 11:57 AM
  • Adapting Mitja's Code:

    byte[] setFile = new byte[]; //
    
    cmd = new SqlComamnd("SELECT * FROM TBL_QPR WHERE FK_Candidate = @param", conn);   
    cmd.Parameters.Add("@FK_Candidate", SqlDbType.Int, Int32.MaxValue).Value = 1; //put some id here as condition
    conn.Open();
    SqlDataReader reader = cmd.ExecuteReader();
    if(reader.Read())
    {
        setFile = (byte[])reader["SetFile"];
    }

    Hope it helps!


    Javier Torrecilla
    Para el correcto funcionamiento, y que otros usuarios se puedan beneficiar de la solucion de esta pregunta por favor marca las respuestas que te hayan ayudado como "Respuesta".
    Si la respuesta te ha sido util Votala.
    Mi Blog: Jtorrecilla
    Enlace a Faq de Winforms en Ingles Muy bueno
    TabControl con Mejoras

    Wednesday, July 25, 2012 12:02 PM
  • Please tell me how to convert this setFile into DataTable

    Thanks for your reply .

    Wednesday, July 25, 2012 12:13 PM
  • Take a look in this thread http://stackoverflow.com/questions/1300043/convert-a-byte-array-into-datatable

    Javier Torrecilla
    Para el correcto funcionamiento, y que otros usuarios se puedan beneficiar de la solucion de esta pregunta por favor marca las respuestas que te hayan ayudado como "Respuesta".
    Si la respuesta te ha sido util Votala.
    Mi Blog: Jtorrecilla
    Enlace a Faq de Winforms en Ingles Muy bueno
    TabControl con Mejoras

    Wednesday, July 25, 2012 12:56 PM
  • Finally i got the solution of my problem. I was wrong here to save Data table object in SQl Server . You can do it by Serializing Data table into file then save it into Varbinary field of sql server. By the reverse process of it you can get Data table again.

    thanks jtorrecilla for your great support.

    Wednesday, July 25, 2012 1:20 PM