使用两个不同表中的两个SELECT语句

时间:2021-10-13 21:21:22

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