Solved

How to save a C# BitArray using SQL Server

Posted on 2008-06-26
15
2,818 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
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB: Convert 2 dates to specific format 24 52
Loops and updating in SQL Query 9 52
C# MVC Insert Multiple Row into DB 2 30
Name Space error VS2015 1 22
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 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