Querying an SQL database using unknown number of values in ASP.NET C#

Question

Okay, I've built a SQL Server database which is being accessed and manipulated by an ASP.NET UI (which I have also developed) to allow others at work to search the DB easily. The database holds data on numerous locations where we have network equipment operating.

I have been asked to build into the UI the capability to query the database for multiple IP addresses - e.g. a user will enter into a textbox "192.168.1.0, 18.15.156.4", click enter and be presented with the results in the gridview. Multiple IP addresses will be separated with a ,.

The code below basically strips out space characters, looks for a , (to determine how many ips there are to query) and if found puts them into an array. A for loop then puts each array item into its own session variable, and from there they are ready to be queried:

protected void siteSearchButton_Click(object sender, EventArgs e)
{
    //checks IP search textbox is empty
    if (ipQueryTextBox.Text != null)
    {
        searchErrorLabel.Visible = false;
        string addresses = ipQueryTextBox.Text;

        //checks for any blank spaces in the addresses variable
        if (addresses.Contains(" "))
        {
            addresses = addresses.Replace(" ", "");
        }

        //sceens for multiple search items by looking for a ','
        if (addresses.Contains(","))
        {
            //declaring int variables to be used in each of the respective loops
            int j = 0;

            string[] IParray = addresses.Split(',');

            //if i is equal to the length of the "addresses" variable, execute the for loop enclosed
            foreach (string s in IParray)
            {
                Session["IP" + j] = IParray[j];
                j = j + 1;
            }
        }
    }
}

As the number of ips to be queried against the database is dynamic I have come to the conclusion that I will have to use C# code (which I'm okay with), but as far as what I've got so far below, I'm unsure how to go about querying the db 'x' amount of times using code presumably I'll need to use a while loop, is anyone able to offer some insight?

//****THE SQL COMMAND BELOW NEEDS ADAPTING TO ALLOW MULTIPLE QUERIES FOR EACH OF THE VALUES STORED IN IParray ---> each session variable
if()
{
    //opens a new sqlconnection to read and populate edit textboxes from the Inventory database
    using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=Inventory;Integrated Security=True"))
    {
        //declares SQLCommand type named 'command' and assigns it a string value of SQL code
        SqlCommand command =
            new SqlCommand("select * from LOCATION WHERE IP_ADDRESS=@IP_ADDRESS", connection);

        //outlines parameters
        command.Parameters.Add("@IP_ADDRESS", System.Data.SqlDbType.VarChar);
        command.Parameters["@IP_ADDRESS"].Value = Session["IP"+j];;
        connection.Open();

        //opens database connection
        SqlDataReader read = command.ExecuteReader();

        //while loop will convert each record to string value and print entry into textbox. Will continue untill it runs out of lines
        while (read.Read())
        {

        }
        read.Close();
    }
}

Answer 1

Instead of using multiple queries, just use SQL's IN clause. It does require a little bit more work to set the query parameters though.

string[] ips = new string[] { "192.168.0.1", "192.168.0.2", "192.168.0.3" };
string[] parameters = ips.Select(
    (ip, index) => "@ip" + index.ToString()
).ToArray();

string commandFormat = "SELECT * FROM LOCATION WHERE IP_ADDRESS IN ({0})";
string parameterText = string.Join(",", parameters);
string commandText = string.Format(commandFormat, parameterText);

using (SqlCommand command = new SqlCommand(commandText)) {
    for(int i = 0; i < parameters.Length; i++) {
       command.Parameters.AddWithValue(parameters[i], ips[i]);
    }
}

In the above example, the generated command will be SELECT * FROM LOCATION WHERE IP_ADDRESS IN (@ip1,@ip2,@ip3), and the parameter values will be set accordingly.

(The above solution was very much inspired by this answer.)

Answer 2

First thing - why create multiple session objects when you needs just one to store the values?

I'd try to change your code like this:

if (ipQueryTextBox.Text != null)
{
    searchErrorLabel.Visible = false;
    string addresses = ipQueryTextBox.Text;
    addresses = addresses.Replace(" ", "");
    addresses = addresses.Replace(",", "','");
    Session["addresses"] = addresses;
}

For the SQL part, you can now easily utilize SQL IN operator, documented here for instance: http://www.w3schools.com/sql/sql_in.asp

SqlCommand command = new SqlCommand("select * from LOCATION WHERE IP_ADDRESS IN (@IP_ADDRESSES)", connection);
command.Parameters.AddWithValue("IP_ADDRESSES", Session["addresses"]);

This should probably work, but I haven't tested it, so it may require some tweaking. Hope you get the idea.

Answer 3

why do you need parameters at all.

//get IP address from UI;

string IPAddress = ipQueryTextBox.Text; //e.g. "192.168.0.1,192.168.0.2,192.168.0.3"

string commandFormat = "SELECT * FROM LOCATION WHERE IP_ADDRESS IN ('" + string.Join("','", IPAddress.split(",")) + "')";

now execute the query

Answer 4

Thank you to everyone who has responded, here is the solution I came up with derived from the answers above:

protected void siteSearchButton_Click(object sender, EventArgs e)
{
    //checks IP search textbox is empty
    if (ipQueryTextBox.Text != null)
    {
        searchErrorLabel.Visible = false;
        string addresses = ipQueryTextBox.Text;

        //checks for any blank spaces in the addresses variable
        if (addresses.Contains(" "))
        {
            addresses = addresses.Replace(" ", "");
        }

        //sceens for multiple search items by looking for a ','
        if (addresses.Contains(","))
        {
            string[] IParray = addresses.Split(',');

            string[] Parameters= IParray.Select((IP, index)=>"@ip"+ index.ToString()).ToArray();
            string commandformat ="SELECT * FROM LOCATION WHERE IP_ADDRESS IN ({0})";
            string parametertxt= string.Join(",",Parameters);
            string commandtxt= string.Format(commandformat,parametertxt);

            //creates an SQL connection "connection" opens the connection creates the sql command to be executed & binds and refreshes the gridview
            using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=Inventory;Integrated Security=True"))
            {
                SqlDataReader reader = null;

                connection.Open();
                SqlCommand command = new SqlCommand(commandtxt, connection);                

                for(int i =0; i<Parameters.Length; i++)
                {
                    command.Parameters.AddWithValue(Parameters[i],IParray[i]);                        
                }

                reader = command.ExecuteReader();

                browseSiteGridView.DataSource = reader;
                browseSiteGridView.DataBind();
                reader.Close();
                connection.Close();

            }
        }
        else
        {
            //creates an SQL connection "connection" opens the connection creates the sql command to be executed & binds and refreshes the gridview
            string commandtxt="SELECT * FROM LOCATION WHERE IP_ADDRESS ='"+addresses+"'";

            using (SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=Inventory;Integrated Security=True"))
            {
                SqlDataReader reader = null;

                connection.Open();
                SqlCommand command = new SqlCommand(commandtxt, connection);

                reader = command.ExecuteReader();

                browseSiteGridView.DataSource = reader;
                browseSiteGridView.DataBind();

                reader.Close();
                connection.Close();

            }

        }
    }