How do I solve this SqlConnection in C#?

Question

This is my connection class

class connection
{
        public SqlConnection con;
        public SqlCommand cmd;
        public SqlDataAdapter sda;
        String pkk;

        public void connectionFunc()
        {
            con = new SqlConnection(@"Data Source=.;Initial Catalog=payroll;Integrated Security=True");
            con.Open();
        }

        public void dataSend(String SQL)
        {
            try
            {
                connectionFunc();
                cmd = new SqlCommand(SQL, con);
                cmd.ExecuteNonQuery();
                pkk = "";
            }
            catch(Exception)
            {
                pkk = "error";
            }
            con.Close();
        }

        public void dataGet(String SQL)
        {
            try
            {
                connectionFunc();
                sda = new SqlDataAdapter(SQL, con);
            }
            catch(Exception)
            {
            }
        }
}

And this is the use of the class:

connection con = new connection();
con.dataGet("Select * from [users] Where Userame = '" + textBox1.Text + "' and Password = '" + textBox2.Text + "'");

DataTable dt = new DataTable();

// this line throws an error
con.sda.Fill(dt);   

if(dt.Rows.Count > 0)
{
    this.Hide();
    Mainpage obj = new Mainpage();
    obj.Show();
}
else
{
    MessageBox.Show("Invalid UserName Or Password..!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

I get the following error:

An unhandled exception of type 'System.NullReferenceException' occurred in Payroll_Manegement.exe

I don't know what is the issue here if someone could help me that would be great.

Answer 1

To answer your question: the error comes from the fact that your DataTable only is instantiated but not populated before the SqlDataAdapter.Fill is fired, so when the latter tries to do something useful it can only return an exception explaining exactly what is wrong.

Your connection class pains my eyes in terms of insecure variable scope (why use public everywhere?) and non-existent connection pooling. Using a class like this opens up your application for all kinds of insecure horror. Please try to keep things simple if your application is not too complicated; the basics from the .NET Framework should already be enough for your purposes. Just do something similar to the code below (with regard to parameterization of your queries like the others suggested):

string connectionString = "server=myServer;User ID=myUser;Password=myPwd;"; // could also be internal static on class level

string theQuery = "SELECT * FROM dbo.Users WHERE Username = @userName AND Password = @password";

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
    sqlConnection.Open();

    using (SqlCommand sqlCommand = new SqlCommand(theQuery, sqlConnection))
    {
        sqlCommand.Parameters.AddWithValue("@userName", textBox1.Text);
        sqlCommand.Parameters.AddWithValue("@password", textBox2.Text);

        DataTable dataTable = new DataTable();
        dataTable.Load(sqlCommand.ExecuteReader());
        if (dataTable.Rows > 0)
        {
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.Fill(dataTable);
        }
    }
}

Alternatively, for the more complex applications you could study the Entity Framework https://learn.microsoft.com/en-us/ef/ef6/get-started first and train yourself in not re-inventing wheels. It will take some time to adapt but will really pay off.

Good luck improving your code!