C# - How to Export All tables of a database to Excel Files with Date-time in C#

Scenario : Download Script

You are working as C# developer, you need to write a program that should export all the tables to Excel files. Each Table should be export to separate Excel file and Excel file should have date-time added to it.

Below script can be use to export all the tables from SQL Server Database to Excel files.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;


namespace TechBrothersIT.com_CSharp_Tutorial
{
    class Program
    {
        static void Main(string[] args)
        {

            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            string LogFolder = @"C:\Log\";
            try
            {

                string FolderPath = @"C:\Destination\"; //Provide Destination folder path 
                //Create Connection to SQL Server
                SqlConnection SQLConnection = new SqlConnection();
                SQLConnection.ConnectionString = "Data Source = (local); Initial Catalog =TechBrothersIT; "
                   + "Integrated Security=true;";


                //Read list of Tables with Schema from Database to Export to Excel Files
                string query = "SELECT Schema_name(schema_id) AS SchemaName,name AS TableName"; 
                        query+=" FROM   sys.tables WHERE  is_ms_shipped = 0";

                //Get Table Names and Schema Names for Export to Excel files
                SqlCommand cmd = new SqlCommand(query, SQLConnection);
                SQLConnection.Open();
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                SQLConnection.Close();

                //Loop through datatable(dt) that has schema and table names
              foreach (DataRow dt_row in dt.Rows)
                {
                    string SchemaName = "";
                    string TableName = "";
                    object[] array = dt_row.ItemArray;
                    SchemaName = array[0].ToString();
                    TableName = array[1].ToString();


                    string ExcelFileName = "";
                    ExcelFileName = SchemaName + "_" + TableName + "_" + datetime;
                    OleDbConnection Excel_OLE_Con = new OleDbConnection();
                    OleDbCommand Excel_OLE_Cmd = new OleDbCommand();

                    //Construct ConnectionString for Excel
                    string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source="
                        + FolderPath + ExcelFileName
                        + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";

                    //Load Data into DataTable from SQL ServerTable
                    string queryString =
                      "SELECT * from " + SchemaName + "." + TableName;
                    SqlDataAdapter adapter = new SqlDataAdapter(queryString, SQLConnection);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);

                    //Get Header Columns
                    string TableColumns = "";

                    // Get the Column List from Data Table so can create Excel Sheet with Header
                    foreach (DataTable table in ds.Tables)
                    {
                        foreach (DataColumn column in table.Columns)
                        {
                            TableColumns += column + "],[";
                        }
                    }

                    // Replace most right comma from Columnlist
                    TableColumns = ("[" + TableColumns.Replace(",", " Text,").TrimEnd(','));
                    TableColumns = TableColumns.Remove(TableColumns.Length - 2);
                    //MessageBox.Show(TableColumns);


                    //Use OLE DB Connection and Create Excel Sheet
                    Excel_OLE_Con.ConnectionString = connstring;
                    Excel_OLE_Con.Open();
                    Excel_OLE_Cmd.Connection = Excel_OLE_Con;
                    Excel_OLE_Cmd.CommandText = "Create table [" + SchemaName + "_" +
                                                TableName + "] (" + TableColumns + ")";
                    Excel_OLE_Cmd.ExecuteNonQuery();


                    //Write Data to Excel Sheet from DataTable dynamically
                    foreach (DataTable table in ds.Tables)
                    {
                        String sqlCommandInsert = "";
                        String sqlCommandValue = "";
                        foreach (DataColumn dataColumn in table.Columns)
                        {
                            sqlCommandValue += dataColumn + "],[";
                        }

                        sqlCommandValue = "[" + sqlCommandValue.TrimEnd(',');
                        sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2);
                        sqlCommandInsert = "INSERT into [" + SchemaName + "_" + TableName 
                                            + "] (" + sqlCommandValue + ") VALUES(";

                        int columnCount = table.Columns.Count;
                        foreach (DataRow row in table.Rows)
                        {
                            string columnvalues = "";
                            for (int i = 0; i < columnCount; i++)
                            {
                                int index = table.Rows.IndexOf(row);
                                columnvalues += "'" + table.Rows[index].ItemArray[i] + "',";

                            }
                            columnvalues = columnvalues.TrimEnd(',');
                            var command = sqlCommandInsert + columnvalues + ")";
                            Excel_OLE_Cmd.CommandText = command;
                            Excel_OLE_Cmd.ExecuteNonQuery();
                        }

                    }
                    Excel_OLE_Con.Close();
                }
              
            }

            catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(LogFolder
                    + "\\" + "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());

                }

            }

        }
    }
}

I executed above program and it was able to generate Excel files for all the SQL Server tables and load data to them.

How to Export all the SQL Server Tables to Excel Files Dynamically in C#