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