Exporting from SQL

Matt Butler

I am trying to export from my SQL server. I have the connection working perfectly, but I am receiving an error when I export dates/GUIDs/etc.

The error says that it is not able to cast the object from datetime to string or from uniqueidentifier to string. Is there something that I am missing here, or should I not be using string at all? I am new to C# - I am a java convert. Please be nice. If you have questions, I will do my best to answer them. Below is the code that I have thus far.

Kind Regards

         /*
         * Exports data to the CSV file.
         */

		private void exportToCSVfile(string fileOut)
		{
            // Connects to the database, and makes the select command.
            SqlConnection conn = new SqlConnection(prop.sqlConnString);
			string sqlQuery = "select * from " + this.lbxTables.SelectedItem.ToString();
			SqlCommand command = new SqlCommand(sqlQuery, conn);
			conn.Open();
			
            // Creates a SqlDataReader instance to read data from the table.
            SqlDataReader dr = command.ExecuteReader();

            // Retrives the schema of the table.
			DataTable dtSchema = dr.GetSchemaTable();

            // Creates the CSV file as a stream, using the given encoding.
			StreamWriter sw = new StreamWriter(fileOut, false, this.encodingCSV);
			
            string strRow; // represents a full row

            // Writes the column headers if the user previously asked that.
			if (this.chkFirstRowColumnNames.Checked)
			{
				sw.WriteLine(columnNames(dtSchema, this.separator));
			}

            // Reads the rows one by one from the SqlDataReader
            // transfers them to a string with the given separator character and
            // writes it to the file.
            try
            {
                while (dr.Read())
                {
                    strRow = "";
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        strRow += dr.GetString(i);
                        if (i < dr.FieldCount - 1)
                        {
                            strRow += this.separator;
                        }
                    }
                    sw.WriteLine(strRow);
                }
            }
            catch (Exception)
            {
                MessageBox.Show("Error encountered! File creation halted.","Fatal Error!");
            }
			


            // Closes the text stream and the database connenction.
			sw.Close();
			conn.Close();

            // Notifies the user.
			MessageBox.Show("Done!");
		}

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mahesh Chand16y ago
Accepted answer
Matt,
What format are you trying to expert your data into? Simplest and fastest way to export data is using DataSet.WriteXXX() methods. Using this method, you can save a DataSet contents to any format - XML, text, string etc.

You will need to change your code to get data in a DataSet and then call one of the WriteXXX methods. In this case, you don't have to loop through one line at a time and there will be no errors.

Here is an example.
```
private void WriteXmlToFile(DataSet thisDataSet) 
{
    if (thisDataSet == null) { return; }

    // Create a file name to write to.
    string filename = "XmlDoc.xml";

    // Create the FileStream to write with.
    System.IO.FileStream stream = new System.IO.FileStream
        (filename, System.IO.FileMode.Create);

    // Write to the file with the WriteXml method.
    thisDataSet.WriteXml(stream);   
}
```
Definition of all WriteXXX methods is listed here:
http://msdn.microsoft.com/en-us/library/system.data.dataset_methods.aspx

Cheers!
Suthish Nair16y ago
Matt,
refer Our recommended articles for Export to Excel, might helps you..
Matt Butler16y ago
I am looking at the dataset right now. I have plenty of data to export and I think you are right about it being the best way. Thanks Mahesh!
Mahesh Chand16y ago
Also, as a note, this method (using row by row) is not efficient when you have lot of data. I recommend use DataSet.Write method :)
Mahesh Chand16y ago
This code will fail for columns that do not have string values (I AM GUESSING), I think for Guid column type.
strRow += dr.GetString(i);
What you need to do is, before you convert direct, you need to find out what is the data type and then convert using Convert class's ToString method. If you have a Guid column, it will not be converted direct. You need to use Guid class to convert it.

I do not remember exact syntax but if you search this site, you will find some code samples.
Matt Butler16y ago
I am getting the error on this line:

strRow += dr.GetString(i);

System.InvalidCastException was unhandled
Message=Unable to cast object of type 'System.Guid' to type 'System.String'.
Source=System.Data
StackTrace:
       at System.Data.SqlClient.SqlBuffer.get_String()
       at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)
       at CSV_import_export.frmExport.exportToCSVfile(String fileOut) in C:\Users\mbutler\Documents\Visual Studio 2010\Projects\CSV_import_export\CSV_import_export\frmExport.cs:line 241
       at CSV_import_export.frmExport.exportToCSV() in C:\Users\mbutler\Documents\Visual Studio 2010\Projects\CSV_import_export\CSV_import_export\frmExport.cs:line 198
       at CSV_import_export.frmExport.btnExportToCSV_Click(Object sender, EventArgs e) in C:\Users\mbutler\Documents\Visual Studio 2010\Projects\CSV_import_export\CSV_import_export\frmExport.cs:line 175
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at CSV_import_export.Program.Main() in C:\Users\mbutler\Documents\Visual Studio 2010\Projects\CSV_import_export\CSV_import_export\Program.cs:line 17
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
       at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
       at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
       at System.Activator.CreateInstance(ActivationContext activationContext)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
InnerException:
Matt Butler16y ago
I have delimiters setup for TAB, comma and semi-colon. I need it to be a simple text file (or CSV). Here is one example of the exception text I am getting.

************** Exception Text **************

System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.String'.

   at System.Data.SqlClient.SqlBuffer.get_String()

   at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)

   at CSV_import_export.frmExport.exportToCSVfile(String fileOut)

   at CSV_import_export.frmExport.exportToCSV()

   at CSV_import_export.frmExport.btnExportToCSV_Click(Object sender, EventArgs e)

   at System.Windows.Forms.Control.OnClick(EventArgs e)

   at System.Windows.Forms.Button.OnClick(EventArgs e)

   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)

   at System.Windows.Forms.Control.WndProc(Message& m)

   at System.Windows.Forms.ButtonBase.WndProc(Message& m)

   at System.Windows.Forms.Button.WndProc(Message& m)

   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)

   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)

   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
Suthish Nair16y ago
Matt,

On which line number you getting the error..