2 dimensional string array to SQLite database table

Suzie999 0 3K Views

Earlier I posted a snippet which showed how to create a 2 dimensional string array from an SQLite database table.

This snippet is the reverse, if you will, where we create an SQLite database table from a 2d string array.

It should be noted that this code has it's limitations, as is, and should be used where the strings in array are less than 128 characters long. This can easily be amended to accommodate strings of any length, but I felt it was beyond the scope of this snippet.

This code demonstrates one way to create a transaction which greatly speeds up table insertion where many single queries might otherwise be used.

TrimRight is just a little helper method which trims n characters from right side of string.

Once again, any comments, critisisms or improvement suggestions are very welcome.

public int SQLiteArrayToTable(string[,] array, string tablename, SQLiteConnection sqlcon, string[] columnnames = null)
        {
            string[] colnames;
            SQLiteCommand cmd = new SQLiteCommand();

            /*if caller passed an array of column names, use it else create a default array*/
            if ((columnnames == null))
            {
                colnames = new string[array.GetUpperBound(1) + 1];
                for (int i = 0; i <= colnames.GetUpperBound(0); i++)
                {
                    colnames[i] = "Col_" + i.ToString();
                }
            }
            else
            {
                colnames = new string[columnnames.GetUpperBound(0) + 1];
                for (int i = 0; i <= columnnames.GetUpperBound(0); i++)
                {
                    colnames[i] = columnnames[i];
                }
            }

            /*build a string to create a database table*/
            string CreatTableString = "CREATE TABLE " + tablename + "(";
            for (int i = 0; i <= colnames.GetUpperBound(0); i++)
            {
                CreatTableString += colnames[i] + " char(128),";
            }

            /*trim last comma from end of string*/
            CreatTableString = TrimRight(CreatTableString, 1);
            CreatTableString += ")";

            /*create the table*/
            try
            {
                cmd = new SQLiteCommand(CreatTableString, sqlcon);
                cmd.ExecuteNonQuery();
            }
            catch (SQLiteException)
            {
                if (cmd.Connection != null)
                {
                    cmd.Dispose();
                   
                }
				/*fail*/
				return -1;
            }

            /*create and build a transaction query*/
            cmd = new SQLiteCommand();
            cmd.Connection = sqlcon;
            SQLiteTransaction sqltrans;
            sqltrans = sqlcon.BeginTransaction();
            cmd.Transaction = sqltrans;

            try
            {
                for (int y = 0; y <= colnames.GetUpperBound(0); y++)
                {
                    string tnames = "";
                    for (int x = 0; x <= array.GetUpperBound(1); x++)
                    {
                        tnames += colnames[x] + ",";
                    }

                    /*trim last comma from end of string*/
                    tnames = TrimRight(tnames, 1);

                    string tvalues = "";
                    for (int x = 0; x <= array.GetUpperBound(1); x++)
                    {
                        tvalues += '"' + array[y, x] + '"' + ",";
                    }

                    /*trim last comma from end of string*/
                    tvalues = TrimRight(tvalues, 1);
                    
                    
                    string sql = "INSERT INTO " + tablename + "(" + 
                        tnames + ") VALUES (" +
                        tvalues +  ")";

                    
                    
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                    
                }

                /*commit the transaction*/
                sqltrans.Commit();

            }
            catch (Exception)
            {
                sqltrans.Dispose();
                cmd.Dispose();
                sqltrans.Rollback();
                return -2;
            }

            /*success*/
            sqltrans.Dispose();
            cmd.Dispose();
            return 0;
        }


private string TrimRight(string s, int count)
        {
            int len = s.Length;

            if (count < 0)
            {
                return s;
            }

            if (count >= len)
            {
                return "";
            }
            return s.Substring(0, len - count);
        }

Dani AI

Generated

Nice snippet, . Two small tips that might make it more robust and a bit faster:

  • Loop bounds: your explanation about GetUpperBound() is spot on. To avoid the < vs <= debate entirely, you can iterate with GetLength, which returns the count rather than the last index. It reads clearly and prevents off-by-one mistakes.
for (int r = 0; r < data.GetLength(0); r++)
for (int c = 0; c < data.GetLength(1); c++)
{
    // use data[r, c]
}
  • Parameterize inserts and reuse the same prepared command inside a single transaction. That guards against quoting issues, handles nulls cleanly, and usually outperforms building a new SQL string per cell/row. Also, if you want to lift the 128-char limit, declare columns as TEXT when you create the table.
// data is string[,]
int rows = data.GetLength(0), cols = data.GetLength(1);

using (var tx = conn.BeginTransaction())
using (var cmd = conn.CreateCommand())
{
    var colsSql = string.Join(",", Enumerable.Range(0, cols).Select(i => "\"c" + i + "\""));
    var valsSql = string.Join(",", Enumerable.Range(0, cols).Select(i => "@p" + i));
    cmd.CommandText = "INSERT INTO \"MyTable\" (" + colsSql + ") VALUES (" + valsSql + ");";
    cmd.Transaction = tx;

    for (int i = 0; i < cols; i++)
        cmd.Parameters.Add(new SQLiteParameter("@p" + i, DbType.String));

    cmd.Prepare(); // reuse compiled plan

    for (int r = 0; r < rows; r++)
    {
        for (int c = 0; c < cols; c++)
            cmd.Parameters[c].Value = (object)data[r, c] ?? DBNull.Value;

        cmd.ExecuteNonQuery();
    }
    tx.Commit();
}

A couple of gotchas:

  • If your first array row contains headers, sanitize and use those as column names (or auto-name like c0, c1, ... as above).
  • Empty string vs NULL: decide deliberately ("" vs DBNull.Value).
  • On trimming: as hinted, prefer Remove(len - n) for fixed-count trimming; Trim removes by character set, not count.
Member Avatar for ddanbe
ddanbe 2,724 Professional Procrastinator

Looks OK.
May I also point your atention to trimming strings in .NET and C#

Member Avatar for Suzie999
Suzie999 245 Coding Hobbyist

Thanks. I was looking at the Trim* methods, but never seen Remove.

Member Avatar for Suzie999
Suzie999 245 Coding Hobbyist

There is a mistake in the code, line 61.

It should be.

for (int y = 0; y <= array.GetUpperBound(0); y++)
Member Avatar for ddanbe
ddanbe 2,724 Professional Procrastinator

Shouldn't it be for (int y = 0; y < array.GetUpperBound(0); y++)?
Same for linr 64 I guess.

Member Avatar for Suzie999
Suzie999 245 Coding Hobbyist

I found that GetUpperBound() returns the highest index/sub index number, rather than the index count.

If I use < rather than <= the last index is omitted.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview