August 2009 - Posts

This post provides an implementation of a method that saves data to a SQL Server database using a stored procedure. You application can use this method to insert, update, or delete rows in any database table.

NOTE: This post assumes you already have a stored procedure defined to insert, update, or delete the data from your database. For more information and examples of stored procedures, see this link.

The code is first shown in both VB and C# and then described in detail below.

NOTE: Be sure to set a reference to System.Data.SqlClient.

In C#:

public static class Dac
{

  public static int ExecuteNonQuery(string storedProcedureName,
                              params SqlParameter[] arrParam)
  {
    int retVal=0;
    SqlParameter firstOutputParameter = null;

    // Open the connection
    using (SqlConnection cnn = new SqlConnection(
           "Data Source=.\sqlexpress;Initial Catalog=AcmeRentals;
                                    Integrated Security=True"))
    {
        cnn.Open();

        // Define the command
        using (SqlCommand cmd= new SqlCommand())
        {
            cmd.Connection = cnn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedProcedureName;

            // Handle the parameters
            if (arrParam != null)
            {
                foreach (SqlParameter param in arrParam)
                {
                    cmd.Parameters.Add(param);
                    if (firstOutputParameter == null && 
                     param.Direction==ParameterDirection.Output && 
                     param.SqlDbType == SqlDbType.Int)
                        firstOutputParameter = param;
                }
            }

            // Execute the stored procedure
            cmd.ExecuteNonQuery();

            // Return the first output parameter value
            if (firstOutputParameter != null)
                retVal = (int)firstOutputParameter.Value;
        }
    }
    return retVal;
  }

}

In VB:

Public Class Dac

  Public Shared Function ExecuteNonQuery( _
        ByVal storedProcedureName As String, _
        ByVal ParamArray arrParam() As SqlParameter) As Integer

    Dim retVal As Integer = 0
    Dim firstOutputParameter As SqlParameter = Nothing

    ' Open the connection
    Using cnn As New SqlConnection(
           "Data Source=.\sqlexpress;Initial Catalog=AcmeRentals;
                                    Integrated Security=True")

        cnn.Open()

        ' Define the command
        Using cmd As New SqlCommand
            cmd.Connection = cnn
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = storedProcedureName

            ' Handle the parameters
            If arrParam IsNot Nothing Then
                For Each param As SqlParameter In arrParam
                    cmd.Parameters.Add(param)
                    If firstOutputParameter Is Nothing AndAlso _
                     param.Direction = _
                             ParameterDirection.Output AndAlso _
                     param.SqlDbType = SqlDbType.Int Then
                        firstOutputParameter = param
                    End If
                next
            End If

            ' Execute the Query
            cmd.ExecuteNonQuery()

            ' Return the first output parameter value
            If firstOutputParameter IsNot Nothing Then
                retVal = CInt(firstOutputParameter.Value)
            End If
        End Using

    End Using

    Return retVal
End Function

End Class

The ExecuteNonQuery method is in my Dac (data access component) class and is defined public and static (shared in VB). It is public so it can be called from any other code. It is static/shared because it does not use or retain any state. That allows the function to be called without creating an instance of the class containing the function.

The function has two parameters. The first is the name of a stored procedure. Since this code calls the SQLCommand ExecuteNonQuery, it assumes that the stored procedure contains a non-query statement such as an Insert, Update, or Delete. See this link for example stored procedures.

The second parameter is a parameter array that allows defining any parameters required by the stored procedure. Insert and Update stored procedures for a table normally define a parameter for any field in the table that can be updated.

NOTE: A parameter array permits a variable number of arguments to be passed to a method. So this allows any number of parameters.

The function will return an integer value containing the first integer output parameter defined for the stored procedure. This provides a “quick and dirty” way to return an autonumber column defining the Id of a new row. You can change this as appropriate to handle your primary keys.

The first two statements set up for the return value.

The first Using statement defines the connection. You will need to replace the connection string in the example with a connection string appropriate for your database.

The connection is then opened. The connection is automatically closed at the end of the Using block.

The second Using statement defines the command. In this case, the command is a stored procedure and the CommandText property of the command defines the stored procedure name.

If parameters were passed in, the parameters are added to the command’s Parameters. As the parameters are processed, the code looks for the first output parameter that is an integer. The code will later return the value of this parameter, if it is found.

The code then calls the ExecuteNonQuery method of the command to execute the query.

Finally, if there was an integer output parameter found, its value is retrieved and returns.

As an example, this is how you call this method to insert a new Customer row.

In C#:

SqlParameter idParameter = new SqlParameter("@CustomerID", CustomerId);
idParameter.Direction = ParameterDirection.Output;

int retVal = Dac.ExecuteNonQuery("CustomerInsert",
              idParameter,
              new SqlParameter("@LastName", LastName),
              new SqlParameter("@FirstName", FirstName));

In VB:

Dim idParameter As SqlParameter = _
                        New SqlParameter("@CustomerId", CustomerId)
idParameter.Direction = ParameterDirection.Output

Dim retVal As Integer = Dac.ExecuteNonQuery("CustomerInsert", _
              idParameter, _
              New SqlParameter("@LastName", LastName), _
              New SqlParameter("@FirstName", FirstName))

This code first creates a parameter for the CustomerId. This parameter is defined as an output parameter so the autonumber field set by the database when the row is insert can be returned.

Here is an example of calling this method to update a Customer rows.

In C#:

Dac.ExecuteNonQuery("CustomerInsert",
              new SqlParameter("@CustomerID", CustomerId),
              new SqlParameter("@LastName", LastName),
              new SqlParameter("@FirstName", FirstName));

In VB:

Dac.ExecuteNonQuery("CustomerUpdate", _
              New SqlParameter("@CustomerId", CustomerId), _
              New SqlParameter("@LastName", LastName), _
              New SqlParameter("@FirstName", FirstName))

The update example is much more straightforward because it does not require any return parameters.

Enjoy!

P.S. (Edited 8/21/09)

In the interest of having code that any reader can copy/paste into their own application without much external setup, I have removed the code that uses an application setting to define the connection string.

The removed text is here: "The connection string itself is stored in the configuration file using the Settings feature in Visual Studio. (See this for information on settings in C# and this for information on settings in VB.)"

Instead, a connection string is hard-coded into the example. Though I don't recommend this approach, it will help readers use these examples more easily.

with 6 comment(s)
Filed under: , , , ,

If you are working with months and need to convert a month name to a month number, such as Feb to 02 or Sep to 09, you can use the following code.

NOTE: Be sure to set a reference to System.Globalization.

In C#:

private string GetMonthNumberFromAbbreviation(string mmm)
{
   string[] monthAbbrev =
      CultureInfo.CurrentCulture.DateTimeFormat.AbbreviatedMonthNames;
   int index  = Array.IndexOf(monthAbbrev, mmm) + 1;
   return index.ToString("0#");
}

In VB:

Private Function GetMonthNumberFromAbbreviation(ByVal mmm As String) _
                                                           As String
    Dim monthAbbrev As String() = _
       CultureInfo.CurrentCulture.DateTimeFormat.AbbreviatedMonthNames
    Dim index As Integer = Array.IndexOf(monthAbbrev, mmm) + 1
    Return index.ToString("0#")
End Function

This function uses the CultureInfo class to get the correct set of month abbreviations based on the user’s current culture.

It then uses IndexOf to find the month entry in the array. Since the resulting monthAbbrev is 0-based, the code adds 1 to the index.

It then uses custom numeric string formatting to format the number as two digits.

You call this code as follows:

In C#:

string num = GetMonthNumberFromAbbreviation("Dec");

In VB:

Dim num As String = GetMonthNumberFromAbbreviation("Dec")

Enjoy!

with 6 comment(s)
Filed under: , , ,

If you have a data in a Windows Forms DataGridView, you may want to use some color to highlight specific rows of the grid to draw in the user’s attention.

In this example, the code paints a border around a specific row and sets the background color.

In C#:

private void myGrid_RowPostPaint(object sender,
               DataGridViewRowPostPaintEventArgs e)
{
    if (e.RowIndex == 2)
    {
        // Calculate the bounds of the row
        int rowHeaderWidth = myGrid.RowHeadersVisible ?
                             myGrid.RowHeadersWidth : 0;
        Rectangle rowBounds = new Rectangle(
            rowHeaderWidth,
            e.RowBounds.Top,
            myGrid.Columns.GetColumnsWidth(
                    DataGridViewElementStates.Visible) -
                    myGrid.HorizontalScrollingOffset + 1, 
           e.RowBounds.Height);

        // Paint the border
        ControlPaint.DrawBorder(e.Graphics, rowBounds, 
                         Color.Red, ButtonBorderStyle.Solid);

        // Paint the background color
        myGrid.Rows[e.RowIndex].DefaultCellStyle.BackColor =
                                             Color.BlanchedAlmond;
    }
}

In VB:

Private Sub myGrid_RowPostPaint(ByVal sender As Object, _
        ByVal e As DataGridViewRowPostPaintEventArgs)
        Handles myGrid.RowPostPaint
    If e.RowIndex = 2 Then
        ' Calculate the bounds of the row
        Dim rowHeaderWidth As Integer = _
                If(myGrid.RowHeadersVisible,  _
                   myGrid.RowHeadersWidth, 0)
        Dim rowBounds As New Rectangle( _
            rowHeaderWidth, _
            e.RowBounds.Top, _
            myGrid.Columns.GetColumnsWidth( _
                    DataGridViewElementStates.Visible) - _
                    myGrid.HorizontalScrollingOffset + 1, _
            e.RowBounds.Height)

        ' Paint the border
        ControlPaint.DrawBorder(e.Graphics, rowBounds, _
                         Color.Red, ButtonBorderStyle.Solid)

        ' Paint the background color
        myGrid.Rows(e.RowIndex).DefaultCellStyle.BackColor = _
                                        Color.BlanchedAlmond
    End If

End Sub

In both examples, this code hard-codes the highlighted row to the 3rd row (0-based). You can use any criteria that makes sense for your application. For example, any row with a date after a specific date or with a quantity greater than a specific amount.

The code then determines whether or not the grid displays row headers. If the headers are displayed, the start location of the border will be after the row header. Otherwise, the border will start at 0.

The next step is to determine the bounds of the border. This is done by creating a rectangle with the appropriate upper left corner (x and y location), width and height.

The x location depends on whether the row headers are displayed. In this example the border did not include the row headers. The y location is the top of the underlying row. The rectangle width uses the GetColumnsWidth method to ensure only visible columns widths are included in calculating the rectangle width. The rectangle height is the height of the underlying row.

The DrawBorder method is used to draw a red border around the created rectangle. You can select any color or border style you desire.

To draw more attention to the row, the background color is also changed. This step is optional if the border provides enough notice for your application.

The resulting grid appears as follows:

image

If the grid has row headers, it will appear like this:

image

Notice that the row header is not included in the border.

Enjoy!

with 11 comment(s)
Filed under: , , , ,

There are many uses for generating a set of random numbers, especially if you are developing a game. .NET provides a Random class just for this purpose.

To generate a set of 10 random numbers between 1 and 100, you use the Random class as follows:

In C#:

Random rand = new Random();
List<int> randomNumbers = new List<int>();

for (int i = 0; i < 10; i++)
{
    randomNumbers.Add(rand.Next(1, 101));
}

randomNumbers.ForEach(i => Debug.WriteLine(i));

In VB:

Dim rand As New Random
Dim randomNumbers As New List(Of Integer)

For i As Integer = 1 To 10
    randomNumbers.Add(rand.Next(1, 101))
Next

For Each i As Integer In randomNumbers
    Debug.WriteLine(i)
Next

In both examples, the random numbers are generated and added to a list. The contents of the list is then displayed in the debug window.

The Next method of the Random class returns a random number within the defined range, inclusive of the lower bound and exclusive of the upper bound. This means that using Next(1, 101) picks a random number from 1 to 100.

A second overload of the Next method takes only one parameter, which is the upper bound. Using Next(101) picks a random number from 0 to 100.

A third overload of the Next method takes no parameters. Using Next() picks a random number from 0 to the maximum integer value – 1.

Enjoy!

with 10 comment(s)
Filed under: , , ,

A common task in many applications is to convert a string value to a number. For example, if a user types a number into a TextBox, the Text property of the TextBox returns a string. But if you want to work with the value as a number, you need to convert it.

There are many ways to accomplish this task:

  • Val(myString)
    VB Only. This function returns the value of the first set of numbers in the string. If the string contains other characters, the evaluation stops at the first non-numeric character. So Val(“10 5th street”) returns 105. A run-time exception is generated if the value is too large or if the parameter cannot be converted to a string.
  • CType(myString, Integer)
    VB Only. This function converts the expression to the specified data type. A run-time exception is generated if the conversion fails.
  • CInt(myString)
    VB Only. This function converts the expression to an integer. It takes an object as a parameter. A run-time exception is generated if the conversion fails.
  • Convert.ToInt32(myString)
    This function converts the expression to an integer. It provides overloads to convert from many different types of values to an integer. A run-time exception is generated if the conversion fails.
  • Integer.Parse(myString) / int.Parse(myString)
    This function converts the expression to an integer. A run-time exception is generated if the conversion fails.

You may have noticed that each of these functions generate exceptions. In many cases, it would be nice to perform the conversion without generating an exception. This is especially important when processing user input because the user could leave the value blank or type in a value that is not numeric.

This is where TryParse comes in handy. It returns true or false defining whether the conversion was successful instead of generating an exception. The TryParse function was new in .NET 2.0.

In C#:

string value  = "10";
int num;

if (int.TryParse(value, out num))
    Debug.Print("Success! Value is {0}", num);
else
    Debug.Print("Please enter a numeric value");

In VB:

Dim value As String = "10"
Dim num As Integer

If Integer.TryParse(value, num) Then
    Debug.Print("Success! Value is {0}", num)
Else
    Debug.Print("Please enter a numeric value")
End If

In both examples, the TryParse takes a string as the first parameter and an integer as an out (By Ref in VB) second parameter. If the conversion succeeds, the second parameter contains the converted value and TryParse returns true. If the conversion fails, the second parameter contains a value of 0 and TryParse returns false.

TryParse is also available for other data types such as Boolean, DateTime, Double, Decimal, and so on.

Enjoy!

with 9 comment(s)
Filed under: , , ,
More Posts « Previous page