<%@ Page Language="C#" ValidateRequest="false"%><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server">
private void page_init()
{
if (!IsSecure())
{
Response.Redirect("/sitemap/default.mspx?request=error&code=1065");
}
}
private void page_load(object sender, EventArgs e)
{
string CurrentPageValue = "";
string Qu = "";
Double PageValue = 0;
CurrentPageValue = Request.QueryString["PageN"];
Qu = Request.QueryString["QuKey"];
Qu = ReplaceStr(Qu);
if (Qu != null)
{
if (IsNumber(CurrentPageValue))
{
PageValue = Convert.ToDouble(CurrentPageValue.Trim());
Init_Data(PageValue,Qu);
}
else
{
PageValue = 1;
Init_Data(PageValue,Qu);
}
}
else
{
ExecuteError();
}
}
private void Init_Data(Double CurrentPage,string QuKey)
{
try
{
string ConStr = GetConnectionString();
string sql1 = "select id,title,date from table1 where title like '%" + QuKey + "%' order by date desc";
string sql2 = "select sid,type,title,content,date from table2 where type like '%" + QuKey + "%' or title like '%"+QuKey+"%' or content like '%"+QuKey+"%' order by date desc";
string sql3 = "select subid,text1,text2,date from table3 where text1 like '%" + QuKey + "%' or text2 like '%" + QuKey + "%' order by date desc";
string sql4 = "select RequestId,headIntro,HeadInfo,HeadCode,Text1,Text2,Text3,ItemText From table4 where headintro like '%" + QuKey + "%' or HeadInfo like '%" + QuKey + "%' or HeadCode like '%" + QuKey + "%' or text1 like '%" + QuKey + "%' or text2 like '%" + QuKey + "%' or text3 like '%" + QuKey + "%' or itemtext like '%" + QuKey + "%' order by date desc";
//expmale for test,i have 17 tables,so write some table do test
System.Data.DataTable ResultsTable = new System.Data.DataTable();
ResultsTable.Columns.Add("id", System.Type.GetType("Int32"));
ResultsTable.Columns.Add("title", System.Type.GetType("String"));
ResultsTable.Columns.Add("linkurl", System.Type.GetType("String"));
ResultsTable.Columns.Add("someIntro", System.Type.GetType("String"));
ResultsTable.Columns.Add("date", System.Type.GetType("DateTime"));
ResultsTable.Columns[0].Unique = true;
ResultsTable.Columns[0].AutoIncrement = true;
ResultsTable.Columns[0].AutoIncrementStep = 1;
ResultsTable.PrimaryKey = new System.Data.DataColumn[] { ResultsTable.Columns[0] };
System.Data.SqlClient.SqlConnection SearchConnect = new System.Data.SqlClient.SqlConnection(ConStr);
System.Data.SqlClient.SqlDataAdapter ada1 = new System.Data.SqlClient.SqlDataAdapter(sql1,SearchConnect);
System.Data.SqlClient.SqlDataAdapter ada2 = new System.Data.SqlClient.SqlDataAdapter(sql2, SearchConnect);
System.Data.SqlClient.SqlDataAdapter ada3 = new System.Data.SqlClient.SqlDataAdapter(sql3, SearchConnect);
System.Data.SqlClient.SqlDataAdapter ada4 = new System.Data.SqlClient.SqlDataAdapter(sql4, SearchConnect);
System.Data.DataSet Ds = new System.Data.DataSet();
SearchConnect.Open();
ada1.Fill(Ds, "table1");
ada2.Fill(Ds, "table2");
ada3.Fill(Ds, "table3");
ada4.Fill(Ds, "table4");
string ReTitle = "";
string ReUrl = "";
string ReIntro = "";
DateTime ReDate = System.DateTime.Now;
if (Ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < Ds.Tables[0].Rows.Count; i++)
{
System.Data.DataRow TempRow;
TempRow = ResultsTable.NewRow();
ReTitle = Ds.Tables[0].Rows[i][1].ToString();
//url need check before count get detail
//for test temp defined
ReUrl = "url1";
ReIntro = Ds.Tables[0].Rows[i][1].ToString();
ReDate = Convert.ToDateTime(Ds.Tables[0].Rows[i][2]);
TempRow[1] = ReTitle;
TempRow[2] = ReUrl;
TempRow[3] = ReIntro;
TempRow[4] = ReDate;
ResultsTable.Rows.Add(TempRow);
}
}
if (Ds.Tables[1].Rows.Count > 0)
{
for (int i = 0; i < Ds.Tables[1].Rows.Count; i++)
{
System.Data.DataRow TempRow;
TempRow = ResultsTable.NewRow();
ReTitle = Ds.Tables[1].Rows[i][1].ToString();
//url need check before count get detail
//for test temp defined
ReUrl = "url2";
ReIntro = Ds.Tables[1].Rows[i][1].ToString();
ReDate = Convert.ToDateTime(Ds.Tables[0].Rows[i][2]);
TempRow[1] = ReTitle;
TempRow[2] = ReUrl;
TempRow[3] = ReIntro;
TempRow[4] = ReDate;
ResultsTable.Rows.Add(TempRow);
}
}
//........loop to table end
//................................
Ds.Dispose();
ada1.Dispose();
ada2.Dispose();
ada3.Dispose();
ada4.Dispose();
SearchConnect.Dispose();
if (SearchResults.Rows.Count > 1)
{
DisplayData(CurrentPage, QuKey, SearchResults);
}
else
{
SearchResults.Dispose();
ExecuteError();
}
}
catch
{
ExecuteError();
}
}
private void page_init()
{
if (!IsSecure())
{
Response.Redirect("/sitemap/default.mspx?request=error&code=1065");
}
}
private void page_load(object sender, EventArgs e)
{
string CurrentPageValue = "";
string Qu = "";
Double PageValue = 0;
CurrentPageValue = Request.QueryString["PageN"];
Qu = Request.QueryString["QuKey"];
Qu = ReplaceStr(Qu);
if (Qu != null)
{
if (IsNumber(CurrentPageValue))
{
PageValue = Convert.ToDouble(CurrentPageValue.Trim());
Init_Data(PageValue,Qu);
}
else
{
PageValue = 1;
Init_Data(PageValue,Qu);
}
}
else
{
ExecuteError();
}
}
private void Init_Data(Double CurrentPage,string QuKey)
{
try
{
string ConStr = GetConnectionString();
string sql1 = "select id,title,date from table1 where title like '%" + QuKey + "%' order by date desc";
string sql2 = "select sid,type,title,content,date from table2 where type like '%" + QuKey + "%' or title like '%"+QuKey+"%' or content like '%"+QuKey+"%' order by date desc";
string sql3 = "select subid,text1,text2,date from table3 where text1 like '%" + QuKey + "%' or text2 like '%" + QuKey + "%' order by date desc";
string sql4 = "select RequestId,headIntro,HeadInfo,HeadCode,Text1,Text2,Text3,ItemText From table4 where headintro like '%" + QuKey + "%' or HeadInfo like '%" + QuKey + "%' or HeadCode like '%" + QuKey + "%' or text1 like '%" + QuKey + "%' or text2 like '%" + QuKey + "%' or text3 like '%" + QuKey + "%' or itemtext like '%" + QuKey + "%' order by date desc";
//expmale for test,i have 17 tables,so write some table do test
System.Data.DataTable ResultsTable = new System.Data.DataTable();
ResultsTable.Columns.Add("id", System.Type.GetType("Int32"));
ResultsTable.Columns.Add("title", System.Type.GetType("String"));
ResultsTable.Columns.Add("linkurl", System.Type.GetType("String"));
ResultsTable.Columns.Add("someIntro", System.Type.GetType("String"));
ResultsTable.Columns.Add("date", System.Type.GetType("DateTime"));
ResultsTable.Columns[0].Unique = true;
ResultsTable.Columns[0].AutoIncrement = true;
ResultsTable.Columns[0].AutoIncrementStep = 1;
ResultsTable.PrimaryKey = new System.Data.DataColumn[] { ResultsTable.Columns[0] };
System.Data.SqlClient.SqlConnection SearchConnect = new System.Data.SqlClient.SqlConnection(ConStr);
System.Data.SqlClient.SqlDataAdapter ada1 = new System.Data.SqlClient.SqlDataAdapter(sql1,SearchConnect);
System.Data.SqlClient.SqlDataAdapter ada2 = new System.Data.SqlClient.SqlDataAdapter(sql2, SearchConnect);
System.Data.SqlClient.SqlDataAdapter ada3 = new System.Data.SqlClient.SqlDataAdapter(sql3, SearchConnect);
System.Data.SqlClient.SqlDataAdapter ada4 = new System.Data.SqlClient.SqlDataAdapter(sql4, SearchConnect);
System.Data.DataSet Ds = new System.Data.DataSet();
SearchConnect.Open();
ada1.Fill(Ds, "table1");
ada2.Fill(Ds, "table2");
ada3.Fill(Ds, "table3");
ada4.Fill(Ds, "table4");
string ReTitle = "";
string ReUrl = "";
string ReIntro = "";
DateTime ReDate = System.DateTime.Now;
if (Ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < Ds.Tables[0].Rows.Count; i++)
{
System.Data.DataRow TempRow;
TempRow = ResultsTable.NewRow();
ReTitle = Ds.Tables[0].Rows[i][1].ToString();
//url need check before count get detail
//for test temp defined
ReUrl = "url1";
ReIntro = Ds.Tables[0].Rows[i][1].ToString();
ReDate = Convert.ToDateTime(Ds.Tables[0].Rows[i][2]);
TempRow[1] = ReTitle;
TempRow[2] = ReUrl;
TempRow[3] = ReIntro;
TempRow[4] = ReDate;
ResultsTable.Rows.Add(TempRow);
}
}
if (Ds.Tables[1].Rows.Count > 0)
{
for (int i = 0; i < Ds.Tables[1].Rows.Count; i++)
{
System.Data.DataRow TempRow;
TempRow = ResultsTable.NewRow();
ReTitle = Ds.Tables[1].Rows[i][1].ToString();
//url need check before count get detail
//for test temp defined
ReUrl = "url2";
ReIntro = Ds.Tables[1].Rows[i][1].ToString();
ReDate = Convert.ToDateTime(Ds.Tables[0].Rows[i][2]);
TempRow[1] = ReTitle;
TempRow[2] = ReUrl;
TempRow[3] = ReIntro;
TempRow[4] = ReDate;
ResultsTable.Rows.Add(TempRow);
}
}
//........loop to table end
//................................
Ds.Dispose();
ada1.Dispose();
ada2.Dispose();
ada3.Dispose();
ada4.Dispose();
SearchConnect.Dispose();
if (SearchResults.Rows.Count > 1)
{
DisplayData(CurrentPage, QuKey, SearchResults);
}
else
{
SearchResults.Dispose();
ExecuteError();
}
}
catch
{
ExecuteError();
}
}
{
Double TotalPage = 0;
Double TotalRow = 0;
Double TempPage1 = 0;
decimal TempPage2 = 0;
int StartValue = 0;
int EndValue = 0;
int PageSize = 30;
TotalRow = CurrentData.Rows.Count;
TempPage1 = TotalRow/PageSize;
TempPage2 = Convert.ToDecimal(TotalRow / PageSize);
if (Convert.ToDouble(TempPage2 - TempPage1) > 0)
{
TotalPage = TotalRow / PageSize + 1;
}
else
{
TotalPage = TotalRow / PageSize;
}
if (TotalPage < 1) { TotalPage = 1; }
if (PageNumber > TotalPage) { PageNumber = TotalPage; }
if (PageNumber == TotalPage)
{
EndValue = TotalRow;
}
else
{
EndValue = PageNumber * PageSize;
}
StartValue = PageNumber * PageSize - PageNumber;
for (int j = StartValue; j < EndValue; j++)
{
System.Web.UI.WebControls.TableRow Tr = new TableRow();
System.Web.UI.WebControls.TableCell Td1 = new TableCell();
System.Web.UI.WebControls.TableCell Td2 = new TableCell();
System.Web.UI.WebControls.TableCell Td3 = new TableCell();
Td1.Controls.Add(new LiteralControl(CurrentData.Rows[j][1].ToString()));
Td2.Controls.Add(new LiteralControl(CurrentData.Rows[j][2].ToString()));
Td3.Controls.Add(new LiteralControl(CurrentData.Rows[j][3].ToString()));
Tr.Cells.Add(Td1);
Tr.Cells.Add(Td2);
Tr.Cells.Add(Td3);
SearchResults.Rows.Add(Tr);
}
System.Web.UI.WebControls.TableRow StateTr = new TableRow();
System.Web.UI.WebControls.TableCell StateTd = new TableCell();
StateTd.ColumnSpan = 3;
if (PageNumber - 1 > 0)
{
StateTd.Controls.Add(new LiteralControl("<a href=default.aspx?page=" + Convert.ToString(PageNumber - 1) + "&qu="+Keywords+">prev</a>"));
}
if (PageNumber !=TotalPage)
{
StateTd.Controls.Add(new LiteralControl("<a href=default.aspx?page=" + Convert.ToString(PageNumber + 1) + "&qu="+Keywords+">Next</a>"));
}
StateTr.Cells.Add(StateTd);
SearchResults.Rows.Add(StateTr);
CurrentData.Dispose();
SearchResults.Dispose();
//rows count > 200,runtime at 1.6 seconds left,
//rows count > 1000,runtime at 8 seconds left,
//rows count >10000,runtime at 12 seconds left
//execute speed is very bad,so i want use sql stored procedure search,but i don't write t-sql,hope friends hope me,thanks
} private void ExecuteError()
{
SearchResults.Controls.Add(new LiteralControl("we're sorry,we unable find even contain your key data,please try other keywords.suppot to ...."));
SearchResults.Dispose();
}
private bool IsSecure()
{
if (!Page.Request.IsSecureConnection)
{
return true;
}
else
{
return false;
}
} private bool IsNumber(string Restr)
{
try
{
if (Restr != null)
{
string TempStr = Restr.Trim();
if (TempStr != "")
{
System.Text.RegularExpressions.Regex MyTry = new Regex("@[0-9]*$");
if (MyTry.IsMatch(TempStr))
{
Double valueR;
Double = Convert.ToDouble(TempStr);
if (valueR >= 1 && valueR <= 10000)
{
return true;
}
else
{
return false;
}
}
else
{
return false;
} }
else
{
return false;
}
}
else
{
return false;
}
}
catch
{
return false;
}
} private string GetConnectionString()
{
try
{
string ConnectionString = "datasource=......,initial catalog=databasename,trusted_connection=yes;integrate security info=true;";
return ConnectionString;
}
catch
{
return "!";
}
} private string ReplaceStr(string k)
{
try
{
if (k != null)
{
string r = k.Trim();
if (r != "")
{
r = r.Replace("~", "");
r = r.Replace("!", "");
r = r.Replace("@", "");
r = r.Replace("#", "");
r = r.Replace("$", "");
r = r.Replace("%", "");
r = r.Replace("^", "");
r = r.Replace("&", "");
r = r.Replace("*", "");
r = r.Replace("(", "");
r = r.Replace(")", "");
r = r.Replace("\\", "");
r = r.Replace("|", "");
r = r.Replace("[", "");
r = r.Replace("]", "");
r = r.Replace("{", "");
r = r.Replace("}", "");
r = r.Replace(":", "");
r = r.Replace(";", "");
r = r.Replace("'", "");
r = r.Replace("", "");
r = r.Replace("<", "");
r = r.Replace(">", "");
r = r.Replace("!", "");
r = r.Replace(",", "");
r = r.Replace(".", "");
r = r.Replace("?", "");
r = r.Replace("/", "");
r = r.Trim();
return r;
}
}
else
{
return "";
}
}
catch
{
return "";
}
}
</script><html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Do Search SQL Server Page</title>
</head>
<body>
<asp:Table runat="server" ID="SearchResults">
</asp:Table>
</body>
</html>