c# - 如何使用 Join 搜索字段？

Question

我有以下列:

Employee Name:

Department:

Designation:

                  Search Button

我想通过连接表来编写搜索查询。搜索查询将如何？

我只想要一个使用内连接的搜索查询。

我尝试了以下方法:

select e.empname, d.depname,designation 
from employee e  
inner join Department d on e.deptid=d.deptid 
inner join Designation de on de.deid=
where empname LIKe '"+Textbox1.text+'%'"' 
   or depname Like '"+ddldept.SelectedValue+"' 
   or designation Like '"+ddldesignation.SelectedValue+"'

如有错误请指正

Answer 1

我假设您使用的是 SQL Server。你的 table 是这样的:

Employee { id, deptid, empname }
Department { id, depname }
Designation { id, empid, designation }

select e.empname, d.depname, de.designation 
from Employee e  
inner join Department d on e.deptid = d.id 
inner join Designation de on e.id = de.empid
where e.empname LIKe '%"+Textbox1.text+"%' 
   or d.depname Like '%"+ddldept.SelectedValue+"%' 
   or de.designation Like '%"+ddldesignation.SelectedValue+"%'

这实际上是不好的做法，最好在 C# 中使用参数化查询:

string sql = "select e.empname, d.depname, de.designation " +
    "from Employee e " +
    "inner join Department d on e.deptid = d.id " +
    "inner join Designation de on e.id = de.empid " +
    "where e.empname LIKe '%@empname%' " +
    "   or d.depname Like '%@deptname%' " +
    "   or de.designation Like '%@designation%'"; 

using (SqlConnection connection = new SqlConnection(/* connection info */))
using (SqlCommand command = new SqlCommand(sql, connection))
{
    command.Parameters.AddWithValue("@empname", Textbox1.text);
    command.Parameters.AddWithValue("@deptname", ddldept.SelectedValue);
    command.Parameters.AddWithValue("@designation", ddldesignation.SelectedValue);
    var results = command.ExecuteReader();
}