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.
– Alex K., Commented Mar 8, 2019 at 12:49

emumcu · Accepted Answer · 2019-03-08 12:58:34Z

0

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.

answered Mar 8, 2019 at 12:58

emumcu

2351 gold badge2 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mikael Dúi Bolinder · Accepted Answer · 2019-03-08 13:27:38Z

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 8, 2019 at 13:27

answered Mar 8, 2019 at 13:22

Mikael Dúi Bolinder

2,2862 gold badges26 silver badges53 bronze badges

4 Comments

Jaskier Over a year ago

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?

Mikael Dúi Bolinder Over a year ago

@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.

user3560798 Over a year ago

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 .

Mikael Dúi Bolinder Over a year ago

@user3560798 Call it like you do in you question, var resultTable = ExecuteSelectQuery(query, parameters);.

Collectives™ on Stack Overflow

sqlcommand c# method with sql paramather

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related