how to return int value from select query in function?

Question

I need to retrieve Ticket_Id from tbl_Ticket to pass into body section of sending email function.. Is the below code correct? every times i get Ticket_Id 1..

public int select_TicketId(){
    string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString.ToString();
    SqlConnection sqlCon = new SqlConnection(strConn);
    string getId = ("select Ticket_Id from tbl_Ticket where Client_EmailAdd='" + objNewTic_BAL.email + "' ");
    sqlCon.Open();
    SqlCommand cmd1 = new SqlCommand(getId, sqlCon);
    int i=cmd1.ExecuteNonQuery();
    return i;
}

Answer 1

You are searching for ExecuteScalar which returns the first value.

using System.Configuration;
//
public int select_TicketId()
    {
       string strConn = ConfigurationManager.ConnectionStrings["conString"].ConnectionString.ToString();
    SqlConnection sqlCon = new SqlConnection(strConn);
       string getId = ("select TOP 1 Ticket_Id from tbl_Ticket where Client_EmailAdd='" + objNewTic_BAL.email + "' ");
       sqlCon.Open();
       SqlCommand cmd1 = new SqlCommand(getId, sqlCon);
       return Convert.ToInt32(cmd1.ExecuteScalar());
    }

Also use CommandProperties to set the where statement for better security, like below:

public int select_TicketId()
{
    string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    int result = -1;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandType = CommandType.Text;
        command.CommandText = "select TOP 1 Ticket_Id from tbl_Ticket where Client_EmailAdd=@email";
        command.Parameters.Add("@email", SqlDbType.Text).Value = objNewTic_BAL.email;
        result = Convert.ToInt32(command.ExecuteScalar());
    }

    return result;
}