public static void PopulateInCollection(String FileName, String SheetName)
{
Datacollection.ClearData();
//DataTable table = null;
DataTable table = ExcelToDataTable(FileName, SheetName);
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
Datacollection dtTable = new Datacollection()
{
rowNumber = row,
colName = table.Columns[col].ColumnName,
colValue = table.Rows[row - 1][col].ToString()
};
dataCollection.Add(dtTable);
}
}
}
public void PopulateInCollection(string fileName)
{
System.Data.DataTable table = ExcelToDataTable(fileName);
//Iterate through the rows and columns of the Table
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 0; col <= table.Columns.Count; col++)
{
Datacollection dtTable = new Datacollection()
{
rowNumber = row,
colName = table.Columns[col].ColumnName,
colValue = table.Rows[row - 1][col].ToString()
};
//Add all the details for each row
dataCol.Add(dtTable);
}
}
}
public static void PopulateInCollection(string fileName, string sheetName)
{
ClearData();
var table = ExcelToDataTable(fileName, sheetName);
//Iterate through the rows and columns of the Table
for (var row = 1; row <= table.Rows.Count; row++)
{
for (var col = 0; col < table.Columns.Count; col++)
{
var dtTable = new Datacollection
{
RowNumber = row,
ColName = table.Columns[col].ColumnName,
ColValue = table.Rows[row - 1][col].ToString()
};
//Add all the details for each row
DataCol.Add(dtTable);
}
}
}
public static void PopulateInCollection(string filename)
{
DataTable table = ExcelToDataTable(filename);
totalRowCount = table.Rows.Count;
//table.Rows.Count=give us the maximum number of rows
//Datacollection dtTable = new Datacollection:store all the values(columnName nd value)
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
Datacollection dtTable = new Datacollection()
{
rowNumber = row,
colName = table.Columns[col].ColumnName,
colValue = table.Rows[row - 1][col].ToString()
};
dataCol.Add(dtTable);
}
}
}
public static void PopulateInCollection()
{
dataCol = new List <Datacollection>();
string filepath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);
DataTable table = ExcelToDataTable(filepath + "\\DataPools\\BCU_InternetBanking.xlsx");
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 0; col <= table.Columns.Count - 1; col++)
{
Datacollection dtTable = new Datacollection()
{
rowNumber = row,
colName = table.Columns[col].ColumnName,
colValue = table.Rows[row - 1][col].ToString()
};
//Add all the details for each row
dataCol.Add(dtTable);
}
}
}
public static List <Datacollection> PopulateInCollection(string fileName, string sheetName = "Sheet1")
{
DataTable table = ExcelToDataTable(fileName, sheetName);
List <Datacollection> dataCol = new List <Datacollection>();
//Iterate through the rows and columns of the Table
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
Datacollection dtTable = new Datacollection()
{
rowNumber = row,
colName = table.Columns[col].ColumnName,
colValue = table.Rows[row - 1][col].ToString()
};
//Add all the details for each row
dataCol.Add(dtTable);
}
}
return(dataCol);
}
public static async void PopulateInCollection(string fileName, string SheetName)
{
ExcelLib.ClearData();
DataTable table = await ExcelToDataTable(fileName, SheetName).ConfigureAwait(false);
//Iterate through the rows and columns of the Table
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
Datacollection dtTable = new Datacollection()
{
rowNumber = row,
colName = table.Columns[col].ColumnName,
colValue = table.Rows[row - 1][col].ToString()
};
//Add all the details for each row
dataCol.Add(dtTable);
}
}
}
public static void PopulateInCollection(string path, string sheet)
{
table = ExcelToDataTable(@path, sheet);
// Console.WriteLine("Datatable count is : " + table.Rows.Count);
//Iterate through the rows and columns of the Table
//for (int row = 1; row <= table.Rows.Count; row++)
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
Datacollection dtTable = new Datacollection()
{
rowNumber = row,
colName = table.Columns[col].ColumnName,
colValue = table.Rows[row - 1][col].ToString()
};
// Console.WriteLine("Datatable value is : " + table.Rows[row - 1][col].ToString());
//Add all the details for each row
// Console.WriteLine("Datatable is : " + dtTable);
dataCol.Add(dtTable);
}
}
}
public void Connect_To_Database()
{
try
{
string connectionstring = @"Data Source=servicebookingdbservtest.database.windows.net;Initial Catalog=ServiceBookingDb;User ID=ExperiecoAdmin;Password=9aJrtENEU6JV3h$";
using (sqlConn = new SqlConnection(connectionstring))
{
sqlConn.Open();
SqlCommand command;
SqlDataReader datareader;
//string sql = "select * from dbo.Customers where customerno in( 123123,300300,200200,1, 128228)";
string sql = "select * from Appointments;";
command = new SqlCommand(sql, sqlConn);
//command.ExecuteNonQuery();
//datareader = command.ExecuteNonQuery(); -- For Update/Delete/Insert
datareader = command.ExecuteReader(); //For Select
var columns = new List <string>();
int k = 1;
while (datareader.Read())
{
for (int j = 0; j < datareader.FieldCount; j++)
{
Datacollection dtTable = new Datacollection()
{
rowNumber = k,
colName = datareader.GetName(j),
colValue = datareader.GetValue(j).ToString()
};
dbdataCol.Add(dtTable);
}
k++;
}
Console.WriteLine(columns);
//DataTable table = new DataTable();
//table.Load(command.ExecuteReader());
//string WhereClause = "JobDate = '2018-11-26'";
//DataRow[] foundRows;
//foundRows = table.Select(WhereClause);
//for(int i = 0; i < foundRows.Length; i++)
//{
// Console.WriteLine(foundRows[i][0]);
//}
datareader.Close();
command.Dispose();
sqlConn.Close();
}
}
catch (Exception e)
{
//Log the exception in Error log
Console.WriteLine("Exception occurred in Connect_To_Database!" + Environment.NewLine + e.Message.ToString());
}
finally
{
if (sqlConn != null)
{
sqlConn.Close();
}
}
}
