Data Access Application Block

Stored Procedures, Enums, Microsoft Data Access Application Block The Right Way to do Data Access by Les Smith

We have written many articles on doing Data Access correctly, from a code maintenance, performance, and security points of view.  This article will set forth what I believe to be Best Practices in dealing with Data Access.  Obviously, everyone has an opinion, and mine does not invalidate yours.  This article reflects many years of experience and the frustration of not always doing things right.  So, take my recommendations for what they are worth to you.

First, if you are not using Stored Procedures, in my humble opinion, you are not doing right with respect to maintenance, performance, or security.  Several of us at KnowDotNet have gone on record to say that Dynamic SQL should go.  You have the right to disagree, but if you do, then you get to pay the bill in all three of the aforementioned areas.

Secondly, if you are "hard coding" your column/field references in dealing with DataSets, then you are causing yourself a maintenance problem.  If someone changes the order of fields in a SQL statement or Stored Procedure, you have code to change.  If you make a practice of using Enums, you only need change the Enum and the code will be changed automatically.  This can be an over simplification, but not generally.

Thirdly, if you have not tried the Microsoft Data Access Application Block, you are missing a bet!  It's easy to use, and you can forget about having to worry about Command Objects, Connection objects, DataAdaptors, etc.  It's all done for you in the Application Block.  I would not build a database application again without using the Application Block.  Figure 1 shows a complete Database class for using th Microsoft Data Access Application Block.
Figure 1 - Database Class.

using Microsoft.ApplicationBlocks.Data;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using System.Diagnostics;using System; namespace WindowsApplication3_2003_CSharp.MyClasses {  public class Database   {    public Database()     {    //  // TODO: Add constructor logic here  //    }     private const string  PROD_STRING  =       "Server=LESSMITH;Database=MyDatabase;Integrated Security=SSPI";     private string ConnectionString     {       // Returns the value of ConnectProduction.      get{return PROD_STRING;}     }     /// <summary>    /// return LocationTitle for author    ///      ///      ///      ///      /// <returns>    public DataSet usp_DTP_GetLocationTitle(int dept,       string author, string title)     {       return SqlHelper.ExecuteDataset(ConnectionString,         "usp_GetLocationTitle",         new object[] {dept,author,title});     } // method: usp_GetLocationTitle  } }

In addition to talking about doing your Data Access right, I will also talk about Refactoring your existing code to make it right, even if it wasn' done right to begin with.  Being a little biased about Refactoring tools, I will be talking about Net Refactor, because it has some features for refactoring your data access code (and building it right to start with) that no other refactoring tool currently has, for VB.NET or C#.  One of these features has been in Net Refactor for some time but others have been added and new shortcuts to old features have been implemented.

To start with, let's say you have a Stored Procedure and you need to make a method to call it and you don't want to spend the time worrying about parameters and all of the associated objects.  You just need to get the method done quickly.   Let's also say that you are going to use the Data Access Application Block, and that you already have a Database Class similiar to the one shown in Figure 1.  Finally, after you get the DataSet (in this example) back, you want to have an enum (this example will be in C#, but could have just as well been generated in VB.NET code by Net Refactor.  You want the enum so that you can reference columns by a mnemonic name and at the same time use an interger index.  I certainly hope you don't hard code indexes, such as " dr[12] "...

First, I will place the cursor in my Database class where I want the new method for calling the Stored Procedure to be placed.

Next, I will go to the Server Explorer, find the desire Stored Procedure, and click on the Net Refactor menu to create a calling method. a picture of this is shown in Figure 2.
Figure 2 - Invoking Net Refactor's Create Stored Procedure Call.



Once I select the Create Calling Method menu option, Net Refactor will grab the code of the Stored Procedure and load it into the Create Calling Method dialog as shown in Figure 3.  In the picture in Figure 3, I have selected to create a method for the Data Access Application Block and Net Refactor automatically deduced that I was working in a C# project.  I then clicked the Generate button to create the calling method shown in the bottom pane.  When the dialog loaded, the selected Stored Procedure was already in the upper pane.  Net Refactor will analyze the Stored Procedure to generate the calling method.

Figure 3 - Dialog for Creating Stored Procedure Calling Method.



I can now click the Copy or Paste button to copy the new method to the Clipboard or I can directly paste the method into the Database class where I placed the cursor, as described earlier.  I will choose to Paste, from the context menu on the button. and the code will be pasted into the Code Window of the Database Class, as shown in Figure 4.
Figure 4 - New Stored Procedure Calling Method.

public DataSet usp_DTP_EOD_GetUnbatchedJobs(int FacID,       DateTime StDate,       DateTime EndDate,       string WorkTypes,       int FinalSaveMDA)     {       return SqlHelper.ExecuteDataset(ConnectionString,         "usp_DTP_EOD_GetUnbatchedJobs",         new object[]{FacID,StDate,EndDate,WorkTypes,FinalSaveMDA});     }

Now, I will click the Create Enum Tab on the Dialog, and enter a new name for my enum, ubJobs.  You will notice that there is a fairly complex Select Statement in the Stored Procedure.  When I click the Generate Enum button, Net Refactor will extract the body of the Select clause, removing the Select keyword and place it in the top pane code window.  It will then create the enum from the analysis of the fields in the Select Clause and place the newly created enum in the bottom pane code window.  This is all shown in Figure 5.
Figure 5 - Creating the Enum Automatically.



I will now click the Copy or Paste button and choose to copy the enum to the clipboard, because I did not pick a place to put the enum before I created it.  Once on the clipboard, I will paste the new enum in the code window as shown below.

public enum ubJobs     {       @Select,       Department,       WorkType,       JobID,       JobNumber,       @AuthorName,       @AuthorDate,       @PatientName,       SSN,       Length,       TransID,       TxFile,       TxTime,       DelCode     }

Now, I can reference the fields of the DataSet using code as shown in Figure 6.
Figure 6 - The Enum Provides Intellisense.



When I select the highlighted enum value, the code will look like that shown below.

private void HandleData()     {       foreach ( DataRow dr in ds.Tables[0].Rows )       {         int jobID = Convert.ToInt32(dr[(int)ubJobs.JobID]);       }     }

In the reference to JobID, shown above, not only have I used a meaningful name, but the resultant code will use an integer index into the rows column collection, which is best for performance, readability, and future maintenance.

Net Refactor has helped me to be productive, produce readable, maintainable code without the hassle of hand coding.  This can be done in VB.NET just as easily.  With the right tools, I can be productive and at the same time produce good code that anyone can maintain.

Please note that the Data Access Block is not the only interface for which Net Refactor will generate calling.  It can generate the traditional method with a command object, DataAdapter, etc.  Obviously, it can generate update methods also.  Just choose the type operation that you desire to perform from the Operation Combo Box on the creation dialog.

We continue to try to improve Net Refactor to be a help to you.  Why not give it a real try today?  Refactoring is not for the faint at heart and no tool is perfect.  No tool can be everything to everybody.

How do I code the Stored Procedure and the parameter to it to use the LIKE operator?

How do I code the Stored Procedure and the parameter to it to use the LIKE operator? 

The answer should seem quite simple, but it is not exactly as you would do it in dynamic sql.  It is almost the same with a subtile difference.

In dynamic sql, you might do something like this:

string searchText = "register";string sql = "select * from FAQs where QuestionColumn LIKE '%" +                     searchText + "%'";

to create the dynamic sql statement, and it will work fine.  However, it does not appear that passing a parameter value like the following code works in the Stored Procedure; at least it failed to make a find for me, even though I had copied the code from VBScript in an old ASP page, where it was working. 

string searchString = "'%register%'";     sqlCommand.Parameters.Add(new SqlParameter("@SearchString",         SqlDbType.VarChar, 50));     sqlCommand.Parameters[0].Value = searchString;

and the code in the stored procedure was:

SELECT Question, Answer     FROM SP_FAQs     WHERE Question LIKE @SearchString OR           Answer LIKE @SearchString

So, to get it to work, I changed the parameter setting code to:

string searchString = "register";     sqlCommand.Parameters.Add(new SqlParameter("@SearchString",         SqlDbType.VarChar, 50));     sqlCommand.Parameters[0].Value = searchString;

and the Stored Procedure code to the following:

SELECT Question, Answer     FROM SP_FAQs     WHERE Question LIKE '%' + @SearchString + '%' OR           Answer LIKE '%' + @SearchString + '%'

The subtile difference is that the adding of the "%" to the beginning and end of the parameter is done inside the Stored Procedure instead of passing it in with the parameter.  Even if somehow I made a simple mistatke that was causing the search to fail, it is still better to have the stored procedure do the work of enclosing the searchString in "%" than for each call have to worry about passing in anything but the actual text for which to search.

Find Duplicate Records in SQL Oracle. SQL Server, or Access

Find Duplicate Records in SQL Oracle. SQL Server, or Access by Les Smith

How can I find duplicate records on a field in a database table?  This works in SQL Server, Oracle, or Access.

Quite often I encounter the need to find duplicate rcords in database tables.  This brief article will return a results set of duplicate records.
In order to find duplicate values in a table, use the following SQL.
SIMPLE:

select distinct employee from employees (nolock) group by employee having count(employee) > 1 order by employee

Complex:

select employee, firstname, lastname, dob from employees (nolock) where employee in (select employee from employees (nolock) group by employee having count(employee)>1) order by employee

Smart TextBoxes

Smart TextBoxes Making TextBoxes Work Like MaskedEdit by Les Smith

In VB6, there was a MaskedEdit Control, but it could not be right justified.  Windows Forms in .NET does not have a MaskedEdit Control.  This article will create a TextBox that behaves like a VB6 MaskedEdit Control, only it can be right justified, and it is all done with code, not a 3rd party control.

I once had the job of replacing Excel functionality with a VB6 Application.  In other words, the user wanted all TextBoxes to work like Excel cells with respect to formatting.  Additionally, they wanted them to behave like MaskedEdit Controls so that the input was validated as it was entered, and they wanted currency data to be right justified.  The problem was that the VB6 MaskedEdit Control did not have a property that would allow right justification.  So I wrote add-in code to automatically generate the code that I am going to demonstrate in this article.  The code, shown below, is automatically generated by the TextBox Designer that comes withNetCommander.  NetCommander can generate this code in both VB.NET and C# projects.  You can download a fully feature copy for a free 30-day trial by clicking here.  

For almost all TextBoxes, that are used for input, you want them to have the following attributes.

   1. The text should be highlighted upon receiving focus so that the user can input into the control without having to delete the previous text.
   2. An Enter key should work just like a Tab key, and it should not Beep.
   3. The Up and Down arrow keys should work like the Tab key.

In order to add this functionality to a TextBox, I always add the following code behind each one that is used for data input.

' Prevents the Enter key from Beeping   Private Sub TextBox1_KeyPress(ByVal sender As Object, _       ByVal e As System.Windows.Forms.KeyPressEventArgs) _       Handles TextBox1.KeyPress       If e.KeyChar = Chr(13) Then         e.Handled = True         Exit Sub      End If   End Sub    ' turns Enter, Up, and Down Keys into Tabs   Private Sub TextBox1_KeyDown(ByVal sender As Object, _       ByVal e As System.Windows.Forms.KeyEventArgs) _       Handles TextBox1.KeyDown       If e.KeyCode = Keys.Enter Then SendKeys.Send("{TAB}")       If e.KeyCode = Keys.Up Then SendKeys.Send("+{TAB}")       If e.KeyCode = Keys.Down Then SendKeys.Send("{TAB}")    End Sub    ' Selects the contents of the control on receiving focus   Private Sub TextBox1_Enter(ByVal sender As Object, _       ByVal e As System.EventArgs) Handles TextBox1.Enter       TextBox1.Text = UnFmt_T_B(TextBox2)       TextBox1.SelectionStart = 0       TextBox1.SelectionLength = TextBox1.Text.Trim.Length    End Sub

In order to create the MaskedEdit "look-a-like" code, I added additional code (in Bold Font) to the KeyPress event and added code to the Leave Event. 

' Prevents the Enter key from Beeping   ' and calls the keystroke validation method   Private Sub TextBox2_KeyPress(ByVal sender As Object, _       ByVal e As System.Windows.Forms.KeyPressEventArgs) _       Handles TextBox2.KeyPress       If e.KeyChar = Chr(13) Then         e.Handled = True         Exit Sub      End If      e.Handled = CkKeyPressNumeric(Asc(Val(e.KeyChar)), TextBox2) = 0   End Sub    ' Selects the contents of the control on receiving focus   ' after it is unformatted   Private Sub TextBox2_Enter(ByVal sender As Object, _       ByVal e As System.EventArgs) Handles TextBox2.Enter       TextBox2.Text = UnFmt_T_B(TextBox2)      TextBox2.SelectionStart = 0       TextBox2.SelectionLength = TextBox2.Text.Trim.Length    End Sub    ' Formats the code according to the format specified   ' in the Tag property of the control   Private Sub TextBox2_Leave(ByVal sender As Object, _       ByVal e As System.EventArgs) Handles TextBox2.Leave       TextBox2.Text = Fmt_T_B(TextBox2)   End Sub

The following code is the helper functions that format, unformat, and validate input.  Please forgive the code leftover from VB6.  It was migrated and has not been updated.  But, you can get the picture.

' Format contents of textbox when it loses focus.   Public Function Fmt_T_B(ByVal roTB As TextBox) As String      On Error Resume Next      If InStr(1, roTB.Tag, ";", 1) > 0 Then         If InStr(roTB.Text, "-") > 0 Or _             (InStr(roTB.Text, "(") > 0 And _             InStr(roTB.Text, ")") > 0) Then            Return Format(Math.Abs(Val(roTB.Text)), _                Mid$(roTB.Tag, InStr(roTB.Tag, ";") + 1))          Else            Return Format$(Math.Abs(Val(roTB.Text)), _                Microsoft.VisualBasic.Left(roTB.Tag, _                InStr(roTB.Tag, ";") - 1))          End If      ElseIf InStr(1, roTB.Tag, "%", 1) > 0 Then         Return Format$(roTB.Text, roTB.Tag)       Else         Return Format$(roTB.Text, roTB.Tag)       End If   End Function

' Unformat the textbox when it receives focus   Public Function UnFmt_T_B(ByVal roTB As TextBox) As Object      On Error Resume Next      UnFmt_T_B = Val(Replace(Replace(Replace(Replace(Replace _         (roTB.Text, "$", ""), ",", ""), ")", ""), "(", ""), "%", ""))       If InStr(roTB.Text, "%") Then         UnFmt_T_B = UnFmt_T_B / 100       End If      If InStr(roTB.Text, "(") > 0 And InStr(roTB.Text, ")") > 0 Then         UnFmt_T_B = UnFmt_T_B * -1       End If      Return UnFmt_T_B    End Function    ' validate keyboard input   Private Function CkKeyPressNumeric(ByVal riKeyAscii As Integer, _       ByVal roTB As TextBox) As Integer      Dim liKeyReturn As Integer      ' allow 0-9,., Back, Del,-,Ins, and / if in tag format      On Error Resume Next      CkKeyPressNumeric = riKeyAscii       If riKeyAscii = Keys.Back Or _          riKeyAscii = Keys.Insert Or _          riKeyAscii = Keys.Delete Or _          riKeyAscii = 46 Or _          (riKeyAscii >= Keys.D0 And riKeyAscii <= Keys.D9) Or _          riKeyAscii = 45 Or _          riKeyAscii = 46 Or _          (InStr(roTB.Tag, "/") > 0 And riKeyAscii = Keys.Divide) _          Then         If roTB.SelectionLength = 0 Then            If InStr(roTB.Text, ".") > 0 Then               If Len(Mid(roTB.Text, InStr(roTB.Text, ".") + 1)) > 1 Then                  SendKeys.Send("{TAB}")                   CkKeyPressNumeric = 0                End If            End If         Else            roTB.Text = ""          End If         Return CkKeyPressNumeric       End If      Return 0    End Function

Figure 1 shows the Tag property of a Smart TextBox.  Several other formats are usable in the Tag property, which directs the helper methods in the process of formatting and validating the contents of the TextBox.

Figure 1 - Smart TextBox Properties.



Figure 2 shows a simple application which as several Smart TextBoxes.  One of them has focus and you can see that the formatting characters have been removed and the text is selected and ready to be overlaid by any input.  If focus moves away from that box without any new input, it will be reformatted and appear as the other TextBoxes.

Figure 2 - Smart TextBoxes in Action.

Use Constructor in Windows Forms to Ensure Proper Initialization

Use Constructor in Windows Forms to Ensure Proper Initialization Article #3 on RAD by Les Smith

Use the Constructor in a Windows Form for ensuring that initialization is done properly.  Event firing order is not an exact science, and you cannot always depend on the order in which events fire, regardless of what you have seen in the past.

For example, in VB6, you could count on the Form_Load event firing before the Activate event.  Therefore, you could set a formLoading variable to True in the Form_Load, test it in the Activate event, and if it was not set, execute some initialization code.  I covered a better way to do this in .NET in my previous article on Custom Properties.  Having spent a good bit of time in that article, I will now suggest another, probably better way to handle the problem of initialization code in a Windows Form.  Unlike VB6, with the Activate Event firing after the Form_Load Event (unless you were debugging and stepping through the Form_Load, in which case the Activate usually did not fire), in VB.NET the sequence of the firing of these two events is not necessarily predictable, and in many, if not all cases, the Activate event will fire prior to the Form_Load.  

For this reason Microsoft recommends that you handle initialization code in the Forms Constructor, assuming that you do not have a really time-comsuming initialization that could get time-sliced or do a DoEvents().  If that happens your form could be shown before the initialization is complete.  In the code generated by the Windows Form Designer, you always see the following code.

#Region " Windows Form Designer generated code "     Public Sub New()         MyBase.New()         'This call is required by the Windows Form Designer.        InitializeComponent()         'Add any initialization after the InitializeComponent() call     End Sub

You can add code after the comment ' Add any initialization.... and it will be executed before the form shows.  If this sequence of code is really time consuming, you might consider encapsulating the initialization code in a Private method and calling it from both the Form_Load and Activate events.  You would ensure that it was only executed once by placing the following line of code in the constructor.

FormState = State.Loading

And then, in the Form_Activate event, place the following code.  Checking FormState guarantees that you only execute the code once.  If you want to see an explanation of the FormState property and its Enums, see the article described earlier on Custom Properties.  

Private Sub frmDialogTemplate_Activated(ByVal sender As Object, _       ByVal e As System.EventArgs) Handles MyBase.Activated       If FormState = State.Loading Then         ' this code will only execute once         ' perform initialization code here,         ' connect to database, load grids, etc.         ' then turn off formLoading so we dont execute again          Call InitializeFormMethod()          FormState = State.Loaded       End If   End Sub

Use a DataView to Print the Contents of a Sorted Grid

Use a DataView to Print the Contents of a Sorted Grid by Les Smith and Brian Davis

How can I print the contents of a DataGrid, which has been sorted by the user?  The DataView.Table returns the original, unsorted table, and does not reflect any sorting done by the user.

If you have a need to print or simply access the data from a DataGrid, after the user has sorted on one or more columns, you can do that by following the code in this article.  There are several ways to accomplish this, but this seems to be the simpliest method that we have found.

First, recognize that you must use a DataView as the DataSource for the DataGrid.  Second, you must also understand that you can not simply reference the DataView.Table after the DataGrid has been sorted.  If you do that it returned DataTable will be the original DataTable from which the DataView was derived and will not reflect the order of the data in the sorted DataGrid.

So, to populate the grid, follow the code shown below.  I have a form, with a DataGrid and other controls, from which the user can select the data to be retrieved from the database.  I also have a Print button.  To fill the DataGrid, I follow several steps.  First, I retrieve a DataTable of the desired results from the database.  Next, I call my CGrid Class to add TableStyles to the DataGrid for formatting purposes.  Finally, I use the following code to bind the DataTable to the DataGrid.

dv = dt.DefaultView          dgTransRouting.SetDataBinding(dv, "")

The grid will now be populated and display the data to the user.  If the user should click on the column header of one or more columns, the data will be sorted in the DataGrid, according the last column header clicked.

Next, the user may click the Print button on the form and will expect to see the data on the resulting report be in the same sequence that they saw in the DataGrid.  Since I am using a Print Class, CPrintReportString forVBNET or C#, I will not illustrate the code for printing here.  The code shown below will be used in the Print Button Click Event.  This code simply gets the DataView from the CurrencyManager of the DataGrid and passes the DataView to the Print method of the Class supporting this form.

Private Sub btnPrint_Click(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles btnPrint.Click       Dim cm As CurrencyManager = _          CType(MeBindingContext(dgTransRouting.DataSource, _           dgTransRouting.DataMember), _          CurrencyManager)       Dim dvSorted As DataView = CType(cm.List, DataView)       tr.Print(dvSorted, cbFTP.Text, Me.dtpStTime.Value)    End Sub

The following code is a subset of the Print Method of the Class that supports the DataGrid form described earlier.  The object of this method is to fill a StringBuilder with the data for the desired report.  This is done by reading through the DataRows of the DataView's DataTable.  I will show only the code that is necessary to illustrate accessing the sorted DataView's rows.

Public Sub Print(ByVal dv As DataView, _       ByVal stDate As Date)      ' all code has been removed except that which is required for       ' pertinent illustration for brevity's sake.      For Each dr As DataRow In dv.Table.Select("", dv.Sort)           ' here we can access the fields of the datarow dr          sb.Append(MNI(dr("Department")).ToString.PadRight(6))       Next           ' here I would normally have the code for calling the       ' print class to do the actual printing of the report     End Sub

The key line of code is the "For Each.." line.  The "dv.Table.Select("", dv.Sort) line creates an array of DataRows and iterates through the array, accessing the rows in the sequence in which they are sorted in the grid.  At first glance, it might appear that an array is created each time through the For Each, but if that were true, you would not be iterating.  If I had used a "For i As Integer..." type loop, that would probably be the case, but the For Each only creates the array one time.  You can verify this by setting the DataView (dv = Nothing) inside the loop, and the code will still work, even though the local instance of the DataView is set to Nothing.

How To Move a Window on the Scrren When It is Off The Screen

How To Move a Window on the Scrren When It is Off The Screen Move a Window on to Screen When Not Visible by Les Smith

Here's a quick tip that can save you a good bit of frustration and embarassment with an undocked laptop that normally has an auxillary screen attached to it.

I normally run two screens, Laptop and auxillary, and I closed an application (Notepad) while it was on the secondary screen.  If I undock my Laptop, and load Notepad, it loads off the screen of the Laptop.  How do I get it back?

This is a fairly common problem, especially with docked laptops and secondary screens.  You unplug your laptop to go to a meeting, only to find that an application is hidden and you want to bring it back onto your laptop screen without closing the application.  Obviously, you right-click on the application on the taskbar and click the Maximize option.  It will maximize on your main screen, but you can't move it while mazimized! 

The same type of problem occurs when an application like Notepad remembers where it was when you last closed it.  Guess what?  That's where it will reopen. 

To bring the application back to the main screen do the following:

1. Alt-Tab to the desired window
2. Press Alt-Space to open the Control Menu
3. Press the M key for Move
4. Press the left or right arrow key, depending on where you think the window is

Almost magically, the invisible window will move back onto your screen.