Is there way to execute a SQL Statement and export the result set to Excel without using a stored procedure from a C# application on a windows platform?

I am writing a C# application under a windows platform using VS2005.
In the following routine, is there a way to revise this routine to execute a SQL statement instead of processing a STORED PROCEDURE? I ultimately export a result set to an Excel file and the records don't come out in the correct sequence to the Excel file even from a SQL Server table with an ORDER BY clause unless I generate the SQL Statement without having it stored into a table.

private void ReportToExcel(string reportyr, string cmdText, string ftnote, string flename)
        {            
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            string connetionString = null;
            SqlConnection connection;
            SqlDataAdapter adapter;
            SqlCommand command = new SqlCommand();
            SqlParameter param;            

            //int i = 0;
            connetionString = MyGlobals.connectLiteral;
            connection = new SqlConnection(connetionString);

            connection.Open();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
            //command.CommandText = "procFlACSExclAAA";
            command.CommandText = cmdText;

            if (reportyr != "bypass")
            {
                param = new SqlParameter("@RptYear", reportyr);
                param.Direction = ParameterDirection.Input;
                param.DbType = DbType.String;
                command.Parameters.Add(param);
            }
            else
            {
                reportyr = "";
            }

            DataSet ds = new DataSet();
            adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);

            string MyProjectDir = DirProject();
            string footnote = ftnote;

            DataTable myDataTable = ds.Tables[0];
            string ExportedFile = MyProjectDir + flename + "_" + reportyr + "_" + DateTime.Now.ToString("MMddhhmmss") + ".XLS";
            ExcelExport(myDataTable, ExportedFile);
            ExcelFormat(ExportedFile, footnote);
            Cursor.Current = Cursors.Default;
        }
zimmer9Asked:
Who is Participating?
 
käµfm³d 👽Connect With a Mentor Commented:
Change

command.CommandType = CommandType.StoredProcedure;

to

command.CommandType = CommandType.Text;

I assume cmdText holds the SQL query you would like to execute.
0
 
keyuCommented:
You can do it multiple way

1) using SSMS Tool - > here you can directly get export to excel option

2) using commandline BCP option.
0
All Courses

From novice to tech pro — start learning today.