Skip to content

Instantly share code, notes, and snippets.

@cbilson

cbilson/Insert.cs

Created December 7, 2009 18:19
Show Gist options
  • Save cbilson/250986 to your computer and use it in GitHub Desktop.
Save cbilson/250986 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Insert.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace DbSlim
{
public class Insert
{
private string connectionString;
private string tableName;
public Insert(string connectionString, string tableName)
{
this.connectionString = connectionString;
this.tableName = tableName;
}
public List<object> DoTable(List<List<object>> fitTable)
{
if (fitTable.Count < 2)
throw new ApplicationException("There needs to be at least one header row and a data row.");
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
var table = GetTableSchema(fitTable, connection);
LoadDataTableFromSlimTable(fitTable, table);
BulkLoadTableIntoDatabase(connection, table);
}
return null;
}
private DataTable GetTableSchema(List<List<object>> fitTable, SqlConnection connection)
{
using (var selectCommand = new SqlCommand("select * from " + tableName, connection))
{
var adapter = new SqlDataAdapter(selectCommand);
var table = new DataTable();
adapter.FillSchema(table, SchemaType.Source);
VerifyTableColumnsMatch(fitTable, table);
return table;
}
}
private void VerifyTableColumnsMatch(List<List<object>> slimTable, DataTable table)
{
for (int i = 0; i < slimTable[0].Count; i++)
{
var columnName = slimTable[0][i].ToString();
if (!table.Columns.Contains(columnName))
{
string message = string.Format("Table {0} does not contain a column {1}",
tableName, columnName);
throw new ApplicationException(message);
}
}
}
private static void LoadDataTableFromSlimTable(List<List<object>> slimTable, DataTable table)
{
for (var i = 1; i < slimTable.Count; i++)
{
var row = table.NewRow();
for (int j = 0; j < slimTable[0].Count; j++)
{
var columnName = slimTable[0][j].ToString();
Console.WriteLine("columnName {0}", columnName);
var value = slimTable[i][j];
var type = table.Columns[columnName].DataType;
row[columnName] = Convert.ChangeType(value, type);
}
}
}
private void BulkLoadTableIntoDatabase(SqlConnection connection, DataTable table)
{
using (var bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = tableName;
bulkCopy.WriteToServer(table);
}
}
}
}
Raw
Wiki.txt
!define TEST_SYSTEM {slim}
!define COMMAND_PATTERN {%m -r fitSharp.Slim.Service.Runner,fitsharp.dll %p}
!define TEST_RUNNER {Runner.exe}
!path C:\Users\CBilson\Documents\Projects\experimental\DbSlim\DbSlim\bin\Debug\DbSlim.dll
!|Import|
|DbSlim|
!|Table:Insert|Server=.\SQLEXPRESS;Database=Holding;Integrated Security=SSPI|position|
|effective_date|entity_id|src_intfc_inst|position_id|update_date|total_market_value|
|2009-12-06|6QAX-10L|1|99|2009-12-06|1000000|
!|Table:Insert|Server=.\SQLEXPRESS;Database=Holding;Integrated Security=SSPI|position_detail|
|position_detail_id|position_id|base_currency|local_currency|market_value|share_par_value|update_date|local_market_value|price|long_short_ind|security_alias|
|1|1|USD|USD|100|10|4/1/2009|100|10|L|1|
|1|1|USD|USD|100|10|4/2/2009|100|10|L|2|
|1|1|USD|USD|100|10|4/3/2009|100|10|L|3|
|1|1|USD|USD|100|10|4/4/2009|100|10|L|4|
|1|1|USD|USD|100|10|4/5/2009|100|10|L|5|
|1|1|USD|USD|100|10|4/6/2009|100|10|L|6|
|1|1|USD|USD|100|10|4/7/2009|100|10|L|7|
|1|1|USD|USD|100|10|4/8/2009|100|10|L|8|
|1|1|USD|USD|100|10|4/9/2009|100|10|L|9|
|1|1|USD|USD|100|10|4/10/2009|100|10|L|10|
|1|1|USD|USD|100|10|4/11/2009|100|10|L|11|
|1|1|USD|USD|100|10|4/12/2009|100|10|L|12|
|1|1|USD|USD|100|10|4/13/2009|100|10|L|13|
|1|1|USD|USD|100|10|4/14/2009|100|10|L|14|
|1|1|USD|USD|100|10|4/15/2009|100|10|L|15|
|1|1|USD|USD|100|10|4/16/2009|100|10|L|16|
|1|1|USD|USD|100|10|4/17/2009|100|10|L|17|
|1|1|USD|USD|100|10|4/18/2009|100|10|L|18|
|1|1|USD|USD|100|10|4/19/2009|100|10|L|19|
|1|1|USD|USD|100|10|4/20/2009|100|10|L|20|
|1|1|USD|USD|100|10|4/21/2009|100|10|L|21|
|1|1|USD|USD|100|10|4/22/2009|100|10|L|22|
|1|1|USD|USD|100|10|4/23/2009|100|10|L|23|
|1|1|USD|USD|100|10|4/24/2009|100|10|L|24|
|1|1|USD|USD|100|10|4/25/2009|100|10|L|25|
|1|1|USD|USD|100|10|4/26/2009|100|10|L|26|
|1|1|USD|USD|100|10|4/27/2009|100|10|L|27|
|1|1|USD|USD|100|10|4/28/2009|100|10|L|28|
|1|1|USD|USD|100|10|4/29/2009|100|10|L|29|
|1|1|USD|USD|100|10|4/30/2009|100|10|L|30|
|1|1|USD|USD|100|10|5/1/2009|100|10|L|31|
|1|1|USD|USD|100|10|5/2/2009|100|10|L|32|
|1|1|USD|USD|100|10|5/3/2009|100|10|L|33|
|1|1|USD|USD|100|10|5/4/2009|100|10|L|34|
|1|1|USD|USD|100|10|5/5/2009|100|10|L|35|
|1|1|USD|USD|100|10|5/6/2009|100|10|L|36|
|1|1|USD|USD|100|10|5/7/2009|100|10|L|37|
|1|1|USD|USD|100|10|5/8/2009|100|10|L|38|
|1|1|USD|USD|100|10|5/9/2009|100|10|L|39|
|1|1|USD|USD|100|10|5/10/2009|100|10|L|40|
|1|1|USD|USD|100|10|5/11/2009|100|10|L|41|
|1|1|USD|USD|100|10|5/12/2009|100|10|L|42|
|1|1|USD|USD|100|10|5/13/2009|100|10|L|43|
|1|1|USD|USD|100|10|5/14/2009|100|10|L|44|
|1|1|USD|USD|100|10|5/15/2009|100|10|L|45|
|1|1|USD|USD|100|10|5/16/2009|100|10|L|46|
|1|1|USD|USD|100|10|5/17/2009|100|10|L|47|
|1|1|USD|USD|100|10|5/18/2009|100|10|L|48|
|1|1|USD|USD|100|10|5/19/2009|100|10|L|49|
|1|1|USD|USD|100|10|5/20/2009|100|10|L|50|
|1|1|USD|USD|100|10|5/21/2009|100|10|L|51|
|1|1|USD|USD|100|10|5/22/2009|100|10|L|52|
|1|1|USD|USD|100|10|5/23/2009|100|10|L|53|
|1|1|USD|USD|100|10|5/24/2009|100|10|L|54|
|1|1|USD|USD|100|10|5/25/2009|100|10|L|55|
|1|1|USD|USD|100|10|5/26/2009|100|10|L|56|
|1|1|USD|USD|100|10|5/27/2009|100|10|L|57|
|1|1|USD|USD|100|10|5/28/2009|100|10|L|58|
|1|1|USD|USD|100|10|5/29/2009|100|10|L|59|
|1|1|USD|USD|100|10|5/30/2009|100|10|L|60|
|1|1|USD|USD|100|10|5/31/2009|100|10|L|61|
|1|1|USD|USD|100|10|6/1/2009|100|10|L|62|
|1|1|USD|USD|100|10|6/2/2009|100|10|L|63|
|1|1|USD|USD|100|10|6/3/2009|100|10|L|64|
|1|1|USD|USD|100|10|6/4/2009|100|10|L|65|
|1|1|USD|USD|100|10|6/5/2009|100|10|L|66|
|1|1|USD|USD|100|10|6/6/2009|100|10|L|67|
|1|1|USD|USD|100|10|6/7/2009|100|10|L|68|
|1|1|USD|USD|100|10|6/8/2009|100|10|L|69|
|1|1|USD|USD|100|10|6/9/2009|100|10|L|70|
|1|1|USD|USD|100|10|6/10/2009|100|10|L|71|
|1|1|USD|USD|100|10|6/11/2009|100|10|L|72|
|1|1|USD|USD|100|10|6/12/2009|100|10|L|73|
|1|1|USD|USD|100|10|6/13/2009|100|10|L|74|
|1|1|USD|USD|100|10|6/14/2009|100|10|L|75|
|1|1|USD|USD|100|10|6/15/2009|100|10|L|76|
|1|1|USD|USD|100|10|6/16/2009|100|10|L|77|
|1|1|USD|USD|100|10|6/17/2009|100|10|L|78|
|1|1|USD|USD|100|10|6/18/2009|100|10|L|79|
|1|1|USD|USD|100|10|6/19/2009|100|10|L|80|
|1|1|USD|USD|100|10|6/20/2009|100|10|L|81|
|1|1|USD|USD|100|10|6/21/2009|100|10|L|82|
|1|1|USD|USD|100|10|6/22/2009|100|10|L|83|
|1|1|USD|USD|100|10|6/23/2009|100|10|L|84|
|1|1|USD|USD|100|10|6/24/2009|100|10|L|85|
|1|1|USD|USD|100|10|6/25/2009|100|10|L|86|
|1|1|USD|USD|100|10|6/26/2009|100|10|L|87|
|1|1|USD|USD|100|10|6/27/2009|100|10|L|88|
|1|1|USD|USD|100|10|6/28/2009|100|10|L|89|
|1|1|USD|USD|100|10|6/29/2009|100|10|L|90|
|1|1|USD|USD|100|10|6/30/2009|100|10|L|91|
|1|1|USD|USD|100|10|7/1/2009|100|10|L|92|
|1|1|USD|USD|100|10|7/2/2009|100|10|L|93|
|1|1|USD|USD|100|10|7/3/2009|100|10|L|94|
|1|1|USD|USD|100|10|7/4/2009|100|10|L|95|
|1|1|USD|USD|100|10|7/5/2009|100|10|L|96|
|1|1|USD|USD|100|10|7/6/2009|100|10|L|97|
|1|1|USD|USD|100|10|7/7/2009|100|10|L|98|
|1|1|USD|USD|100|10|7/8/2009|100|10|L|99|
|1|1|USD|USD|100|10|7/9/2009|100|10|L|100|
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment