//public NpgsqlConnection Connection = new NpgsqlConnection(@"Data Source=LAPTOP-CNDERC7H\SQLEXPRESS;Initial Catalog=RemoteSystem;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
protected void Page_Load(object sender, EventArgs e)
{
GenerateQuery QueryObj = new GenerateQuery();
string UserId = Request.QueryString["UserId"];
if (UserId != "" && UserId != null)
{
string Query = QueryObj.GetQueryViaFileAndTagName("LogIn.xml", "RemoteControlList");
DataTable DataTable = new DataTable("DataTable");
Connection.Open();
NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(Query, Connection);
DataSet dataSetObj = new DataSet();
adapter.Fill(dataSetObj);
GridView1.DataSource = dataSetObj;
GridView1.DataBind();
}
else
{
string message4 = "You do not have rights of this page. Please Contact Admin.";
System.Text.StringBuilder sb3 = new System.Text.StringBuilder();
sb3.Append("<script type = 'text/javascript'>");
sb3.Append("window.onload=function(){");
sb3.Append("alert('");
sb3.Append(message4);
sb3.Append("')};");
sb3.Append("</script>");
ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", sb3.ToString());
}
}
public T Load(T obj, string[] primaryKeyNames)
{
IList <T> sessions = new List <T>();
IFactory factory = DBHelper.CreateFactory();
DbCommand cmd = null;
try
{
string query = GenerateQuery.CommandTextLoad(obj, primaryKeyNames);
cmd = factory.MakeCommand(query);
GenerateQuery.PrepareParametersLoad(cmd, obj, primaryKeyNames);
IDataReader dre = factory.ExecuteReader(cmd);
sessions = Results(dre);
if (sessions.Count > 0)
{
return(sessions[0]);
}
}
catch (Exception ex)
{
Write2Log.WriteLogs("Generic<T>", string.Format("Load({0} obj, string[] primaryKeyNames)", typeof(T).Name), ex.Message);
}
finally
{
cmd.Connection.Close();
cmd.Connection.Dispose();
}
return(default(T));
}
public int InsertIDENTITY(T obj)
{
IFactory factory = DBHelper.CreateFactory();
factory.BeginTransaction();
object output = null;
int re = int.MinValue;
try
{
string query = GenerateQuery.CommandTextInsertIDENTITY(obj);
DbCommand cmd = factory.MakeCommand(query);
GenerateQuery.PrepareParametersInsert(cmd, obj);
output = factory.ExecuteScalar(cmd);
factory.Commit();
re = DBConvert.ParseInt(output);
}
catch (Exception ex)
{
Write2Log.WriteLogs("Generic<T>", string.Format("Insert({0} obj)", obj.GetType().Name), ex.Message);
factory.Rollback();
}
finally
{
factory.Release();
}
return(re);
}
public static void Main()
{
GenerateQuery t = new GenerateQuery();
Person person = new Person()
{
Id = 1,
PersonAge = 12,
PersonLName = "bidzia",
PersonName = "backsoon"
};
Console.WriteLine("Select: " + t.GenerateSelectQuery(person, new List <string> {
"id > 1", "id < 100"
}));
//
Console.WriteLine("insert: " + t.GenerateInsertQuery(person));
Console.WriteLine("Update: " + t.GenerateUpdateQuery(person, new List <string> {
"id = 11"
}));
Console.WriteLine("Delete: " + t.GenerateDeleteQuery(person, new List <string> {
"id = 11"
}));
Console.ReadKey();
}
public IList <T> GetList(string storedProc, DGCParameter[] parameters, out int total)
{
total = 0;
IFactory factory = DBHelper.CreateFactory();
IList <T> lst = new List <T>();
try
{
DbCommand cmd = factory.MakeCommandFromStore(storedProc);
GenerateQuery.PrepareParametersList(cmd, parameters);
IDbDataParameter para = cmd.CreateParameter();
para.ParameterName = "@total";
para.DbType = DbType.Int32;
para.Direction = ParameterDirection.Output;
para.Value = total;
cmd.Parameters.Add(para);
IDataReader dre = factory.ExecuteReader(cmd);
lst = Results(dre);
object strTemp = cmd.Parameters["@total"].Value;
total = Convert.ToInt32(strTemp);
}
catch (Exception ex)
{
Write2Log.WriteLogs("Generic2C<T, TDesc>", string.Format("GetList(string storedProc, DGCParameter[] parameters, out {0} obj, out TDesc objDesc, out int total)", typeof(T).Name), ex.Message);
}
finally
{
factory.Release();
}
return(lst);
}
public void ExcuteNonQueryFromStore(string storedProc, DGCParameter[] parameters, IFactory factory)
{
DbCommand cmd = factory.MakeCommandFromStore(storedProc);
GenerateQuery.PrepareParametersList(cmd, parameters);
factory.ExecuteNonQuery(cmd);
}
public void UpdateWithTransaction(T currentObj, T expectedObj, string[] primaryKeyNames, IFactory factory)
{
string query = GenerateQuery.CommandTextUpdate(currentObj, primaryKeyNames);
DbCommand cmd = factory.MakeCommand(query);
GenerateQuery.PrepareParametersUpdate(cmd, currentObj, expectedObj, primaryKeyNames);
factory.ExecuteNonQuery(cmd);
}
public int InsertIDENTITYWithTransactionWithMaxField(T obj, IFactory factory, string fieldMax)
{
string query = GenerateQuery.CommandTextInsertIDENTITYWithMaxFiels(obj, fieldMax);
DbCommand cmd = factory.MakeCommand(query);
GenerateQuery.PrepareParametersInsert(cmd, obj);
object output = factory.ExecuteScalar(cmd);
return(int.Parse(output.ToString()));
}
public int InsertWithTransaction(T obj, IFactory factory)
{
string query = GenerateQuery.CommandTextInsert(obj);
DbCommand cmd = factory.MakeCommand(query);
GenerateQuery.PrepareParametersInsert(cmd, obj);
object output = factory.ExecuteScalar(cmd);
return(DBConvert.ParseInt(output));
}
protected void LoginUser_Click(object sender, EventArgs e)
{
Registrations registrationObj = new Registrations();
string Pass = registrationObj.encryptpass(Password.Text);
GenerateQuery QueryObj = new GenerateQuery();
string[,] ReplaceValues = new string[2, 2];
ReplaceValues[0, 0] = "_UserName_";
ReplaceValues[0, 1] = UserName.Text;
ReplaceValues[1, 0] = "_Password_";
ReplaceValues[1, 1] = Password.Text;
long UserId = 0;
string User = "";
Registration registration = null;
string Query = QueryObj.GetQueryViaFileAndTagName("LogIn.xml", "ValidUserOrNot");
Query = Query.Replace("_UserName_", UserName.Text.ToString());
Query = Query.Replace("_Password_", Password.Text.ToString());
if (Query != "")
{
UserId = QueryObj.GetSingleNumericValueViaQuery(Query, ReplaceValues, Connection);
User = QueryObj.GetSingleStringValueViaQuery(Query, ReplaceValues, Connection);
if (UserId > 0)
{
Response.Redirect("ListPage.aspx?UserId=" + UserId);
}
else
{
UserName.Text = "";
Password.Text = "";
Label1.Text = "Enter User Name And Password is incurrect please try again.";
}
}
if (Query != "")
{
registration = QueryObj.GetCollectionOfDataViaQuery(Query);
DataTable DataTableObj = new DataTable("DataTable");
DataTableObj = QueryObj.GetCollectionOfDataSetViaQuery(Query);
if (DataTableObj != null)
{
//Data Set havinf data now retrive it to its sutiable properties
}
}
if (Query != "")
{
registration = QueryObj.GetCollectionSearchDataViaQuery(Query, ReplaceValues, null, null, "", registration);
}
}
public static DataTable ExcuteFromCmd(string query, DGCParameter[] parameters)
{
DataTable dt = null;
Database db = CreateDB();
try
{
DbCommand cmd = db.GetSqlStringCommand(query);
GenerateQuery.PrepareParametersList(cmd, parameters);
cmd.CommandTimeout = 200;
DataSet ds = db.ExecuteDataSet(cmd);
dt = ds.Tables[0];
}
catch (Exception ex)
{
Write2Log.WriteLogs("Generic<T>", "ExcuteFromCmd(string query, DGCParameter[] parameters)", ex.Message);
}
return(dt);
}
public void UpdateDoub(T currentObj, T expectedObj, string[] primaryKeyNames, string[] primaryKeyNames2)
{
IFactory factory = DBHelper.CreateFactory();
try
{
string query = GenerateQuery.CommandTextUpdate(currentObj, primaryKeyNames, primaryKeyNames2);
DbCommand cmd = factory.MakeCommand(query);
GenerateQuery.PrepareParametersUpdate(cmd, currentObj, expectedObj, primaryKeyNames, primaryKeyNames2);
cmd.CommandTimeout = 10;
factory.ExecuteNonQuery(cmd);
}
catch (Exception ex)
{
Write2Log.WriteLogs("Generic<T>", string.Format("Update({0} currentObj, {0} expectedObj, string[] primaryKeyNames)", typeof(T).Name), ex.Message);
}
finally
{
factory.Release();
}
}
public IList <T> GetListT(string storedProc, DGCParameter[] parameters)
{
IFactory factory = DBHelper.CreateFactory();
IList <T> lst = new List <T>();
try
{
DbCommand cmd = factory.MakeCommandFromStore(storedProc);
GenerateQuery.PrepareParametersList(cmd, parameters);
IDataReader dre = factory.ExecuteReader(cmd);
lst = Results(dre);
}
catch (Exception ex)
{
Write2Log.WriteLogs("Generic2C<T, TDesc>", string.Format("GetList(string storedProc, DGCParameter[] parameters, out {0} obj, out TDesc objDesc, out int total)", typeof(T).Name), ex.Message);
}
finally
{
factory.Release();
}
return(lst);
}
public IList <T> GetAllBy(T obj, string selectClause, string whereClause, DGCParameter[] parameters)
{
IList <T> sessions = new List <T>();
IFactory factory = DBHelper.CreateFactory();
try
{
string query = selectClause + whereClause;
DbCommand cmd = factory.MakeCommand(query);
GenerateQuery.PrepareParametersList(cmd, parameters);
IDataReader dre = factory.ExecuteReader(cmd);
sessions = Results(dre);
}
catch (Exception ex)
{
Write2Log.WriteLogs("Generic<T>", string.Format("GetAllBy({0} obj, string whereClause, DGCParameter[] parameters)", typeof(T).Name), ex.Message);
}
finally
{
factory.Release();
}
return(sessions);
}
public bool Insert(T entity)
{
var sql = GenerateQuery <T> .GenerateInsertQuery(false);
return(_dbConnection.Execute(sql, entity) > 0);
}
public bool Update(T entity)
{
var sql = GenerateQuery <T> .GenerateUpdateQuery();
return(_dbConnection.Execute(sql, entity) > 0);
}
// public string Email { set; get; }
protected void Page_Load(object sender, EventArgs e)
{
if (Connection == null)
{
Connection.Open();
}
TableItemStyle tableStyle = new TableItemStyle();
tableStyle.HorizontalAlign = HorizontalAlign.Center;
tableStyle.VerticalAlign = VerticalAlign.Middle;
tableStyle.Width = Unit.Pixel(100);
string UserId = Request.QueryString["UserId"];
Registration registration = null;
GenerateQuery QueryObj = new GenerateQuery();
long rowNum = 0;
string Query = QueryObj.GetQueryViaFileAndTagName("LogIn.xml", "RemoteControlData");
registration = QueryObj.GetCollectionOfDataViaQuery(Query);
DataTable DataTableObj = new DataTable("DataTable");
DataTableObj = QueryObj.GetCollectionOfDataSetViaQuery(Query);
TableRow tempRow1 = new TableRow();
TableRow tempRow2 = new TableRow();
TableRow tempRow3 = new TableRow();
SqlDataAdapter adapter = new SqlDataAdapter(Query, Connection);
DataSet dataSetObj = new DataSet();
adapter.Fill(dataSetObj);
StringBuilder html = new StringBuilder();
html.Append("<table border = '1'>");
html.Append("<tr>");
foreach (DataColumn column in DataTableObj.Columns)
{
if (column.ColumnName != "UserId" && column.ColumnName != "RemoteControlSessionId")
{
html.Append("<th>");
html.Append(column.ColumnName);
html.Append("</th>");
}
}
html.Append("</tr>");
foreach (DataRow row in DataTableObj.Rows)
{
html.Append("<tr>");
foreach (DataColumn column in DataTableObj.Columns)
{
if (column.ColumnName != "UserId" && column.ColumnName != "RemoteControlSessionId")
{
html.Append("<td>");
html.Append(row[column.ColumnName]);
html.Append("</td>");
}
}
html.Append("</tr>");
}
html.Append("</table>");
PlaceHolder1.Controls.Add(new Literal {
Text = html.ToString()
});
if (DataTableObj != null)
{
int DataSetRowCount = DataTableObj.Rows.Count;
foreach (DataRow dr in DataTableObj.Rows)
{
TableCell tempCell1 = new TableCell();
TableCell tempCell2 = new TableCell();
TableCell tempCell3 = new TableCell();
for (int cellNum = 0; cellNum < 1; cellNum++)
{
tempCell1.Text = Convert.ToString(dr["RemoteControlSessionId"]);
tempCell2.Text = Convert.ToString(dr["Name"]);
tempCell3.Text = Convert.ToString(dr["Email"]);
DataSetRowCount = DataSetRowCount - 1;
}
tempRow1.Cells.AddAt(Convert.ToInt32(rowNum), tempCell1);
tempRow2.Cells.AddAt(Convert.ToInt32(rowNum), tempCell2);
tempRow3.Cells.AddAt(Convert.ToInt32(rowNum), tempCell3);
//Table1.Rows.Add(tempRow1);
//Table1.Rows.Add(tempRow2);
//Table1.Rows.Add(tempRow3);
SessionAddress.InnerText = Convert.ToString(dr["Email"]);
rowNum = rowNum + 1;
}
Table1.Rows.Add(tempRow1);
}
foreach (TableRow rw in Table1.Rows)
{
foreach (TableCell cel in rw.Cells)
{
cel.ApplyStyle(tableStyle);
}
}
TableHeaderCell header1 = new TableHeaderCell();
header1.RowSpan = 1;
header1.ColumnSpan = 5;
header1.Text = "Remote Control Accept Reject List";
header1.Font.Bold = true;
header1.BackColor = Color.Gray;
header1.HorizontalAlign = HorizontalAlign.Center;
header1.VerticalAlign = VerticalAlign.Middle;
TableRow headerRow1 = new TableRow();
headerRow1.Cells.Add(header1);
Table1.Rows.AddAt(0, headerRow1);
}
public int InsertTakeId(T entity)
{
var sql = GenerateQuery <T> .GenerateInsertQuery(true);
return(_dbConnection.Execute(sql, entity));
}
protected void ApproveReject_RowCommand(object sender, GridViewCommandEventArgs e)
{
string Message = "";
try
{
if (Connection == null)
{
Connection.Open();
}
int index = Convert.ToInt32(e.CommandArgument);
GridViewRow row = GridView1.Rows[index];
long RemoteControlSessionId = Convert.ToInt64(row.Cells[0].Text);
string AlredyApproved = Convert.ToString(row.Cells[3].Text).ToString();
GenerateQuery QueryObj = new GenerateQuery();
string Query = QueryObj.GetQueryViaFileAndTagName("LogIn.xml", "GetUserId");
Query = Query.Replace("_RemoteControlSessionId_", RemoteControlSessionId.ToString());
long UserId = QueryObj.GetSingleNumericValueViaQuery(Query, null, Connection);
if (e.CommandName == "Reject")
{
Connection.Open();
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = "SP_RemoteControlSession_Delete";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@RemoteControlSessionId", RemoteControlSessionId);
cmd.ExecuteNonQuery();
Message = "Selected User Request Rejected.";
}
}
if (e.CommandName == "Approved" && AlredyApproved == "N")
{
Connection.Open();
using (var cmd = Connection.CreateCommand())
{
cmd.CommandText = "SP_RemoteControlSession_SAVE";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@RemoteControlSessionId", RemoteControlSessionId);
cmd.Parameters.AddWithValue("@UserId", UserId);
cmd.Parameters.AddWithValue("@Reject", 'N');
cmd.Parameters.AddWithValue("@Approved", 'Y');
cmd.Parameters.AddWithValue("@CreatedBy", UserId);
cmd.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
cmd.Parameters.AddWithValue("@LastModifyBy", UserId);
cmd.Parameters.AddWithValue("@LastModifyDate", DateTime.Now);
cmd.ExecuteNonQuery();
Message = "Selected User Request Get Approved.";
}
}
else
{
Message = "Selected User are alredy Approved.";
}
}
catch (Exception ex)
{
Message = ex.Message;
}
finally
{
Connection.Close();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("<script type = 'text/javascript'>");
sb.Append("window.onload=function(){");
sb.Append("alert('");
sb.Append(Message);
sb.Append("')};");
sb.Append("</script>");
ClientScript.RegisterClientScriptBlock(this.GetType(), "alert", sb.ToString());
Page_Load(sender, e);
}
}
