locked
How can i run a sql query with prameters ? RRS feed

  • Question

  • User1253338400 posted

    I have a select query that i need to pass in a parameter. I want to make a generic select so it doesnt matter how many parameters are passed in 

    If you look at the msdn example:

    static void GetSalesByCategory(string connectionString,
        string categoryName)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Create the command and set its properties.
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "SalesByCategory";
            command.CommandType = CommandType.StoredProcedure;
    
            // Add the input parameter and set its properties.
            SqlParameter parameter = new SqlParameter();
            parameter.ParameterName = "@CategoryName";
            parameter.SqlDbType = SqlDbType.NVarChar;
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = categoryName;
    
            // Add the parameter to the Parameters collection. 
            command.Parameters.Add(parameter);
    
            // Open the connection and execute the reader.
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
                    }
                }
                else
                {
                    Console.WriteLine("No rows found.");
                }
                reader.Close();
            }
        }
    }

    I may have any number of parameters , How can i make it generic so that i pass the parameters in ?

    thanks

    Monday, April 20, 2020 7:38 AM

Answers

  • User-18289217 posted
    public static DataTable ReadFromDatabase(string SP, [Optional] List<SqlParameter> parameters, string connectionstring)
    {
        DataTable data = new DataTable();
        using (SqlConnection connection = new SqlConnection(connectionstring))
        {
                    connection.Open();
                    using (SqlCommand command = connection.CreateCommand())
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = SP;
                        if (parameters != null)
                        {
                            if (parameters.Count > 0)
                            {
                                foreach (SqlParameter param in parameters)
                                {
                                    command.Parameters.Add(param);
                                }
                            }
                        }
                        data.Load(command.ExecuteReader());
                    }
        }
        return data;
    }

    HTH

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 20, 2020 7:57 AM

All replies

  • User-18289217 posted
    public static DataTable ReadFromDatabase(string SP, [Optional] List<SqlParameter> parameters, string connectionstring)
    {
        DataTable data = new DataTable();
        using (SqlConnection connection = new SqlConnection(connectionstring))
        {
                    connection.Open();
                    using (SqlCommand command = connection.CreateCommand())
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = SP;
                        if (parameters != null)
                        {
                            if (parameters.Count > 0)
                            {
                                foreach (SqlParameter param in parameters)
                                {
                                    command.Parameters.Add(param);
                                }
                            }
                        }
                        data.Load(command.ExecuteReader());
                    }
        }
        return data;
    }

    HTH

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 20, 2020 7:57 AM
  • User1253338400 posted

    Thankyou , looks so simple..  everyday is a learning day .

    Monday, April 20, 2020 8:10 AM