sqlcommand c# method with sql paramather

Question

I have this method, which I have in the base class that helps me to select anything from the children classes and also to reduce code repetition. The problem is when I call it I get an error which is a NullReferenceException (and when I look it up I find that the command in the method is empty). This is the method in question: This way I already know how to use but the other one I don't

SqlCommand command = new SqlCommand("select * from Customers where idCustomer=@idCustomer", OpenConnection());
command.Parameters.AddWithValue("@idCustomer", Id);
SqlDataReader reader = command.ExecuteReader();
Customer Onecustomer = null;
if (reader.Read())
{
    Onecustomer = ReadCustomer(reader);
}

protected DataTable ExecuteSelectQuery(String query, params SqlParameter[] sqlParameters)
{
    SqlCommand command = new SqlCommand();
    DataTable dataTable;
    DataSet dataSet = new DataSet();

    try
    {
        command.Connection = OpenConnection();
        command.CommandText = query;
        command.Parameters.AddRange(sqlParameters);
        command.ExecuteNonQuery();
        adapter.SelectCommand = command;
        adapter.Fill(dataSet);
        dataTable = dataSet.Tables[0];
    }
    catch (SqlException e)
    {
        return null;
        throw new Exception("Error :" + e.Message);
    }
    finally
    {
        CloseConnection();
    }
    return dataTable;
}

Here how I call it

string author = "Alfred Schmidt";
int id = 1;

//  ExecuteEditQuery("UPDATE Books SET Title =@param1 WHERE idBook =@param2", sqlParameters);
//SqlParameter[] sqlParameters = new SqlParameter[1]
//{
//    new SqlParameter ("@param1",author),
//};

SqlParameter[] myparm = new SqlParameter[1];
myparm[0] = new SqlParameter("@Author", SqlDbType.NVarChar, 200);
myparm[0].Value = author;

String query = @"SELECT * FROM Books  WHERE Author =@Author";
DataTable dt = ExecuteSelectQuery(query, myparm);

for (int i = 0; i < dt.Rows.Count; i++)
{
    Console.WriteLine(dt.Rows.ToString());
}
Console.Write("");

1

`adapter` is not defined anywhere in the code you have posted? - The `command.ExecuteNonQuery();` should not be there, remove it, `SqlCommand` implements `IDisposable` so should be withn a `using` block. — Alex K., Mar 08 '19 at 12:49

score 0 · Answer 1 · answered Mar 08 '19 at 12:58

Is your OpenConnection() method returns a connection object. It may couse the error, the implementation of the method is not given. Also the adpater is not defined in the code, may be it can be the cause of error too, if it is not initialized.

And i want to say few things about your code:

1) You have and unnecessary command.ExecuteNonQuery(); statement in your ExecuteSelectQuery method.

2) DataAdapter can directly fill DataTable, you dont have to use DataSet.

Mikael Dúi Bolinder · Answer 2 · 2019-03-08T13:27:38.393

0

Here's a proper rewrite of your method.

protected DataTable ExecuteSelectQuery(String query, params SqlParameter[] sqlParameters)
{
    using (SqlCommand command = new SqlCommand())    
        try
        {
            command.CommandText = query;
            command.Parameters.AddRange(sqlParameters);
            command.Connection = OpenConnection();

            DataTable dataTable = new DataTable();
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                adapter.Fill(dataTable);
            return dataTable;
        }
        catch (SqlException e)
        {
            return null;
            throw new Exception("Error :" + e.Message);
        }
        finally
        {
            CloseConnection();
        }
}

Note that the SqlDataAdapter can Open() and Close() the connection by itself, if the SqlConnection is Closed when Fill is called.

edited Mar 08 '19 at 13:27

answered Mar 08 '19 at 13:22

Mikael Dúi Bolinder

2,080
2
19
44

If you're throwing an exception already in your `catch`, which will stop execution, what's the point of having `return null;` ? That seems as though the exception is unreachable since the method exists first? – Jaskier Mar 08 '19 at 13:36
@Symon copied from the question, and yes, it's unreachable right now. It should also contain the `SqlException` as an `InnerException` and possibly be using `ApplicationException` instead of the `Exception` base class. – Mikael Dúi Bolinder Mar 08 '19 at 13:40
Thank you fro the explaination . now if i want to call this method and give args to it how i can do this ?? like i prepare a query and paramter list but i dont know how to do it . – user3560798 Mar 08 '19 at 15:33
@user3560798 Call it like you do in you question, `var resultTable = ExecuteSelectQuery(query, parameters);`. – Mikael Dúi Bolinder Mar 08 '19 at 15:57

sqlcommand c# method with sql paramather

2 Answers2