Solved

How to save a C# BitArray using SQL Server

Posted on 2008-06-26
15
2,877 Views
Last Modified: 2010-04-21
I have a BitArray in C# and need to be able to retrieve it from, and save it in, SQL Server 2005.
How can I do this?  I have tried writing the BitArray to a byte arrays using the CopyTo method and then encoding the byte array using the GetString of System.Text.Encoding.  This works fine in C# - I can code a bit array as a string and decode the string as a bit array.  However, when the string is saved in the database table as a varchar (or nvarchar) and later retrieved, bits are lost.  In particular, the problem seems to lie with bit7 of each byte.  I have checked the string just before storing it and all is OK.  I have also checked it on retrieving it and it is there the missing bits arise.
I can work around this by ensuring expanding the bitarray so every 7th bit of the bit array has a 0 and then contracting the retrieved bit array by removing these 0s, but this seems clumsy.
My questions are
(a) What is actually happening and
(b) What is the best way to implement this?
Thanks
 
0
Comment
Question by:Stuart_Laird
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
15 Comments
 
LVL 7

Expert Comment

by:nsanga
ID: 21873262
I think when you directly convert byte to a char ( ascci char )  if converts to a spcial character, that is not store correctly in database......

You can try storing them as numbers......follow these steps...

1) convert the bit array to byte array....
2) convert each byte to integer ( using binary to decimal conversion)
                      you can user System.BitConverter class
3)  Store these integers as strings into the database.....
0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21880531
what is the maximum size of the array? if its < 64 bits, why not 'encode' the bits into an int64 and then store it in the database as a bigint?
0
 

Author Comment

by:Stuart_Laird
ID: 21881189
The array size is 800 bits.  At present, the best solution seems to me now is to convert the bit array into an integer array and the turn this array into a hex string.  How does this seem?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 11

Expert Comment

by:CMYScott
ID: 21883208
I'm not sure I see anything wrong with what you're proposing - but I think I'd take the approach of converting the 800 bits to 100 bytes and storying them in a binary field in SQL.
0
 

Author Comment

by:Stuart_Laird
ID: 21883417
Hi CMYScott
That sounds better but can you indicate how I can send and retrieve  the 100 bytes from the C# code to a binary field in SQL.
0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21883648
without looking it up - I think it would be like this to insert..


using (SqlConnection db = new SqlConnection(yourConnectionString))
{
   db.Open();
   using (SqlCommand cmd = new SqlCommand("INSERT INTO yourTable (PKColumn,BinaryColumn) VALUES (@idx,@binary)",db))
   {
      cmd.Parameters.AddWithValue("@idx",yourKeyValue);
      cmd.Parameters.AddWithValue("@binary",yourByteArray);
      cmd.ExecuteNonQuery();
   }
}

Open in new window

0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21883743
and like this to read..
using (SqlConnection db = new SqlConnection(yourConnectionString))
{
  db.Open();
  using (SqlCommand cmd = new SqlCommand("SELECT PKColumn,BinaryColumn FROM yourTable WHERE PKColumn = @idx",db))
  {
    cmd.Parameters.AddWithValue("@idx",idxValue);
    SqlDataReader rdr = cmd.ExecuteReader();
    Byte[] buffer = new Byte[100];
    rdr.GetBytes(1,0,buffer,0,100);
  }
}

Open in new window

0
 

Author Comment

by:Stuart_Laird
ID: 21889758
Hi CMYScott
Thanks for your code
I'm trying this out with a test program using set of 80 bits (all T) so each associated Byte is 255.
I had to modify your code a little to get it to run.
I have set up a Table  AABitTest with 2 columns:  the ID column BitTestID, and the data column BitTest
I think the code to insert works fine, as my table now has a row with 1001 in the first column and <Binary> appearing in the second.
However, my code to read back in from the table gives the error:
System.InvalidOperationException: Invalid attempt to read when no data is present.
associated with the line: rdr.GetBytes(1, 0, buffer, 0, 10);
What am I doing wrong?
I've attached all my test code below
Thanks
protected void Page_Load(object sender, EventArgs e)
    {
        BitArray ba = new BitArray(80);
        ba.SetAll(true);
 
        byte[] byteArray = new byte[10];
        ba.CopyTo(byteArray, 0);
 
        InsertBits(byteArray);  
        ReadBits(1000);
    }
 
    protected void ReadBits(int bitTestID)
    {
        string connectionString = ConfigurationManager.ConnectionStrings["JLM2008ConnectionString"].ConnectionString;
        IDbConnection dbConnection = new SqlConnection(connectionString);
 
        dbConnection.Open();
        string queryString = "SELECT BitTest FROM AABitTest WHERE BitTestID=1001";
 
        System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
        dbCommand.CommandText = queryString;
        dbCommand.Connection = dbConnection;
 
        System.Data.IDataReader rdr = dbCommand.ExecuteReader();
        Byte[] buffer = new Byte[10];
        rdr.GetBytes(1, 0, buffer, 0, 10);
    }
 
    protected void InsertBits(byte[] byteArray)
    {
        string connectionString = ConfigurationManager.ConnectionStrings["JLM2008ConnectionString"].ConnectionString;
        IDbConnection dbConnection = new SqlConnection(connectionString);
 
        dbConnection.Open();
        string queryString = "INSERT INTO AABitTest (BitTest) VALUES (@BitTest)";
 
        System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
        dbCommand.CommandText = queryString;
        dbCommand.Connection = dbConnection;
 
        IDataParameter dbParam_BitTest = new SqlParameter("BitTest", byteArray);
        dbCommand.Parameters.Add(dbParam_BitTest);
 
        dbCommand.ExecuteNonQuery(); 
    }

Open in new window

0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21895313
well - the problem with your code is

rdr.GetBytes(1, 0, buffer, 0, 10);

should be:

rdr.GetBytes(0,0,buffer,0,10);

as the first parameters is the zero-based ordinal of the column - based on the columns in your select - since you are only specificying to SELECT 'bitTest' - its ordinal is 0.


I would also suggest you re-write your code as follows..

1.  wrapping your blocks in Using's takes care of the close and dispose automatically..
2.  using the correct types instead of the interfaces should improve performance a little bit

Good Luck
        protected void ReadBits(int bitTestID)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["JLM2008ConnectionString"].ConnectionString;
            byte[] buffer = new byte[10];
 
            using (SqlConnection dbConnection = new SqlConnection(connectionString))
            {
 
                dbConnection.Open();
                string queryString = "SELECT BitTest FROM AABitTest WHERE BitTestID=1001";
 
                using (SqlCommand dbCommand = new SqlCommand(queryString, dbConnection))
                {
                    using (SqlDataReader rdr = dbCommand.ExecuteReader())
                    {
                        rdr.GetBytes(0, 0, buffer, 0, 10);
                        rdr.Close();
                    }
                }
            }
        }
 
        protected void InsertBits(byte[] byteArray)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["JLM2008ConnectionString"].ConnectionString;
 
            using (SqlConnection dbConnection = new SqlConnection(connectionString))
            {
                dbConnection.Open();
                string queryString = "INSERT INTO AABitTest (BitTest) VALUES (@BitTest)";
 
                using (SqlCommand dbCommand = new SqlCommand(queryString, dbConnection))
                {
                    dbCommand.Parameters.AddWithValue("@BitTest", byteArray);
                    dbCommand.ExecuteNonQuery();
                }
            }
        }

Open in new window

0
 

Author Comment

by:Stuart_Laird
ID: 21897039
Hi CMYScott

Thanks So Much for Your trouble and Advice which I shall follow as a genereal model in future.

. I copied and pasted your code in but unfortunately I still get the same error  - System.InvalidOperationException: Invalid attempt to read when no data is present.  This refers to line 16 in the code you gave me:-  rdr.GetBytes(1, 0, buffer, 0, 10);

Here is the Stack Trace for the error:  I'm afraid it's a bit obscure to me.
[InvalidOperationException: Invalid attempt to read when no data is present.]
   System.Data.SqlClient.SqlDataReader.GetBytes(Int32 i, Int64 dataIndex, Byte[] buffer, Int32 bufferIndex, Int32 length) +883747
   Default2.ReadBits(Int32 bitTestID) in c:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\WebSites\WebSite7\Default2.aspx.cs:43
   Default2.Page_Load(Object sender, EventArgs e) in c:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\WebSites\WebSite7\Default2.aspx.cs:26
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +33
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +47
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436

Thanks once again

0
 

Author Comment

by:Stuart_Laird
ID: 21897265
Hi MYScott
I can get the following code, using a data adapter instead of a datareader, to work.  I hope I've followed your model.  I would appreciate it if you could let me know what's up.
Cheers
Stuart
    protected void ReadBits(int bitTestID)
    {
        string connectionString = ConfigurationManager.ConnectionStrings["JLM2008ConnectionString"].ConnectionString;
        System.Data.DataSet dataSet = new System.Data.DataSet();
 
        using (SqlConnection dbConnection = new SqlConnection(connectionString))
        {
 
            dbConnection.Open();
            string queryString = "SELECT BitTest FROM AABitTest WHERE BitTestID=1003";
 
            using (SqlDataAdapter dataAdapter = new SqlDataAdapter(queryString, dbConnection))
            {
                dataAdapter.Fill(dataSet);
            }
        }
 
        byte[] bytesOut=((byte[])dataSet.Tables[0].Rows[0][0]);
    }

Open in new window

0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21898796
glad you got something to work - keep in mind that the dataset is going to be quite a bit slower than data-reader - so if we can get the datareader to work, you'll end up with a better solution.

can you clear up a little confusion for me...

my code sample had the following line:    rdr.GetBytes(0, 0, buffer, 0, 10);
yet, your response says the error is on the line:   rdr.GetBytes(1,0,buffer,0,10);

it seems you have a mis-typing here - the question is whether its in the code you compiled, or just in your posting here describing the error.



0
 

Author Comment

by:Stuart_Laird
ID: 21905167
Hi CMYScott
Sorry, the code I compiled and ran was correct.  I just tried putting a 1 in after it did not work to see what happened as I was not sure how the datareader worked. However, I have just, copied and pasted your code again to make sure and then run it again and got the same messages.

Below is the error message set again and your code for the read

[InvalidOperationException: Invalid attempt to read when no data is present.]
   System.Data.SqlClient.SqlDataReader.GetBytes(Int32 i, Int64 dataIndex, Byte[] buffer, Int32 bufferIndex, Int32 length) +883747
   Default2.ReadBits(Int32 bitTestID) in c:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\WebSites\WebSite7\Default2.aspx.cs:89
   Default2.Page_Load(Object sender, EventArgs e) in c:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\WebSites\WebSite7\Default2.aspx.cs:46
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +33
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +47
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436

protected void ReadBits(int bitTestID)
    {
        string connectionString = ConfigurationManager.ConnectionStrings["JLM2008ConnectionString"].ConnectionString;
        byte[] buffer = new byte[10];
 
        using (SqlConnection dbConnection = new SqlConnection(connectionString))
        {
 
            dbConnection.Open();
            string queryString = "SELECT BitTest FROM AABitTest WHERE BitTestID=1019";
 
            using (SqlCommand dbCommand = new SqlCommand(queryString, dbConnection))
            {
                using (SqlDataReader rdr = dbCommand.ExecuteReader())
                {
                    rdr.GetBytes(0, 0, buffer, 0, 10);
                    rdr.Close();
                }
            }
        }
    }

Open in new window

0
 
LVL 11

Accepted Solution

by:
CMYScott earned 500 total points
ID: 21907139
sorry - I think I missed some a rdr.Read() line - added below in an if-block to make sure data is actually available to be read.


                using (SqlDataReader rdr = dbCommand.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        rdr.GetBytes(0, 0, buffer, 0, 10);
                        rdr.Close();
                    }
                }
0
 

Author Closing Comment

by:Stuart_Laird
ID: 31470882
Thanks MYSccott for your help and your excellent advice.  I have learnt quite a bit from you
Cheers
Stuart
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question