Saving a multidimensional byte array to a SQL Server database

Question

I want to store a multidimensional byte array into a SQL Server database.

I know how to save an array of bytes, which is converting an image to a database. The data type used is image . But now I want to save another byte array, which is a multi-dimensional byte array byte [,] temp , which has two dimensions with x, y values.

I searched on the Internet and it says that using the VARBINARY format. All I want to know is if I store my multidimensional array in a data column of the VARBINARY data type, will the values ​​be changed? Is it possible to get the data again as a multidimensional array?

Answer 1

Yes, you can return your multidimensional array unchanged.

How can you do this? Using the Varbinary (max) field on the Sql server and storing a serialized multidimensional array of bytes in it. To return your array obviusly, you need to deserialize what you store in the database.

Here is an example of how to do this:

 public void TestSO() { using (SqlConnection conexion = new SqlConnection()) { using (SqlCommand command = new SqlCommand()) { //This is the original multidimensional byte array byte[,] byteArray = new byte[2, 2] {{1, 0}, {0,1}}; ConnectionStringSettings conString = ConfigurationManager.ConnectionStrings["ConnectionString"]; conexion.ConnectionString = conString.ConnectionString; conexion.Open(); command.Connection = conexion; command.CommandType = CommandType.Text; command.CommandText = "UPDATE Table SET VarBinaryField = @Content WHERE Id = 73 "; command.Parameters.Add(new SqlParameter("@Content", SqlDbType.VarBinary, -1)); //Serialize the multidimensional byte array to a byte[] BinaryFormatter bf = new BinaryFormatter(); MemoryStream ms = new MemoryStream(); bf.Serialize(ms, byteArray); //Set the serialized original array as the parameter value for the query command.Parameters["@Content"].Value = ms.ToArray(); if (command.ExecuteNonQuery() > 0) { //This method returns the VarBinaryField from the database (what we just saved) byte[] content = GetAttachmentContentsById(73); //Deserialize Content to a multidimensional array MemoryStream ms2 = new MemoryStream(content); byte[,] fetchedByteArray = (byte[,])bf.Deserialize(ms2); //At this point, fetchedByteArray is exactly the same as the original byte array } } } } 

Answer 2

As I know, Microsoft SQL Server does not have a suitable data type for storing multidimensional arrays. However, there are many ways to save information about the structure of an array. Some of them:

• create several columns of the BINARY data type (fixed length) and each row of your multidimensional array into the corresponding column; in this case, the number of rows in your array is expected to be constant;

• saves the entire array as a one-dimensional array into one VARBINARY column (variable length) and store in a separate column of the INT data type - the number of elements in each row is a multidimensional array; in this case, it is expected that the number of elements in each row are the same (not C # notched array); when reading an array, then you can divide the elements by this length into separate lines of a multidimensional array.