October 17, 2013 at 12:11 am
Following asp.net code fetches data from CSV file and inserts it into SQL server 2005 table.
However, i need to specify Table name, Field names whose data will be fetched, plus mapping info(Mapping between 'column names of CSV' and 'column names of table') statically in code.
How can i modify below code so that it works for any Table name.
In short, Table name and Mapping should be handled dynamically.
{
protected const string _truncateLiveTableCommandText = @"TRUNCATE TABLE Account";
protected const int _batchSize = 100000;
static void Main(string[] args)
{
LoadCsvDataIntoSqlServer();
}
public static void LoadCsvDataIntoSqlServer()
{
// This should be the full path
var fileName = @"D:\output.csv";
var createdCount = 0;
using (var textFieldParser = new TextFieldParser(fileName))
{
textFieldParser.TextFieldType = FieldType.Delimited;
textFieldParser.Delimiters = new[] { "," };
textFieldParser.HasFieldsEnclosedInQuotes = true;
// var connectionString = ConfigurationManager.ConnectionStrings["CMSConnectionString"].ConnectionString;
string connectionString = "Data Source= 172.25.10.4" + ";Initial Catalog= SFdata" + ";Persist Security Info=True;User ID= sa" + ";Password= Newuser@123";
var dataTable = new DataTable("Account");
// Add the columns in the temp table
dataTable.Columns.Add("Name");
dataTable.Columns.Add("shippingstreet");
dataTable.Columns.Add("shippingpostalcode");
dataTable.Columns.Add("ShippingCountry");
dataTable.Columns.Add("shippingstate");
//dataTable.Columns.Add("LastName");
using (var sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
// Truncate the live table
using (var sqlCommand = new SqlCommand(_truncateLiveTableCommandText, sqlConnection))
{
sqlCommand.ExecuteNonQuery();
}
// Create the bulk copy object
var sqlBulkCopy = new SqlBulkCopy(sqlConnection)
{
DestinationTableName = "Account"
};
// Setup the column mappings, anything ommitted is skipped
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("shippingstreet", "shippingstreet");
sqlBulkCopy.ColumnMappings.Add("shippingpostalcode", "shippingpostalcode");
sqlBulkCopy.ColumnMappings.Add("ShippingCountry", "ShippingCountry");
sqlBulkCopy.ColumnMappings.Add("shippingstate", "shippingstate");
// Loop through the CSV and load each set of 100,000 records into a DataTable
// Then send it to the LiveTable
while (!textFieldParser.EndOfData)
{
dataTable.Rows.Add(textFieldParser.ReadFields());
createdCount++;
if (createdCount % _batchSize == 0)
{
InsertDataTable(sqlBulkCopy, sqlConnection, dataTable);
break;
}
}
InsertDataTable(sqlBulkCopy, sqlConnection, dataTable);
sqlConnection.Close();
}
}
}
protected static void InsertDataTable(SqlBulkCopy sqlBulkCopy, SqlConnection sqlConnection, DataTable dataTable)
{
sqlBulkCopy.WriteToServer(dataTable);
dataTable.Rows.Clear();
}
}
October 17, 2013 at 1:34 am
You can read out the metadata of the table from INFORMATION_SCHEMA.COLUMNS.
The columns in the flat file have to have the same names though.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply