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.
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);
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}";
}
}