• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2849
  • Last Modified:

C# writing stored procedure results to Excel file

Hi.

I have a stored procedure that executes the query attached (Sample Query).  The result of the query is similar to this:

Name      Acc_No    Pol_No     Date_Sold       Product
David      121822      P001      2012-06-12      Prod001
Tim              121822      P054      2012-04-14       Prod009
Sarah      121822      P066      2012-06-26      Prod001

What we are looking for is people with different names but the same account number, as in the example above.  I then need to note the earliest date sold (per account num), as that will be the correct account number and the rest will have to be corrected.

I need to put the results in an Excel spreadsheet that looks like the attached (Sample format Excel).  I have the query correct, I just have no idea how to get the results printed to Excel in that format.
Sample-Format-Excel.xlsx
SampleQuery.txt
0
Jasmin01
Asked:
Jasmin01
1 Solution
 
Michael FowlerSolutions ConsultantCommented:
Instead of using C# to do this why just do the whole thing from excel. VBA is very similar to VB.Net and it will make doing what you are after mush easier.

Here is a link to get you started

http://www.excelguru.ca/content.php?135-Retrieve-Data-From-A-Database-To-Excel-Using-SQL

There are lots of excel experts on this site who can assist you further if needed.

Michael
0
 
Jasmin01Author Commented:
It has to be in C# because it will be added to a C# project once it has been tested.  Unfortunately, I cannot use anything else.
0
 
NorieData ProcessorCommented:
How are you running the procedure from C# and where are you storing the results?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Jasmin01Author Commented:
At the moment, I only have the sql query.  I need to take those results from the query and somehow export it to Excel.
0
 
NorieData ProcessorCommented:
You don't have any code to connect to the database and run the query?

What database is it?
0
 
Jasmin01Author Commented:
It's a SQL database.
0
 
NorieData ProcessorCommented:
Jasmin

Is that SQL Server?
0
 
Jasmin01Author Commented:
Its Sql Server 2008.
0
 
NorieData ProcessorCommented:
I've exported data from SQL Server to Excel using 2 different methods in the past - ADO and SQLClient.

Here's the code for ADO.
     ADODB.Connection conn = new ADODB.Connection();
            ADODB.Recordset rst = new ADODB.Recordset();
            try
            {

                conn.ConnectionString = "Driver={SQL Server};Server=<servername>;Database=<databasename>";

                conn.Open();

                string strSQL = "SELECT * FROM customers;";

                rst.Open(strSQL, conn);

                
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

                Workbook xlwb = xlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

                Worksheet xlsheet = xlwb.Worksheets.get_Item(1);

                xlApp.Visible = true;

                Range rng = xlsheet.get_Range("A1", "A1");

                foreach (ADODB.Field fldname in rst.Fields)
                {
                    rng.Value = fldname.Name;
                    rng = rng.get_Offset(0, 1);
                }

                rng = xlsheet.get_Range("A2", "A2");

                rng.CopyFromRecordset(rst);

                rst.Close();
                conn.Close();

                label1.Text = "ADO Export succesful";
            }
            catch (Exception exp)
            {
                label1.Text = exp.Message;

                conn.Close();
            }

Open in new window


Here's the SQL client code.
 SqlConnection myConnection = new SqlConnection("server=<servername>;" +
                                                            "Trusted_Connection=yes;" +
                                                             "database=<databasename>; " +
                                                            "connection timeout=30");

            SqlCommand comm = new SqlCommand("SELECT statename, capital FROM tblStates", myConnection);

            try
            {
                myConnection.Open();

                SqlDataReader rdr = comm.ExecuteReader();

                SqlDataAdapter radp = new SqlDataAdapter(comm);

                DataSet dt = new DataSet();

                radp.Fill(dt);

                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

                Workbook xlwb = xlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

                Worksheet xlsheet = xlwb.Worksheets.get_Item(1);

                xlApp.Visible = true;

                Range rng = xlsheet.get_Range("A1", "A1");

                foreach (DataColumn fldname in dt.Tables[0].Columns)
                {
                    rng.Value = fldname.ColumnName;
                    rng = rng.get_Offset(0, 1);
                }

                rng = xlsheet.get_Range("A2", "A2");

                foreach (DataRow rw in dt.Tables[0].Rows)
                {
                    foreach (DataColumn cl in dt.Tables[0].Columns)
                    {

                        rng.Value = rw[cl].ToString();
                        rng = rng.get_Offset(0, 1);

                    }

                    rng = rng.get_Offset(1, -rng.Column + 1);
                }


                dataGridView1.DataSource = dt.Tables[0];

                myConnection.Close();

            label1.Text = "Dataset export succesful";

            }
            catch (SqlException exp)
            {
                label1.Text = exp.Message;
                myConnection.Close();

            }

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now