Tutorials‎ > ‎

Transfer SQL to PostgreSQL using C# (Intermediate Level)

posted Aug 31, 2014, 9:23 AM by Davin Timothy Ivander   [ updated Aug 15, 2016, 11:38 PM by Surya Wang ]

Hello, This tutorial will guide you to create an application which function is to automatically transfer/migrate data from SQL to PostgreSQL. ( do not worry, I will include my finished apps in the end of tutorial )

There are some things you will need to be installed in the computer you will be using, they are:
- SQL Server (express works too)
- PostGreSQL
- MS Visual Studio (2012 preferred)
- PostGreSQL DRIVER installed (x32 or x64 based on your computer architecture)

Things you will need to prepare:
- Adding PostGreSQL driver to odbc.
- Preparing sample table on sql and postgresql.
- Preparing sample data on sql.

After all above things are done, let's move to the tutorial.

For Starter, create the project, they can be either console, windows form, or WCF (pick one which suites you better). I Create the sample apps using WIndows Form.
I will create another class that will do all the works, instead creating them on the windows form. Some of you may ask WHY? The answer is simple, by creating them in another class, i can easily re-used them for another apps.

SInce we will using ODBC as our main database connection you will need to use this namespace:

using System.Data.OleDb;
using System.Data.Odbc;

I named my class Logic.cs, so let's start by creating the variable to store the target driver.

private static string SqlDriver = "Driver={SQL Server Native Client 10.0}";
private static string PostGreDriver = "Driver={PostgreSQL UNICODE(x64)}";

Remember that the driver name will depends on your driver name on ODBC. Now lets move to create a connection to both of database.

static public bool checkSQLConnection()
{
    if (SqlCon.State != null || SqlCon.State == ConnectionState.Open) SqlCon.Close();
    SqlCon.Open();
    ConnectionState state = SqlCon.State;

    if (state == ConnectionState.Open) { sql_status = true; return true; }
    else { sql_status = false; return false; }
}

static public bool setSQL(string server, string db, string user, string pass)
{
    try
    {
        string sql_con = "Server=" + server + ";Database=" + db + ";Uid=" + user + ";Pwd=" + pass + ";";

        SqlCon = new OdbcConnection(SqlDriver + ";" + sql_con);
        return checkSQLConnection();
    }
    catch (Exception ex) { return false; }
}

static public bool checkPostGreConnection()
{
    if (PostGreCon.State != null || PostGreCon.State == ConnectionState.Open) PostGreCon.Close();
    PostGreCon.Open();
    ConnectionState state = PostGreCon.State;

    if (state == ConnectionState.Open) { postgre_status = true; return true; }
    else { postgre_status = false; return false; }
}

static public bool setPostGre(string server, string port, string db, string user, string pass)
{
    try
    {
        string postgre_con = "Server=" + server + ";Port=" + port + ";Database=" + db + ";Uid=" + user + ";Pwd=" + pass + ";";

        PostGreCon = new OdbcConnection(PostGreDriver + ";" + postgre_con);
        return checkPostGreConnection();
    }
    catch (Exception ex) { return false; }

}

For check and set, i separate them because it more efficient that way. We have met the checkpoint for now, you can go on to test your connection whether they are connected or not connected.
If you managed to get both of them connected than we can go into the process itself. 

The process will inside one function, and i will explain them one by one.

public static string startTransfer(string sql_table, string posg_table)
{
    string result = "";
    try
    {
        int s_row = 0;
        int r_row = 0;

        using (OdbcCommand com = new OdbcCommand("SELECT * FROM " + sql_table, SqlCon))
        {
            using (OdbcDataReader read = com.ExecuteReader())
            {
                string posg_query = "INSERT INTO " + posg_table + " (";
                string posg_query2 = " VALUES (";
                List<string> col = getPostGreTableColumn(posg_table); // get all column name from postgre
                for (int i = 0; i < col.Count; i++)
                {
                    posg_query += "\"" + col[i] + "\"";
                    posg_query2 += "?";
                    if (i < col.Count - 1)
                    {
                        posg_query += ",";
                        posg_query2 += ",";
                    }
                }
                posg_query += ")" + posg_query2 + ");";
                using (OdbcCommand posg_com = new OdbcCommand(posg_query, PostGreCon))
                {
                    while (read.Read())
                    {
                        s_row++;
                        try
                        {
                            posg_com.Prepare(); // prepare the command
                            posg_com.Parameters.Clear(); // clear all existing parameter

                            for (int i = 0; i < read.FieldCount; i++)
                            {
                                posg_com.Parameters.AddWithValue("@" + col[i], read[i]); // set the value for the parameter
                            }

                            r_row += posg_com.ExecuteNonQuery();
                        }
                        catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); }
                    }
                }
            }
        }
        result = "Rows Readed = " + s_row + ", Rows Inserted = " + r_row;
    }
    catch (Exception ex) { return "Something Went Wrong Before Transfer Process Begin!"; }
    return result;
}

If you can understand all of that, that means you just create an application which function is to transfer sql to postgre.
But let me explain about parameter here:

Let say the Query were like this -> INSERT INTO <table>(id,user,pass) VALUES(?,?,?)
AddWithValue will automatically set the value based on the table column which is (id,user,pass)
so when AddWIthValue("@id","asd") are called, that means the query became like this -> 
INSERT INTO <table>(id,user,pass) VALUES('asd',?,?)



Again why i used parameter instead of just inputting the value in the query?
the answer are simple, consider you got a multiple type of the column (varchar, int, datetime, etc), you will need to check everything before inserting them into the query.
But when we used parameter, they automatically know which type of data needed to be insrted. This will make your code more efficient.

the idea here, is to create the query and insert them one by one. You may find another way to do this in more efficient way than my current codes in the future,
but this code were efficient enough for me right now.

Thanks for reading the tutorial until finish, i hope you can understand and create your own version of it.

OH, I've Included the finished version using MS Visual Studio 2012, and .NET 4. (you may need to adjust the postgre driver to your own).
ċ
Database Management.zip
(89k)
Davin Timothy Ivander,
Aug 31, 2014, 9:23 AM