I am trying to implement a script in my application that will dump the entire contents (for now, but I am trying to write the code so that I can easily customize it to only grab certain columns) of a sql db (running ms sql server express 2014) to a .csv file.
Here is the code I have written currently:
public void doCsvWrite(string timeStamp){ try { //specify file name of log file (csv). string newFileName = "C:/TestDirectory/DataExport-" + timeStamp + ".csv"; //check to see if file exists, if not create an empty file with the specified file name. if (!File.Exists(newFileName)) { FileStream fs = new FileStream(newFileName, FileMode.CreateNew); fs.Close(); //define header of new file, and write header to file. string csvHeader = "ITEM1,ITEM2,ITEM3,ITEM4,ITEM5"; using (FileStream fsWHT = new FileStream(newFileName, FileMode.Append, FileAccess.Write)) using(StreamWriter swT = new StreamWriter(fsWHT)) { swT.WriteLine(csvHeader.ToString()); } } //set up connection to database. SqlConnection myDEConnection; String cDEString = "Data Source=localhost\NAMEDPIPE;Initial Catalog=db;User Id=user;Password=pwd"; String strDEStatement = "SELECT * FROM table"; try { myDEConnection = new SqlConnection(cDEString); } catch (Exception ex) { //error handling here. return; } try { myDEConnection.Open(); } catch (Exception ex) { //error handling here. return; } SqlDataReader reader = null; SqlCommand myDECommand = new SqlCommand(strDEStatement, myDEConnection); try { reader = myDECommand.ExecuteReader(); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { if(reader["Column1"].ToString() == "") { //does nothing if the current line is "bugged" (containing no values at all, typically happens after reboot of 3rd party equipment). } else { //grab relevant tag data and set the csv line for the current row. string csvDetails = reader["Column1"] + "," + reader["Column2"] + "," + String.Format("{0:0.0}", reader["Column3"]) + "," + String.Format("{0:0.000}", reader["Column4"]) + "," + reader["Column5"]; using (FileStream fsWDT = new FileStream(newFileName, FileMode.Append, FileAccess.Write)) using(StreamWriter swDT = new StreamWriter(fsWDT)) { //write csv line to file. swDT.WriteLine(csvDetails.ToString()); } } } } } catch (Exception ex) { //error handling here. myDEConnection.Close(); return; } myDEConnection.Close(); } catch (Exception ex) { //error handling here. MessageBox.Show(ex.Message); } }
Now, this was working fine when I was using it with a 3rd party SQLite-based database, but the output I’m getting after modifing this to my MSSQL db looks something like this (ITEM1 is the primary key, a standard auto-incrementing ID-field):
ITEM1,ITEM2,ITEM3,ITEM4,ITEM5 1,row1_item2,row1_item3,row1_item4,row1_item5 1,row1_item2,row1_item3,row1_item4,row1_item5 1,row1_item2,row1_item3,row1_item4,row1_item5 1,row1_item2,row1_item3,row1_item4,row1_item5 1,row1_item2,row1_item3,row1_item4,row1_item5 1,row1_item2,row1_item3,row1_item4,row1_item5 2,row2_item2,row2_item3,row2_item4,row2_item5 2,row2_item2,row2_item3,row2_item4,row2_item5 2,row2_item2,row2_item3,row2_item4,row2_item5 2,row2_item2,row2_item3,row2_item4,row2_item5 2,row2_item2,row2_item3,row2_item4,row2_item5 3,row3_item2,row3_item3,row3_item4,row3_item5 3,row3_item2,row3_item3,row3_item4,row3_item5 3,row3_item2,row3_item3,row3_item4,row3_item5 3,row3_item2,row3_item3,row3_item4,row3_item5 ....
So it seems that it writes several entries of the same row, where I would just like one single line each row. Any suggestions?
Thanks in advance.
edit: Thanks everyone for your answers!
Advertisement
Answer
The for loop isn’t needed in the section below. Because it loops from 0 to FieldCount I assume the loop was originally meant to append the text from each column together but inside the loop there’s a single line that concatenates the text and assigns it to csvDetails.
try { reader = myDECommand.ExecuteReader(); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { if(reader["Column1"].ToString() == "") { //does nothing if the current line is "bugged" (containing no values at all, typically happens after reboot of 3rd party equipment). } else { //grab relevant tag data and set the csv line for the current row. string csvDetails = reader["Column1"] + "," + reader["Column2"] + "," + String.Format("{0:0.0}", reader["Column3"]) + "," + String.Format("{0:0.000}", reader["Column4"]) + "," + reader["Column5"]; using (FileStream fsWDT = new FileStream(newFileName, FileMode.Append, FileAccess.Write)) using(StreamWriter swDT = new StreamWriter(fsWDT)) { //write csv line to file. swDT.WriteLine(csvDetails.ToString()); } } } } }