How to retrieve and display Database info into Textbox using Dapper c# .net

Question

How to retrieve and display Database info into Textbox using Dapper c# .net

asp.net c# dapper

Question

I already tried displaying info from database into a textbox using ADO.NET and now i'm currently learning to do that using dapper

Here's my dapper code:

        try
        {

            using (var command = new SqlCommand("DisplayInfo", con))
            using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString))
            {
                SqlDataAdapter sda = new SqlDataAdapter(command);

                if (db.State != ConnectionState.Open)
                    db.Open();

                DynamicParameters param = new DynamicParameters();

                param = new DynamicParameters("@Fname");
                param.Add("@Fname", tb2.Text);
                param = new DynamicParameters("@exist");
                param.Add("@exist", SqlDbType.Bit);

                con.Execute("DisplayInfo", param, commandType: CommandType.StoredProcedure);

                DataTable dt = new DataTable();
                sda.Fill(dt);
                tb2.Text = dt.Rows[0]["Fname"].ToString();
                tb3.Text = dt.Rows[0]["Lname"].ToString();
                tb4.Text = dt.Rows[0]["Age"].ToString();

                exist = Convert.ToBoolean(command.Parameters["@exist"].Value);

                if (db.State != ConnectionState.Closed)
                    db.Close();
            }
        }

And the Stored Procedure DisplayInfo is just a basic SELECT query What am I doing wrong?

Code for the SP:

   @Fname nvarchar(50),
   @exist bit output
   AS
   BEGIN
   IF NOT EXISTS
(SELECT ID,Fname,Lname,Age FROM CloneInfo 
WHERE Fname=@Fname)
BEGIN
SET @exist=0
END
ELSE
BEGIN
SELECT ID,Fname,Lname,Age FROM CloneInfo
WHERE Fname=@Fname
SET @exist=1
END
END

Plus CloneInfo table has: ID,Fname,Lname,Age as columns

And I'm getting those two errors:

1-Procedure or function 'DisplayInfo' expects parameter '@Fname', which was not supplied.

2-An explicit value for the identity column in table 'CloneInfo' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Calvin

Popular Answer

It seems you need to change the call from 'con.Execute' to 'db.Query', please see below example, I am assuming 'con' is of type SqlConnection.

class Program
{
    static string connectionString = @"xxxxxxxxxx";

    static void Main(string[] args)
    {
        Console.WriteLine("Input name: ");
        var name = Console.ReadLine();

        IEnumerable<TestClass> results;

        using (IDbConnection db = new SqlConnection(connectionString))
        {
            var parameters = new DynamicParameters();
            parameters.Add("@FName", name, DbType.String, ParameterDirection.Input, 20);

            results = db.Query<TestClass>("Procedure", 
                commandType: CommandType.StoredProcedure,
                param: parameters);
        }

        foreach(var result in results)
        {
            Console.WriteLine(result);
        }

        Console.ReadLine();
    }
}

class TestClass
{
    public string FName { get; set; }
    public string LName { get; set; }
    public int Age { get; set; }
    public override string ToString()
    {
        return $"FName: {FName}, LName: {LName}, Age: {Age}";
    }
}

Andrew

View more on Stack Overflow

M_Idrees · Accepted Answer · 8/16/2017 9:02:48 AM

Although its not good practice to insert identity values, but as per your scenario. You can try this.

First put first line for your SP:

SET IDENTITY_INSERT CloneInfo ON

and last line will be:

SET IDENTITY_INSERT CloneInfo OFF

And pass parameters as:

DynamicParameters param = new DynamicParameters();

param.Add("@Fname", tb2.Text);
param.Add("@exist", SqlDbType.Bit);

con.Execute("DisplayInfo", param, commandType: CommandType.StoredProcedure);

How to retrieve and display Database info into Textbox using Dapper c# .net

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator