none
Iterating thorough varbinary data RRS feed

  • Question

  • Hi

    How can I open an sql server table in ado.net, iterate through the records and determine which of the varbinary(Max) fields are empty and which have data in them?

    Many Thanks

    Regards

    Tuesday, January 31, 2012 3:24 PM

Answers

  • Manyways You can do it. A Simple approach would be,

    Step1. Get All Column Name from Table whose

    data_type='varbinary' and character_maximum_length=-1

    Step2. Based on Step1 OutPut Get Rows From Table where <Columns> is null

    Step3. Iterate Rows and Update it or a Batch Update will do the job.

    You can refer below Code snipet for that.

    SqlConnection sqlCon = new SqlConnection("Server=.;Database=TestDB;Trusted_Connection=True;");
    sqlCon.Open();
    SqlDataAdapter adp = new SqlDataAdapter("SELECT column_name FROM information_schema.COLUMNS WHERE table_name ='test' and data_type='varbinary' and character_maximum_length=-1", sqlCon);
    DataSet ds= new DataSet();
    adp.Fill(ds);

    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
    {
       adp.SelectCommand.CommandText = "Select " + ds.Tables[0].Rows[i][0].ToString() + " FROM Test Where Col1 is null";
       DataTable dt = new DataTable();
       adp.Fill(dt);
           for (int j = 0; j < dt.Rows.Count; j++)
           {
                dt.Rows[j][0] = new byte[2];
           }
        adp.Update(dt);
    }


    Lingaraj Mishra
    • Edited by Lingaraj Mishra Wednesday, February 1, 2012 12:23 PM
    • Marked as answer by Y a h y a Wednesday, February 1, 2012 9:34 PM
    Wednesday, February 1, 2012 12:22 PM

All replies

  • Is there any reason why you want to determine the data length using ADO.Net instead of SQL? Normally if you just want to know the length, sending the whole data over the wire before checking for length is a big waste of bandwidth. Writing code that runs at the database server (E.g. write a view that has a column calling DataLength function) is far more efficient.

    The TSQL forum is at http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads in case you need more SQL help.

     



    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP
    Tuesday, January 31, 2012 10:17 PM
  • Hi

    I just need to see which of the varbinary fields are not filled up so I can fill them up. I don’t need the exact length of the filed varbinary field.

    Thanks

    Regards

    Tuesday, January 31, 2012 11:11 PM
  • Hi Yahya,

    I think you can try to fill your datatable in DataSet by SqlDataAdapter then iterate the datatables in dataset to see if it is empty, the type for varbinary is Byte[] in CLR.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 1, 2012 7:39 AM
    Moderator
  • Manyways You can do it. A Simple approach would be,

    Step1. Get All Column Name from Table whose

    data_type='varbinary' and character_maximum_length=-1

    Step2. Based on Step1 OutPut Get Rows From Table where <Columns> is null

    Step3. Iterate Rows and Update it or a Batch Update will do the job.

    You can refer below Code snipet for that.

    SqlConnection sqlCon = new SqlConnection("Server=.;Database=TestDB;Trusted_Connection=True;");
    sqlCon.Open();
    SqlDataAdapter adp = new SqlDataAdapter("SELECT column_name FROM information_schema.COLUMNS WHERE table_name ='test' and data_type='varbinary' and character_maximum_length=-1", sqlCon);
    DataSet ds= new DataSet();
    adp.Fill(ds);

    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
    {
       adp.SelectCommand.CommandText = "Select " + ds.Tables[0].Rows[i][0].ToString() + " FROM Test Where Col1 is null";
       DataTable dt = new DataTable();
       adp.Fill(dt);
           for (int j = 0; j < dt.Rows.Count; j++)
           {
                dt.Rows[j][0] = new byte[2];
           }
        adp.Update(dt);
    }


    Lingaraj Mishra
    • Edited by Lingaraj Mishra Wednesday, February 1, 2012 12:23 PM
    • Marked as answer by Y a h y a Wednesday, February 1, 2012 9:34 PM
    Wednesday, February 1, 2012 12:22 PM