Handling SQL data in C#

Question

namespace iss_farmacie_spitali
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlDataReader reader = null;
            DataTable table = null;

            try
            {
                sqlConnection1.Open();
            }

            catch(Exception f)
            {
                Console.WriteLine(e.ToString());
            }
            sqlCommand1.Parameters.AddWithValue("@user",textBox1.Text);
            sqlCommand1.Parameters.AddWithValue("@pass",textBox2.Text);
            sqlCommand1.CommandText = "SELECT id,parola FROM ANGAJAT WHERE id='@user' AND parola='@pass'";

            reader = sqlCommand1.ExecuteReader();
            table.Load(reader);  


            sqlConnection1.Close();
        }


    }
}

So i'm trying to do my faithful logon here, but i keep getting the null reference error. What i need help with is a bit of understanding how to handle the data i get from sql, and how to make it useable as strings and whatnot.

Answer 1

There are multiple problems here.

One is that you haven't shown where sqlCommand1 or sqlConnection1 are declared or initialized. My guess is that they're both null, which is why you're getting the exception. They should be declared and initialized within the method, using using statements so they get disposed appropriately:

using (var connection = new SqlConnection(...))
{
    connection.Open();
    using (var command = new SqlComand(...))
    {
        ...
    }
}

Then there's this problem.

SELECT id,parola FROM ANGAJAT WHERE id='@user' AND parola='@pass'

The fact that you're got quotes means you're looking for an ID of exactly @user - not the value of the @user parameter. You want:

SELECT id,parola FROM ANGAJAT WHERE id=@user AND parola=@pass

EDIT: And as noted, initialize your DataTable too :)

Answer 2

In your code table variable has been initialized to null so it will throw NullReferenceException.

Make following corrections in your code -

1. Initialize table variable
2. Initialize SqlConnection & make use of using for object cleanup after use
3. Initialize SqlCommand & make use of using for object cleanup after use
4. Set connection string
5. Make use of using for object cleanup after use of reader

AS -

private void button1_Click(object sender, EventArgs e) 
{
    SqlDataReader reader = null;

    //Corrections
    DataTable table = new DataTable(); //#1 : Initialize Table

    using(SqlConnection sqlConnection1 = new SqlConnection()) //#2: Initialize SqlConnection & make use of `using` for object cleanup after use
    using(SqlCommand sqlCommand1 = new SqlCommand()) //#3: Initialize SqlCommand & make use of `using` for object cleanup after use
    {

        sqlConnection1.ConnectionString = "<connection string>"; //#4: Set connection string
        sqlCommand1.Connection = sqlConnection1;

        sqlConnection1.Open();

        sqlCommand1.CommandText = "SELECT id,parola FROM ANGAJAT WHERE id=@user AND parola=@pass";

        sqlCommand1.Parameters.AddWithValue("@user",textBox1.Text);
        sqlCommand1.Parameters.AddWithValue("@pass",textBox2.Text);

        using(SqlReader reader = sqlCommand1.ExecuteReader()) //#4 : Make use of `using` for object cleanup after use
        {
            table.Load(reader);  
        }

        sqlConnection1.Close();
    }
}

EDIT :

Summarized corrections to improve asnwer as per Jon Skeet's observations :)

Answer 3

You are not checking if there is any row in the reader or not , thats why you are getting null point exception.

here is the code snippets

reader = sqlCommand1.ExecuteReader();

while (reader.Read()) 
{
   // Do somthing here
}

hope it will help.