Solved

How to save a C# BitArray using SQL Server

Posted on 2008-06-26
15
2,732 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
  • 7
  • 7
15 Comments
 
LVL 7

Expert Comment

by:nsanga
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 11

Expert Comment

by:CMYScott
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Stuart_Laird
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks MYSccott for your help and your excellent advice.  I have learnt quite a bit from you
Cheers
Stuart
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
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…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now