Passing binary data to a stored procedue from C# program

I need to know if the following will work as far as passing binary data to a stored procedure

The input parameter startTime to the stored procedure  p_startProcess is defined as Binary(8).

First I get the byte[] from a method DBReader by passing a query string to get the minimum time.
In the C# prorgam the byte [] that is returned from the method below returns a byte array of 36.
which is then passed to the startTime input parameter of the p_startProcess.

The question is this going to work to pass a byte[] of 36 to a input parameter of Binary(8)

public static byte[] DBReader(string queryString, string readerField)
        {
            Byte[] minTimeStamp = null;
            using (SqlConnection connection = new SqlConnection(
                       connectionString))
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.CommandTimeout = timeout;
                command.Connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    MemoryStream ms = new MemoryStream();
                    BinaryFormatter bf = new BinaryFormatter();
                    bf.Serialize(ms, reader[readerField]);
                    minTimeStamp = ms.ToArray();
                }
                reader.Close();
                return minTimeStamp;
            }
        }
 
public static void DBProcedure(string processName, byte[] beginTimeStamp)
        {            
             
                      
            Logger.LogMessage(Constants.Information, "Begin DB Processing : " + Thread.CurrentThread.Name + " : " + DateTime.Now.ToLocalTime());
         
           
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                SqlCommand command = new SqlCommand("p_startProcess" , connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();                
                command.Parameters.Add("@ProcessName", SqlDbType.VarChar, 200).Value = processName;
                command.Parameters.Add("@BeginTimeStamp", SqlDbType.Binary).Value = beginTimeStamp;                             
                command.CommandTimeout = timeout;
                command.Connection.Open();
                command.ExecuteNonQuery();            
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error : " + ex.Message);              
                Logger.LogMessage(Constants.Error, ex.Message);
 
            }
            finally
            {
                connection.Close();                
            }
            
        }

Open in new window

LVL 1
countrymeisterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Göran AnderssonCommented:
No, it's won't work.

The binary(8) type is a fixed size binary of eight bytes. The only thing that makes sense to send to it is a byte[] with the size of 8. If you send a shorter array, the data will be padded (with zeroes?), if you send a longer array, it will be truncated.

If the data is always 36 bytes, you could use binary(36). Otherwise use a varbinary() or image type in the stored procedure.

The varbinary should be set to a size that is at least as large as the longest array that you will ever send to it. The image type can handle any size up to 2 GB.
0
countrymeisterAuthor Commented:
Hi ! GreenGhost

Thanks for the info, I will try the Binary(8), since the SQL server guy will not change his stored procedure. I will keep you posted once I test my changes.

Here is what I am doing I changed the method  DBReader, and I am not using the binaryformatter like before, since the select statement returns the timestamp which is also a binary(8) field.


string querystring = SELECT MIN(XTimeStamp) FROM TableData (nolock) WHERE LastModifyTime BETWEEN '01/28/2008' AND '01/28/200812:00:00.000'  
 
public static byte[] DBReader(string queryString, string connectionString)
        {
            byte[] byteValue = null;
            using (SqlConnection connection = new SqlConnection(
                       connectionString) )
            {
                SqlCommand command = new SqlCommand(queryString, connection);
                command.Connection.Open();
                SqlDataReader reader = command.ExecuteReader();
               
                while (reader.Read())
                {
                   byteValue = (byte[])reader[0];
                                 
                }
                reader.Close();
                return byteValue;
                
                
 
            }
        }

Open in new window

0
Göran AnderssonCommented:
If you end up with an array that is exactly eight bytes, that would work.

If the result contains only one record, there is no reason to have a loop for reading it:

public static byte[] DBReader(string queryString, string connectionString) {
   byte[] byteValue;
   using (SqlConnection connection = new SqlConnection(connectionString)) {
      SqlCommand command = new SqlCommand(queryString, connection);
      command.Connection.Open();
      using (SqlDataReader reader = command.ExecuteReader()) {
         reader.Read();
         byteValue = (byte[])reader[0];
      }
      return byteValue;
   }
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
countrymeisterAuthor Commented:
Thanks for your help, all works fine
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.