Efficient way to loop through data

Question

1.00/5 (1 vote)

See more:

In c# I have written a code to first fetch data from database, loop through it and create insert statement for same. Below is the C# code...

C#

StringBuilder strSql = new StringBuilder();
            strSql.Append("ATTACH 'D:\\Application\\Demo Applications\\Windows\\WindowsFormsApplication_SQLite\\Database\\Source\\TestDB.db' AS SourceDB;");
            strSql.Append("ATTACH 'D:\\Application\\Demo Applications\\Windows\\WindowsFormsApplication_SQLite\\Database\\Destination\\TestDB.db' AS DestDB;");
            
            using (SQLiteConnection con = new SQLiteConnection(SourceconnectionString))
            {
                con.Open();

                using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM dbSchema", con))
                {
                    using (SQLiteDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read()) // Reading Rows
                        {
                            strSql.Append("INSERT INTO DestDB.");
                            strSql.Append(rdr["TableName"].ToString());
                            strSql.Append(" (");
                            strSql.Append(rdr["Columns"].ToString());
                            strSql.Append(") ");
                            strSql.Append("SELECT ");
                            strSql.Append(rdr["Columns"].ToString());
                            strSql.Append(" FROM SourceDB.");
                            strSql.Append(rdr["TableName"].ToString());
                            strSql.Append(";");
                            var tableName = rdr["TableName"].ToString();
                            var Columns = rdr["Columns"].ToString();
                        }
                    }
                }
            }

            using (SQLiteConnection conn = new SQLiteConnection(DestinationConnectionString))
            {
                conn.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandText = strSql.ToString();
                    int i = cmd.ExecuteNonQuery();
                    if (i > 0)
                    {
                        MessageBox.Show("Data dumped successfully ...!!!");
                    }
                }
            }

Above code is just working fine. It is doing exactly what is desired.

But, I wonder is there more ELEGANT and EFFICIENT way to achieve same.

What I have tried:

C#

StringBuilder strSql = new StringBuilder();
            strSql.Append("ATTACH 'D:\\Application\\Demo Applications\\Windows\\WindowsFormsApplication_SQLite\\Database\\Source\\TestDB.db' AS SourceDB;");
            strSql.Append("ATTACH 'D:\\Application\\Demo Applications\\Windows\\WindowsFormsApplication_SQLite\\Database\\Destination\\TestDB.db' AS DestDB;");
            
            using (SQLiteConnection con = new SQLiteConnection(SourceconnectionString))
            {
                con.Open();

                using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM dbSchema", con))
                {
                    using (SQLiteDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read()) // Reading Rows
                        {
                            strSql.Append("INSERT INTO DestDB.");
                            strSql.Append(rdr["TableName"].ToString());
                            strSql.Append(" (");
                            strSql.Append(rdr["Columns"].ToString());
                            strSql.Append(") ");
                            strSql.Append("SELECT ");
                            strSql.Append(rdr["Columns"].ToString());
                            strSql.Append(" FROM SourceDB.");
                            strSql.Append(rdr["TableName"].ToString());
                            strSql.Append(";");
                            var tableName = rdr["TableName"].ToString();
                            var Columns = rdr["Columns"].ToString();
                        }
                    }
                }
            }

            using (SQLiteConnection conn = new SQLiteConnection(DestinationConnectionString))
            {
                conn.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandText = strSql.ToString();
                    int i = cmd.ExecuteNonQuery();
                    if (i > 0)
                    {
                        MessageBox.Show("Data dumped successfully ...!!!");
                    }
                }
            }

Posted 7-Dec-16 0:51am

sopy7

Updated 7-Dec-16 2:00am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Accepted Answer · 2016-12-07T02:00:00

Well, that is frought with danger because a) you're relying on the data in rdr to always be there, and b) You're not using parameterized queries. I would do something like this (caution - this is untested code, so I might have gotten some stuff wrong):

C#

using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM dbSchema", con))
{
    using (SQLiteDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read()) // Reading Rows
        {
            string tablename;
            int columns;
            try
            {
                tablename = rdr.GetString("TableName");
                columns   = rdr.GetInt32("Columns");

                string newQuery = "INSERT INTO DestDB.@tablename (Columns) Values(@columns);";
                string newQuery2 = "SELECT [Columns] FROM SourceDB.@tablename";

                SQLiteCommand cmd = new SQLiteCommand(newQuery, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add(new SqlParameter("@tablename", tablename));
                cmd.Parameters.Add(new SqlParameter("@columns", columns));
                cmd.ExceuteNonQuery();

                cmd.CommandText = newQuery2;
                using (SqliteDataReader rdr2 = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        // ... do something ...
                    }
                }
            }
            catch (Exception ex)
            {
               // inspect for problems here (couldn't retrieve tablename 
               // or columns info, other db stuff)
            }
        }
    }
}

FWIW, you don't have to re-query the database to see if the data was added to the table. All you have to do is see if the number of rows changed is equal to 1 (the return value from ExecuteNonQuery).