Guide to enhance my code

Answer 1

1. Use a SELECT query to find the non-empty rows (you seem to get along with the EOF issue).
2. On form_load event make the form invisible by program arguments.
3. Why not to use INSERT INTO (and then DELETE)?

Answer 2

That sure is some code and I sure could recommend a lot of things to improve it if you care.

First thing I would do is read up on IDisposable then I would re-write that DataReader as following.

using(StreamWriter tw = File.AppendText("c:\INMS.txt"))
{
    using(SqlDataReader reader = cmd.ExecuteReader())
    {
      tw.WriteLine("id, ip_add, message, datetime");
      while (reader.Read())
      {
         tw.Write(reader["id"].ToString());
         tw.Write(", " + reader["ip_add"].ToString());
         tw.Write(", " + reader["message"].ToString());
         tw.WriteLine(", " + reader["datetime"].ToString());
      }
      tw.WriteLine(DateTime.Now);
      tw.WriteLine("---------------------------------");
   }
}

Then after your catch, put the following and remove the close call.

finally
{
   sqlConnection1.Dispose(); //close con
}

Answer 3

A few suggestions:

• Move it out of the form. Business logic and data access does not belong in the form (View). Move it to a separate class.
• Keep the MessageBox code in the form. That's display logic. The entire try..catch can be moved out of the method; just have the method throw exceptions. And don't catch System.Exception - catch the database one(s) you expect.
• I echo Ty's comments on IDisposable and using statements.
• Read up on Extract Method and the Single Responsibility Principle. This method does a lot, and it's long. Break it up.
• Move some of the string hardcodes out. What if your connection string or file paths change? Why not put those in a configuration file (or at least use some constants)?

For starters, anyway. :)

Answer 4

In addition to some of the other answers already given, you might also want to consider protecting the data operation with a Transaction.

I assume that you don't want any of the following operation to partially complete:

  cmdCopy.ExecuteScalar();
  cmd.ExecuteNonQuery(); //execute insert query
  cmdDel.ExecuteScalar();//execute delete query

If you are processing MANY rows you might want to batch your updates but that is a whole different issue.

Answer 5

Firstly kudos to you for trying to improve your skill and being open to publish your code like this. I believe that is the first step to being a better programmer, is to have this type of attitude.

Here is an implementation that answers some of your questions. I have extracted some of the old code into methods and also moved some of the responsibilities to their own classes.

Disclaimer:

• Although the code compiles I didn't run it against a database, therefore there might be a couple of small things I missed.
• I had to stop short on certain refactorings not knowing the exact requirements and also to still try and keep some of the concepts simple.

.

using System;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;

// Program.cs
static class Program
{
    [STAThread]
    static void Main()
    {
        try
        {
            MailArchiver.Run();
            Console.WriteLine("Application completed successfully");
        }
        catch (Exception ex)
        {
            Console.WriteLine("Unexpected error occurred:");
            Console.WriteLine(ex.ToString());
        }

    }
}

// Reads new messages from DB, save it to a report file 
// and then clears the table
public static class MailArchiver
{

    public static void Run()
    {
        // Might be a good idea to a datetime suffix 
        ReportWriter.WriteFile(@"c:\INMS.txt");
        CopyAndClearMessages();
    }

    private static void CopyAndClearMessages()
    {
        SqlConnection cn = DbConnectionFactory.CreateConnection();
        cn.Open();

        try
        {
            SqlTransaction tx = cn.BeginTransaction();

            try
            {
                CopyMessages(cn, tx);
                DeleteMessages(cn, tx);
                tx.Commit();
            }
            catch
            {
                tx.Rollback();
                throw;
            }
        }
        finally
        {
            cn.Close();
        }
    }

    private static void DeleteMessages(SqlConnection cn, SqlTransaction tx)
    {
        var sql = "DELETE FROM tblOutbox";
        var cmd = new SqlCommand(sql, cn, tx);
        cmd.CommandTimeout = 60 * 2;  // timeout 2 minutes 
        cmd.ExecuteNonQuery();
    }

    private static void CopyMessages(SqlConnection cn, SqlTransaction tx)
    {
        var sql = "INSERT INTO tblSend (ip, msg, date) SELECT ip, msg, date FROM tblOutbox";
        var cmd = new SqlCommand(sql, cn, tx);
        cmd.CommandTimeout = 60 * 2;  // timeout 2 minutes 
        cmd.ExecuteNonQuery();
    }
}

// Provides database connections to the rest of the app.
public static class DbConnectionFactory
{
    public static SqlConnection CreateConnection()
    {
        // Retrieve connection string from app.config
        string connectionString = ConfigurationManager.ConnectionStrings["MailDatabase"].ConnectionString;
        var cn = new SqlConnection(connectionString);

        return cn;
    }
}

// Writes all the data in tblOutbox to a CSV file
public static class ReportWriter
{
    private static SqlDataReader GetData()
    {
        SqlConnection cn = DbConnectionFactory.CreateConnection();
        cn.Open();

        try
        {
            var cmd = new SqlCommand();
            cmd.CommandText = "SELECT * FROM tblOutbox";
            cmd.Connection = cn;

            return cmd.ExecuteReader();
        }
        finally
        {
            cn.Close();
        }
    }

    public static void WriteFile(string filename)
    {
        if (File.Exists(filename))
        {
            // This might be serious, we may overwrite data from the previous run.
            // 1. You might want to throw your own custom exception here, should want to handle this
            //    condition higher up.
            // 2. The logic added here is not the best and added for demonstration purposes only.
            throw new Exception(String.Format("The file [{0}] already exists, move the file and try again"));
        }
        var tw = new StreamWriter(filename);

        try
        {
            // Adds header record that describes the file contents
            tw.WriteLine("id,ip address,message,datetime");

            using (SqlDataReader reader = GetData())
            {
                while (reader.Read())
                {
                    var id = reader["id"].ToString();
                    var ip = reader["ip"].ToString();

                    //msg might contain commas, surround value with double quotes
                    var msg = reader["msg"].ToString();
                    var date = reader["data"].ToString();

                    if (IfValidRecord(id, ip, msg, msg, date))
                    {
                        tw.WriteLine(string.Format("{0},{1},{2},{3}", id, ip, msg, date));
                    }
                }

                tw.WriteLine("Report generated at : " + DateTime.Now);
                tw.WriteLine("--------------------------------------");
            }

        }
        finally
        {
            tw.Close();
        }

    }

    private static bool IfValidRecord(string id, string ip, string msg, string msg_4, string date)
    {
        // this answers your question on how to handle validation per record.
        // Add required logic here
        return true;
    }
}