After writing dozens of data tiers for many websites and applications, I felt it was time to write a data tier generator for myself. About 5 years ago, I created a data tier generator for VB6, but never really found time to create one for C#...until now. This generator has already saved me countless hours from writing classes (properties and methods) and stored procedures. Not only are they done for me now, but they're 100% accurate!

Download application

1. Run data tier generator
The application asks for three things:
Connection String: database you wish to generate the data tier for.
Output Path: location to generate the code to.
Project Name: name of the project. Application will create folder, if not found.



2. Add MDAB to project
Once the application completes, the output path will have a folder for the project you just created. Open the project and simply add in the Microsoft Data Access Blocks. The project will compile and is ready to be accessed from a UI or Business layer.



3. Create the Stored Procedures
Finally, execute the SQL scripts in the "Data" folder to create the stored procedures.



What is generated?
The data tier should encapsulate all database information and expose all data through objects (classes) or properties. This is exactly what my data tier generator does. All database fields become public properties and all data is received/returned in objects. Extended the classes is very easy once the initial generation is complete...and if you decide to change a table drastically, simply rerun the generator.

A recent project at my current contract required us to research and obtain a 3rd party advanced search engine to fulfill some of the project requirements. I was not involved in the research, but the company decided to go with Verity. It turned out the Verity K2 Enterprise software had everything we needed to get the project developed and deployed fast. Working with the Verity software was very interesting and I decided to see what else was out there (open source only). Almost immediately, I came across the Apache Lucene project. "Apache Lucene is a high-performance, full-featured text search engine library written entirely in Java. It is a technology suitable for nearly any application that requires full-text search, especially cross-platform.". Getting the Java examples to work was very easy and so I started to look for more online...that's when I came across the .NET version at SearchBlackBox.com. "SearchBlackBox Lucene Edition is a 100% C# based native .NET assembly that is fully optimized for the .NET Framework.". The website doesn't offer a lot of example, but it's easy to get it working. Also, the software product Lookout (aquired by Microsoft in June 2004) runs on the Lucene.NET code.

This application walks you through a simple implementation of the Lucene .NET DLL. We're bascially going to build out own Desktop Search Engine.

Download code

1. LuceneEngine.IndexFiles.cs
Lines 12-59: Input public properties (FilesLocation, IndexLocation, StopProcessing) and output public properties (Error, NumDocsIndexed, NumDocsSkipped, NumDocsErrored, TotalTime).
Lines 63-68: Custom events that are fired when the percentage of files completed changes and when errors occur.
Lines 69-135: StartIndexing is the entry method to get the indexing process going. After validating the input properties, I iterate through the directory chosen to index and all of its subdirectories. After I have all of the directories stored in mstrSubDirectories, I iterate through each one and call IndexDocs.
Lines 136-182: IndexDocs is the real worker in this application. This method loops through each file and, if the extension is supported, adds the document to the index.

2. LuceneEngine.MakeFileDoc.cs
Lines 12-46: The only method here is Document(), which returns a Lucene.Net.Documents.Document object. A Lucene Document contains multiple properties and the code populates some of them: filename, path, name, length, contents, creation_time, last_write_time and last_access_time.

3. LuceneEngine.SearchFiles.cs
Lines 16-72: Input public properties (IndexLocation, SearchFor, LastWriteFrom, LastWriteTo, NumHitsRequested) and output public properties (NumHitsFound, ResultsXML, ResultsDataView, TotalTime, Error).
Lines 73-158: The only method here is StartSearch(). This method creates an instance of the IndexSearcher and StandardAnalyzer (other Analyzers are available, but the StandardAnalyzer is basic enough for this project). It then creates a Lucene.Net.Search.Query object and selects the "contents" to be searched. Then the filtering criteria is loaded into a Lucene.Net.Search.DateFilter object. Finally, we call the IndexSearcher's Search method and return a Lucene.Net.Search.Hits object. From that, I get the number of hits and can iterate through the results collection. In this example, I create an XML resultset and a DataView (gives more flexibility to the consumer, i.e. WinForms App, Website or Web Service). The success result (true/false) is returned.

4. LuceneUI.frmMain
The top section consists of the indexing criteria. The "Files Location" is the root folder for the files you want to index. The "Index Location" is the location where the index should be stored.

 

The middle section consists of the search criteria and results. Simply enter in the search phrase and date range and the results will return immediately. To launch any item, double-click the row.

 

5. LuceneUI.frmMain.cs
Lines 383-478: The cmdStart_Click event handles all of the processing to index the files. After validating the input data, it creates an instance of LuceneEngine.Index.IndexFiles, sets up the events, loads the properties and calls the StartIndexing method. Upon return, it displays the results. While indexing was occurring, events were being fired constantly and the IndexFiles_OnPercentCompleteChangedHandler (Lines 486-497) method was processing them.
Lines 506-618: The cmdSearch_Click event handles all processing to search  the index. After validating the input data, it creates an instance of LuceneEngine.Search.SearchFiles, load sthe properties and calls the StartSearch method. Upon return, it displays the statistical results in a label, binds the results DataView to the DataGrid and automatically resizes the columns so they can be viewed (SizeColumnsToContent).

Have you ever wondered how to load a Microsoft Excel spreadsheet into an ADO.NET DataSet? Actually, it's pretty easy...only six lines of code! Now you can let people import data into your website in batches. Here's how:

1DataSet objDS = new DataSet();
2string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + 
3 "Data Source=" + strFileName.Replace("\\", "\\\\") + ";" +
4 "Extended Properties=\"Excel 8.0;\"";
5objOLE = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
6objOLE.Fill(objDS);

The above code snippet works great, but you must know the name of the worksheet (Sheet1 is the default for Excel). But what if you don't know the Worksheet name? What if you just want the first sheet? Then, you must use the Excel objects to get it:

1#region AnalyzeSpreadsheet
2public void AnalyzeSpreadsheet(string strFileName)
3{
4 //Excel variables
5 object con_true = true;
6 Excel.ApplicationClass objExcel = null;
7 Excel.Workbook objBook = null;
8 Excel.Worksheet objSheet = null; 
9 try
10 {
11 //Create new instance of Excel Application
12 objExcel = new Excel.ApplicationClass();
13 //Set some options
14 objExcel.DisplayAlerts = false;
15 objExcel.ScreenUpdating = false;
16 objExcel.Visible = false;
17 objExcel.UserControl = false;
18 //Open spreadsheet
19 objBook = objExcel.Workbooks.Open(strFileName, Type.Missing,
20 Type.Missing, Type.Missing, Type.Missing, Type.Missing,
21 Type.Missing, Type.Missing, Type.Missing, Type.Missing,
22 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
23 //Find the 1st worksheet
24 objSheet = (Excel.Worksheet)objBook.Worksheets.get_Item(1); 
25 if (objSheet == null)
26 throw new Exception("Worksheet #1 not found!");
27 else
28 {
29 //Do something...
30 }
31 }
32 catch
33 {
34 //Handle exception
35 }
36 finally
37 {
38 ReleaseComObject(objSheet);
39 objSheet = null;
40 objBook.Close(con_true, strFileName, null);
41 ReleaseComObject(objBook);
42 objBook = null;
43 objExcel.Workbooks.Close();
44 objExcel.Application.Quit();
45 ReleaseComObject(objExcel);
46 objExcel = null;
47 }
48}
49#endregion
50#region ReleaseComObject
51private void ReleaseComObject(object o)
52{
53 Int32 i = 0;
54 Int32 j = 0;
55 try 
56 {
57 for (i = 1; i <= 
System.Runtime.InteropServices.Marshal.ReleaseComObject(o); i++)
58 {
59 j = 
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
60 }
61 }
62 catch
63 {
64 }
65 finally
66 {
67 o = null;
68 }
69}
70#endregion

Using custom objects is always a great practice, especially in n-tiered development where abstraction across the layers is crucial. All too often I see people (including myself) use custom objects to handle most of the logic, but then databind repeaters, datagrids, etc. with datasets, datatables and data readers. By doing so, the database and user interface are now tightly coupled and maintenance can become a headache later on.

Download code

What's the solution? Bind your UI controls with your custom objects. Here's how:

1. UserColl.cs - Create a class that defines your custom object (UserDetails)
Lines 6-34: A simple class with three members (Email Address, First name and Last Name)

2. UserColl.cs - Create a custom collection class that implements the IEnumerable interface (Users)
Line 37: Inherit System.Collections.IEnumerable interface
Line 40: Declare private ArrayList to store data
Lines 43-46: Return the enumerator from my ArrayList
Lines 49-109: Methods to add/update/retrieve objects from the collection

3. WebForm1.aspx - Implement into a WebForm


Lines 8-11: I added JavaScript that confirms the delete.
Lines 23-43: DataGrid TemplateColumn's - Notice that each item is pulled from the Container and is referenced by the member name.
Line 47: TemplateColumn for the Delete LinkButton - Coded this way so was can find it during the ItemBound event and add the JavaScript confirm function.

4. WebForm1.aspx.cs - Implement into the CodeBehind
Lines 47-52: On first load, call LoadNewUsers to, you guessed it, load new users into the object. In a real life scenario, this would be pulled from a database.
Line 55: For this example, I simply put the session's data into a Session object.
Lines 58-86: LoadNewUsers - Create 3 objects and load them with details...each being added to our collection class. At the end, store this in Session State (for this example).
Lines 94-99: Look familiar? Usually, you bind with a dataset, datatable, etc...this time you're binding with a custom object!
Lines 101-114: dgUsers_ItemCreated - just a little something extra. When DataBind is called, the ItemCreated event is caught here. For each item, I find the Delete LinkButton and and the JavaScript function as an "onclick" attribute.
Line 116-167: dgUsers_ItemCommand - Some examples on how to access the collection. When "View" is clicked, I retrieve the object from the collection by "index" (Line 124). When "Delete" is clicked, I retrieve the object from the collection by "Email Address". To do this, I first need to find the LinkButton (Line 140). Then I can retrieve the object (Line 144). Once I have the object, I can delete it (Line 146). Finally, I rebind the custom object to the DataGrid to reflect the changes (Line 163) and refresh the Session variable (Line 166). For this example, the rebind and session variable refresh should just be in the "Delete" if structure, but I had originally planned to have an "Edit" link.