Answered Sending multiple values to an SQL Query

[giasone777]

Hello,

I would like to send multiple queries to an sql server. It works for one but I am trying to make it work for multiple values stored in an array which came from multiple selections from a listbox.

So right now @RealVms is determining the query, so for instance I would like that query to pull the values from within the array gotselections[num] so it would basically have the following effect

string selectSql = ("select Name, vmHost, Uid from db_owner.vms where UID Like @gotselections[0] and where UID like @gotselections[1] "); or higher depending on the number of selections from the listbox.

Please help - Thank-you in advance. Jason.

These are VMware objects stored in an sql database. At the end the query is sent to an asp grid. The following code does work but for only one query.

string hosty = vmtofind.Text;
String GetSelection = objectList.SelectedItem.Text;
string RealVms = "%" + GetSelection + "%";

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconnection"].ConnectionString))
{
    con.Open();

    string selectSql = ("select Name, vmHost, Uid from db_owner.vms where UID Like @RealVms");
    SqlCommand cmd = new SqlCommand(selectSql, con);

    cmd.Parameters.AddWithValue("@RealVms", RealVms);

    SqlDataReader dr = cmd.ExecuteReader();

    GridView1.DataSource = dr;
    GridView1.DataBind();

    con.Close();

 

[giasone777]

I have another question about the code you gave me.

Right now if someone produces a list of 200 machines and enters them into the list box via a text file. The page searches and produces the report based on what it found.
For instance if it found 195 out of 200 machines it will only show the 195 machines that it found. The user though might not be aware that only 195 machines were found.

Is it possible to alter your query so that the following is output

Listbox Shows
Host1
Host2
Host3
Host4
Host5
Host6
Host7
Host8
Host9
Host10

Query Runs and desired output is -

Name Result
Host1 - found
Host2 - found
Host3 - found
Host4 - found
Host5 - found
Host6 - found
Host7 - found
Host8 - found
Host9 - not found
Host10 - not found

Since Host9 and Host10 were not found in the database, I want the user to be aware of this.

Currently if only 8 hosts are found the output is -

Name
Host1
Host2
Host3
Host4
Host5
Host6
Host7
Host8

Thank-you.

Jason.

 

[Skydiver]

I think there is somekind of disconnect here. The query in post #13 builds up a query looking for specific IDs. (For now let's set aside the performance implications of having a SQL query that has almost 200 boolean expressions.) That query doesn't do anything about actually populating the results in your listbox.

What you can do is iterate over the results and keep a scorecard of what was found and not found. And then use that scorecard for populating your results listbox.

 

[giasone777]

The output is going to a grid. As shown below. So are you saying compare the contents of the listbox with the contents of the results in the grid? Can you give me an example of how that might look? Thank-you.

                command.CommandText = query.ToString();
                command.Connection = connection;
                SqlDataReader dr = command.ExecuteReader();

                GridView1.DataSource = dr;

        
                GridView1.DataBind();

 

[Skydiver]

First of all, you shouldn't be using your UI as your data model. We programmers have learned (the hard way) in the late 90's and early 2000's to keep the data model, the view of the data, and the business logic separate from each other. This is why Ruby on Rails was such a hit because of it's push for the MVC architecture (Model, View, Controller) which cut down on the fragile tightly coupled code of previous generations. In your case above, you are using your UI (the list) as your data model.

Instead of feeding the data reader directly as a data source for your grid, you'll need to go through the results found by the data reader and correlate it with the things you were originally searching for. Create a list of that correlation and bind to that list.

 

[giasone777]

First of all, you shouldn't be using your UI as your data model. We programmers have learned (the hard way) in the late 90's and early 2000's to keep the data model, the view of the data, and the business logic separate from each other. This is why Ruby on Rails was such a hit because of it's push for the MVC architecture (Model, View, Controller) which cut down on the fragile tightly coupled code of previous generations. In your case above, you are using your UI (the list) as your data model.

Instead of feeding the data reader directly as a data source for your grid, you'll need to go through the results found by the data reader and correlate it with the things you were originally searching for. Create a list of that correlation and bind to that list.

Can you show me how I send the results from the Data Reader to an Array?

Thank-you.

 

[Skydiver]

Why would you need an array? You would need to iterate over the reader to build a list. Then from the list, build an array. Then you would iterate over the array to do you comparisons. So skip the middle man and do the comparisons directly.

Here's the pseudo code of building the array:

// build list of records
var list = new List<Record>()
while (reader.Read())
{
    var record = new Record()
    record.Name = record["Name"]
    record.VMHost = record["vmHost"]
    record.Uuid = record["uuid"]
    list.Add(record);
}

// convert list of records to an array
var recordArray = list.ToArray();

 

[giasone777]

Isn't the list already in the Data Reader?

 

[Skydiver]

No. Or more like the data reader has acecss to the list, but it's not in a readily consumable form. You need to iterate over the reader to get each item one at a time.

See the documentation.

SqlDataReader Class (System.Data.SqlClient)

Provides a way of reading a forward-only stream of rows from a SQL Server database. This class cannot be inherited.

docs.microsoft.com

 

[giasone777]

Ok thanks I will take a look at it.

 

[giasone777]

I changed it as follows since there were a couple of red squiggles one under list which I changed to values which made the squiggled disappear but Record is still show as a red squiggle, why is that?

It says the namespace Record could not be found.

 var values = new List<string>();
                while (dr.Read())
                {
                  var record = new Record();
                    record.Name = record["Name"];
                    record.VMHost = record["vmHost"];
                    record.Uuid = record["uuid"];
                     values.Add(record);
                }

 

[Skydiver]

As I said, I was showing you some pseudo-code.

You are supposed to create your own Record class that will hold your data that you want the DataGridView to display.

Out of curiosity, what tutorial or book are you using to learn C#? You seem to be missing several fundamentals and jumped straight into using ADO.NET.

 

[giasone777]

Mostly just throwing it together, no books, what fundamentals do you suggest?

I am Vmware support at a well known company, just struggling through the c# when I have down time - I am not a programmer, as you can see.

 

[Skydiver]

I would suggest these, albeit some of the items have steeper learning curve than they need to be:

C# Guide - .NET managed language

The C# guide has everything you need to learn C# and get started writing .NET apps. Learn C# programming - for beginning developers, developers new to C#, and experienced C# / .NET developers.

docs.microsoft.com

Since I taught myself C# while the language was still in development and alpha, I can't really recommend any good current books for C#. Perhaps others can chime in with their suggestions. As for learning just ADO.NET itself, I found this to be pretty good:

ADO.NET

Learn about ADO.NET, classes that expose data access services for .NET Framework programmers for creating distributed, data-sharing applications.

docs.microsoft.com

 

[giasone777]

It is amazing though how shear determination can make up for a lack of knowledge/intelligence.

 

[Skydiver]

Yup: Michael Faraday - Wikipedia