How do I make a query to retrieve specific data

Question

I am learning C# and SQL Server. I was following a simple tutorial, which led me to the creation of a class DBconnection that connects and updates the DB. Then I have a simple C# form that navigates back and forth on table rows using a button and a DataSet to clone the data, and then displays the info on some labels.

No problem 'till here, but then I thought, what if I wanted to display a single value(column) of a specific row, say "show me the last name of the person with a certain first name".

I'm familiar with the SQL query commands, so what I want is something like this:

SELECT last_name FROM Employees WHERE first_name = 'Jason'

Follows my code...

DBconnection.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace cemiGEST
{
/// <summary>
/// A class that makes the connection to the SQL Database
/// </summary>
class DBconnection
{
    // variables
    private string sql_string;
    private string strCon;
    System.Data.SqlClient.SqlDataAdapter da_1;

    // set methods
    public string Sql
    {
        set { sql_string = value; }
    }

    public string connection_string
    {
        set { strCon = value; }
    }

    // DataSet
    public System.Data.DataSet GetConnection
    {
        get { return MyDataSet(); }
    }

    // MyDataSet method
    private System.Data.DataSet MyDataSet()
    {
        System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(strCon);
        con.Open();

        da_1 = new System.Data.SqlClient.SqlDataAdapter(sql_string, con);

        System.Data.DataSet dat_set = new System.Data.DataSet();
        da_1.Fill(dat_set, "Table_Data_1");

        con.Close();

        return dat_set;
    }

    // Update DB method
    public void UpdateDB(System.Data.DataSet ds)
    {
        System.Data.SqlClient.SqlCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder(da_1);
        cb.DataAdapter.Update(ds.Tables[0]);
    }
}
}

I am accessing the values (when moving back and forth) by just incrementing by one a variable that updates the row number. Some exemplifying code follows.

public partial class Example : Form
{

// variables
DBconnection objConnect;
string conStringAUTH;
DataSet ds;
DataRow dr;
int maxRows;
int inc = 0;

    private void Login_Load(object sender, EventArgs e)
    {
        CloseBeforeLogin = true;

        try
        {
            objConnect = new DBconnection();
            conStringAUTH = Properties.Settings.Default.authConnectionString;

            objConnect.connection_string = conStringAUTH;
            objConnect.Sql = Properties.Settings.Default.authSQL;

            ds = objConnect.GetConnection;

            maxRows = ds.Tables[0].Rows.Count;

            if (maxRows == 0)
            {
                MessageBox.Show("No user found. Loading first run wizard.");
                NewUser newUser = new NewUser();
                newUser.ShowDialog();
            }

        }
        catch (Exception err)
        {
            MessageBox.Show(err.Message);
        }
    }
}

I'm sure this is simple, but I'm not getting there.

EDIT:

I would prefer using my class DBconnection and without external libraries. I'm not at the PC with the project, so can't test anything right now, but after a good night of sleep over the matter, and after (re)looking at my code, I may have found the answer. Please tell me if you think this would do the trick:

In my second class (where I connect to and access the DB), I have already using a SQL query, more specifically here:

objConnect.Sql = Properties.Settings.Default.authSQL;

This query (authSQL), was created by me and embedded on the Settings, and here I am importing it. So, if I do the following instead, do you think it would work:

objConnect.Sql = "SELECT last_name FROM Employees WHERE first_name = 'Jason'";

The "Properties.Settings.Default.authSQL" code is nothing more than a shortcut to a string "SELECT * FROM AUTH" - AUTH is my table, which I called Employees for the sake of simplicity.

So, it would be something like this:

public partial class Example : Form
{

// variables
DBconnection objConnect;
string conStringAUTH;
DataSet ds;
DataRow dr;
int maxRows;
int inc = 0;

    private void Login_Load(object sender, EventArgs e)
    {
        CloseBeforeLogin = true;

        try
        {
            objConnect = new DBconnection();
            conStringAUTH = Properties.Settings.Default.authConnectionString;

            objConnect.connection_string = conStringAUTH;
            objConnect.Sql = "SELECT last_name FROM Employees WHERE first_name = 'Jason'";

            ds = objConnect.GetConnection;

            // Data manipulation here

        }
        catch (Exception err)
        {
            MessageBox.Show(err.Message);
        }
    }

Answer 1

Check this ....hope this also works...

//String Declaration
string Sqlstr = "select CountryCode,Description,Nationality from  ca_countryMaster where isDeleted=0 and CountryCode = 'CAN' ";

string DBCon = "Data Source=RAJA;Initial Catalog=CareHMS;Integrated Security=True;";

            SqlConnection SqlCon = new SqlConnection(DBCon);
            SqlDataAdapter Sqlda;
            DataSet ds = new DataSet();

            try
            {
                SqlCon.Open();
                Sqlda = new SqlDataAdapter(Sqlstr, SqlCon);
                Sqlda.Fill(ds);

                gdView.DataSource = ds.Tables[0];
                gdView.DataBind();
            }
            catch (Exception ex)
            {
                lbl.text = ex.Message;
            }
            finally
            {
                ds.Dispose();                
                SqlCon.Close();
            }

Answer 2

There's no need for a dataset here. If you know the sql you want: use it - perhaps with some parameterization. For example, using "dapper", we can do:

string firstName = "Jason";
var lastNames = con.Query<string>(
    "SELECT last_name FROM Employees WHERE first_name = @firstName",
    new { firstName }).ToList();