• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1216
  • Last Modified:

How to pass binary data to a stored procedure from a c# program

I need to pass data to an input parameter of a stored procedure which is defined as Binary(8)

This is what I have so far , but it does not seem to do any processing. So if someone couls let me know if

public static void DBProcedure(string processName, byte[] beginTimeStamp, byte[] endTimeStamp, string destination, params string[] tradingArea)
        {            
             
                      
            Logger.LogMessage(Constants.Information, "Begin DB Processing : " + Thread.CurrentThread.Name + " : " + DateTime.Now.ToLocalTime());
         
           
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                SqlCommand command = new SqlCommand(GetProcName(destination), 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.Parameters.Add("@EndTimeStamp", SqlDbType.Binary).Value = endTimeStamp;
                               if (destination.ToUpper() != Constants.MAIN && destination.ToUpper() != Constants.SEPARATE_DB)
                    command.Parameters.Add("@DestinationTables", SqlDbType.VarChar).Value = destination;
                if ((tradingArea.Length > 0) && (destination != Constants.MAIN))
                    command.Parameters.Add("@TradingArea", SqlDbType.VarChar).Value = tradingArea[0];
                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

0
countrymeister
Asked:
countrymeister
1 Solution
 
countrymeisterAuthor Commented:
and here is the query string that I pass to the method below for each call one to get minimum timestamp and the other for maxtimestamp

SELECT MIN(TimeStamp) AS MinTimeStamp FROM Table1 (nolock) WHERE LastModifyTime BETWEEN  '1/23/2008' AND '1/23/2008 23:59:00.000'

SELECT MAX(TimeStamp) AS MaxTimeStamp FROM Table1 (nolock) WHERE LastModifyTime BETWEEN  '1/23/2008' AND '1/23/2008 23:59:00.000'

readerFiedl is MinTimeStamp for the first call and maxtimestamp for the second call

I get the data in a byte array of length 36, whcih I then pass to the DBProcedure method above

 

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;
            }
        }

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now