Loading data from a file into a SQL Server table using C# classes SqlBulkCopy and StreamReader

by Granville Bonyata on December 28, 2012

By Nicholas Geiger

One of the ways that data can be loaded from a file into SQL Server is to create a .NET application that uses the C# classes SqlBulkCopy and StreamReader. Below is an example of how. This application gets multiple lines of data from the file Data.txt and uploads them to the SQL Server table PackageItem.

This example was tested using Visual Studio 2010 with the .NET 4.0 framework and SQL Server 2008. When the .NET application is ran, it should insert rows found in Data.txt into the PackageItem database table.

To set up the example on a PC, you can follow the below instructions.
1) Create the file Data.txt in the directory C:\Temp\.
2) Run the database script below in the SQL Server database.
3) Create an .NET application using the ASP.NET and C# code below. The only items needed in the Visual Studio project are Default.aspx, Default.aspx.cs, and web.config file (which is created automatically by Visual Studio).
4) Change the SQL Server connection string in the C# code so that it can be used to connect to your SQL Server database.

One of the nice aspects of this solution is that only a small number of changes are needed to make the C# code work for an Oracle database. Oracle has its own C# bulk copy class called OracleBulkCopy which is very similar to SqlBulkCopy.


Data.txt file contents are within double quotes:  "Raw Organic Whole Milk           5
Organic Rolled Oats              1"

-- Database script

create table PackageItem
(
    ItemDescription varchar(30) null,
    WeightInPounds int not null
);

<%--Default.aspx--%>

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    </div>
    </form>
</body>
</html>

// Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    private String _connectionString = "Data Source=SSD-PC\\SQLEXPRESSPRI;initial catalog=master;user id=ssd;password=6i8~rHvfgd";
    private Int32 _errorRow, _errorColumn;

    protected void Page_Load(object sender, EventArgs e)
    {
        // Set uploadTable.
        DataTable uploadTable = new DataTable();
        uploadTable.Columns.AddRange(new DataColumn[] {
            new DataColumn("ItemDescription", Type.GetType("System.String")),
            new DataColumn("WeightInPounds", Type.GetType("System.Int32"))
        });

        // Declare and instantiate streamReaderInstance.
        using (StreamReader streamReaderInstance
            = new StreamReader(@"C:\Temp\Data.txt"))
        {
            // Use streamReaderInstance to gather lines of data from the data file Data.txt.
            while (!streamReaderInstance.EndOfStream)
            {
                String line = streamReaderInstance.ReadLine(); // Get the next line.                    
                DataRow row = uploadTable.NewRow(); // Get a new row for the DataTable.

                // Set column Description with the value from the row.
                row["ItemDescription"] = line.Substring(0, 30).TrimEnd(' ');

                // Set column WeightInPounds with the value from the row.
                row["WeightInPounds"] = line.Substring(30, 4).TrimStart(' ');

                uploadTable.Rows.Add(row); // Add the row to uploadTable.
            }
        }

        // Declare and instantiate bulkCopy.
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(_connectionString))
        {
            bulkCopy.DestinationTableName = "PackageItem"; // Set DestinationTableName.

            // Map the columns from uploadTable to the database table PackageItem.
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("ItemDescription", "ItemDescription"));
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("WeightInPounds", "WeightInPounds"));

            // Perform copy from uploadTable to database table PackageItem.
            bulkCopy.WriteToServer(uploadTable);
        }
    }
}

Previous post:

Next post: