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
– nsb, Commented Jul 11, 2014 at 8:08

Community · Accepted Answer · 2017-05-23 12:29:28Z

1

Refer to the Codeproject Link and similar question on this site

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Jul 11, 2014 at 7:44

tom

7292 gold badges11 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pablo Caballero · Accepted Answer · 2014-07-11 07:55:22Z

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?!!!".

Pablo Caballero · Accepted Answer · 2014-07-11 08:08:55Z

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();
}

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Linked

Related