Import Text File data to PostgreSQL database using C#

Question

private void GetTextFile()
{
     NpgsqlConnection npgsqlConnection = new NpgsqlConnection();
     npgsqlConnection.ConnectionString = "Server=127.0.0.1;Port=5432;User 
     Id=postgres;Password=rutuparna;Database=Employee";
     npgsqlConnection.Open();

     NpgsqlCommand command = new NpgsqlCommand("Select * from employee_details", npgsqlConnection);
     NpgsqlDataReader dataReader = command.ExecuteReader();

     using (System.IO.StreamWriter writer = new System.IO.StreamWriter(@"D:\Rutu\txtfile.txt", false, Encoding.UTF8))
     {

        while (dataReader.Read())
        { 
         writer.WriteLine(dataReader[0] + "; " + dataReader[1] + ";" + dataReader[2] + ";" + dataReader[3]);
        }
     }

     MessageBox.Show("Data fetched Properly");
}

Here I have done how to convert data into a text file.

Can somebody please give me the code of how to export Text File data to the SQL database using C# ?

Hello! Welcome to StackOverflow.Does this answer your question?stackoverflow.com/questions/34851800/… — user8214728
– user8214728, Commented Dec 11, 2019 at 6:40

Athanasios Kataras · Accepted Answer · 2019-12-11 06:41:55Z

Why not something like this

using (SqlConnection con = new SqlConnection(@"your connection string"))
{
    con.Open();
    using(StreamReader file = new StreamReader(@"D:\Rutu\txtfile.txt"))
    {
         while((line = file.ReadLine()) != null)
         {
             string[] fields = line.Split(',');

             SqlCommand cmd = new SqlCommand("INSERT INTO employee_details(column1, column2, column3,column4) VALUES (@value1, @value2, @value3, @value4)", con);
             cmd.Parameters.AddWithValue("@value1", fields[0].ToString());
             cmd.Parameters.AddWithValue("@value2", fields[1].ToString());
             cmd.Parameters.AddWithValue("@value3", fields[2].ToString());
             cmd.Parameters.AddWithValue("@value4", fields[3].ToString());
             cmd.ExecuteNonQuery();
         }
    }
 }

I don't know the names of your columns, so you'll need to replace them.

Jonathan Willcock · Accepted Answer · 2019-12-11 07:36:24Z

If speed is a consideration (and IMHO it always should be) and you are using PostgreSQL (which you seem to be), then you should take a look at the COPY function. Single inserts are always slower than a bulk operation.

It is relatively easy to wrap the COPY function within c#. The following is a cut down version of a method, to illustrate the point. My method loops through the rows of a DataTable, but it is easy to adapt for a file situation (ReadLine() etc).

using (var pgConn = new NpgsqlConnection(myPgConnStr))
{
    using (var writer = pgConn.BeginBinaryImport("COPY " + destinationTableName + " (" + commaSepFieldNames + ") FROM STDIN (FORMAT BINARY)"))
    {
        //Loop through data
        for (int i = 0; i < endNo; i++)
        {
            writer.StartRow();

            //inner loop through fields
            for (int j = 0; j < fieldNo; j++)
            {

                //test for null
                if (true)
                {
                    writer.WriteNull();
                }
                else
                {
                    //Write data using column types
                    writer.Write(value, type);
                }
            }
        }
        writer.Complete();
    }
}

WriteNull() is a special method for correctly adding NULL to the stream, otherwise you use Write<T>(). This latter has three overloads, just the value, value with type enumeration, or value with type name. I prefer to use the enumeration. commaSepFieldNames is a comma separated list of the field names. Other variables should (I hope) be self-explanatory.

Collectives™ on Stack Overflow

Import Text File data to PostgreSQL database using C#

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related