Using SQL image data type

[Smeat]

H Foks

I have the following code in a c# class:

#region Data Access

internal void PersistSessionData(SqlTransaction Tr, string SessionID)
{
string connectionString = System.Configuration.ConfigurationSettings.AppSettings["DB:ShowroomLog"].ToString();
SqlConnection Cn = new SqlConnection(connectionString);
Cn.Open();
SqlCommand Cmd = new SqlCommand();
Cmd.Connection = Cn;

try
{
  Cmd.CommandType = CommandType.StoredProcedure;
  Cmd.CommandText = "Core_SaveSessionData";

  SqlParameter[] parameters = {new SqlParameter("@SessionID", SqlDbType.NVarChar, 100),
  new SqlParameter("@SessionKey", SqlDbType.NVarChar, 50),
  new SqlParameter("@SessionValue", SqlDbType.Image) };

  parameters[0].Value = SessionID;
  parameters[1].Value = mKey;
  parameters[2].Value = mValue;
							
  Cmd.Parameters.Add(parameters[0]);
  Cmd.Parameters.Add(parameters[1]);
  Cmd.Parameters.Add(parameters[2]);

  Cmd.ExecuteNonQuery();
}
catch
{
  throw;
}
finally
{
  Cmd.Dispose();
  Cmd = null;
  Cn.Close();
  Cn.Dispose();
}
}

#endregion

I'm trying to pass mValue as an object to a sql server stored proc that expects an image data type and get the following error:

System.InvalidCastException: Invalid cast from System.String to System.Byte[].

Any ideas how I can convert the mValue object data type into an acceptable format for the sql image data type?

Thanks in advance

Smeat

 

[chiph]

When writing a BLOB type to the database, you need to include the length on your SqlParameter constructor.

Also, how did your image get into a String in the first place? If it started off as a System.Drawing.Image class, you probably should have used the .Save method to put it into a MemoryStream, which has a ToArray method.

Chip H.



____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first

 

[Smeat]

Hi Chip

Thanks for your reply

The reason that image is now a string because it never started out as an image, it's actually a c# object which may be a string or a serialized object.

What i'm attempting to do is a custom session management object so I need to be able to have any type of object saved to the database without actually knowing what type of object it is. This is the reason I am using an image data type in sql server so as to allow for the potentially large size of the object.

If i'm correct, the sqlparameter should be initialized as size 16, is this correct?

Smeat

 

[chiph]

In that case, use the GetBytes method of the UnicodeEncoding class to convert your string into an array of bytes. To go the other way, use the GetChars method.

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first