Free asp.net web hosting Advertise with us

Login         Members       Shared Hosting   |                       Upgrade   |   Advertise   |   Tutorials   |  

Home » Resources » How to Search data in the MS Access database for ASP.NET application



ASP.NETADO.NETWeb ServicesRemotingVisual Studio 2005
Error BankInterview QuestionsTips & TricksXMLHTML
Jscript/JavascriptIISWindowsGeneral
Submit Resource or code snippet   ... and get surprise gifts ASP.NET web hosting   Win Digital camera, ASP.NET Books, Free softwares!!

How to Search data in the MS Access database for ASP.NET application

20 Jun, 2010
Author: Alhadith hazro
Summary
You can use LINQ-to-Dataset for searching data in the MS Access database for ASP.NET
.NET Classes used :



How to Search data in the MS Access database for ASP.NET application
Following demo provides you the quickest way to learn how to search data in the mdb database  while using asp.net. You can search data using LINQ-to-SQL, which is the easiest way to search. However, here I used an outside algorith (i.e. method) for best searching-result, and when I used this method with conjuction to LINQ-to-SQL, error occurred. So, as you will see here, this is LINQ-to-DataSet style code and uses rich-algorith for searching purpose. The algorith is encapsulated in the SoundEx method.
Here is how I used above technique:
namespace DashingData
{
public class DashingDataUser
{
private void OnSearchingData()
{
string connectionString = string.Format(Driver={{Microsoft Access Driver (*.mdb)}};DBQ={0}, ~/App_Data/YourDataFile.mdb);
           try
           {
               string queryString = select * FROM CustomerTable;

               using (OdbcConnection connection = new OdbcConnection(connectionString))
               {
                   OdbcDataAdapter adapter = new OdbcDataAdapter(queryString, connectionString);
                   DataSet dataset = new DataSet();

                   connection.Open();
                   adapter.Fill(dataset);

                   DataTable table = dataset.Tables[0];
                   string soundExCode = SoundEx(txtFind.Text);  // remember this - important
                   var query = table.AsEnumerable().Where(
                           x => x.Field(CustomerName).Equals(txtFind.Text)
                               || x.Field(CustomerName).Contains(txtFind.Text)
                               || SoundEx(x.Field(CustomerName)) == soundExCode // used here
                           ).Select(c => c);

                   DataView view = query.AsDataView();
                   view.RowStateFilter = DataViewRowState.CurrentRows;

                   var newQuery = from DataRowView c in view
                                  select new
                                  {
                                      No = c.Row.Field(CustomerID),
                                      Name = c.Row.Field(CustomerName),
                                      Father = c.Row.Field(FatherName),
                                      Phone = c.Row.Field(PhoneNumber),
                                      Address = c.Row.Field(CustomerAddress)
                                  };

                   GridView1.DataSource = newQuery.ToList();
                   GridView1.DataBind();
               }
           }
           catch (Exception ex)
           {
               lblError.Text = ex.Message;
           }
}

#region SoundEx method
       static private string SoundEx(string word)
       {
           // The length of the returned code.
           int length = 4;

           // Value to return.
           string value = ;

           // The size of the word to process.
           int size = word.Length;

           // The word must be at least two characters in length.
           if (size > 1)
           {
               // Convert the word to uppercase characters.
               word = word.ToUpper(System.Globalization.CultureInfo.InvariantCulture);

               // Convert the word to a character array.
               char[] chars = word.ToCharArray();

               // Buffer to hold the character codes.
               StringBuilder buffer = new StringBuilder();
               buffer.Length = 0;

               // The current and previous character codes.
               int prevCode = 0;
               int currCode = 0;

               // Add the first character to the buffer.
               buffer.Append(chars[0]);

               // Loop through all the characters and convert them to the proper character code.
               for (int i = 1; i < size; i++)
               {
                   switch (chars[i])
                   {
                       case 'A':
                       case 'E':
                       case 'I':
                       case 'O':
                       case 'U':
                       case 'H':
                       case 'W':
                       case 'Y':
                           currCode = 0;
                           break;
                       case 'B':
                       case 'F':
                       case 'P':
                       case 'V':
                           currCode = 1;
                           break;
                       case 'C':
                       case 'G':
                       case 'J':
                       case 'K':
                       case 'Q':
                       case 'S':
                       case 'X':
                       case 'Z':
                           currCode = 2;
                           break;
                       case 'D':
                       case 'T':
                           currCode = 3;
                           break;
                       case 'L':
                           currCode = 4;
                           break;
                       case 'M':
                       case 'N':
                           currCode = 5;
                           break;
                       case 'R':
                           currCode = 6;
                           break;
                   }

                   // Check if the current code is the same as the previous code.
                   if (currCode != prevCode)
                   {
                       // Check to see if the current code is 0 (a vowel); do not process vowels.
                       if (currCode != 0)
                           buffer.Append(currCode);
                   }
                   // Set the previous character code.
                   prevCode = currCode;

                   // If the buffer size meets the length limit, exit the loop.
                   if (buffer.Length == length)
                       break;
               }
               // Pad the buffer, if required.
               size = buffer.Length;
               if (size < length)
                   buffer.Append('0', (length - size));

               // Set the value to return.
               value = buffer.ToString();
           }
           // Return the value.
           return value;
       }
       #endregion
}
}

My mdb file contains a table, named: CustomerTable. I used it above.








Feedbacks about this page from members:
- No Feedbacks yet !! -
 
Submit Feedback        View All Feedbacks




Total members: 250989
Average new registrations per day (in last 7 days): 8


New Registration: Open

Register Now




Talk to Webmaster
Tony John

Facebook
Google+
Twitter


Advertise   Privacy Policy   Terms of use   Contact Us

SpiderWorks Technologies Pvt Ltd.
All Rights Reserved.