SQL query in c#

[Giorgi Dalakishvili]

You can use SqlCommand object to execute any sql command you wish.

Giorgi Dalakishvili #region signature my articles #endregion

[Yevgeny Efter]

But, can I get a return value from the DB using SqlCommand?

[Giorgi Dalakishvili]

Yes, you can. You can use ExecuteScalar method or by using output parameters. What command are you trying to run?

Giorgi Dalakishvili #region signature my articles #endregion

[Yevgeny Efter]

I don't know yet, but I know that depending on some data in the db, it should return true or false.

[Lost User]

Then just use ExecuteScalar

[Yevgeny Efter]

Thanks! I hope I will manage with returning the value in SQL. Thanks again!

[Luis Alonso Ramos]

It's not so hard, but it depends on your query. Is it a regular query or a stored procedure? if it is a sotred procedure, do you use output parameters or a regular return value. Here is some sample code:

using(SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();
 
    //
    // Regular query - returns a bit value from a table
    //
 
    string sql = "SELECT BitColumn FROM Table WHERE Name = 'Luis'";
    SqlCommand cmd = new SqlCommand(sql, conn);
    bool result = Convert.ToBoolean(cmd.ExecuteScalar());
 
    //
    // Stored procedure - returns bit value in output parameter and in return value
    //
 
    cmd = new SqlCommand("sp_Test", conn);
    cmd.Parameters.Add("@BoolVal", SqlDbType.Boolean).Direction = ParameterDirection.Output;
    cmd.Parameters.Add("RetVal", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
    cmd.ExecuteNonQuery();

    int result1 = Convert.ToInt32(cmd.Parameters["RetVal"].Value);
    bool result2 = Convert.ToBoolean(cmd.Parameters["@BoolVal"].Value);
}

For the second example, you will need a stored procedure like this:

CREATE PROCEDURE sp_Test (@BoolVal bit OUT) AS
BEGIN
    -- Return in output parameter
    SELECT @BoolVal = BitColumn FROM Table WHERE NAME = 'Luis';
 
    -- Return value of the stored procedure
    IF @BoolVal IS NULL
        RETURN 0
    ELSE
        RETURN 1
END

When you execute a query, it returns a resultset (a table with data). A stored procedure that SELECTs something and doesn't get it into a variable is returning a resultset. ExecuteReader and data adapters get the whole resultset, but ExecuteScalar gets the data in the first column of the first row of the first table (useful for returning one value). The stored procedure example calls ExecuteNonQuery because no resultset is returned. Output values are either an output parameter or the return value. You access both through the Commands collection of the SqlCommand object. I hope this helps!

Luis Alonso Ramos Intelectix Chihuahua, Mexico

My Blog!

[Yevgeny Efter]

Thank you very much! Just one more question: If I decide to write a stored procedure, where do I store it? When you write "cmd = new SqlCommand("sp_Test", conn);", how will SQL Server know what "sp_Test" is?

Have a nice day!

[Luis Alonso Ramos]

Yevgeny Efter wrote:

If I decide to write a stored procedure, where do I store it?

It goes in your SQL Server database. You need to run the SQL query so the SP is created (the CREATE PROCEDURE thing). Search Google for "SQL server stored procedures"[^] and you will get a lot of info.

Yevgeny Efter wrote:

When you write "cmd = new SqlCommand("sp_Test", conn);", how will SQL Server know what "sp_Test" is?

Sorry, I missed that part (I didn't compile and run the code). By default it is a regular query (CommandType.CommandText). For a SP, you must specify it:

cmd.CommandTpe = CommandType.StoredProcedure;

I hope this helps!

Luis Alonso Ramos Intelectix Chihuahua, Mexico My Blog!

[Yevgeny Efter]

Thank you very much!

Have a nice day!