/// <summary>
/// Method to fetch module info for given HRA Id
/// </summary>
private void FetchModuleInfo()
{
SQLConnect sql = new SQLConnect();
string query = "Select PageID , Header From Flywheel.dbo.FLY_Page Where HRAID = 89 Order by PageId asc";
sql.OpenConnection();
pageinfo = sql.Execute(query);
if (pageinfo.HasRows)
{
while (pageinfo.Read())
{
pages.Add(new string[] { pageinfo[0].ToString(), pageinfo[1].ToString() });
}
sql.CloseConnection();
//Console.WriteLine("================= Page Info ==================");
//for (int i = 0; i < pages.Count; i++)
//{
// Console.WriteLine(pages.ElementAt(i)[0] + " | " + pages.ElementAt(i)[1]);
//}
}
else
{
Console.WriteLine(this.GetType().Name + ": No module info found");
}
}
// Delete Customer
public void Delete(string firstName)
{
connect = new SQLConnect();
string query = "DELETE FROM CustomerDetails WHERE FirstName =" + "'" + firstName + "'";
SqlCommand cmd = new SqlCommand(query, connect.OpenConnection());
cmd.ExecuteNonQuery();
connect.CloseConnection();
}
// Create new Customer
public void Create(Customer customer)
{
// Check Email is valid
bool validateEmailAddress = ValidateEmail(customer.Email);
if (validateEmailAddress == false)
{
Console.WriteLine("Email address is invalid");
}
// Check if customer already exists
connect = new SQLConnect();
string customerExitsQuery = "SELECT FirstName FROM CustomerDetails WHERE FirstName =" + "'" + customer.FirstName + "'";
SqlDataAdapter da = new SqlDataAdapter(customerExitsQuery, connect.OpenConnection());
DataTable table = new DataTable();
da.Fill(table);
if (table.Rows.Count >= 1)
{
Console.WriteLine("Customer already exists");
}
// Add new customer to database
if (table.Rows.Count == 0 && validateEmailAddress == true)
{
string query = "INSERT INTO CustomerDetails " +
"(FirstName, MiddleName, LastName, PhoneNumber, Address, Email, CreationDate)" +
"VALUES (@FirstName, @MiddleName, @LastName, @PhoneNumber, @Address, @Email, @CreationDate)";
SqlCommand cmd = new SqlCommand(query, connect.OpenConnection());
cmd.Parameters.Add("@Firstname", SqlDbType.VarChar).Value = customer.FirstName;
cmd.Parameters.Add("@Middlename", SqlDbType.VarChar).Value = customer.MiddleName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = customer.LastName;
cmd.Parameters.Add("@PhoneNumber", SqlDbType.Int).Value = customer.PhoneNumber;
cmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = customer.Address;
cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = customer.Email;
cmd.Parameters.Add("@CreationDate", SqlDbType.NVarChar).Value = customer.DateCreated;
cmd.ExecuteNonQuery();
connect.CloseConnection();
}
}
// Update Customer Details
public void Edit(Customer customer)
{
connect = new SQLConnect();
string query = "UPDATE CustomerDetails SET " +
"FirstName =" + "'" + customer.FirstName + "'," +
"MiddleName =" + "'" + customer.MiddleName + "'," +
"LastName =" + "'" + customer.LastName + "'," +
"PhoneNumber =" + "'" + customer.PhoneNumber + "'," +
"Address =" + "'" + customer.Address + "'," +
"Email =" + "'" + customer.Email + "'," +
"CreationDate =" + "'" + customer.DateCreated + "'" +
"WHERE FirstName =" + "'" + customer.FirstName + "'"
;
SqlCommand cmd = new SqlCommand(query, connect.OpenConnection());
cmd.ExecuteNonQuery();
connect.CloseConnection();
}
// Print Customer table
public void PrintCustomers()
{
connect = new SQLConnect();
string query = "SELECT * FROM CustomerDetails";
SqlCommand cmd = new SqlCommand(query, connect.OpenConnection());
DataTable dataTable = new DataTable();
SqlDataAdapter dap = new SqlDataAdapter(cmd);
dap.Fill(dataTable);
var table = new ConsoleTable("Id", "Firstname", "Middlename", "Lastname", "PhoneNumber", "Address", "Email", "DateCreated");
foreach (DataRow dataRow in dataTable.Rows)
{
table.AddRow(dataRow[0], dataRow[1], dataRow[2], dataRow[3], dataRow[4], dataRow[5], dataRow[6], dataRow[7]);
}
Console.WriteLine(table);
connect.CloseConnection();
}
/// <summary>
/// Method to fetch response info for given page Id
/// </summary>
/// <param name="pageid"></param>
public DataTable FetchResponseInfo(string pageid)
{
SQLConnect sql = new SQLConnect();
DataTable responsetbl = new DataTable("ResponseInfo");
try
{
string selectstmt = "Select RID,fr.QID,RText,fr.TypeId,frt.Description, fq.isReadOnly, fq.QText From Flywheel.dbo.FLY_Response fr ";
string join1 = " Join Flywheel.dbo.LU_FLY_Response_Type frt on frt.TypeId = fr.TypeId";
string join2 = " join Flywheel..FLY_Questions fq on fq.QID = fr.QID";
string wherecond = " Where fr.QID in (Select QID from Flywheel..FLY_Questions Where PageID = " + pageid + ") and fr.TypeID in (1,2)";
string query = selectstmt + join1 + join2 + wherecond;
sql.OpenConnection();
responseinfo = sql.Execute(query);
DataTable dtSchema = responseinfo.GetSchemaTable();
// You can also use an ArrayList instead of List<>
List <DataColumn> listCols = new List <DataColumn>();
if (dtSchema != null)
{
foreach (DataRow drow in dtSchema.Rows)
{
string columnName = Convert.ToString(drow["ColumnName"]);
DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
column.Unique = (bool)drow["IsUnique"];
column.AllowDBNull = (bool)drow["AllowDBNull"];
column.AutoIncrement = (bool)drow["IsAutoIncrement"];
listCols.Add(column);
responsetbl.Columns.Add(column);
}
}
// Read rows from DataReader and populate the DataTable
while (responseinfo.Read())
{
DataRow dataRow = responsetbl.NewRow();
for (int i = 0; i < listCols.Count; i++)
{
dataRow[((DataColumn)listCols[i])] = responseinfo[i];
}
responsetbl.Rows.Add(dataRow);
}
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
finally
{
sql.CloseConnection();
}
Console.WriteLine("Rows Count in Responsetbl: " + responsetbl.Rows.Count);
//string expression = "RText = 'Hours' and QText like '%Moderate%'";
//DataRow[] foundRows;
//// Use the Select method to find all rows matching the filter.
//foundRows = responsetbl.Select(expression);
//// Print column 0 of each returned row.
//for (int i = 0; i < foundRows.Length; i++)
//{
// Console.WriteLine(foundRows[i][0] + " | " + foundRows[i][1] + " | " + foundRows[i][2] + " | " + foundRows[i][3] + " | " + foundRows[i][4] + " | " + foundRows[i][5] + " | " + foundRows[i][6]);
//}
return(responsetbl);
}