How to make a request to receive certain data

Question

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

No problem, "but then I thought that if I wanted to display one value (column) of a certain row, say" show me the last name of the person with a certain name. "

I am familiar with SQL query commands, so I want something like this:

SELECT last_name FROM Employees WHERE first_name = 'Jason'

Executes 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]);
    }
}
}

( ), , . .

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);
        }
    }
}

, , .

EDIT:

DBconnection . , , () , , . , , , :

( ) SQL-, :

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

(authSQL) , . , , , :

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

"Properties.Settings.Default.authSQL" - , "SELECT * FROM AUTH" - AUTH - , Employees .

, :

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

. sql, : - , . , "dapper", :

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

Answer 2

.... , ...

//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();
            }