CODEWITHSUNDEEP
c#sql-server
hks
hks

Reputation: 699

how to return int value from select query in function?

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

Upvotes: 2

Views: 24531

Answers (4)

Ralf de Kleine
Ralf de Kleine

Reputation: 11734

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

Upvotes: 7

Jon Skeet
Jon Skeet

Reputation: 1500065

You're calling ExecuteNonQuery. But it's a query. This should have rung some warning bells :)

Try ExecuteScalar instead, and cast the result to int...

return (int) cmd1.ExecuteScalar();

Note that you should use using statements for the command and connection as well, so that both are closed appropriately.

And (I hadn't spotted this before) you should definitely use parameterized SQL instead of including a value directly into your SQL. Otherwise you're open to SQL Injection attacks...

So something like:

private const string FetchTicketIdSql =
    "select Ticket_Id from tbl_Ticket where Client_EmailAdd = @Email";

public int FetchTicketId()
{
    // No need for ToString call...
    string connectionString =
        ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (SqlCommand command = new SqlCommand(connection, FetchTicketIdSql))
        {
            command.Parameters.Add("@Email", SqlDbType.NVarChar).Value = 
                bjNewTic_BAL.email;
            return (int) command.ExecuteScalar();
        }
    }
}

You should consider what you want to happen if there isn't exactly one result though...

Upvotes: 2

Tihi
Tihi

Reputation: 65

Hiral, ExecuteNonQuery in 

int i=cmd1.ExecuteNonQuery();

will return number of records that satisfy your query. In this case it is 1 (or 0 if there are no emails)

Try using ExecuteReader instead.

Upvotes: 0

ionden
ionden

Reputation: 12776

You should call int i=(int)cmd1.ExecuteScalar(); method

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx

Upvotes: 2

Related Questions