public DataTable GetSingleTableByCondition(string tableName, SearchParameter sp)
{
try
{
if (dt == null)
{
dt = new DataTable();
}
string SqlStr = "";
SqlStr = "SELECT * FROM " + tableName;
ArrayList arrayList = sp.Keys();
SqlCommand sqlCommand = new SqlCommand();
for (int i = 0; i < arrayList.Count; i++)
{
if (i == 0)
{
switch (sp.GetHashTable[arrayList[i]].GetType().Name)
{
case "String":
SqlStr += " where " + arrayList[i].ToString().Replace(":", "") + " like '%" + sp.GetValue(arrayList[i].ToString()) + "%'";
break;
case "Int32":
SqlStr += " where " + arrayList[i].ToString().Replace(":", "") + "= " + sp.GetValue(arrayList[i].ToString()) + "";
break;
case "DateTime":
if (arrayList[i].ToString().Trim().ToUpper().Contains("_FROM"))
{
SqlStr += " where " + arrayList[i].ToString().Replace(":", "").Replace("_FROM", "") + ">= '" + sp.GetValue(arrayList[i].ToString()) + "'";
}
else if (arrayList[i].ToString().Trim().ToUpper().Contains("_TO"))
{
SqlStr += " where " + arrayList[i].ToString().Replace(":", "").Replace("_TO", "") + "<= '" + sp.GetValue(arrayList[i].ToString()) + "'";
}
break;
default:
SqlStr += " where " + arrayList[i].ToString().Replace(":", "") + " like '%" + sp.GetValue(arrayList[i].ToString()) + "%'";
break;
}
}
else
{
switch (sp.GetHashTable[arrayList[i]].GetType().Name)
{
case "String":
SqlStr += " and " + arrayList[i].ToString().Replace(":", "") + " like '%" + sp.GetValue(arrayList[i].ToString()) + "%'";
break;
case "Int32":
SqlStr += " and " + arrayList[i].ToString().Replace(":", "") + " = " + sp.GetValue(arrayList[i].ToString()) + "";
break;
case "DateTime":
if (arrayList[i].ToString().Trim().ToUpper().Contains("_FROM"))
{
SqlStr += " and " + arrayList[i].ToString().Replace(":", "").Replace("_FROM", "") + ">= '" + sp.GetValue(arrayList[i].ToString()) + "'";
}
else if (arrayList[i].ToString().Trim().ToUpper().Contains("_TO"))
{
SqlStr += " and " + arrayList[i].ToString().Replace(":", "").Replace("_TO", "") + "<= '" + sp.GetValue(arrayList[i].ToString()) + "'";
}
break;
default:
SqlStr += " and " + arrayList[i].ToString().Replace(":", "") + " like '%" + sp.GetValue(arrayList[i].ToString()) + "%'";
break;
}
}
}
sqlCommand.CommandText = SqlStr;
GetDataTable(sqlCommand, dt);
return(dt);
}
catch (Exception ex)
{
return(null);
}
finally
{
connection.Close();
}
}
public DataTable GetTableBySqlStrUnLike(string sqlStr, SearchParameter sp)
{
dt = new DataTable();
ArrayList arrayList = sp.Keys();
SqlCommand sqlCommand = new SqlCommand();
for (int i = 0; i < arrayList.Count; i++)
{
if (i == 0)
{
switch (sp.GetHashTable[arrayList[i]].GetType().Name)
{
case "String":
sqlStr += " where " + arrayList[i].ToString().Replace(":", "") + " like '" + sp.GetValue(arrayList[i].ToString()) + "'";
break;
case "Int32":
sqlStr += " where " + arrayList[i].ToString().Replace(":", "") + "= " + sp.GetValue(arrayList[i].ToString()) + "";
break;
case "DateTime":
if (arrayList[i].ToString().Trim().ToUpper().Contains("_FROM"))
{
sqlStr += " where " + arrayList[i].ToString().Replace(":", "").Replace("_FROM", "") + ">= '" + sp.GetValue(arrayList[i].ToString()) + "'";
}
else if (arrayList[i].ToString().Trim().ToUpper().Contains("_TO"))
{
sqlStr += " where " + arrayList[i].ToString().Replace(":", "").Replace("_TO", "") + "<= '" + sp.GetValue(arrayList[i].ToString()) + "'";
}
break;
default:
sqlStr += " where " + arrayList[i].ToString().Replace(":", "") + " like '" + sp.GetValue(arrayList[i].ToString()) + "'";
break;
}
}
else
{
switch (sp.GetHashTable[arrayList[i]].GetType().Name)
{
case "String":
sqlStr += " and " + arrayList[i].ToString().Replace(":", "") + " like '" + sp.GetValue(arrayList[i].ToString()) + "'";
break;
case "Int32":
sqlStr += " and " + arrayList[i].ToString().Replace(":", "") + " = " + sp.GetValue(arrayList[i].ToString()) + "";
break;
case "DateTime":
if (arrayList[i].ToString().Trim().ToUpper().Contains("_FROM"))
{
sqlStr += " and " + arrayList[i].ToString().Replace(":", "").Replace("_FROM", "") + ">= '" + sp.GetValue(arrayList[i].ToString()) + "'";
}
else if (arrayList[i].ToString().Trim().ToUpper().Contains("_TO"))
{
sqlStr += " and " + arrayList[i].ToString().Replace(":", "").Replace("_TO", "") + "<= '" + sp.GetValue(arrayList[i].ToString()) + "'";
}
break;
default:
sqlStr += " and " + arrayList[i].ToString().Replace(":", "") + " like '" + sp.GetValue(arrayList[i].ToString()) + "'";
break;
}
}
}
sqlCommand.CommandText = sqlStr;
GetDataTable(sqlCommand, dt);
return(dt);
}