Export SQL Server Data to Excel Using C#?

OldZero

I'm working on an assignment that requires a simple data transfer data of a SQL Server to MS Excel; using C#. I was able to develop the "foundation" of what I believe works/ However I'm unable to run the program. Assistance is appreciated!

namespace ProjectLab1
{
class Program
{
static void Main(string[] args)
{
//Connection string needed to talk to Omnymbus
string conString = "Driver={MySQL ODBC 5.3 ANSI Driver};"
+ "Server=devry.edupe.net;Port=4300;"
+ "Database=Inventory_XXXX;"
+ "uid=XXXX;pwd=DeVry_Student";
OdbcConnection connection = new OdbcConnection(conString);
connection.Open();
//Get all data
//string theQuery = "SELECT * FROM item";
string theQuery = "SELECT * FROM item i, inventory v where i.invent_id=v.invent_id";
OdbcDataAdapter DataAdapter = new OdbcDataAdapter(theQuery, connection);
DataSet theData = new DataSet();
DataAdapter.Fill(theData, "item");
DataTable theTable = theData.Tables[0];

        Console.Write("ITEM".PadRight(30));
        Console.Write("QUANT".PadLeft(10));
        Console.Write("SIZE".PadLeft(10));
        Console.Write("COLOR".PadLeft(10));
        Console.Write("PRICE\\n".PadLeft(10));//the "\\n" moves cursor to next line
        Console.WriteLine("-".PadLeft(70, '-'));// "WriteLine" places cursor next line

        // Loop through all data results
        foreach (DataRow dataRow in theTable.Rows)
        {
            Console.Write(dataRow\["invent\_desc"\].ToString().PadRight(30));
            Console.Write(dataRow\["qoh"\].ToString().PadLeft(10));
            Console.Write(dataRow\["itemsize"\].ToString().PadLeft(10));
            Console.Write(dataRow\["color"\].ToString().PadLeft(10));
            Console.WriteLine(((decimal)dataRow\["curr\_price"\]).ToString("C").PadLeft(10));

        }

        {



            StreamWriter file = new StreamWriter(@"C:\\Users\\redfaction\\Desktop\\Data\\" + strFileName);
            file.WriteLine(sb.ToString());
            File.Close();
            connection.Close(); // Close connection

            //Have program pause to keep from closing console window



        }
    }
}

}

Richard Andrew x64

Member 11527519 wrote:

I'm unable to run the program.

Care to be more specific? How is anyone supposed to know what you mean by that?

The difficult we do right away... ...the impossible takes slightly longer.

OldZero

I'm sorry about that. The main issue here is that the code simply doesn't work and I've tried different ways to make a cmd-based program take a .sql file and export the data as a MS Excel file - as end result. Out of all the attempts I've made; this is the closest I've gotten but I'm unable to run the program due to a small number of errors I'm not familiar with. If possible I would appreciate it if you took a look at the code though a program like Visual Studio.

Lost User

"Unable to run"? There are so many things wrong with this code, I don't think it even compiles. The best part is where you create a flat file (with a name ending in ".xls") and somehow think this is "Excel"? You're doomed.

Lost User

Start over by first studying and running this example: Using MySQL With C#[^] Oh ... And why are you talking "SQL Server" even though you are using a MySQL ODBC driver?

Subramanyam Shankar

Have you tried the writing to excel using the interop and you are connecting using MySQL odbc. is your db really sql server?

OldZero

Yes, it is a server that is holding SQL Datatables on a cloudserver that can be connected using ODBC. I'm able to connect to this server (that I have done before using cmd-based program to simply see particular tables, which HAS worked) I'm just simply trying to figure out how to export the data to MS Excel, by making a CSV file.

Pete OHanlon

What are these small number of errors? Are they compilation errors? Are they runtime exceptions?

OldZero

There are about 6 errors in total; But 5 are name errors giving the message of "ddlExportFormat, strFormat, ds and connection does not exist in current content". I felt like within the code I posted above they where accurately addressed towards their methods. and the Last error address a System.IO file saying

'System.IO.File; does not contain a definition for Close"