public string consultDB()
{
if (conexion.OpenConnection().Equals("Connected"))
{
try
{
// mariaDB.Insert(db);
// string Query = "select * from metadatadb.servidores";
// SqlCommand cmd = new SqlCommand(Query);
// cmd.ExecuteNonQuery();
//conexion.CloseConnection();
return("Correcto SQL");
}
catch (Exception e)
{
return("Error leyendo" + e);
}
}
else
{
return("No hay conexion con la base de datos");
}
}
public void execommand()
{
SQLConnect sq = new SQLConnect();
sq.OpenConnection();
sq.ExecuteStoreProcedure();
}
/// <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");
}
}
// 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();
}
}
// 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();
}
public void ExecuteDBQuery()
{
SQLConnect sqlcn = new SQLConnect();
sqlcn.OpenConnection();
string joinservicecycle = "join Onlife..tbl_ServiceCycle sc on sc.ServiceCycleId = scgm.ServiceCycleId and sc.ServiceCycleEndDate > getDate()";
string joingroupid = "join OnlifeEntity..tbl_Group g on g.GroupGuid = scgm.GroupGuid";
string joinpointmatrix = "join Onlife..tbl_PointsMatrixActionCategoryMap pmacm on pmacm.PointsMatrixId = sc.PointsMatrixId";
string joinrulepackage = "join Onlife.dbo.tbl_RulePackage rp on rp.RulePackageID = sc.RulePackageID";
string joinactioncategory = "join Onlife.dbo.LU_ActionCategory ac on pmacm.ActionCategoryID = ac.ActionCategoryID";
string joinpointsfreq = "join onlife..lu_PointsFrequency PF on pf.PointsFrequencyId = pmacm.FrequencyDays";
//string sqlquery = "Select g.GroupId, g.GroupName,sc.ServiceCycleId,sc.PointsMatrixId, pmacm.ActionCategoryId,ac.ActionCategoryInternalName--,acm.ActionId,pmacm.InstancePoints,pmacm.MaxInstances,pmacm.frequencyMaxInstances,pmacm.FrequencyDays, pf.FrequencyDescription From Onlife..ServiceCycleGroupMap scgm"+joinservicecycle+joingroupid+joinpointmatrix+joinrulepackage+joinactioncategory+joinpointsfreq+"Where G.GroupID = 50";
string sqlquery = "Select g.GroupName"
+ ",pmacm.InstancePoints,pmacm.frequencyMaxInstances, pf.FrequencyDescription,ac.ActionCategoryInternalName"
+ " From Onlife..ServiceCycleGroupMap scgm"
+ " join Onlife..tbl_ServiceCycle sc on sc.ServiceCycleId = scgm.ServiceCycleId and sc.ServiceCycleEndDate > getDate()"
+ " join OnlifeEntity..tbl_Group g on g.GroupGuid = scgm.GroupGuid"
+ " join Onlife..tbl_PointsMatrixActionCategoryMap pmacm on pmacm.PointsMatrixId = sc.PointsMatrixId"
+ " join Onlife.dbo.tbl_RulePackage rp on rp.RulePackageID = sc.RulePackageID"
+ " join Onlife.dbo.LU_ActionCategory ac on pmacm.ActionCategoryID = ac.ActionCategoryID"
+ " join onlife..lu_PointsFrequency PF on pf.PointsFrequencyId = pmacm.FrequencyDays"
+ " Where G.GroupName like '%nucor%'";
DataTable responsetbl = sqlcn.Execute1(sqlquery);
foreach (DataColumn col in responsetbl.Columns)
{
Console.Write(col.ColumnName.ToString() + "\t\t");
}
Console.WriteLine();
foreach (DataRow row in responsetbl.Rows)
{
foreach (DataColumn col in responsetbl.Columns)
{
Console.Write(row[col] + "\t\t\t");
}
Console.WriteLine();
}
//for (int i=0;i<responsetbl.Rows.Count;i++)
//{
// for(int j=0;j<responsetbl.Columns.Count;j++)
// {
// Console.WriteLine(responsetbl.Rows[i][j]);
// }
//}
}
internal string check()
{
try
{
if (conexion.OpenConnection().Equals("Connected"))
{
return("Conectado al pool conexiones SQL");
}
else
{
return("Cant- connect");
}
}
catch (Exception e)
{
return("Cant- connect");
}
}
internal string check(dbModel model)
{
SQLConnect conexion = new SQLConnect(model.username, model.pass, model.server, model.port, model.alias);
try
{
if (conexion.OpenConnection().Equals("Connected"))
{
return("Connected");
}
else
{
return("Cant- connect");
}
}
catch (Exception e)
{
return("Cant- connect");
}
}
// 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);
}