C# SQLServer retrieving results and place in a .cs

问题:

I had a look on the site and on Google, but I couldn't seem to find a good solution to what I'm trying to do.

Basically, I have a client server application (C#) where I send the server an SQL select statement (Connecting to SQL Server 2008) and would like to return results in a CSV manner back to the client.

So far I have the following:

if (sqlDataReader.HasRows)
{
    while(sqlDataReader.Read())
    {
       //not really sure what to put here and if the while should be there!
    }

} `

Unfortunately, I'm really new to connecting C# with SQL. I need any tips on how to simply put the results in a string in a csv format. The columns and fields are likely to be different so I cannot use the method of something[something] as I've seen in a few sites. I'm not sure if I'm being comprehensible tbh!

I would really appreciate any tips / points on how to go about this please!

回答1:

Here is a method I use to dump any IDataReader out to a StreamWriter. I generally create the StreamSwriter like this: new StreamWriter(Response.OutputStream). I convert any double-quote characters in the input into single-quote characters (maybe not the best way to handle this, but it works for me).

public static void createCsvFile(IDataReader reader, StreamWriter writer) {
    string Delimiter = "\"";
    string Separator = ",";

    // write header row
    for (int columnCounter = 0; columnCounter < reader.FieldCount; columnCounter++) {
        if (columnCounter > 0) {
            writer.Write(Separator);
        }
        writer.Write(Delimiter + reader.GetName(columnCounter) + Delimiter);
    }
    writer.WriteLine(string.Empty);

    // data loop
    while (reader.Read()) {
        // column loop
        for (int columnCounter = 0; columnCounter < reader.FieldCount; columnCounter++) {
            if (columnCounter > 0) {
                writer.Write(Separator);
            }
            writer.Write(Delimiter + reader.GetValue(columnCounter).ToString().Replace('"', '\'') + Delimiter);
        }   // end of column loop
        writer.WriteLine(string.Empty);
    }   // data loop

    writer.Flush();
}

回答2:

You may be able to adapt the implementation of a CSV writer available here.

If you also need to parse CSV files, the implementation here is relatively good.

The CSV format is more complicated than it looks - particularly if you're going to deal with arbitrary data coming back from a query. You would need to be able to handle escaping of special characters (like quotes and commas), dealing with line breaks, and the like. You are better off finding and using a proven implementation - especially if you're new to C#.

回答3:

You can get the table column names like this:

SqlConnection conn = new SqlConnection(connString);
conn.Open();

SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader rdr = cmd.ExecuteReader();

DataTable schema = rdr.GetSchemaTable();
foreach (DataRow row in schema.Rows) 
{
    foreach (DataColumn col in schema.Columns)
        Console.WriteLine(col.ColumnName + " = " + row[col]);
}

rdr.Close()
conn.Close();

Of course you can determine the columns names with the first row only, here it does it on every rows.

You can now put your own code to join the columns into a CSV line pretty easily...

Thanks

回答4:

As mentioned, there are quite a few issues with delimiters, escaping characters correctly, and formatting different types correctly. But if you are just looking for an example of putting data into a string, here is yet another one. It does not do any checking for the aforementioned complications.

  public static void ReaderToString( IDataReader Reader )
     {
     while ( Reader.Read() )
        {
        StringBuilder str = new StringBuilder();
        for ( int i = 0; i < Reader.FieldCount; i++ )
           {

           if ( Reader.IsDBNull( i ) )
              str.Append( "null" );
           else
              str.Append( Reader.GetValue( i ).ToString() );

           if ( i < Reader.FieldCount - 1 )
              str.Append( ", " );

           }
        // do something with the string here
        Console.WriteLine(str);
        }
     }

回答5:

When dealing with CSV file I usually go for the FileHelpers library: it has a SqlServerStorage class which you can use to read records from a SQL server and write them to a CSV file.