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; }