I'm using C# to input and output multi 2d arrays into SQL database. The 2D array is something like :
Pt[100,50]={0.3,0.2,0.1,...,0.8;
0.2,0.5,0.5,...,0.1;
. .
. .
. .
0.1,0.6,0.5,...,0.2}
I know to design a (index_x,index_y) table in SQL and use two loops on C# to get it done.
Is there an better way to input and output the 2D array more efficiently?
Any examples would be great!
By executing a separate SQL statement at each point, you are doing as many database round-trips as there are points to insert. Each round-trip incurs a communication latency (especially if the database is not local) as well as some bookkeeping costs at the level of the DBMS.
The crude and not very scalable, but database-agnostic way to perform multiple inserts within only one database round-trip is to simply pack multiple INSERT statements into a single DbCommand object.
Assuming your table looks similar to this (use your DBMS-specific types as appropriate)...
CREATE TABLE YOUR_TABLE(
INDEX_X int NOT NULL,
INDEX_Y int NOT NULL,
VALUE numeric(18, 4) NOT NULL,
PRIMARY KEY ( INDEX_X, INDEX_Y )
)
...here is one way to do it:
class Program {
static void Main(string[] args) {
double[,] pt = {
{ 0.3, 0.2, 0.1, 0.8 },
{ 0.2, 0.5, 0.5, 0.1 },
{ 0.1, 0.6, 0.5, 0.2 }
};
// Replace SqlConnection with what is specific to your ADO.NET provider / DBMS.
using (var conn = new SqlConnection("your connection string")) {
conn.Open();
using (var cmd = conn.CreateCommand()) {
// Construct SQL text (use parameter prefix specific to your DBMS instead of @ as appropriate).
var sb = new StringBuilder();
for (int y = 0; y < pt.GetLength(0); ++y)
for (int x = 0; x < pt.GetLength(1); ++x)
sb.Append(
string.Format(
"INSERT INTO YOUR_TABLE (INDEX_X, INDEX_Y, VALUE) VALUES (@index_x_{0}_{1}, @index_y_{1}_{1}, @value_{0}_{1});",
x,
y
)
);
cmd.CommandText = sb.ToString();
// Bind parameters.
for (int y = 0; y < pt.GetLength(0); ++y)
for (int x = 0; x < pt.GetLength(1); ++x) {
cmd.Parameters.AddWithValue(string.Format("@index_x_{0}_{1}", x, y), x);
cmd.Parameters.AddWithValue(string.Format("@index_y_{0}_{1}", x, y), y);
cmd.Parameters.AddWithValue(string.Format("@value_{0}_{1}", x, y), pt[y, x]);
}
// Perform the actual insert.
cmd.ExecuteNonQuery();
}
}
}
}
There are more efficient, but DBMS-specific solutions, such as:
You could store the array as string in the db. Either line by line or the whole array at once, depending on the maximum text size that you can store in one field. Either use your own format, like comma separated or XML. If the array size varies, store its size in separate fields or in the first text line.
Serialize it with something like JSON.NET and store it as a string. If this is all you are storing then you don't even need a database.
