public static void InsertProjectData(TestContext testContext) { RemoveAllData(); List<string> ExcelFiles = GetExcelFiles(); foreach (string excelFile in ExcelFiles) { Console.WriteLine(String.Format("{0} exists: {1}", excelFile, File.Exists(excelFile))); DataTable dt = new DataTable(); cExcel _cExcel = new cExcel(); string strSheetName = "Sheet1"; dt = _cExcel.ReadExcelFile(strSheetName, excelFile); string connectionString = getConnectionString(); Console.WriteLine(String.Format("Connection String: {0}", connectionString)); using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using(SqlCommand command = new SqlCommand()) { command.Connection = connection; setIdentityInsert(command, dt.Rows[0]["Table"].ToString(), "ON"); foreach (DataRow row in dt.Rows) // Loop over the rows. { string query = row["Query"].ToString(); int numRowsAffected = 0; if (query.Length > 1) { Console.WriteLine(query); command.CommandText = query; numRowsAffected = command.ExecuteNonQuery(); if (numRowsAffected != 1){ Console.WriteLine(String.Format("Query affected {0} rows instead of the expected 1 row.", numRowsAffected)); } } } setIdentityInsert(command, dt.Rows[0]["Table"].ToString(), "OFF"); } } } }
public static void RemoveAllData() { string connectionString = getConnectionString(); using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlCommand command = new SqlCommand()) { command.Connection = connection; foreach (string excelFile in GetExcelFiles()) { Console.WriteLine(String.Format("{0} exists: {1}", excelFile, File.Exists(excelFile))); DataTable dt = new DataTable(); cExcel _cExcel = new cExcel(); string strSheetName = "Sheet1"; dt = _cExcel.ReadExcelFile(strSheetName, excelFile); string table = dt.Rows[0]["Table"].ToString(); string query = string.Format("DELETE FROM {0}", table); Console.WriteLine(query); command.CommandText = query; int numRowsAffected = command.ExecuteNonQuery(); Console.WriteLine(String.Format("\t{0} rows affected", numRowsAffected)); } } } }