ITECH-PLANET ITECH-PLANET - 3 months ago 28
C# Question

I want to copy data in a CSV file to SQL SERVER using C#

I am trying out something new is standalone application development using C#. I want to copy data in a CSV file to an SQL Server database, but it seems to something else.I have been on this problem for a whole day.Please somebody should help me out.

StreamReader sr = new StreamReader(@filePath);
string line = sr.ReadLine();
string[] value = line.Split(',');
DataTable dt = new DataTable();
DataRow row;

foreach (string dc in value)
{
dt.Columns.Add(new DataColumn(dc));
}

while (!sr.EndOfStream)
{
value = sr.ReadLine().Split(',');
if (value.Length == dt.Columns.Count)
{
row = dt.NewRow();
row.ItemArray = value;
dt.Rows.Add(row);
}
}

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
{
conn.Open();
bulkCopy.DestinationTableName = "receipts";
foreach (var column in dt.Columns)
{
bulkCopy.ColumnMappings.Add(column.ToString(), column.ToString());
//MessageBox.Show(column.ToString());
}
bulkCopy.WriteToServer(dt);
bulkCopy.Close();
conn.Close();
}


Please what is the problem with my code?

Answer

I finally resolved the problem, everything is working perfect! Sorry to post the answer late. What I did was to use for loop to read data from the datatable and insert it into the database.

Here is the code;

            StreamReader sr = new StreamReader(@filePath);
            string line = sr.ReadLine();
            string[] value = line.Split(',');
            DataTable dt = new DataTable();
            DataRow row;

            foreach (string dc in value)
            {
                dt.Columns.Add(new DataColumn(dc));
            }

            while (!sr.EndOfStream)
            {
                value = sr.ReadLine().Split(',');
                if (value.Length == dt.Columns.Count)
                {
                    row = dt.NewRow();
                    row.ItemArray = value;
                    dt.Rows.Add(row);
                }
            }

            dgv.DataSource = dt;

            conn.Open();
            for (int i = 0; i < dt.Rows.Count; i++)
            {

                SqlCommand command = new SqlCommand("INSERT INTO [receipts] ([fullname], [state], [regno], [dept], [year], [session], [amount], [date], [faculty], [bank], [jamb]) VALUES('" + dt.Rows[i][1].ToString() + "', '" + dt.Rows[i][2].ToString() + "', '" + dt.Rows[i][3].ToString() + "', '" + dt.Rows[i][5].ToString() + "', '" + dt.Rows[i][6].ToString() + "', '" + dt.Rows[i][7].ToString() + "', '" + dt.Rows[i][8].ToString() + "', '" + dt.Rows[i][9].ToString() + "', '" + dt.Rows[i][10].ToString() + "', '" + dt.Rows[i][11].ToString() + "', '" + dt.Rows[i][4].ToString() + "')", conn);
                command.ExecuteNonQuery();

            }
            conn.Close();
Comments