2 dimensional string array to SQLite database table

Suzie999 0 Tallied Votes 2K Views Share

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);
        }
ddanbe 2,724 Professional Procrastinator Featured Poster

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

Suzie999 245 Coding Hobbyist

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

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++)
ddanbe 2,724 Professional Procrastinator Featured Poster

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

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.