C# SQL Server Examples

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=;" +
                                      "password=;server=;" +
                                      "database=; " +
                                      "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().
                                  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().
                                  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