Fetch Data from DB to CSV

 

Sometimes we will face scenarios to fetch data from database and need to store in csv , in this blog we are discussing on one of those scenarios .

We only need to write the SQL  query and the code is capable to fetch the column name and that will be the  header name in the CSV and the respective data inside the column will be added as different rows .

If the data is dynamic in the DB every time when you run the code the data in the csv will be deleted and newly added data will be replaced in the file.

C# Sample :  

       
        public void ExportDataToCSV()

        {

            string fileName = "DBTestData.csv";

            string fullFilepath = Path.Combine(Environment.CurrentDirectory, @"TestData\", fileName);

            string otherFileLocation = @"..\..\\..\\TestData\\" + fileName;

            string connectionString = "server = servername; uid = userid; pwd = password; database = dbname";

            string csvHeader = string.Empty;

            string csvData = string.Empty;


            using (SqlConnection con = new SqlConnection(connectionString))

            {

                using (SqlCommand cmd = new SqlCommand("SQL QUERY;"))

                {

                    using (SqlDataAdapter sda = new SqlDataAdapter())

                    {

                        cmd.Connection = con;

                        sda.SelectCommand = cmd;

 

                        using (DataTable dt = new DataTable())

                        {

                            sda.Fill(dt);

 
                            foreach (DataColumn column in dt.Columns)

                            {

                                //Add the Header row for CSV file.

                                csvHeader += column.ColumnName + ',';

                            }

                            //Add new line.

                            csvHeader += "\r\n";

 
                            File.WriteAllText(fullFilepath, csvHeader);

                            File.WriteAllText(otherFileLocation, csvHeader);

                            foreach (DataRow row in dt.Rows)

                            {

                                foreach (DataColumn column in dt.Columns)

                                {

                                    //Add the Data rows.

                                    csvData += row[column.ColumnName].ToString().Replace(",", ";") + ',';

                               }


                                //Add new line.

                                csvData += "\r\n";

                            }


                            //Setting the file Paths

                            File.AppendAllText(fullFilepath, csvData);

                            File.AppendAllText(otherFileLocation, csvData);

 
                        }

                    }

                }

            }