public void CreateTable(Database db)
{
//Create datatable for bulk copy
DataTable dt = new DataTable();
dt.Columns.Add("pegged_id");
dt.Columns.Add("assembly_id");
string cs = CreateConnectionString(db);
//create table and get ids
using (SqlConnection con = new SqlConnection(cs))
{
try
{
SqlCommand cmd = new SqlCommand(createTableQuery, con);
con.Open();
cmd.ExecuteNonQuery();
cmd.CommandText = getIds;
cmd.ExecuteNonQuery();
SqlDataReader reader = cmd.ExecuteReader();
//add rows to data table for bulk insert
while (reader.Read())
{
dt.Rows.Add(reader.GetInt32(0), reader.GetInt32(1));
}
con.Close();
}
catch(Exception ex)
{
Console.WriteLine(ex);
}
}
//create table and insert data
using (SqlBulkCopy sbc = new SqlBulkCopy(cs))
{
try
{
sbc.DestinationTableName = "PeggedToAssembly";
sbc.BatchSize = dt.Rows.Count;
sbc.ColumnMappings.Add("pegged_id", "pegged_id");
sbc.ColumnMappings.Add("assembly_id", "assembly_id");
sbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(SqlBatchInsertStatus);
sbc.WriteToServer(dt);
sbc.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
}
public string CreateConnectionString(Database db)
{
return String.Format(connectionTemplate, db.Server, db.Catalog, db.UserId, db.Password);
}
public bool CheckIfTableExists(Database db)
{
string cs = CreateConnectionString(db);
bool response = false;
using(SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand(checkTableQuery, con);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
response = reader.GetBoolean(0);
}
con.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
return response;
}