Searching SQL database with TableAdapter

[MilantoMinsk]

Searching SQL database

I am trying to write a code for searching a local SQL database for my desktop application (VS 2012). At this stage, all I want to do is check database for user inputted barcode. I made a simple parameterised query:

SELECT        ID, Name, Price, Barcode
FROM          ItemList
WHERE        (Barcode = @Barcode)

And this is code for a button:

Dim g As New Database1DataSet.ItemListDataTable
 
If Me.ItemListTableAdapter.FillBy(g, (CInt(TextBox1.Text)) > 0 Then
            MsgBox("Item Found!")
End If

Is this a good way to do it? Or is there maybe more efficient way? I am expecting for my database to have about three thousand items.

Thanks

 

[jmcilhinney]

First of all, you're supposed to change the name of that method based on the filter you're using. If you're filtering by barcode then the method should be named FillByBarcode.

As for the question, do you actually want to get the data for the record or just determine whether the record exists? If it's the former then I would suggest calling the GetDataBy method, which returns a new DataTable, rather than the FillBy method, which requires an existing DataTable. If it's the latter then you should create a query that returns a COUNT of matching records rather than the records themselves. That means adding it to a different table adapter because the schema then won't match the original DataTable.

 

[JohnH]

If it's the latter then you should create a query that returns a COUNT of matching records rather than the records themselves. That means adding it to a different table adapter because the schema then won't match the original DataTable.

At least in VB 2010 you can add a query in existing table adapter, choose option "SELECT which return a single value" and enter the sql expression (default presents a Count expression), and rename it from default "ScalarQuery". This creates a method that calls SqlCommand.ExecuteScalar behind the scenes.