Universal Search Engine for MS Access database

Universal Search Engine for MS Access database

<script runat="server">
OleDbConnection conn;
int vCount = 0;
string cStr = null;

void Page_Load(Object Sender, EventArgs E)
{
MakeConnString();

if (!IsPostBack)
{
ListTables();
}

}

void SearchMe(Object Sender, EventArgs E)
{
conn = new OleDbConnection(cStr);
conn.Open();

string Sql = "select * from " + RadioButtonList1.SelectedItem.Value;
OleDbDataAdapter dAd = new OleDbDataAdapter(Sql, conn);
DataSet dSet = new DataSet();
dAd.Fill (dSet, "ThisTable");
dAd = null;


string vSearch = TextBox1.Text.ToString().ToLower();

string vRestString = null;
Regex r = new Regex(vSearch); int vNum = 0;
string vSearchInto = null;

StringBuilder vString = new StringBuilder("<table cellpadding='3' cellspacing='1' border='1' style='border-collapse:collapse;' class='tblParent'>", 5000);

for (int i = 0; i < dSet.Tables["ThisTable"].Rows.Count; i++)
{
vSearchInto = null;

for (int j = 0; j < dSet.Tables["ThisTable"].Columns.Count; j++)
{
vSearchInto += dSet.Tables["ThisTable"].Rows[i][j].ToString().ToLower()+" ";
}
//write search results
vRestString = null;
bool t = r.IsMatch(vSearchInto);
if (t)
{
vCount++;
vString.Append("<tr><td>"+ vCount + ".</td>");
foreach (DataColumn j in dSet.Tables["ThisTable"].Columns)
{
if (dSet.Tables["ThisTable"].Rows[i][j].ToString().Trim() != "")
{
vNum++;
if (vNum == 4) //add show/hide table
vString.Append("<td><a href=javascript:ShowResults('"+vCount+"')>more ...</a><div id='tblRes"+ vCount +"' style='display:none;'><table cellpadding='2' cellspacing='1' border='1' width='600' style='border-collapse:collapse;position:absolute;' class='tblMore'>");
if (vNum >= 4)
vString.Append("<tr valign='top'><td class='heading' width='100'>"+ j + "</td><td width='500'>" + Regex.Replace(dSet.Tables["ThisTable"].Rows[i][j].ToString(), vSearch, "<font color='blue'>"+ vSearch + "</font>", RegexOptions.IgnoreCase) + "</td></tr>");
else
vString.Append("<td class='heading'>"+ j + "</td><td>" + Regex.Replace(dSet.Tables["ThisTable"].Rows[i][j].ToString(), vSearch, "<font color='blue'>"+ vSearch + "</font>", RegexOptions.IgnoreCase) + "</td>");
}
}
if (vNum > 4) //close show/hide table
vString.Append("</table></div></td></tr>");
else
vString.Append("</tr>");
vNum = 0;

}
}
vString.Append("</table>");
Label1.Text = vString.ToString();

lblStatus.Text = "Searching <font color='red'> " + RadioButtonList2.SelectedItem.Value + "->"+ RadioButtonList1.SelectedItem.Value +"</font> for <font color='red'>"+ vSearch + "</font>, " + vCount + " results found.";

conn.Close();

}



void ListTables()
{
conn = new OleDbConnection(cStr);
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null, null, null, "TABLE"});
RadioButtonList1.Items.Clear();
for (int i = 0; i < schemaTable.Rows.Count ; i++)
{
RadioButtonList1.Items.Add (schemaTable.Rows[i]["TABLE_NAME"].ToString());
}
conn.Close();
}

void ChangeMe(Object Sender, EventArgs E)
{
MakeConnString();
ListTables();
}

void MakeConnString()
{
switch (RadioButtonList2.SelectedItem.Value)
{
case "ABC" :
cStr = ConfigurationSettings.AppSettings["AConn"];
break;
case "EFG" :
cStr = ConfigurationSettings.AppSettings["BConn"];
break;
case "FGGD" :
cStr = ConfigurationSettings.AppSettings["CConn"];
break;
case "FDSAF" :
cStr = ConfigurationSettings.AppSettings["DConn"];
break;
case "fixdate" :
cStr = ConfigurationSettings.AppSettings["EConn"];
break;
default :
cStr = ConfigurationSettings.AppSettings["AConn"];
break;
}

}

</script>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Searching Ms-Access database easy here by Sheo Narayan</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
body {
margin-left: 0px;
margin-top: 0px;
}
body {
font-family: Arial, Helvetica, sans-serif;
font-size: 8pt;
}
.heading {
color:#339900;
}
.tblMore
{
background-color:#F0F0F0;

}
.tblParent
{
font-size:10pt;
}
.style1 {
color: #FFFFFF;
font-weight: bold;
}
-->
</style>
<script language="javascript">
var vLoop = <%=vCount%>;
function ShowResults(id)
{
for (var i = 1; i <= vLoop; i++)
{
if (i==id)
document.getElementById('tblRes'+i).style.display==''?document.getElementById('tblRes'+i).style.display='none': document.getElementById('tblRes'+i).style.display='';
else
document.getElementById('tblRes'+i).style.display='none';
}
}
</script>

</head>
<body>
<form runat="server">
<table width="100%" style="border-collapse:collapse;" border="1" cellpadding="2" cellspacing="1" bordercolor="#996600">
<tr align="center" bgcolor="#6699FF">
<td colspan="3"><span class="style1">Search into tables </span></td>
</tr>
<tr valign="top">
<td>Select database </td>
<td><asp:RadioButtonList ID="RadioButtonList2" runat="server" RepeatLayout="table" RepeatDirection="Horizontal"
OnSelectedIndexChanged="ChangeMe" AutoPostBack="true">
<asp:ListItem value="clinic" Selected="true">clinic</asp:ListItem>
<asp:ListItem value="fixdate">fixdate</asp:ListItem>
<asp:ListItem value="pasadena">pasadena</asp:ListItem>
<asp:ListItem value="transaction">transaction</asp:ListItem>
<asp:ListItem value="verify">verify</asp:ListItem>
</asp:RadioButtonList></td>
<td> </td>
</tr>
<tr valign="top">
<td width="22%">Search </td>
<td width="64%"><asp:TextBox ID="TextBox1" runat="server" AutoPostBack="false" />
<asp:RequiredFieldValidator ControlToValidate="TextBox1" runat="server" Display="Dynamic" Text="*"></asp:RequiredFieldValidator> </td>
<td width="14%"><asp:Button ID="Button1" Text=" Search " runat="server" OnClick="SearchMe" /></td>
</tr>
<tr valign="top">
<td>Search into </td>
<td><asp:RadioButtonList ID="RadioButtonList1" runat="server" RepeatLayout="table" RepeatDirection="Horizontal" RepeatColumns="5"></asp:RadioButtonList> </td>
<td><asp:RequiredFieldValidator ControlToValidate="RadioButtonList1" runat="server" Display="Dynamic" Text="*"></asp:RequiredFieldValidator></td>
</tr>
<tr valign="top">
<td colspan="3"><asp:Label ID="lblStatus" runat="server" ForeColor="#0033CC" EnableViewState="false" Font-Size="12" Font-Bold="true" /> <br>
<asp:Label ID="Label1" runat="server" EnableViewState="false" /></td>
</tr>
<tr valign="top">
<td> </td>
<td> </td>
<td> </td>
</tr>
</table>
</form>
</body>
</html>




I have used the value of connection string from web.config file.

like
<add key="AConn" value="Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\InetPub\ABC\db\A.mdb;" />

So, what I have done in this article is just listed the tables from the database using schema and when the Search button is clicked. It catches all records from the table and make all fields data as a string and then search into it using Regular expression.

Hope this will be solution of someone else problem
.
<script runat="server">
OleDbConnection conn;
int vCount = 0;
string cStr = null;

void Page_Load(Object Sender, EventArgs E)
{
MakeConnString();

if (!IsPostBack)
{
ListTables();
}

}

void SearchMe(Object Sender, EventArgs E)
{
conn = new OleDbConnection(cStr);
conn.Open();

string Sql = "select * from " + RadioButtonList1.SelectedItem.Value;
OleDbDataAdapter dAd = new OleDbDataAdapter(Sql, conn);
DataSet dSet = new DataSet();
dAd.Fill (dSet, "ThisTable");
dAd = null;


string vSearch = TextBox1.Text.ToString().ToLower();

string vRestString = null;
Regex r = new Regex(vSearch); int vNum = 0;
string vSearchInto = null;

StringBuilder vString = new StringBuilder("<table cellpadding='3' cellspacing='1' border='1' style='border-collapse:collapse;' class='tblParent'>", 5000);

for (int i = 0; i < dSet.Tables["ThisTable"].Rows.Count; i++)
{
vSearchInto = null;

for (int j = 0; j < dSet.Tables["ThisTable"].Columns.Count; j++)
{
vSearchInto += dSet.Tables["ThisTable"].Rows[i][j].ToString().ToLower()+" ";
}
//write search results
vRestString = null;
bool t = r.IsMatch(vSearchInto);
if (t)
{
vCount++;
vString.Append("<tr><td>"+ vCount + ".</td>");
foreach (DataColumn j in dSet.Tables["ThisTable"].Columns)
        {
if (dSet.Tables["ThisTable"].Rows[i][j].ToString().Trim() != "")
          {
vNum++;
if (vNum == 4) //add show/hide table
vString.Append("<td><a href=javascript:ShowResults('"+vCount+"')>more ...</a><div id='tblRes"+ vCount +"' style='display:none;'><table cellpadding='2' cellspacing='1' border='1' width='600' style='border-collapse:collapse;position:absolute;' class='tblMore'>");
if (vNum >= 4)
vString.Append("<tr valign='top'><td class='heading' width='100'>"+ j  + "</td><td width='500'>" + Regex.Replace(dSet.Tables["ThisTable"].Rows[i][j].ToString(), vSearch, "<font color='blue'>"+ vSearch +  "</font>", RegexOptions.IgnoreCase) + "</td></tr>");
else
vString.Append("<td class='heading'>"+ j  + "</td><td>" + Regex.Replace(dSet.Tables["ThisTable"].Rows[i][j].ToString(), vSearch, "<font color='blue'>"+ vSearch +  "</font>", RegexOptions.IgnoreCase) + "</td>");
  }
        }
if (vNum > 4) //close show/hide table
vString.Append("</table></div></td></tr>");
else
vString.Append("</tr>");
vNum = 0;

}
}
vString.Append("</table>");
Label1.Text = vString.ToString();

lblStatus.Text = "Searching <font color='red'> " + RadioButtonList2.SelectedItem.Value + "->"+ RadioButtonList1.SelectedItem.Value +"</font> for <font color='red'>"+ vSearch + "</font>, " + vCount + " results found.";

conn.Close();

}



void ListTables()
{
conn = new OleDbConnection(cStr);
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null, null, null, "TABLE"});
RadioButtonList1.Items.Clear();
for (int i = 0; i < schemaTable.Rows.Count ; i++)
{
RadioButtonList1.Items.Add (schemaTable.Rows[i]["TABLE_NAME"].ToString());
}
conn.Close();
}

void ChangeMe(Object Sender, EventArgs E)
{
MakeConnString();
ListTables();
}

void MakeConnString()
{
switch (RadioButtonList2.SelectedItem.Value)
{
case "ABC" :
cStr = ConfigurationSettings.AppSettings["AConn"];
break;
case "EFG" :
cStr = ConfigurationSettings.AppSettings["BConn"];
break;
case "FGGD" :
cStr = ConfigurationSettings.AppSettings["CConn"];
break;
case "FDSAF" :
cStr = ConfigurationSettings.AppSettings["DConn"];
break;
case "fixdate" :
cStr = ConfigurationSettings.AppSettings["EConn"];
break;
default :
cStr = ConfigurationSettings.AppSettings["AConn"];
break;
}

}

</script>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Searching Ms-Access database easy here by Sheo Narayan</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
body {
margin-left: 0px;
margin-top: 0px;
}
body {
font-family: Arial, Helvetica, sans-serif;
font-size: 8pt;
}
.heading {
color:#339900;
}
.tblMore
{
background-color:#F0F0F0;

}
.tblParent
{
font-size:10pt;
}
.style1 {
color: #FFFFFF;
font-weight: bold;
}
-->
</style>
<script language="javascript">
var vLoop = <%=vCount%>;
function ShowResults(id)
{
for (var i = 1; i <= vLoop; i++)
{
if (i==id)
document.getElementById('tblRes'+i).style.display == ''?document.getElementById('tblRes'+i).style.display='none': document.getElementById('tblRes'+i).style.display='';
else
document.getElementById('tblRes'+i).style.display='none';
}
}
</script>

</head>
<body>
<form runat="server">
  <table width="100%" style="border-collapse:collapse;" border="1" cellpadding="2" cellspacing="1" bordercolor="#996600">
    <tr align="center" bgcolor="#6699FF">
      <td colspan="3"><span class="style1">Search into tables </span></td>
    </tr>
    <tr valign="top">
      <td>Select database </td>
      <td><asp:RadioButtonList ID="RadioButtonList2" runat="server" RepeatLayout="table" RepeatDirection="Horizontal"
  OnSelectedIndexChanged="ChangeMe" AutoPostBack="true">
        <asp:ListItem value="clinic" Selected="true">clinic</asp:ListItem>
        <asp:ListItem value="fixdate">fixdate</asp:ListItem>
<asp:ListItem value="pasadena">pasadena</asp:ListItem>
        <asp:ListItem value="transaction">transaction</asp:ListItem>
<asp:ListItem value="verify">verify</asp:ListItem>
      </asp:RadioButtonList></td>
      <td> </td>
    </tr>
    <tr valign="top">
      <td width="22%">Search </td>
      <td width="64%"><asp:TextBox ID="TextBox1" runat="server" AutoPostBack="false" />
    <asp:RequiredFieldValidator ControlToValidate="TextBox1" runat="server" Display="Dynamic" Text="*"></asp:RequiredFieldValidator>   </td>
      <td width="14%"><asp:Button ID="Button1" Text="   Search   " runat="server" OnClick="SearchMe" /></td>
    </tr>
    <tr valign="top">
      <td>Search into </td>
      <td><asp:RadioButtonList ID="RadioButtonList1" runat="server" RepeatLayout="table" RepeatDirection="Horizontal" RepeatColumns="5"></asp:RadioButtonList>         </td>
      <td><asp:RequiredFieldValidator ControlToValidate="RadioButtonList1" runat="server" Display="Dynamic" Text="*"></asp:RequiredFieldValidator></td>
    </tr>
    <tr valign="top">
      <td colspan="3"><asp:Label ID="lblStatus" runat="server" ForeColor="#0033CC" EnableViewState="false" Font-Size="12" Font-Bold="true" />      <br>
      <asp:Label ID="Label1" runat="server" EnableViewState="false" /></td>
    </tr>
    <tr valign="top">
      <td> </td>
      <td> </td>
      <td> </td>
    </tr>
  </table>
</form>
</body>
</html>




I have used the value of connection string from web.config file.

like
<add key="AConn" value="Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\InetPub\ABC\db\A.mdb;" />


So, what I have done in this article is just listed the tables from the database using schema and when the Search button is clicked. It catches all records from the table and make all fields data as a string and then search into it using Regular expression.

Hope this will be solution of someone else problem.
By shailesh joge   Popularity  (2203 Views)
Biography - shailesh joge
I am working as a Software developer in C# ASP.NET.