public static string getTableDataWhole(string tableName)
{
string sql = string.Format("select * from [{0}]", tableName);
DataBaseTable db = new DataBaseTable();
db.setTableName(tableName);
string[] names = db.getColumnNames();
int length = names.Length;
SqlDataReader s = executeQuery(sql);
string table = "<table border=\"1\">";
table += "<tr>";
for (int i = 0; i < length; i++)
{
table += string.Format("<th>{0}</th>", names[i]);
}
table += "</tr>";
if (s != null)
{
while (s.Read())
{
table += "<tr>\n";
for (int i = 0; i < length; i++)
{
table += string.Format("<td>{0}</td>", s.GetValue(i).ToString());
}
table += "<tr/>";
}
}//if
table += "</table>";
return table;
}
}//buildSql function
private static string buildSql(List<string[]> conditions, string tableName)
{
DataBaseTable db = new DataBaseTable();
db.setTableName(tableName);
string[] names = db.getColumnNames();
string[] types = getDataTypes(tableName);
int length = conditions.Count;//names.Length;
string sql = string.Format("SELECT * FROM [{0}] ", tableName);
string text = "";
string and = " ";
int count = 0;
for (int i = 0; i < length; i++)
{
if (types[i] == "text")
{
string equals = conditions.ElementAt(i)[0];
string contains = conditions.ElementAt(i)[1];
if (equals != "")
{
text += string.Format("{0} {1} like @{2}_equals ", and, names[i], names[i]);
count++;
and = " AND ";
}
else if (contains != "")
{
if (count < 1)
{
text += string.Format("{0} {1} like @{2}_contains ", and, names[i], names[i]);
and = " AND ";
count++;
}
else
{
text += string.Format("{0} {1} like @{2}_contains ", and, names[i], names[i]);
}
}
}//if text
else if (types[i] == "number")
{
string equals = conditions.ElementAt(i)[0];
string lessThen = conditions.ElementAt(i)[1];
string greaterThen = conditions.ElementAt(i)[2];
if (equals != "")
{
text += string.Format(" {0} {1} = @{2}_equals ", and, names[i], names[i]);
and = " AND ";
count++;
}
if (lessThen != "")
{
if (count > 0)
text += string.Format("{0} {1} < @{2}_lessThen ", and, names[i], names[i]);
else
{
text += string.Format(" {0}< @{1}_lessThen ", names[i], names[i]);
and = " AND ";
count++;
}
}
if (greaterThen != "")
{
if (count > 0)
text += string.Format("{0} {1} > @{2}_greaterThen ", and, names[i], names[i]);
else
{
text += string.Format(" {0} > @{1}_greaterThen ", names[i], names[i]);
and = " AND ";
count++;
}
}
}//number
else if (types[i] == "date")
{
string equals = conditions.ElementAt(i)[0];
string lessThen = conditions.ElementAt(i)[1];
string greaterThen = conditions.ElementAt(i)[2];
if (equals != "")
{
text = string.Format(" {0} = @{1}_equals ", names[i], names[i]);
and = " AND ";
count++;
}
if (lessThen != "")
{
if (count > 0)
text += string.Format("{0} {1} < @{2}_lessThen ", and, names[i], names[i]);
else
{
text += string.Format(" {0}< @{1}_lessThen ", names[i], names[i]);
and = " AND ";
count++;
}
}
if (greaterThen != "")
{
if (count > 0)
text += string.Format("{0} {1} > @{2}_greaterThen ", and, names[i], names[i]);
else
{
text += string.Format(" {0} > @{1}_greaterThen ", names[i], names[i]);
and = " AND ";
count++;
}
}
}//date
}//for
if (text.Trim() != "")
{
sql += string.Format(" WHERE ({0}) ", text);
}
return sql;
}//buildSql function
public static string getFilteredTableData(List<string[]> conditions, string tableName)
{
DataBaseTable db = new DataBaseTable();
db.setTableName(tableName);
string[] names = db.getColumnNames();
string[] types = getDataTypes(tableName);
int length = names.Length;
string query = buildSql(conditions, tableName);
SqlCommand Sqlcommand = new SqlCommand(query, con);
for (int i = 0; i < length; i++)
{
if (types[i] == "text")
{
string equals = conditions.ElementAt(i)[0];
string contains = conditions.ElementAt(i)[1];
if (equals != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_equals", names[i]), string.Format("{0}", equals));
}
else if (contains != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_contains", names[i]), string.Format("%{0}%", contains));
}
}//if text
else if (types[i] == "number")
{
string equals = conditions.ElementAt(i)[0];
string lessThen = conditions.ElementAt(i)[1];
string greaterThen = conditions.ElementAt(i)[2];
if (equals != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_equals", names[i]), equals);
}
if (lessThen != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_lessThen", names[i]), lessThen);
}
if (greaterThen != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_greaterThen", names[i]), greaterThen);
}
}//number
else if (types[i] == "date")
{
string equals = conditions.ElementAt(i)[0];
string lessThen = conditions.ElementAt(i)[1];
string greaterThen = conditions.ElementAt(i)[2];
if (equals != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_equals", names[i]), string.Format("{0}", equals));
}
if (lessThen != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_lessThen", names[i]), string.Format("{0}", lessThen));
}
if (greaterThen != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_greaterThen", names[i]), string.Format("{0}", greaterThen));
}
}//date
}//for
SqlDataReader s = null;
try
{
Sqlcommand.Connection = con;
con.Close();
con.Open();
s = Sqlcommand.ExecuteReader();
}
catch (Exception e)
{
con.Close();
}
string table = "<div class=\"tableData\" > <table border=\"1\">";
table += "<tr>";
for (int i = 0; i < length; i++)
{
table += string.Format("<th>{0}</th>", names[i]);
}
table += "</tr>";
if (s != null)
{
while (s.Read())
{
table += "<tr>\n";
for (int i = 0; i < length; i++)
{
table += string.Format("<td>{0}</td>", s.GetValue(i).ToString());
}
table += "<tr/>";
}
}//if
table += "</table></div>";
if (s != null)
{
s.Close();
}
return table + query;
}
public static string getFilteredTableData(List<string> selects, List<string[]> conditions, string tableName)
{
DataBaseTable db = new DataBaseTable();
db.setTableName(tableName);
string[] names = selects.ToArray();
int length = names.Length;//selected names length
string[] types = new string[length];//selected names datatypes
string[] originalTableName = getNames(tableName);
string[] originalTableTypes = getDataTypes(tableName);
string temp = "";
for (int i = 0; i < length; i++)
{
temp = names[i];
for (int j = 0; j < originalTableName.Count(); j++)
{
if (temp == originalTableName.ElementAt(j))
{
types[i] = originalTableTypes[j];
break;
}
}
}//matching the datatypes
string select = " ";
for (int i = 0; i < selects.Count; i++)
{
select += selects.ElementAt(i);
if (i < selects.Count - 1)
{
select += ", ";
}
else
{
select += " ";
}
}
string query = buildSql(select, conditions, tableName);
SqlCommand Sqlcommand = new SqlCommand(query, con);
length = conditions.Count();
for (int i = 0; i < length; i++)
{
if (originalTableTypes[i] == "text")
{
string equals = conditions.ElementAt(i)[0];
string contains = conditions.ElementAt(i)[1];
if (equals != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_equals", originalTableName[i]), string.Format("{0}", equals));
}
else if (contains != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_contains", originalTableName[i]), string.Format("%{0}%", contains));
}
}//if text
else if (originalTableTypes[i] == "number")
{
string equals = conditions.ElementAt(i)[0];
string lessThen = conditions.ElementAt(i)[1];
string greaterThen = conditions.ElementAt(i)[2];
if (equals != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_equals", originalTableName[i]), equals);
}
if (lessThen != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_lessThen", originalTableName[i]), lessThen);
}
if (greaterThen != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_greaterThen", originalTableName[i]), greaterThen);
}
}//number
else if (originalTableTypes[i] == "date")
{
string equals = conditions.ElementAt(i)[0];
string lessThen = conditions.ElementAt(i)[1];
string greaterThen = conditions.ElementAt(i)[2];
if (equals != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_equals", originalTableName[i]), string.Format("{0}", equals));
}
if (lessThen != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_lessThen", originalTableName[i]), string.Format("{0}", lessThen));
}
if (greaterThen != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_greaterThen", originalTableName[i]), string.Format("{0}", greaterThen));
}
}//date
}//for
SqlDataReader s = null;
try
{
Sqlcommand.Connection = con;
con.Close();
con.Open();
s = Sqlcommand.ExecuteReader();
}
catch (Exception e)
{
con.Close();
}
length = names.Length;
string table = "<div class=\"tableData\" > <table border=\"1\">";
table += "<tr>";
for (int i = 0; i < length; i++)
{
table += string.Format("<th>{0}</th>", names[i]);
}
table += "</tr>";
if (s != null)
{
while (s.Read())
{
table += "<tr>\n";
for (int i = 0; i < length; i++)
{
table += string.Format("<td>{0}</td>", s.GetValue(i).ToString());
}
table += "<tr/>";
}
}//if
table += "</table></div>";
if (s != null)
{
s.Close();
}
return table + query;
}
private void filteringFormSubmittedActionWithSelected()
{
DataBaseTable dbTable = new DataBaseTable();
string tableName = Request.Form["tableName"];
dbTable.setTableName(tableName);
string[] names = dbTable.getColumnNames();
string[] dataTypes = new string[names.Length];
List<string[]> conditions = new List<string[]>();//list of array of where conditions for filtering
for (int i = 0; i < names.Length; i++)//get dataType for each field
{
dataTypes[i] = Request.Form[names[i] + "_dataType"];
}
//check if the user had manually changed the datatypes of the menu
bool flag = DatabaseAccessLayer.areDataTypesMatched(tableName, dataTypes);
if (flag)//ie datatypes are same in database and form submitted
{
for (int i = 0; i < names.Length; i++)
{
conditions.Add(createCondition3(names[i], dataTypes[i]));
}
Literal1.Text = DatabaseAccessLayer.getFilteringForm(tableName);//display filtering form
Literal2.Text = DatabaseAccessLayer.getFilteredTableData(select, conditions, tableName);//data
}
else
{
DatabaseAccessLayer.updateTableDataTypes(tableName, names, dataTypes);
Literal1.Text = DatabaseAccessLayer.getFilteringForm(tableName);
Literal2.Text = DatabaseAccessLayer.getTableDataWhole(tableName);
}
}
private static string buildSql(List<string[]> conditions, string tableName)
{
DataBaseTable db = new DataBaseTable();
db.setTableName(tableName);
string[] names = db.getColumnNames();
string[] types = getDataTypes(tableName);
int length = conditions.Count;//names.Length;
string sql = string.Format("SELECT * FROM [{0}] ", tableName);
string text = "";
string and = " ";
int count = 0;
for (int i = 0; i < length; i++)
{
if (types[i] == "text")
{
string equals = conditions.ElementAt(i)[0];
string contains = conditions.ElementAt(i)[1];
if (equals != "")
{
text += string.Format("{0} {1} like @{2}_equals ", and, names[i], names[i]);
count++;
and = " AND ";
}
else if (contains != "")
{
if (count < 1)
{
text += string.Format("{0} {1} like @{2}_contains ", and, names[i], names[i]);
and = " AND ";
count++;
}
else
{
text += string.Format("{0} {1} like @{2}_contains ", and, names[i], names[i]);
}
}
}//if text
else if (types[i] == "number")
{
string equals = conditions.ElementAt(i)[0];
string lessThen = conditions.ElementAt(i)[1];
string greaterThen = conditions.ElementAt(i)[2];
if (equals != "")
{
text += string.Format(" {0} {1} = @{2}_equals ", and, names[i], names[i]);
and = " AND ";
count++;
}
if (lessThen != "")
{
if (count > 0)
text += string.Format("{0} {1} < @{2}_lessThen ", and, names[i], names[i]);
else
{
text += string.Format(" {0}< @{1}_lessThen ", names[i], names[i]);
and = " AND ";
count++;
}
}
if (greaterThen != "")
{
if (count > 0)
text += string.Format("{0} {1} > @{2}_greaterThen ", and, names[i], names[i]);
else
{
text += string.Format(" {0} > @{1}_greaterThen ", names[i], names[i]);
and = " AND ";
count++;
}
}
}//number
else if (types[i] == "date")
{
string equals = conditions.ElementAt(i)[0];
string lessThen = conditions.ElementAt(i)[1];
string greaterThen = conditions.ElementAt(i)[2];
if (equals != "")
{
text = string.Format(" {0} = @{1}_equals ", names[i], names[i]);
and = " AND ";
count++;
}
if (lessThen != "")
{
if (count > 0)
text += string.Format("{0} {1} < @{2}_lessThen ", and, names[i], names[i]);
else
{
text += string.Format(" {0}< @{1}_lessThen ", names[i], names[i]);
and = " AND ";
count++;
}
}
if (greaterThen != "")
{
if (count > 0)
text += string.Format("{0} {1} > @{2}_greaterThen ", and, names[i], names[i]);
else
{
text += string.Format(" {0} > @{1}_greaterThen ", names[i], names[i]);
and = " AND ";
count++;
}
}
}//date
}//for
if (text.Trim() != "")
{
sql += string.Format(" WHERE ({0}) ", text);
}
return sql;
}
public static string getTableDataWhole(string tableName)
{
string sql = string.Format("select * from [{0}]", tableName);
DataBaseTable db = new DataBaseTable();
db.setTableName(tableName);
string[] names = db.getColumnNames();
int length = names.Length;
SqlDataReader s = executeQuery(sql);
string table = "<table border=\"1\">";
table += "<tr>";
for (int i = 0; i < length; i++)
{
table += string.Format("<th>{0}</th>", names[i]);
}
table += "</tr>";
if (s != null)
{
while (s.Read())
{
table += "<tr>\n";
for (int i = 0; i < length; i++)
{
table += string.Format("<td>{0}</td>", s.GetValue(i).ToString());
}
table += "<tr/>";
}
}//if
table += "</table>";
return table;
}
public static string getFilteredTableData(List<string[]> conditions, string tableName)
{
DataBaseTable db = new DataBaseTable();
db.setTableName(tableName);
string[] names = db.getColumnNames();
string[] types = getDataTypes(tableName);
int length = names.Length;
string query = buildSql(conditions, tableName);
SqlCommand Sqlcommand = new SqlCommand(query, con);
for (int i = 0; i < length; i++)
{
if (types[i] == "text")
{
string equals = conditions.ElementAt(i)[0];
string contains = conditions.ElementAt(i)[1];
if (equals != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_equals", names[i]), string.Format("{0}", equals));
}
else if (contains != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_contains", names[i]), string.Format("%{0}%", contains));
}
}//if text
else if (types[i] == "number")
{
string equals = conditions.ElementAt(i)[0];
string lessThen = conditions.ElementAt(i)[1];
string greaterThen = conditions.ElementAt(i)[2];
if (equals != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_equals", names[i]), equals);
}
if (lessThen != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_lessThen", names[i]), lessThen);
}
if (greaterThen != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_greaterThen", names[i]), greaterThen);
}
}//number
else if (types[i] == "date")
{
string equals = conditions.ElementAt(i)[0];
string lessThen = conditions.ElementAt(i)[1];
string greaterThen = conditions.ElementAt(i)[2];
if (equals != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_equals", names[i]), string.Format("{0}", equals));
}
if (lessThen != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_lessThen", names[i]), string.Format("{0}", lessThen));
}
if (greaterThen != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_greaterThen", names[i]), string.Format("{0}", greaterThen));
}
}//date
}//for
SqlDataReader s = null;
try
{
Sqlcommand.Connection = con;
con.Close();
con.Open();
s = Sqlcommand.ExecuteReader();
}
catch (Exception e)
{
con.Close();
}
string table = "<div class=\"tableData\" > <table border=\"1\">";
table += "<tr>";
for (int i = 0; i < length; i++)
{
table += string.Format("<th>{0}</th>", names[i]);
}
table += "</tr>";
if (s != null)
{
while (s.Read())
{
table += "<tr>\n";
for (int i = 0; i < length; i++)
{
table += string.Format("<td>{0}</td>", s.GetValue(i).ToString());
}
table += "<tr/>";
}
}//if
table += "</table></div>";
if (s != null)
{
s.Close();
}
return table + query;
}
public static string getFilteredTableData(List<string> selects, List<string[]> conditions, string tableName)
{
DataBaseTable db = new DataBaseTable();
db.setTableName(tableName);
string[] names = selects.ToArray();
int length = names.Length;//selected names length
string[] types = new string[length];//selected names datatypes
string[] originalTableName = getNames(tableName);
string[] originalTableTypes = getDataTypes(tableName);
string temp = "";
for (int i = 0; i < length; i++)
{
temp = names[i];
for (int j = 0; j < originalTableName.Count(); j++)
{
if (temp == originalTableName.ElementAt(j))
{
types[i] = originalTableTypes[j];
break;
}
}
}//matching the datatypes
string select = " ";
for (int i = 0; i < selects.Count; i++)
{
select += selects.ElementAt(i);
if (i < selects.Count - 1)
{
select += ", ";
}
else
{
select += " ";
}
}
string query = buildSql(select, conditions, tableName);
SqlCommand Sqlcommand = new SqlCommand(query, con);
length = conditions.Count();
for (int i = 0; i < length; i++)
{
if (originalTableTypes[i] == "text")
{
string equals = conditions.ElementAt(i)[0];
string contains = conditions.ElementAt(i)[1];
if (equals != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_equals", originalTableName[i]), string.Format("{0}", equals));
}
else if (contains != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_contains", originalTableName[i]), string.Format("%{0}%", contains));
}
}//if text
else if (originalTableTypes[i] == "number")
{
string equals = conditions.ElementAt(i)[0];
string lessThen = conditions.ElementAt(i)[1];
string greaterThen = conditions.ElementAt(i)[2];
if (equals != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_equals", originalTableName[i]), equals);
}
if (lessThen != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_lessThen", originalTableName[i]), lessThen);
}
if (greaterThen != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_greaterThen", originalTableName[i]), greaterThen);
}
}//number
else if (originalTableTypes[i] == "date")
{
string equals = conditions.ElementAt(i)[0];
string lessThen = conditions.ElementAt(i)[1];
string greaterThen = conditions.ElementAt(i)[2];
if (equals != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_equals", originalTableName[i]), string.Format("{0}", equals));
}
if (lessThen != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_lessThen", originalTableName[i]), string.Format("{0}", lessThen));
}
if (greaterThen != "")
{
Sqlcommand.Parameters.AddWithValue(string.Format("@{0}_greaterThen", originalTableName[i]), string.Format("{0}", greaterThen));
}
}//date
}//for
SqlDataReader s = null;
try
{
Sqlcommand.Connection = con;
con.Close();
con.Open();
s = Sqlcommand.ExecuteReader();
}
catch (Exception e)
{
con.Close();
}
length = names.Length;
string table = "<div class=\"tableData\" > <table border=\"1\">";
table += "<tr>";
for (int i = 0; i < length; i++)
{
table += string.Format("<th>{0}</th>", names[i]);
}
table += "</tr>";
if (s != null)
{
while (s.Read())
{
table += "<tr>\n";
for (int i = 0; i < length; i++)
{
table += string.Format("<td>{0}</td>", s.GetValue(i).ToString());
}
table += "<tr/>";
}
}//if
table += "</table></div>";
if (s != null)
{
s.Close();
}
return table + query;
}