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.

Edited 1 Year Ago by Suzie999

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);
        }

There is a mistake in the code, line 61.

It should be.

for (int y = 0; y <= array.GetUpperBound(0); y++)

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.

The article starter has earned a lot of community kudos, and such articles offer a bounty for quality replies.