Untitled

mail@pastecode.io avatar
unknown
plain_text
a month ago
3.4 kB
1
Indexable
Never
using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace QueryExecutorApp
{
    public partial class Form1 : Form
    {
        // Connection string for your database
        private string connectionString = "Your_Connection_String_Here";

        public Form1()
        {
            InitializeComponent();
            LoadQueryNames();
        }

        private void LoadQueryNames()
        {
            // Populate dropdown with query names
            comboBoxQueries.Items.Add("Query 1");
            comboBoxQueries.Items.Add("Query 2");
            // Add more query names as needed
        }

        private void btnExecute_Click(object sender, EventArgs e)
        {
            string selectedQuery = comboBoxQueries.SelectedItem.ToString();

            // Execute query
            DataTable dataTable = ExecuteQuery(selectedQuery);

            if (dataTable != null)
            {
                // Export data to Excel
                ExportToExcel(dataTable);
                MessageBox.Show("Data exported to Excel successfully!");
            }
            else
            {
                MessageBox.Show("Failed to execute the query or no data returned.");
            }
        }

        private DataTable ExecuteQuery(string queryName)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand(queryName, connection);
                    command.CommandType = CommandType.Text;
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error executing the query: " + ex.Message);
                return null;
            }
        }

        private void ExportToExcel(DataTable dataTable)
        {
            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook workbook = excelApp.Workbooks.Add();
            Excel.Worksheet worksheet = workbook.ActiveSheet;

            // Add column headers
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = dataTable.Columns[i].ColumnName;
            }

            // Add data rows
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    worksheet.Cells[i + 2, j + 1] = dataTable.Rows[i][j].ToString();
                }
            }

            // Save the workbook
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
            saveFileDialog.FilterIndex = 1;
            saveFileDialog.RestoreDirectory = true;

            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                workbook.SaveAs(saveFileDialog.FileName);
            }

            excelApp.Quit();
        }
    }
}
Leave a Comment