public void ParseStateFederalRelationalXLS(string a_filename)
{
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + a_filename + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
OleDbConnection xlsConnection = new OleDbConnection(connectionString);
xlsConnection.Open();
//OleDbCommand commandtest = new OleDbCommand("SELECT * FROM [2004 Election$]", connection);
OleDbDataAdapter _allData = new OleDbDataAdapter("SELECT * FROM [Seat Mappings$]", xlsConnection);
DataSet ds = new DataSet();
_allData.Fill(ds);
var table = ds.Tables[0];
int i = 1;
OleDbConnection _connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data/db.mdb"); //!testing
_connection.Open();
OleDbCommand deleteCommand = new OleDbCommand("DELETE StateElectorate.* FROM StateElectorate;", _connection);
deleteCommand.ExecuteNonQuery();
_connection.Close();
foreach (DataRow row in table.Rows)
{
try
{
_connection.Open();
string values = "'" + row.ItemArray[1] + "', '" + row.ItemArray[0] + "'";
string command = "INSERT INTO [StateElectorate] (ElectorateName, FederalElectorate) VALUES (" + values + ");";
OleDbCommand insertCommand = new OleDbCommand(command, _connection);
insertCommand.ExecuteNonQuery();
}
finally
{
_connection.Close();
}
i++;
}
}