Click here to Skip to main content
15,867,568 members
Articles / Desktop Programming / Windows Forms

Setting up Stored Procedures with Optional Parameters and Using a Strongly Typed TableAdapter to Execute the said Stored Procedure

Rate me:
Please Sign up or sign in to vote.
4.32/5 (29 votes)
29 Mar 2006CPOL9 min read 321.9K   52   19
Collection of information on optional paramters in Stored Procedures, as well as passing null values into the Fill method of a stongly typed data adapter.

Introduction

I know for a fact the bits and pieces of this article are scattered around this site as well as in other programming sites. What I'm going to try and do is bring them all together in a "from start to finish" solution.

Background

Building even a simple search engine for in-program data can be complicated at times; building an advanced search engine for the same in-program data where any or all of the parameters can be used can be a nightmare.

I would like to share my solution for this problem; however, bear in mind that I'm sure there is more than one way to do this. It's simply the most efficient way I've found (balancing speed and quickly implementing it).

Pre-Planning Stage

We have in existence a database for a tattoo shop manager that contains the following tables:

  • Clients - holds name, address, city, state, date of birth, e-mail, phone numbers, sex, type of advertisement that led them to find the company.
  • Jobs - holds job titles, type, client-ID assigned to job, artist-ID assigned to job, job date, job length, job status, etc.

Part of the shop manager program is a Client Manager which allows the user to search (or, filter is actually a more correct term) the clients in the database based on various criteria.

Image 1

Everything in the image above is optional; it can be searched with all or none of the criteria.

Plan of attack

One way to implement this is once the user clicks the Search Clients button, loop through all the controls above and check each of their values and build an inline SQL Select statement based on the values present.

This approach is fairly straightforward, and you never really have to leave your code. But there are some major set backs in my opinion.

  1. My current WinForm has 30+ input controls used for the search engine; looping through, casting, and checking each control is going to take some time, not to mention probably a couple hundred lines of code to implement.
  2. The search looks at multiple tables; having your code creating the proper joins can potentially be bad.
  3. Adding or removing criteria have the potential to require massive rewriting of the SQL Select statement producing code.

Another way to implement this is to write Stored Procedures that cover every conceivable combination or search criteria.

This, of course, is completely impractical, and makes even less sense than the previous option, but I thought it should be mentioned that it is possible this way.

Some simple reasons why not to use this option:

  1. The time it would take to think up and then write each Stored Procedure, you'd be a couple years older.
  2. Developers implementing the search engine would need a binder to hold the list of Stored Procedures for reference.

Now to the third way of implementing this. To me, this is sort of a mid-ground between the first two options. Using one Stored Procedure, we supply all the possible search parameters, but we make each of them optional so that the Stored Procedure will execute with some, all, or none of the parameters. Using a strongly typed TableAdapter, we use the previous Stored Procedure to fill our search results dataset.

Implementation - Step 1: Writing the Stored Procedure

The parameters section:

SQL
CREATE PROCEDURE dbo.SearchClients
(
  @ClientFirstName varchar(20) = null, 
  @ClientLastName varchar(20) = null,
  @ClientHasTattoo bit = null,
  @JobFinished bit = null,
  @JobTypeID smallint = null,
  @ArtistID Smallint = null,
  @JobTotalHoursMinimum float = null,
  @JobTotalHoursMaximum float = null,
  @AdvertisingID smallint = null,
  @ClientDOBMinimum datetime = null,
  @ClientDOBMaximum datetime = null,
  @ClientStreetAddress varchar(75) = null,
  @ClientCity varchar(20) = null,
  @ClientState varchar(2) = null,
  @ClientZip varchar(10) = null,
  @ClientHomePhone varchar(14) = null,
  @ClientWorkPhone varchar(14) = null,
  @ClientEmail varchar(50) = null,
  @ClientSex bit = null,
  @JobDateMinimum datetime = null,
  @JobDateMaximum datetime = null,
  @JobTitle varchar(50) = null
)

Couple of things to note here:

  1. Every parameter is set to null; this gives it a default value that the Stored Procedure can use when we fail to provide our own.
  2. You'll notice every text field is a varchar. I'll explain why in a moment; for now, I just wanted to bring it to your attention.

The Select sections:

SQL
AS 
IF @JobFinished IS NULL AND 
   @JobTypeID IS NULL AND
   @ArtistID IS NULL AND 
   @JobTotalHoursMaximum IS NULL AND 
   @JobTotalHoursMinimum IS NULL AND 
   @JobDateMinimum IS NULL AND 
   @JobDateMaximum IS NULL AND
   @JobTitle IS NULL
BEGIN 
  SELECT DISTINCT 
    ClientID, ClientFirstName, ClientLastName, ClientStreetAddress, 
    ClientCity, ClientState, ClientZip, ClientHomePhone, 
    ClientWorkPhone, ClientEmail, ClientDOB, ClientSex, ClientHasTattoo, AdvertisingID
  FROM         Clients
  WHERE     
    (ClientFirstName LIKE ISNULL(@ClientFirstName, ClientFirstName) + '%') AND 
    (ClientLastName LIKE ISNULL(@ClientLastName, ClientLastName) + '%') AND 
    (ClientHasTattoo = ISNULL(@ClientHasTattoo, ClientHasTattoo)) AND 
    (AdvertisingID = ISNULL(@AdvertisingID, AdvertisingID)) AND 
    (ClientDOB >= ISNULL(@ClientDOBMinimum, ClientDOB)) AND 
    (ClientDOB <= ISNULL(@ClientDOBMaximum, ClientDOB)) AND 
    (ClientStreetAddress LIKE ISNULL(@ClientStreetAddress, ClientStreetAddress) + '%') AND 
    (ClientCity LIKE ISNULL(@ClientCity, ClientCity) + '%') AND 
    (ClientState LIKE ISNULL(@ClientState, ClientState) + '%') AND   
    (ClientZip LIKE ISNULL(@ClientZip, ClientZip) + '%') AND 
    (ClientHomePhone LIKE ISNULL(@ClientHomePhone, ClientHomePhone) + '%') AND 
    (ClientWorkPhone LIKE ISNULL(@ClientWorkPhone, ClientWorkPhone) + '%') AND 
    (ClientEmail LIKE ISNULL(@ClientEmail, ClientEmail) + '%') AND 
    (ClientSex = ISNULL(@ClientSex, ClientSex))
  ORDER BY ClientID, ClientLastName, ClientFirstName
END
ELSE
BEGIN
  SELECT DISTINCT 
    Clients.ClientID, Clients.ClientFirstName, Clients.ClientLastName, 
    Clients.ClientStreetAddress, Clients.ClientCity, 
    Clients.ClientState, Clients.ClientZip, Clients.ClientHomePhone, 
    Clients.ClientWorkPhone, Clients.ClientEmail, 
    Clients.ClientDOB, Clients.ClientSex, 
    Clients.ClientHasTattoo, Clients.AdvertisingID
  FROM         Job RIGHT OUTER JOIN
                      Clients ON Job.ClientID = Clients.ClientID
  WHERE     
    (Clients.ClientFirstName LIKE ISNULL(@ClientFirstName, Clients.ClientFirstName) + '%') AND 
    (Clients.ClientLastName LIKE ISNULL(@ClientLastName, Clients.ClientLastName) + '%') AND 
    (Clients.ClientHasTattoo = ISNULL(@ClientHasTattoo, Clients.ClientHasTattoo)) AND 
    (Job.JobFinished = ISNULL(@JobFinished, Job.JobFinished)) AND
    (Job.ArtistID = ISNULL(@ArtistID, Job.ArtistID)) AND 
    (Job.JobTotalHours >= ISNULL(@JobTotalHoursMinimum, Job.JobTotalHours)) AND 
    (Job.JobTotalHours <= ISNULL(@JobTotalHoursMaximum, Job.JobTotalHours)) AND 
    (Job.JobTypeID = ISNULL(@JobTypeID, Job.JobTypeID)) AND 
    (Clients.AdvertisingID = ISNULL(@AdvertisingID, Clients.AdvertisingID)) AND 
    (Clients.ClientDOB >= ISNULL(@ClientDOBMinimum, Clients.ClientDOB)) AND 
    (Clients.ClientDOB <= ISNULL(@ClientDOBMaximum, Clients.ClientDOB)) AND 
    (Clients.ClientStreetAddress LIKE ISNULL(@ClientStreetAddress, 
             Clients.ClientStreetAddress) + '%') AND 
    (Clients.ClientCity LIKE ISNULL(@ClientCity, Clients.ClientCity) + '%') AND
    (Clients.ClientState LIKE ISNULL(@ClientState, Clients.ClientState) + '%') AND 
    (Clients.ClientZip LIKE ISNULL(@ClientZip, Clients.ClientZip) + '%') AND
    (Clients.ClientHomePhone LIKE ISNULL(@ClientHomePhone, Clients.ClientHomePhone) + '%') AND
    (Clients.ClientWorkPhone LIKE ISNULL(@ClientWorkPhone, Clients.ClientWorkPhone) + '%') AND
    (Clients.ClientEmail LIKE ISNULL(@ClientEmail, Clients.ClientEmail) + '%') AND 
    (Clients.ClientSex = ISNULL(@ClientSex, Clients.ClientSex)) AND
    (Job.JobDate >= ISNULL(@JobDateMinimum, Job.JobDate)) AND
    (Job.JobDate <= ISNULL(@JobDateMaximum, Job.JobDate)) AND 
    (Job.JobTitle LIKE ISNULL(@JobTitle, Job.JobTitle) + '%')
  ORDER BY Clients.ClientID, Clients.ClientLastName, Clients.ClientFirstName
END

Some things to note here:

  1. You'll notice a rather lengthy IF statement.
  2. SQL
    IF @JobFinished IS NULL AND 
       @JobTypeID IS NULL AND
       @ArtistID IS NULL AND 
       @JobTotalHoursMaximum IS NULL AND 
       @JobTotalHoursMinimum IS NULL AND 
       @JobDateMinimum IS NULL AND 
       @JobDateMaximum IS NULL AND
       @JobTitle IS NULL

    This is checking to see if no parameters that apply to external tables from the client's table are provided; then we can use the slightly more simplified Select statement:

    SQL
    SELECT DISTINCT 
        ClientID, ClientFirstName, ClientLastName, ClientStreetAddress, 
        ClientCity, ClientState, ClientZip, ClientHomePhone, 
        ClientWorkPhone, ClientEmail, ClientDOB, ClientSex, ClientHasTattoo, AdvertisingID
    FROM         Clients
    WHERE     
        (ClientFirstName LIKE ISNULL(@ClientFirstName, ClientFirstName) + '%') AND 
        (ClientLastName LIKE ISNULL(@ClientLastName, ClientLastName) + '%') AND 
        (ClientHasTattoo = ISNULL(@ClientHasTattoo, ClientHasTattoo)) AND 
        (AdvertisingID = ISNULL(@AdvertisingID, AdvertisingID)) AND 
        (ClientDOB >= ISNULL(@ClientDOBMinimum, ClientDOB)) AND 
        (ClientDOB <= ISNULL(@ClientDOBMaximum, ClientDOB)) AND 
        (ClientStreetAddress LIKE ISNULL(@ClientStreetAddress, 
                   ClientStreetAddress) + '%') AND 
        (ClientCity LIKE ISNULL(@ClientCity, ClientCity) + '%') AND 
        (ClientState LIKE ISNULL(@ClientState, ClientState) + '%') AND   
        (ClientZip LIKE ISNULL(@ClientZip, ClientZip) + '%') AND 
        (ClientHomePhone LIKE ISNULL(@ClientHomePhone, ClientHomePhone) + '%') AND 
        (ClientWorkPhone LIKE ISNULL(@ClientWorkPhone, ClientWorkPhone) + '%') AND 
        (ClientEmail LIKE ISNULL(@ClientEmail, ClientEmail) + '%') AND 
        (ClientSex = ISNULL(@ClientSex, ClientSex))
    ORDER BY ClientID, ClientLastName, ClientFirstName

    Or, if one of the parameters are provided to use the more advanced Select statement:

    SQL
    SELECT DISTINCT 
        Clients.ClientID, Clients.ClientFirstName, Clients.ClientLastName, 
        Clients.ClientStreetAddress, Clients.ClientCity, 
        Clients.ClientState, Clients.ClientZip, 
        Clients.ClientHomePhone, Clients.ClientWorkPhone, Clients.ClientEmail, 
        Clients.ClientDOB, Clients.ClientSex, 
        Clients.ClientHasTattoo, Clients.AdvertisingID
    FROM         Job RIGHT OUTER JOIN
                          Clients ON Job.ClientID = Clients.ClientID
    WHERE     
        (Clients.ClientFirstName LIKE ISNULL(@ClientFirstName, 
                   Clients.ClientFirstName) + '%') AND 
        (Clients.ClientLastName LIKE ISNULL(@ClientLastName, 
                   Clients.ClientLastName) + '%') AND 
        (Clients.ClientHasTattoo = 
            ISNULL(@ClientHasTattoo, Clients.ClientHasTattoo)) AND 
        (Job.JobFinished = ISNULL(@JobFinished, Job.JobFinished)) AND
        (Job.ArtistID = ISNULL(@ArtistID, Job.ArtistID)) AND 
        (Job.JobTotalHours >= ISNULL(@JobTotalHoursMinimum, Job.JobTotalHours)) AND 
        (Job.JobTotalHours <= ISNULL(@JobTotalHoursMaximum, Job.JobTotalHours)) AND 
        (Job.JobTypeID = ISNULL(@JobTypeID, Job.JobTypeID)) AND 
        (Clients.AdvertisingID = ISNULL(@AdvertisingID, Clients.AdvertisingID)) AND 
        (Clients.ClientDOB >= ISNULL(@ClientDOBMinimum, Clients.ClientDOB)) AND 
        (Clients.ClientDOB <= ISNULL(@ClientDOBMaximum, Clients.ClientDOB)) AND 
        (Clients.ClientStreetAddress LIKE ISNULL(@ClientStreetAddress, 
                 Clients.ClientStreetAddress) + '%') AND 
        (Clients.ClientCity LIKE ISNULL(@ClientCity, Clients.ClientCity) + '%') AND
        (Clients.ClientState LIKE ISNULL(@ClientState, 
                   Clients.ClientState) + '%') AND 
        (Clients.ClientZip LIKE ISNULL(@ClientZip, Clients.ClientZip) + '%') AND
        (Clients.ClientHomePhone LIKE ISNULL(
             @ClientHomePhone, Clients.ClientHomePhone) + '%') AND
        (Clients.ClientWorkPhone LIKE ISNULL(
             @ClientWorkPhone, Clients.ClientWorkPhone) + '%') AND
        (Clients.ClientEmail LIKE ISNULL(@ClientEmail, 
                   Clients.ClientEmail) + '%') AND 
        (Clients.ClientSex = ISNULL(@ClientSex, Clients.ClientSex)) AND
        (Job.JobDate >= ISNULL(@JobDateMinimum, Job.JobDate)) AND
        (Job.JobDate <= ISNULL(@JobDateMaximum, Job.JobDate)) AND 
        (Job.JobTitle LIKE ISNULL(@JobTitle, Job.JobTitle) + '%')
    ORDER BY Clients.ClientID, Clients.ClientLastName, Clients.ClientFirstName
  3. You'll notice the many ISNULLs in the Select statements above. They are, aside from setting the values in the parameter section to null, the most important part of this procedure working.
  4. SQL
    ISNULL(@JobFinished, Job.JobFinished))

    If @JobFinished = null (we're using the default value), then the current value of the current record's Job.JobFinished is used instead.

  5. Remember those varchars back in the parameters section? Well, here's why we used them.
  6. SQL
    (Clients.ClientFirstName LIKE ISNULL(@ClientFirstName, Clients.ClientFirstName) + '%')

    Using varchar allows us to do partial word searches since trailing whitespaces are automatically trimmed off of varchar strings (not so with nchar an char). Combine that with + '%', and you can now enter the first part of a word and it will still pull all the words that start with those letters. For instance:

    If we pass 'jo' in as @ClientFirstName, the procedure would select "Joe", "John", "Joseph", etc.

  7. One last thing to notice is the DISTINCT keyword before the select statements this ensures you don't get a long list of repeating clients.

Implementation - Step 2: Filling a Strongly Typed Dataset

Using Visual Studio's Dataset Designer, we create a strongly typed dataset with our Stored Procedure. The designer automatically creates a Fill() and a GetData() method based on the parameter's of the Stored Procedure.

Now, it's just a matter of inputting our values into the Fill() method.

Well, here it is in action!

C#
this.searchClientsTableAdapter.Fill(
  this.tattooDataSet.SearchClients,
  this.NewClientFirstNameTextBox.Text == "" ? null : this.ClientFirstNameTextBox.Text,
  this.NewClientLastNameTextBox.Text == "" ? null : this.NewClientLastNameTextBox.Text,
  this.UndecidedRadioButton.Checked ? (bool?)null : 
      (this.HasTattooRadioButton.Checked ? true : false),
  !this.UseJobIsCheckBox.Checked ? (bool?)null : 
      (this.BothRadioButton.Checked ? (bool?)null : 
      (this.CompletedRadioButton.Checked ? true : false)),
  !this.UseJobTypeCheckBox.Checked ? (short?)null : 
     (short)this.JobTypeComboBox.SelectedValue,
  !this.SearchArtistCheckBox.Checked ? (short?)null : 
     (short)this.ArtistsComboBox.SelectedValue,
  !this.MinimumTimeCheckBox.Checked ? (double?)null : 
        double.Parse(this.MinimumHourNumericUpDown.Value.ToString()),
  !this.MaximumTimeCheckBox.Checked ? (double?)null : 
        double.Parse(this.MaximumHourNumericUpDown.Value.ToString()),
  !this.UseAdvertisementsCheckBox.Checked ? (short?)null : 
        (short)this.AdvertismentsComboBox.SelectedValue,
  !this.BornAfterCheckBox.Checked ? (DateTime?)null : 
    this.MinimumDOBDateTimePicker.Value,
  !this.BornBeforeCheckBox.Checked ? (DateTime?)null : 
    this.MaximumDOBDateTimePicker.Value,
  this.NewClientAddressTextBox.Text == "" ? null : 
    this.NewClientAddressTextBox.Text,
  this.NewClientCityTextBox.Text == "" ? null : 
    this.NewClientCityTextBox.Text,
  this.NewClientProvenceTextBox.Text == "" ? null : 
    this.NewClientProvenceTextBox.Text,
  this.NewClientPostalCodeTextBox.Text == "" ? null : 
    this.NewClientPostalCodeTextBox.Text,
  this.NewClientHomePhoneTextBox1.Text == "" ? null : 
    this.NewClientHomePhoneTextBox1.Text + 
    (((this.NewClientHomePhoneTextBox1.TextLength == 
       this.NewClientHomePhoneTextBox1.MaxLength) && 
    (this.NewClientHomePhoneTextBox2.TextLength > 0)) ? 
    ("-" + this.NewClientHomePhoneTextBox2.Text) : ("")) + 
    (((this.NewClientHomePhoneTextBox2.TextLength == 
       this.NewClientHomePhoneTextBox2.MaxLength) && 
    (this.NewClientHomePhoneTextBox3.TextLength > 0)) ? 
    ("-" + this.NewClientHomePhoneTextBox3.Text) : ("")),
  this.NewClientWorkPhoneTextBox1.Text == "" ? null : 
    this.NewClientWorkPhoneTextBox1.Text + 
    (((this.NewClientWorkPhoneTextBox1.TextLength == 
       this.NewClientWorkPhoneTextBox1.MaxLength) && 
    (this.NewClientWorkPhoneTextBox2.TextLength > 0)) ? 
    ("-" + this.NewClientWorkPhoneTextBox2.Text) : ("")) + 
    (((this.NewClientWorkPhoneTextBox2.TextLength == 
       this.NewClientWorkPhoneTextBox2.MaxLength) && 
    (this.NewClientWorkPhoneTextBox3.TextLength > 0)) ? 
    ("-" + this.NewClientWorkPhoneTextBox3.Text) : ("")),
  this.NewClientEmailTextBox.Text == "" ? null : 
       this.NewClientEmailTextBox.Text,
  this.BothSexRadioButton.Checked ? (bool?)null : 
     (this.MaleRadioButton.Checked ? true : false),
  !this.ScheduledAfterCheckBox.Checked ? (DateTime?)null : 
      this.JobMinimumDateTimePicker.Value,
  !this.ScheduledBeforeCheckBox.Checked ? (DateTime?)null : 
      this.JobMaximumDateTimePicker.Value,
  !this.UseJobTitleCheckBox.Checked ? null : this.JobTitleSearchTextBox.Text);

I've wrapped each parameter on its own line for readability, but it's still just one function call. Adding or removing search options is as simple as adding or removing a parameter.

There two really nifty things I want to point out about the Fill() method, and they both involve one single character: ?.

  1. ? is the Conditional Operator, and has been around in one form or another for years (I can remember using it back when I was coding in plain old C).
  2. If you think of if...else and select...case statements as the foot soldiers in your programming arsenal, they get the job done, but can sometimes be brutal and messy in their tactics, and then ? is part of your special forces:

    1. They can go places others can't (try putting an if statement as a parameter in a method call).
    2. They provide a cleaner, more efficient, more concise solution.
    C#
    this.NewClientCityTextBox.Text == "" ? null : this.NewClientCityTextBox.Text,

    The first part this.NewClientCityTextBox.Text == "" is the conditional part; if it evaluates to be true, then the first value after the ?, or in this case, null is used; otherwise, the value following the :, or in this case, this.NewClientCityTextBox.Text is used.

    Conditional statements can be nested, and are evaluated in a right to left fashion.

    C#
    !this.UseJobIsCheckBox.Checked ? (bool?)null : 
      (this.BothRadioButton.Checked ? (bool?)null : 
      (this.CompletedRadioButton.Checked ? true : false)),
    • First, (this.CompletedRadioButton.Checked ? true : false) evaluates to either be true or false.
    • Second, this.BothRadioButton.Checked ? (bool?)null : evaluates to be either null or the result from the previous condition.
    • Finally, !this.UseJobIsCheckBox.Checked ? (bool?)null : evaluates to be either null or the result from the previous condition.

    If you look back at the picture above, you'll notice the Job Is check box is checked, and to its right are three radio buttons that are actually contained in their own panel to group them accordingly.

    Now looking at the previous nested conditional statements in plain English, this is what we're saying:

    • If the Completed radio button is checked, then it's true; if not, we assume that the Incompleted radio button is checked, passing false to the next step.
    • Now if either radio button is checked, then we know that the Incompleted radio button is not checked, so we pass null to the next step; if not, then we know that the Incompleted radio button is definitely checked, so we pass false on to the next step.
    • Finally, if the Job Is checkbox is not checked, then that part of the search is disabled, and regardless of what the previous values are, true, false, or null, it's automatically null; if not, it evaluates to the value of the previous statement.
  3. ? In .NET 2.0, in addition to its previously mentioned duties as the conditional operator, it is also the new nullable operator. This makes anyone who interacts with databases a very happy person!
  4. Certain data types in C#, VB.NET, C, Java, etc., and .NET framework types such as bool, for example, don't have a null state that can be assigned null.

    C#
    bool IsThisNull = null;

    won't compile. In a database, a boolean can be either true, false, or null. The problem in the past has been bridging that null gap between the source code nulls and the database nulls. The Nullable operator does this quite beautifully.

    C#
    bool? IsThisNull = null;

    This compiles and works fine. IsThisNull still works just like a regular bool, with the added bonus that it can be null for passing value to a database Stored Procedure or inline SQL statement.

    The .NET 2.0 framework, in the same fashion that they provide us with overloaded operators like ++, --, =+, also provides an overloaded nullable operator ??.

    C#
    bool? IsThisNull = null;
    bool RegularBool = IsThisNull ?? false;

    RegularBool is assigned whatever true or false value IsThisNull holds, unless IsThisNull is null - then RegularBool is false.

    We're not done with the fun yet boys and girls. Explicit conversions (or boxing):

    C#
    !this.UseAdvertisementsCheckBox.Checked ? (short?)null : 
                (short)this.AdvertismentsComboBox.SelectedValue,

    Notice the (short?)? This handy little ? casts our plain old null value into a nullable short type with a null value, so our Stored Procedure gets the null value it's looking for, and our type safe C# method gets its short type parameter it expects.

    Everyone gets what they want, and in the spirit of MPATQFTHG: "and there was much rejoicing".

Reviewing

Step 1 - Write our Stored Procedure remembering to set each parameter to null as its default value; then use ISNULL statements to ensure the correct values are passed in our Select statements.

Step 2 - Create our strongly typed Dataset with a strongly typed TableAdapter in Visual Studio.

Step 3 - Call the generated Fill() method using the ? conditional operator as well as the ? Nullable operator to filter and pass the correct value to the parameter and to cast the correct types, respectively.

Author's Corner

I probably won't be putting a code sample up to download for the simple fact that this article is not meant to give you a working solution but rather a good step by step plan to creating your own working solution. Besides, all that you need to know is in the article in the <pre></pre> blocks.

In the future, I'll throw together a demo program for you to play around with; at the moment, it's used in a rather massive software suite being developed, and no one here is going to want to buy the whole farm just to find out how the tractor runs.

I always welcome comments, questions, and even complaints, so don't be shy.

History

  • March 24, 2006 - First edition.
  • March 29, 2006 - Made some minor changes; someone pointed out that I was calling it a DataAdapter and not a TableAdapter; I'm so used to using the DataAdapters in 1.1 that I completely missed it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead Broome County Government
United States United States
Not much to say really If you saw me now you'd probably think I was a vagrant or a heavy drug user

Truth is I'm just a geek with an extreemly high pain tollerance. When I'm not writing code I'm either getting something pierced, or getting another tattoo.

In general i've been programing since 6th or 7th grade (not sure when we started apple basic :-P) I've been hooked ever since. Jumped right into C which i pretty much taught myself. Picked up enough C++ when it came out to write what i needed then i learned pascal (if your wondering why i learned pascal after C well my school was a little behind the 8-ball, so I ended up with a class to catch up on some sleep) I picked up C# from the start when all you could get was the .NET sdk and the csc command line compiler. Inbetween time my uncle sent me an SQL book so I learned that (we're talking industry standard SQL not MS or Oracle's extra little extensions). For the most part ever since MS released VS.NET 2002 I've been using it and its subsequent upgrades to write an even mix of windows forms and database applications.

Currently working at Lockheed Martin's System Integration site in Owego, NY I develop custom deployment solutions for site wide software installations, as well as site level support tools, utilities and custom process automation software for both interdepartmental use as well as special requests from other bussiness areas.

Comments and Discussions

 
Questionnon text field conversions???? Pin
Member 1186675516-Jul-19 12:48
Member 1186675516-Jul-19 12:48 
QuestionNice,But what about fields that have null value? Pin
Mohammad A. Amer30-Aug-13 7:29
professionalMohammad A. Amer30-Aug-13 7:29 
Nice it worked very well but when i have null values in a field the stored leaves it.
can u help me to handel it

USE [OVMDB]
GO
/****** Object: StoredProcedure [dbo].[sp_SearchEnquiries] Script Date: 08/30/2013 15:14:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_SearchEnquiries]
(
@ID bigInt = null,
@InquiryCode nvarchar(50) = null,
@ReceivedDateFrom datetime = null,
@ReceivedDateTo datetime = null,
@ReceivedBy nvarchar(50)= null,
@ProjectName nvarchar(300) = null,
@Country nvarchar(50)= null,
@Contractor nvarchar(300)= null,
@Consultant nvarchar(300)= null,
@Client nvarchar(300)= null,
@ProjectStatus nvarchar(50)= null,
@ResponsiblePerson nvarchar(50)= null,
@ContactName nvarchar(50)= null,
@StatusName nvarchar(50)= null,
@Bedget decimal(18, 2)= null,
@OfferValue decimal(18, 2)= null,
@Apprivation nvarchar(50)= null,
@Remarks nvarchar(200)= null
)
AS
SET NOCOUNT ON;
SELECT Inquiry.ID, Inquiry.InquiryCode, CONVERT(nvarchar(50), Inquiry.ReceivedDate, 111) AS ReceivedDate, Users.Name AS ReceivedBy, Inquiry.ProjectName,
Country.Country, Contractor.Contractor, Consultant.Consultant, Client.Client, ProjectStatus.ProjectStatus, Users_1.Name AS ResponsiblePerson,
Contact.FName + N' ' + Contact.LName AS ContactName, InquiryStatus.StatusName, Inquiry.Bedget, Inquiry.OfferValue, Currency.Apprivation, Inquiry.Remarks

FROM Inquiry INNER JOIN
Consultant ON Inquiry.ConsultantID = Consultant.ID INNER JOIN
Contact ON Inquiry.ContactID = Contact.ID INNER JOIN
Contractor ON Inquiry.ContractorID = Contractor.ID INNER JOIN
Country ON Inquiry.CountryID = Country.ID INNER JOIN
ProjectStatus ON Inquiry.ProjectStatus = ProjectStatus.ID INNER JOIN
InquiryStatus ON Inquiry.Status = InquiryStatus.ID INNER JOIN
Currency ON Inquiry.CurrencyID = Currency.ID INNER JOIN
Users ON Inquiry.ReceivedBy = Users.ID INNER JOIN
Users AS Users_1 ON Inquiry.ResbonsiblePerson = Users_1.ID INNER JOIN
Client ON Inquiry.ClientID = Client.ID

WHERE
(Inquiry.ID like ISNull(@ID,Inquiry.ID))
AND(InquiryCode like ISNull(@InquiryCode,InquiryCode))
AND (ReceivedBy like ISNull(@ReceivedBy,ReceivedBy))
-- AND (ProjectName like ISNull(@ProjectName,ProjectName,@ProjectName)+'%') --This field have null values
AND (Country like ISNull (@Country,Country))
AND (Contractor like ISNull (@Contractor,Contractor))
AND (Consultant like ISNull(@Consultant,Consultant))
AND (Client like ISNull(@Client,Client))
AND (ProjectStatus.ProjectStatus like ISNull(@ProjectStatus,ProjectStatus.ProjectStatus))
AND (Users_1.Name like ISNull(@ResponsiblePerson,Users_1.Name))
AND (Contact.FName + N' ' + Contact.LName like ISNull(@ContactName,Contact.FName + N' ' + Contact.LName))
AND (StatusName like ISNull(@StatusName,StatusName))
AND (Bedget like ISNull(@Bedget,Bedget))
AND (OfferValue like ISNull(@OfferValue,OfferValue))
AND (Apprivation like ISNull(@Apprivation,Apprivation))
-- AND (Remarks <> Null OR Remarks like ISNull(@Remarks,Remarks)) --This field have null values
AND (ReceivedDate >= ISNull(@ReceivedDateFrom,ReceivedDate))
AND (ReceivedDate <= ISNull(@ReceivedDateTo,ReceivedDate))
GeneralMy vote of 5 Pin
bidyut1234517-Jun-13 19:11
professionalbidyut1234517-Jun-13 19:11 
QuestionNULL value issues Pin
newoneshere2-May-13 20:42
newoneshere2-May-13 20:42 
GeneralMy vote of 5 Pin
Thitos21-Feb-13 21:46
Thitos21-Feb-13 21:46 
GeneralMy vote of 1 Pin
arulb2w22-Mar-12 20:01
arulb2w22-Mar-12 20:01 
GeneralMy vote of 5 Pin
ut_risingstar13-Dec-10 23:17
ut_risingstar13-Dec-10 23:17 
GeneralMy vote of 3 Pin
pavankar.vemuris20-Sep-10 22:37
pavankar.vemuris20-Sep-10 22:37 
GeneralCritical Logic about the IF used Pin
GnosticMan13-Jul-10 5:12
GnosticMan13-Jul-10 5:12 
GeneralMy vote of 4 Pin
technette28-Jun-10 14:04
technette28-Jun-10 14:04 
QuestionHow to pass multiple values for one parameter Pin
Jay Gamblin27-Jun-08 10:49
Jay Gamblin27-Jun-08 10:49 
GeneralHi, great work! Pin
Giovanni Tresoldi1-Oct-07 5:16
Giovanni Tresoldi1-Oct-07 5:16 
GeneralRe: Hi, great work! Pin
TofuBug2410-Nov-07 15:27
TofuBug2410-Nov-07 15:27 
QuestionFields in Database Null? Pin
Doncp5-Apr-06 11:53
Doncp5-Apr-06 11:53 
AnswerRe: Fields in Database Null? Pin
TofuBug245-Apr-06 12:28
TofuBug245-Apr-06 12:28 
BugRe: Fields in Database Null? Pin
queries128-Mar-12 4:22
queries128-Mar-12 4:22 
GeneralSQL Efficiency Pin
Oskar Austegard4-Apr-06 5:20
Oskar Austegard4-Apr-06 5:20 
GeneralThe good, bad and ugly Pin
kckn4fun29-Mar-06 1:14
kckn4fun29-Mar-06 1:14 
GeneralRe: The good, bad and ugly Pin
TofuBug2429-Mar-06 5:32
TofuBug2429-Mar-06 5:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.