How to Export CSV file to SQL Server using c# Console Application

Question

Table Name in SqlServer DataBase: MyTable

Csv File location : @"d:MyFile.csv"

How to Copy the CSV file "@"d:MyFile.csv" to "MyTable" a table that exists in SQL server database using C# Console application?!!!

I have used following C# code to export from database to CSV file. But how to do the reverse task?!!!

        string strConn = "Data Source=MYSERVER;Initial Catalog=Master;Integrated Security=True";
        SqlConnection conn = new SqlConnection(strConn);
        SqlDataAdapter da = new SqlDataAdapter("select * from QuickBook", conn);
        DataSet ds = new DataSet();
        da.Fill(ds, "QB");
        DataTable dt = ds.Tables["QB"];
        StreamWriter sw = new StreamWriter(@"d:MyFile.csv", false);
        int iColCount = dt.Columns.Count;

        for (int i = 0; i < iColCount; i++)
        {
            sw.Write(dt.Columns[i]);
            if (i < iColCount - 1)
            {
                sw.Write(",");
            }
        }

        sw.Write(sw.NewLine);

        foreach (DataRow dr in dt.Rows)
        {
            for (int i = 0; i < iColCount; i++)
            {
                if (!Convert.IsDBNull(dr[i]))
                {
                   sw.Write(dr[i].ToString());
                }

                if (i < iColCount - 1)
                {
                    sw.Write(",");
                }
            }

            sw.Write(sw.NewLine);
        }

        sw.Close();

    }
}

One of best methods to deal with a large CSV is System.Data.SqlClient.SqlBulkCopy. And you should change the title - csv files are importing into DB :) — nsb, Jul 11 '14 at 08:08

score 1 · Answer 1 · edited May 23 '17 at 12:29

1

Refer to the Codeproject Link and similar question on this site

edited May 23 '17 at 12:29

Community

1
1

answered Jul 11 '14 at 07:44

tom

719
1
11
30

Pablo Caballero · Answer 2 · 2014-07-11T07:55:22.040

First of all you don't want to "Export CSV file to SQL Server", you want to "export CSV file from SQL Server". It's not the same.

I use EntityFramework to generate the model from the date base. You just have to spend two minutes generating the model.

Add new item>Data>ADO.Net Entity Data Model>Generate from database and choose you connection (or make a new one). The next step is select the tables that you want to export.

Finally you just have to go over the properties of you data context (DbSet) and of course you can filter the elements using LINQ.

string path = Path.Combine("D", "MyFile.csv");

using (var dataContext = new MyDataContext())
{
    using (StreamWriter sw = new StreamWriter(path))
    {
        StringBuilder line = new StringBuilder();
        foreach (var quickBook in dataContext.QuickBooks)
        {
            line.AppendFormat("{0};", quickBook.Name);
            // Append the other properties (remember the culture with the numbers)

            sw.WriteLine(line.ToString());
            line.Clear();
        }
    }
}

One sugestion: You could go over all the properties of a class, in your case "QuickBook":

foreach (PropertyInfo property in typeof(QuickBook).GetProperties())
{
    object value = property.GetValue(quickBook, null);
    if (value == null)
        line.Append(";");
    else
        line.AppendFormat("{0};", value);
}

if (line.Length > 0) //Removes the last ';'
    line.Remove(line.Length - 1, 1);

Please read carefully the question. In the last sentence: "But how to do the reverse task?!!!". — v.karbovnichy, Jul 11 '14 at 08:01

score 0 · Answer 3 · answered Jul 11 '14 at 08:08

The reverse task using EntityFramework could be like this:

string path = Path.Combine("D", "MyFile.csv");

using (var dataContext = new MyDataContext())
{
    using (StreamReader stream = new StreamReader(path))
    {
        string line;

        while ((line = stream.ReadLine()) != null)
        {
            string[] columns = stream.ReadLine().Split(';');

            QuickBook item = new QuickBook();
            item.Name = columns[0];
            // And so on with the other properties...

            dataContext.QuickBooks.Add(item);
        }
    }

    dataContext.SaveChanges();
}

How to Export CSV file to SQL Server using c# Console Application

3 Answers3