VSTO & .NET & Excel

June 30, 2007

Check if records exist or not with ADO.NET

Filed under: .NET & Excel, VSTO & Excel — Dennis M Wallentin @ 1:19 am

As a result of that I’m working extensively with ADO.NET I have found another useful (and simple) approach by using the ExecuteScala method to find out if a record exists or not. 

From what I understand the ExecuteScala is faster than the ExecuteReader method as it among other things create less overhead then the later.

Depending on how we construct the SQL query we can either get a resultset that contains:

  • The number of existing records that meet the criteria(s) or
  • The first column of the first returned row (record) when ExecuteScala executes the query.

In this context, i e checking if individual records exist or not, the first approach is used as the following example shows. In the below example we should only allow one record to exist and therefore we can check if the returned value of the aggregated calculation of COUNT is one (1 = does exist) or not (0 = does not exist):

 dbcount1.png

In the following case we check to see if the record exist or not by evaluating the resultset’s first column in the first retrieved record (row). In the following example the first column in the table _Code is of the type Autonumber which means that it can either be greater than 0 ( = does exist) or if not ( = does not exist):

dbautonumber1.png

The code for the first case can be downloaded from here.
The code for the second case above can be downloaded from here.

The more I work with ADO.NET the more I realize its power and compared with classic ADO it’s in many ways far better. The only thing I regret is that Excel and VBA does not support to work with it (for obvious reasons).

Please let me know if You think that the above can be solved in a better way and if there exists alternative approaches (except for the ExecuteReader).

Kind regards,
Dennis

Advertisements

9 Comments »

  1. Given that the title of the post starts “Check if record exist…”, I thought I should point out that ANSI SQL includes an EXISTS predicate, which should be used instead of the “SELECT *” or “SELECT COUNT(*)” queries you use here, both of which are going to be inefficient with large tables, irrespective of the ADO method you use.

    For example, something like this

    SELECT 1
    WHERE EXISTS
    (SELECT 1 FROM [_code] WHERE
    {your where clause goes here}
    )

    …will return the moment a row is found, where the two samples given will (must) look at every row.

    I realise the point of the post was more about ADO.NET alternatives (and I didn’t know about the Scalar thing, so thanks) but I thought the SQL chosen was, well, unfortunate!

    Comment by Mike Woodhouse — July 1, 2007 @ 10:38 am

  2. Mike,

    I always appreciate when people take their time to comment a blogpost like You do.

    Thanks for reminding me about the EXIST function in SQL.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — July 1, 2007 @ 11:25 am

  3. use instead
    Select Top 1 1 from _table
    where {your condition}
    and test if result from executescalar is null

    Comment by Vasile Minea — August 14, 2007 @ 11:48 pm

  4. Thanks for the improved approach 🙂

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — August 15, 2007 @ 3:10 pm

  5. Dennis,
    I just wanted to thank you, it was a nice way, and working great…
    thanks,
    Ahmad

    Comment by Ahmad A — April 15, 2009 @ 3:34 am

  6. I tried the codes and its working fine. Thanks Guys! I hope you could expand it so that the data from the other column ON THE SAME ROW WHERE RECORD EXIST was found can be used… like assigned to a textbox.

    SELECT 1
    WHERE EXISTS
    (SELECT 1 FROM [_code] WHERE
    {your where clause goes here}
    )

    if record found then textbox1.text=SALARY

    i.e where SALARY is in the same row where the record was found.

    Comment by Jazz — May 3, 2009 @ 2:09 am

  7. *****Simply with CSHAP to login***

    public static int Login(String id,String pass) {
    String sqllogin = “if exists(Select * from CustomerAccount where AccountNumber=@id and Password=@pass) set @k=1 else set @k=0 “;
    DBCloudNineBank.GetConnect();
    comm = new SqlCommand(sqllogin, conn);
    comm.Parameters.Add(“@id”, SqlDbType.NChar, 10).Value = id;
    comm.Parameters.Add(“@pass”, SqlDbType.VarChar, 50).Value = pass;
    comm.Parameters.Add(“@k”, SqlDbType.Int).Direction = ParameterDirection.Output;
    comm.ExecuteNonQuery();
    int k = (int)comm.Parameters[2].Value;
    return k;
    }

    The code snippet above in the DBCloudNineBank class

    //////////event occur when click the login button
    }
    }
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
    if (txtAccountNumber.Text != “” && txtPassword.Text != “”)
    {
    String pass = txtPassword.Text;
    String accNumber = txtAccountNumber.Text;
    /// call Login() method from DBCloudNineBank class
    int k = DBCloudNineBank.Login(accNumber, pass);
    if (k > 0)
    {
    //Move to welcome page

    Response.Redirect(“Welcome.aspx?accNum=”+accNumber);
    }
    else {
    lblStatus.Text = “Invali account number or password !”;
    }
    }
    }

    Comment by monkeyAsia — May 17, 2009 @ 9:30 am

  8. Thanks for taking Your time to post that solution – it’s highly appreciated. I will convert it to VB.NET and test it myself.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — May 17, 2009 @ 11:55 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: