Data.ExecuteScalar

Home / Data

ExecuteScalar.cs

using System;
using NUnit.Framework;
using BLToolkit.Data;

namespace HowTo.Data
{
    [TestFixture]
    public class ExecuteScalar
    {
        string GetFirstName(int id)
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetCommand("SELECT FirstName FROM Person WHERE PersonID = @id",
                        db.Parameter("@id", id))
                    .ExecuteScalar<string>();
            }
        }

        [Test]
        public void ReaderTest()
        {
            string firstName = GetFirstName(1);

            Assert.IsNotNull(firstName);
        }

        [Test]
        public void ReturnValueTest()
        {
            using (DbManager db = new DbManager())
            {
                /*
                 * CREATE Function Scalar_ReturnParameter()
                 * RETURNS int
                 * AS
                 * BEGIN
                 *     RETURN 12345
                 * END
                 */
                int n = db
                    .SetSpCommand("Scalar_ReturnParameter")
                    .ExecuteScalar<int>(ScalarSourceType.ReturnValue);

                Assert.AreEqual(12345, n);
            }
        }

        [Test]
        public void OutputParameterAsReturnValueTest()
        {
            using (DbManager db = new DbManager())
            {
                /*
                 * CREATE Procedure Scalar_OutputParameter
                 *     @outputInt    int         = 0  output,
                 *     @outputString varchar(50) = '' output
                 * AS
                 * BEGIN
                 *     SET @outputInt = 12345
                 *     SET @outputString = '54321'
                 * END
                 */
                string returnValue = db
                    .SetSpCommand("Scalar_OutputParameter")
                    .ExecuteScalar<string>(ScalarSourceType.OutputParameter, "outputString");

                Assert.AreEqual("54321", returnValue);
            }
        }
    }
}

App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add
            name             = "DemoConnection"
            connectionString = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"
            providerName     = "System.Data.SqlClient" />
    </connectionStrings>
</configuration>