protected void Button21_Click(object sender, EventArgs e)
{
string gender = string.Empty;
string[] AssumedBlackListKeyWords = { txtFirstName.Text, txtLastName.Text, txtDateOfBirth.Text, txtReligion.Text, txtPhone.Text, txtAddress.Text, txtPostalCode.Text, txtCNIC.Text, txtDateOfJoin.Text };
if (Jvalidate.FilterBlackLIstKeywords(AssumedBlackListKeyWords))
{
if (rbtnMale.Checked == true && rbtnFemale.Checked == false)
{
gender = "male";
}
else
{
gender = "female";
}
string result = objTeacher.UpdateTeacherInformation(Convert.ToInt32(txtGenealID.Text), Convert.ToInt32(txtDOBId.Text), Convert.ToInt32(txtLocationID.Text), Convert.ToInt32(txtTeacherID.Text), txtFirstName.Text, txtLastName.Text, ddlNationality.SelectedItem.ToString(), gender, txtReligion.Text, txtPhone.Text, txtAddress.Text, txtDateOfJoin.Text, txtDateOfBirth.Text, Convert.ToInt32(ddlCity.SelectedValue), Convert.ToInt32(txtPostalCode.Text));
if (result == "true")
{
Response.Write("<script>alert('Record Successfully updated');</script>");
}
else
{
Response.Write("<script>alert('Error occured during updation of data, the new information is rolled back to previous inforamtion and nothing is udpated.');</script>");
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
int rowsAffected = 0;
if (IsAllReadyThere())
{
string[] teacherid_unsecure = { txt_teacherid.Text };
bool teacherid_secure = Jvalidate.FilterBlackLIstKeywords(teacherid_unsecure);
if (teacherid_secure)
{
string query = "update tbl_teacher_of_the_month set teacher_id=" + Convert.ToInt32(Jvalidate.RemoveHtmlTags(txt_teacherid.Text)) + " where school_id='" + schoolID + "';";
mydb.CreateConnection();
mydb.InitializeSQLCommandObject(mydb.GetCurrentConnection, query);
try
{
mydb.OpenConnection();
rowsAffected = mydb.obj_sqlcommand.ExecuteNonQuery();
if (rowsAffected > 0)
{
Response.Write("<script>alert('Successfully updated teacher of the month information');</script>");
}
else
{
Response.Write("<script>alert('0 Records affected');</script>");
}
}
catch {}
finally
{
mydb.CloseConnection();
mydb.obj_sqlcommand.Dispose();
}
}
else
{
Response.Write("<script>alert('Unsecure teacher id');</script>");
}
}
else
{
string[] cols = { "school_id", "teacher_id" };
object[] vals = { schoolID, Convert.ToInt32(txt_teacherid.Text) };
rowsAffected = mydb.InsertQuery("tbl_teacher_of_the_month", cols, vals);
if (rowsAffected > 0)
{
Response.Write("<script>alert('Successfully updated teacher of the month information');</script>");
}
else
{
Response.Write("<script>alert('0 Records affected');</script>");
}
}
}
public int DeleteQuery(string TableName, string WhereClause, object WhereClauseValue)
{
int RowsAffected = 0;
#region Creating Query
Query.Append("delete ");
Query.Append(TableName);
Query.Append(" where ");
Query.Append(WhereClause);
Query.Append("=");
Query.Append("@" + WhereClause);
#endregion
#region Executing Query
CreateConnection();
try
{
InitializeSQLCommandObject(GetCurrentConnection, Query.ToString());
string CurrentColumn = WhereClauseValue.GetType().ToString();
string[] columnArray = { "" + WhereClauseValue + "" };
if (CurrentColumn == "System.String" || CurrentColumn == "System.Text.StringBuilder")
{
if (Jvalidate.FilterBlackLIstKeywords(columnArray))
{
this.obj_sqlcommand.Parameters.AddWithValue("@" + WhereClause, Jvalidate.RemoveHtmlTags(WhereClauseValue.ToString()));
}
}
else if (CurrentColumn == "System.Int32")
{
if (Jvalidate.FilterBlackLIstKeywords(columnArray))
{
this.obj_sqlcommand.Parameters.AddWithValue("@" + WhereClause, Convert.ToInt32(Jvalidate.RemoveHtmlTags(WhereClauseValue.ToString())));
}
}
else if (CurrentColumn == "System.Decimal")
{
if (Jvalidate.FilterBlackLIstKeywords(columnArray))
{
this.obj_sqlcommand.Parameters.AddWithValue("@" + WhereClause, Jvalidate.RemoveHtmlTags((Convert.ToDecimal(WhereClauseValue)).ToString()));
}
}
OpenConnection();
RowsAffected = this.obj_sqlcommand.ExecuteNonQuery();
}
catch { }
finally { CloseConnection(); }
#endregion
return(RowsAffected);
}
public string GetAuthorizedID(string username, string password)
{
string returnvalue = string.Empty;
string[] username_array = { username };
string[] password_array = { password };
if (Jvalidate.FilterBlackLIstKeywords(username_array) && Jvalidate.FilterBlackLIstKeywords(password_array))
{
Query.Clear();
Query.Append("select authorized_id from tbl_authorized_users where username = '******' and password = '******'");
try
{
CreateConnection();
InitializeSQLCommandObject(obj_sqlconnection, Query.ToString());
OpenConnection();
obj_reader = obj_sqlcommand.ExecuteReader();
if (obj_reader.HasRows)
{
while (obj_reader.Read())
{
returnvalue = obj_reader[0].ToString();
}
}
else
{
returnvalue = "no rows found";
}
}
catch (Exception ex)
{
returnvalue = ex.ToString();
}
finally
{
CloseConnection();
obj_reader.Dispose();
obj_sqlcommand.Dispose();
Query.Clear();
}
}
return(returnvalue.ToString());
}
public string PopulateTeacherProfileInformationByID(int _teacherID, string _schoolID)
{
string returnvalue = "true";
if (_teacherID != null)
{
string[] teacheridToBeFiltered = { _teacherID.ToString(), _schoolID };
if (Jvalidate.FilterBlackLIstKeywords(teacheridToBeFiltered))
{
string secureTeacherid = Jvalidate.RemoveHtmlTags(_teacherID.ToString());
mydb.CreateConnection();
mydb.InitializeSQLCommandObject(mydb.GetCurrentConnection, "select * from View_TeacherProfile where teacher_id=" + _teacherID + " and school_id='" + JSchool.SchoolID + "';");
try
{
mydb.OpenConnection();
mydb.obj_reader = mydb.obj_sqlcommand.ExecuteReader();
if (mydb.obj_reader.HasRows)
{
while (mydb.obj_reader.Read())
{
CountryID = Convert.ToInt32(mydb.obj_reader["country_id"]);
Country = mydb.obj_reader["country"].ToString();
FirstName = mydb.obj_reader["firstname"].ToString();
LastName = mydb.obj_reader["lastname"].ToString();
Nationality = mydb.obj_reader["Nationality"].ToString();
Gender = mydb.obj_reader["Gender"].ToString();
Photo = mydb.obj_reader["photo"].ToString();
Religion = mydb.obj_reader["religion"].ToString();
Phone = mydb.obj_reader["phone"].ToString();
Address = mydb.obj_reader["address"].ToString();
Day = Convert.ToInt32(mydb.obj_reader["day"]);
Month = Convert.ToInt32(mydb.obj_reader["month"]);
Year = Convert.ToInt32(mydb.obj_reader["year"]);
DOBID = Convert.ToInt32(mydb.obj_reader["dob_id"]);
LocationID = Convert.ToInt32(mydb.obj_reader["loc_id"]);
CityID = Convert.ToInt32(mydb.obj_reader["city_id"]);
City = mydb.obj_reader["city"].ToString();
PostalCode = Convert.ToInt32(mydb.obj_reader["postal_code"]);
Username = mydb.obj_reader["username"].ToString();
Password = mydb.obj_reader["password"].ToString();
AccountPin = Convert.ToInt32(mydb.obj_reader["account_pin"]);
PrimaryEmail = mydb.obj_reader["primary_email"].ToString();
SecondaryEmail = mydb.obj_reader["secondary_email"].ToString();
UserTypeID = Convert.ToInt32(mydb.obj_reader["usertype_id"]);
LoginCount = Convert.ToInt32(mydb.obj_reader["login_count"]);
LastLoginDate = mydb.obj_reader["last_login_date"].ToString();
UserType = mydb.obj_reader["usertype"].ToString();
TeacherID = Convert.ToInt32(mydb.obj_reader["teacher_id"]);
GeneralID = Convert.ToInt32(mydb.obj_reader["General_Id"]);
AuthorizeID = mydb.obj_reader["authorized_id"].ToString();
DateOfJoin = mydb.obj_reader["date_of_join"].ToString();
CNIC = mydb.obj_reader["cnic_no"].ToString();
SchoolID = mydb.obj_reader["school_id"].ToString();
}
}
else
{
returnvalue = "false";
}
}
catch (Exception ex)
{
HttpContext.Current.Response.Write(ex.ToString());
}
finally
{
mydb.CloseConnection();
mydb.obj_reader.Dispose();
mydb.obj_reader.Close();
}
}
}
return(returnvalue);
}
public int UpdateQuery(string TableName, string[] ColumnNames, object[] ColumnValues, string WhereClause, object WhereClauseValue)
{
int RowsAffacted = 0;
#region Creating update query
Query.Clear();
Query.Append("update ");
Query.Append(TableName);
Query.Append(" set ");
for (int i = 0; i < ColumnNames.Length; i++)
{
if (i + 1 == ColumnNames.Length)
{
Query.Append(ColumnNames[i]);
Query.Append("=");
Query.Append("@" + ColumnNames[i]);
}
else
{
Query.Append(ColumnNames[i]);
Query.Append("=");
Query.Append("@" + ColumnNames[i]);
Query.Append(",");
}
}
Query.Append(" where ");
Query.Append(WhereClause);
Query.Append("=");
Query.Append("@" + WhereClause);
#endregion
#region Execution query
CreateConnection();
try
{
InitializeSQLCommandObject(GetCurrentConnection, Query.ToString());
for (int i = 0; i < ColumnNames.Length; i++)
{
string CurrentColumn = ColumnValues[i].GetType().ToString();
string[] columnArray = Convert.ToString(ColumnValues[i]).Split(' ');
if (CurrentColumn == "System.String" || CurrentColumn == "System.Text.StringBuilder")
{
if (Jvalidate.FilterBlackLIstKeywords(columnArray))
{
this.obj_sqlcommand.Parameters.AddWithValue("@" + ColumnNames[i], Jvalidate.RemoveHtmlTags(ColumnValues[i].ToString()));
}
else
{
break;
}
}
else if (CurrentColumn == "System.Int32")
{
if (Jvalidate.FilterBlackLIstKeywords(columnArray))
{
this.obj_sqlcommand.Parameters.AddWithValue("@" + ColumnNames[i], Jvalidate.RemoveHtmlTags((Convert.ToInt32(ColumnValues[i])).ToString()));
}
else
{
break;
}
}
else if (CurrentColumn == "System.Decimal")
{
if (Jvalidate.FilterBlackLIstKeywords(columnArray))
{
this.obj_sqlcommand.Parameters.AddWithValue("@" + ColumnNames[i], Jvalidate.RemoveHtmlTags((Convert.ToDecimal(ColumnValues[i])).ToString()));
}
else
{
break;
}
}
}
string ClauseValueType = WhereClauseValue.GetType().ToString();
string[] ClauseArray = { "" + WhereClauseValue + "" };
if (ClauseValueType == "System.String" || ClauseValueType == "System.Text.StringBuilder")
{
if (Jvalidate.FilterBlackLIstKeywords(ClauseArray))
{
this.obj_sqlcommand.Parameters.AddWithValue("@" + WhereClause, Jvalidate.RemoveHtmlTags(WhereClauseValue.ToString()));
}
}
else if (ClauseValueType == "System.Int32")
{
if (Jvalidate.FilterBlackLIstKeywords(ClauseArray))
{
this.obj_sqlcommand.Parameters.AddWithValue("@" + WhereClause, Convert.ToInt32(Jvalidate.RemoveHtmlTags(WhereClauseValue.ToString())));
}
}
else if (ClauseValueType == "System.Decimal")
{
if (Jvalidate.FilterBlackLIstKeywords(ClauseArray))
{
this.obj_sqlcommand.Parameters.AddWithValue("@" + WhereClause, Jvalidate.RemoveHtmlTags((Convert.ToDecimal(WhereClauseValue)).ToString()));
}
}
OpenConnection();
RowsAffacted = this.obj_sqlcommand.ExecuteNonQuery();
}
catch { }
finally
{
CloseConnection();
}
#endregion
return(RowsAffacted);
}
//this method will execute insert query.
//This method is fully dynamic and support any type of query and execute it safely.
public int InsertQuery(string TableName, string[] ColumnNames, object[] ColumnValues)
{
string CurrentColumnType, CurrentColumn = string.Empty;
int RowsAffected = 0;
#region Generating dynamic query
//Generating dynamic query depending on number of columns and object array values.
int i = 0;
Query.Append("Insert into ");
//Adding name of the table, passed by parameter value.
Query.Append(TableName);
Query.Append("(");
//Appending column names in query object.
for (i = 0; i < ColumnNames.Length; i++)
{
//Appending , after each column name till ColumnLength-1
if (i + 1 == ColumnNames.Length)
{
Query.Append(ColumnNames[i]);
}
else
{
Query.Append(ColumnNames[i] + ",");
}
}
//Following query syntax
Query.Append(") values(");
//Adding parameters for the values
for (i = 0; i < ColumnValues.Length; i++)
{
//Same as appending line # 68
if (i + 1 == ColumnValues.Length)
{
Query.Append("@" + ColumnNames[i]);
}
else
{
Query.Append("@" + ColumnNames[i] + ",");
}
}
//Query syntax completes
Query.Append(");");
#endregion
#region Query Execution
//Creating connection to database
CreateConnection();
#region Execute Query
try
{
InitializeSQLCommandObject(GetCurrentConnection, Query.ToString());
//check the current column data type and convert object array parameter's value to that data type.
for (i = 0; i < ColumnNames.Length; i++)
{
//Getting current data type
CurrentColumnType = ColumnValues[i].GetType().ToString();
//converting parameter value to array to pass to the FilerBlackListKeywords method.
string[] columnArray = Convert.ToString(ColumnValues[i]).Split(' ');
//string[] columnArray = { "" + ColumnValues[i] + "" };
//Checking current data type.
if (CurrentColumnType == "System.Text.StringBuilder" || CurrentColumnType == "System.String")
{ //if black listed keywords are filtered successfully. Then converting actual value to respective column type match as sql server database table
if (Jvalidate.FilterBlackLIstKeywords(columnArray))
{
this.obj_sqlcommand.Parameters.AddWithValue("@" + ColumnNames[i].ToString(), Jvalidate.RemoveHtmlTags(ColumnValues[i].ToString()));
}
else
{
break;
}
}
else if (CurrentColumnType == "Syste.Decimal")
{
//if black listed keywords are filtered successfully. Then converting actual value to respective column type match as sql server database table
if (Jvalidate.FilterBlackLIstKeywords(columnArray))
{
this.obj_sqlcommand.Parameters.AddWithValue("@" + ColumnNames[i].ToString(), Convert.ToDecimal(Jvalidate.RemoveHtmlTags(ColumnValues[i].ToString())));
}
else
{
break;
}
}
else if (CurrentColumnType == "System.Boolean")
{
//if black listed keywords are filtered successfully. Then converting actual value to respective column type match as sql server database table
if (Jvalidate.FilterBlackLIstKeywords(columnArray))
{
this.obj_sqlcommand.Parameters.AddWithValue("@" + ColumnNames[i].ToString(),
Convert.ToBoolean(Jvalidate.RemoveHtmlTags(ColumnValues[i].ToString())));
}
else
{
break;
}
}
else if (CurrentColumnType == "System.Int32")
{
//if black listed keywords are filtered successfully. Then converting actual value to respective column type match as sql server database table
if (Jvalidate.FilterBlackLIstKeywords(columnArray))
{
this.obj_sqlcommand.Parameters.AddWithValue("@" + ColumnNames[i].ToString(),
Convert.ToInt32(Jvalidate.RemoveHtmlTags(ColumnValues[i].ToString())));
}
else
{
break;
}
}
}
//Opening connection
OpenConnection();
//Executing query.
RowsAffected = this.obj_sqlcommand.ExecuteNonQuery();
}
//Finally closing the connetion to database
finally
{
CloseConnection();
}
#endregion
#endregion
//Return number of records affected by query execution.
return(RowsAffected);
}