I'm currently working with Dapper to display data from Sql server into textboxes. I have these two tables:
我目前正在使用Dapper将数据从Sql server显示到文本框中。我有这两个表：
id | fname | lname | Age
1 | cal | bla | 100
2 | vin | bla | 10
table1
表格1
id | fname | lname | Age
1 | cal | vin | 50
2 | calvin | cal | 25
table2
表2
And my stored proc looks like this:
而我的存储过程看起来像这样：
@Fname nvarchar(50)
AS
BEGIN
SELECT Fname,Lname,Age FROM table1 WHERE Fname=@Fname
UNION
SELECT Fname,Lname,Age FROM table2 WHERE Fname=@Fname
END
i'm inserting the first name in form in textbox and it will display the lname and age. I want the resulting table to include lname from table1 and age from table2, something like this:
我在文本框中插入表单中的第一个名称，它将显示lname和age。我希望结果表包含table1中的lname和table2中的age，如下所示：
id | fname | lname | Age
1 | calvin | bla | 50
My C# function:
我的C＃功能：
public void Multi(String name)
{
try
{
using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString))
{
if (db.State != ConnectionState.Open)
db.Open();
DynamicParameters p = new DynamicParameters();
SqlCommand command = new SqlCommand("multi", con);
p.Add("@Fname", tb2.Text,
DbType.String,ParameterDirection.Input);
var multi = con.QueryMultiple("multi", p, commandType:
CommandType.StoredProcedure);
clients cls = new clients();
cls.C = multi.Read<Client>().ToList();
var cli = multi.Read<clients>().ToList();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
cls.C.Add(new Client()
{
Fname = (String)reader["Fname"],
Lname = (String)reader["Lname"],
Age = (int)reader["Age"]
});
tb2.Text = cls.C[0].Fname;
tb3.Text = cls.C[0].Lname;
tb4.Text = cls.C[0].Age.ToString();
And client Class:
和客户类：
class Client
{
public string Fname { get; set; }
public string Lname { get; set; }
public int Age { get; set; }
}
class clients
{
public List<Client> C = new List<Client>();
}
I'm getting these errors:
我收到这些错误：
1-Procedure or function 'multi' expects parameter '@Fname', which was not
supplied.
2-The reader has been disposed; this can happen after all data has been
consumed Object name: 'Dapper.SqlMapper+GridReader'.
3- 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.
I pointed the cursor on reader and it says null and the execution breaks off
我将光标指向阅读器，它显示为null并且执行中断
ANSWER: Ive added this and it works perfectly:
答案：我添加了这个并且它完美地工作：
DynamicParameters p = new DynamicParameters();
p.Add("@Fname", tb2.Text);
var multi = con.QueryMultiple("multi", p, commandType: CommandType.StoredProcedure);
clients cls = new clients();
clients cls2 = new clients();
cls.C = multi.Read<Client>().ToList();
cls2.C = multi.Read<Client>().ToList();
tb2.Text = cls.C[0].Fname;
tb3.Text = cls.C[0].Lname;
tb4.Text = cls2.C[0].Age.ToString();
if (db.State != ConnectionState.Closed)
db.Close();.C[0].Age.ToString();
1 个解决方案
#1
0
Use following sql:
使用以下sql：
@Fname nvarchar(50)
AS
BEGIN
if ((select count(*) from table1 where Fname=@Fname) > 0)
begin
select t1.Fname, t1.Lname, t2.Age
from table1 as t1
left join table2 as t2 on t1.Fname == t2.Fname
where t1.Fname = @Fname
end
else
begin
select t2.Fname, t1.Lname, t2.Age
from table2 as t2
left join table1 as t1 on t2.Fname == t1.Fname
where t2.Fname = @Fname
end
#1
0
Use following sql:
使用以下sql：
@Fname nvarchar(50)
AS
BEGIN
if ((select count(*) from table1 where Fname=@Fname) > 0)
begin
select t1.Fname, t1.Lname, t2.Age
from table1 as t1
left join table2 as t2 on t1.Fname == t2.Fname
where t1.Fname = @Fname
end
else
begin
select t2.Fname, t1.Lname, t2.Age
from table2 as t2
left join table1 as t1 on t2.Fname == t1.Fname
where t2.Fname = @Fname
end