C#: Is it possible to store a Decimal Array in an SQL database?

Question

Hi there,

I'm working on an application for a lab project and I'm making it in C#. It's supposed to import results from a text file that is exported from the application we use to run the tests and so far, I've hit a road block.

I've gotten the program to save around 250 decimal values as a single-dimension array but then I'm trying to get the array itself to be able to saved in an SQL database so that I can later retrieve the array and use the decimal values to construct a plot of the points.

I need the entire array to be imported into the database as one single value though because the lab project has several specimens each with their own set of 250 or so Decimal points (which will be stored as arrays, too)

Thanks for your help.

EDIT: Thanks for the quick replies, guys but the problem is that its not just results from a specimen with only 1 test ran. Each specimen itself has the same test performed on them with different decibel levels over 15 times. Each test has its own sets of 250 results and we have many specimens.

Also, the specimens already have a unique ID assigned to them and it'd be stored as a String not an Int. What I'm planning on doing is having a separate table in the DB for each specimen and have each row include info on the decibel level of the test and store the array serialized...

I think this would work because we will NOT need to access individual points in the data straight from the database; I'm just using the database to store the data out of memory since there's so much of it. I'm going to query the database for the array and other info and then use zedgraph to plot the points in the array and compare multiple specimens simultaneously.

Answer 1

Short answer is absolutely not. These are two completely different data structures. There are work arounds like putting it in a blob or comma separating a text column. But, I really hate those. It doesn't allow you to do math at the SQL Server level.

IMO, the best option includes having more than one column in your table. Add an identifier so you know which array the data point belongs to.

For example:

AutoId    Specimen    Measurement
   1         A            42
   2         A            45.001
   3         B            47.92

Then, to get your results:

select 
    measurement 
from 
    mytable 
where 
    specimen = 'A'
order by
    autoid asc

Edit: You're planning on doing a separate 250 row table for each specimen? That's absolutely overkill. Just use one table, have the specimen identifier as a column (as shown), and index that column. SQL Server can handle millions upon millions of rows markedly well. Databases are really good at that. Why not play to their strengths instead of trying to recreate C# data structures?

Answer 2

As long as you don't to access the the individual values in your queries, you can serialize the array and store it as a blob in the database.

Answer 3

You can serialize the array and store it as a single chunk of xml/binary/json. Here is an example of serializing it as xml.

public static string Serialize<T>(T obj)
{
    StringBuilder sb = new StringBuilder();
    DataContractSerializer ser = new DataContractSerializer(typeof(T));
    ser.WriteObject(XmlWriter.Create(sb), obj);
    return sb.ToString();
}

Answer 4

Presumably you could serialize the decimal array in C# to a byte array, and save that in a binary field on a table. Your table would have two fields: SpecimenID, DecimalArrayBytes

Alternately you could have a many to many type table and not store the array in one piece, having fields: SpecimenID, DecimalValue, and use SQL like

SELECT DecimalValue FROM Table WHERE SpecimenID = X

Answer 5

I need the entire array to be imported into the database as one single value though because the lab project has several specimens each with their own set of 250 or so Decimal points (which will be stored as arrays, too)

So you're trying to pound a nail, should you use an old shoe or a glass bottle?

The answer here isn't "serialize the array into XML and store it in a record". You really want to strive for correct database design, and in your case the simplest design is:

Specimens
---------
specimenID (pk int not null)

SpecimenData
------------
dataID (pk int not null
specimenID (fk int not null, points to Specimens table)
awesomeValue (decimal not null)

Querying for data is very straightforward:

SELECT * FROM SpecimenData where specimenID = @specimenID

Answer 6

You want two tables. One to store an index, the other to store the decimal values. Something like this:

create table arrayKey (
   arrayId int identity(1,1) not null
)

create table arrayValue (
   arrayID int not null,
   sequence int identity(1,1) not null,
   storedDecimal decimal(12,2) not null
)

Insert into arrayKey to get an ID to use. All of the decimal values would get stored into arrayValue using the ID and the decimal value to store. Insert them one at a time.

When you retrieve them, you can group them by arrayID so that they all come out together. If you need to retrieve them in the same order you stored them, sort by sequence.

Answer 7

Although any given example might be impractical, via programming you can engineer any shape of peg into any shape of hole.

You could serialize your data for storage in a varbinary, XML-ize it for storage into a SQL Server XML type, etc.

Pick a route to analyze and carefully consider. You can create custom CLR libraries for SQL as well so the virtual sky is the limit.