This is for a quick reference whenever I need use some data from SQL server:

1. From SQL to DataTable:

SqlConnection myConnection = new SqlConnection("user id=<Usr>;" +
                                      "password=<PW>;server=<Server>;" +
                                      "database=<DB>; " +
                                      "connection timeout=30");
        myConnection.Open();
        SqlCommand command = new SqlCommand("Select xxx from xxx here xxx =xxx order by xxx", myConnection);
        // Creates a SqlDataReader instance to read data from the table.
        SqlDataReader dataReader = command.ExecuteReader();
 DataTable dt = new DataTable();
 dt.Load(dataReader);

 

2. From DataTable to String Builder

var sb = new StringBuilder();
string[] columnNames = dt.Columns.Cast<DataColumn>().
                                  Select(column => column.ColumnName).
                                  ToArray();
        sb.AppendLine(string.Join(",", columnNames));

        foreach (DataRow row in dt.Rows)
        {
            string[] fields = row.ItemArray.Select(field => field.ToString()).
                                            ToArray();
            sb.AppendLine(string.Join(",", fields));
        }

 3. From DataTable to GridView

this.GridView1.Visible = true;
GridView1.DataSource = dt;
GridView1.DataBind();

 4. From DataTable to CSV

StringBuilder sb = new StringBuilder(); 

string[] columnNames = dt.Columns.Cast<DataColumn>().
                                  Select(column => column.ColumnName).
                                  ToArray();
sb.AppendLine(string.Join(",", columnNames));

foreach (DataRow row in dt.Rows)
{
    string[] fields = row.ItemArray.Select(field => field.ToString()).
                                    ToArray();
    sb.AppendLine(string.Join(",", fields));
}

File.WriteAllText("test.csv", sb.ToString());

 
Reference:

http://stackoverflow.com/questions/4959722/c-sharp-datatable-to-csv

About Lei

I am an IT specialist with over 10 year experience - years on Automation, on-Premise or Azure.

I am happy to develop however never want be a full time developer. Only do what I have to do. If it has to be PowerShell,HTML, PHP, CSS, C#, VBS or JS, front end or backend, so be it, doesn't matter!

Spent years with Windows, SCCM, SharePoint, SQL and Exchange servers. For last several years, I have been actively working under On Premise > Azure environment.

THERE IS NO WAY BACK!!!

Current Certificates:
    Microsoft® Certified-
  • -Enterprise Administrator
  • -Database Administrator
  • -SharePoint Administrator
  • -Administering and Deploying SCCM 2012
Red Hat Certified Technician
ITIL V3 Foundation - Practitioner

Working on Azure Certificates now and hopefully they can stop upgrading their questions one day! GIVE ME A BREAK!

Contact Lei

Name *
Email *
Comments *

Traffic since 10/08/2016

Today53
Yesterday83
This week677
This month1973
Total416333

Visitor Info

  • IP: 54.227.51.103
  • Browser: Unknown
  • Browser Version:
  • Operating System: Unknown

Who Is Online

1
Online

2017-12-17

Login