CODEWITHSUNDEEP
c#sql-servercsv
user3825267
user3825267

Reputation: 13

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

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

    }
}

Upvotes: 0

Views: 3491

Answers (3)

Pablo Caballero
Pablo Caballero

Reputation: 338

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

Upvotes: 0

tom
tom

Reputation: 729

Refer to the Codeproject Link and similar question on this site

Upvotes: 1

Pablo Caballero
Pablo Caballero

Reputation: 338

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

Upvotes: 0

Related Questions