Select Multiple CSV and bulk to Database

Question

I'm using an MDF database with the OpenFileDialog class to import a single CSV file to a database. That works fine for a single CSV file, but I need to open and process multiple CSV files in bulk.

How can I improve my code? I've tried using a for loop.

Here is my code to process a single CSV file:

ofd.Filter = "CSV files (*.csv) | *.csv; | CSV PRN files (*.prn,) |*.prn;";
ofd.FileName = "";
ofd.ShowDialog();
DataTable dt = new DataTable();
string line = null;
int i = 0;
using (StreamReader sr = File.OpenText(ofd.FileName))
{
    while ((line = sr.ReadLine()) != null)
    {
        string[] data = line.Split(',');
        if (data.Length > 0)
        {
            if (i == 0)
            {
                foreach (var item in data)
                {
                    dt.Columns.Add(new DataColumn());
                }

                i++;
            }

            DataRow row = dt.NewRow();
            row.ItemArray = data;
            dt.Rows.Add(row);
        }

    }
}

string symbolName = dt.Rows[1][0].ToString();
string strConnection =
@"Data Source =.\SQLEXPRESS; AttachDbFilename = C:\USERS\JEF\DOCUMENTS\DATABASE1.MDF; Integrated Security = True; Connect Timeout = 30; User Instance = True";
SqlConnection condb2 = new SqlConnection(strConnection);
string createTablerow ="create table ["+symbolName+"] (code1 VARCHAR(100) COLLATE Arabic_CI_AI_KS_WS,date1 varchar(50),open1 varchar(50),high1 varchar(50),low1 varchar(50),close1 varchar(50),vol1 varchar(50))";
using (SqlConnection connection = new SqlConnection(strConnection))
{
    SqlCommand command1 = new SqlCommand(createTablerow, connection);
    connection.Open();
    command1.ExecuteNonQuery();

}
using (SqlConnection cn = new SqlConnection(strConnection))
{
    cn.Open();
    using (SqlBulkCopy copy = new SqlBulkCopy(cn))
    {
        copy.ColumnMappings.Add(0, "code1");
        copy.ColumnMappings.Add(1, "date1");
        copy.ColumnMappings.Add(2, "open1");
        copy.ColumnMappings.Add(3, "high1");
        copy.ColumnMappings.Add(4, "low1");
        copy.ColumnMappings.Add(5, "close1");
        copy.ColumnMappings.Add(6, "vol1");
        copy.DestinationTableName = "[" + symbolName + "]";
        copy.WriteToServer(dt);
    }
}

Answer 1

I moved some of you code around to make it more efficient :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            ofd.FileName = "";
            ofd.ShowDialog();
            string line = null;
            int i = 0;

            string strConnection =
            @"Data Source =.\SQLEXPRESS; AttachDbFilename = C:\USERS\JEF\DOCUMENTS\DATABASE1.MDF; Integrated Security = True; Connect Timeout = 30; User Instance = True";

            using (SqlConnection connection = new SqlConnection(strConnection))
            {
                connection.Open();
                SqlBulkCopy copy = new SqlBulkCopy(connection);

                copy.ColumnMappings.Add(0, "code1");
                copy.ColumnMappings.Add(1, "date1");
                copy.ColumnMappings.Add(2, "open1");
                copy.ColumnMappings.Add(3, "high1");
                copy.ColumnMappings.Add(4, "low1");
                copy.ColumnMappings.Add(5, "close1");
                copy.ColumnMappings.Add(6, "vol1");


                foreach (string file in ofd.FileNames)
                {
                    using (StreamReader sr = File.OpenText(file))
                    {
                        DataTable dt = new DataTable();

                        while ((line = sr.ReadLine()) != null)
                        {
                            string[] data = line.Split(',');
                            if (data.Length > 0)
                            {
                                if (i == 0)
                                {
                                    foreach (var item in data)
                                    {
                                        dt.Columns.Add(new DataColumn());
                                    }

                                    i++;
                                }

                                DataRow row = dt.NewRow();
                                row.ItemArray = data;
                                dt.Rows.Add(row);
                            }
                        }

                        string symbolName = dt.Rows[1][0].ToString();

                        string createTable = string.Format("create table [{0}] (code1 VARCHAR(100) COLLATE Arabic_CI_AI_KS_WS,date1 varchar(50),open1 varchar(50),high1 varchar(50),low1 varchar(50),close1 varchar(50),vol1 varchar(50))",
                            symbolName);

                        using (SqlCommand command1 = new SqlCommand(createTable, connection))
                        {
                            command1.ExecuteNonQuery();

                            copy.DestinationTableName = "[" + symbolName + "]";
                            copy.WriteToServer(dt);

                        }
                    }
                }
            }
        }
    }
}