c# - 澄清两列同名

Question

我有这个问题:

SELECT CableNumber, CblType, ApprxmtLngth, ME.EquipmentNumber, ME2.EquipmentNumber, CblStatus, InstallMthd, S.PrdtCd FROM CableId C
 INNER JOIN MajorEquipment ME ON C.FromLoc = ME.MEId
 INNER JOIN MajorEquipment ME2 ON C.ToLoc = ME2.MEId
 INNER JOIN SupplierInfo S ON C.SupplierId = S.SupplierId WHERE
 (@CblType IS NULL OR CblType LIKE @CblType)
 AND (@CblStatus IS NULL OR CblStatus LIKE @CblStatus)
 AND (@FromLoc IS NULL OR ME.EquipmentNumber LIKE @FromLoc)
 AND (@ToLoc IS NULL OR ME2.EquipmentNumber LIKE @ToLoc);

当我将结果传递到模型中时，如何定义哪个 EquipmentNumber 进入哪个变量？

while (mySqlReader.Read())
{
    CableID_Controller.CList.Add(new CableID_Model
    {
        CableNumber = Convert.ToString(mySqlReader["CableNumber"]),
        CableType = Convert.ToString(mySqlReader["CblType"]),
        SupplierPart = Convert.ToString(mySqlReader["PrdtCd"]),
        CableLength = Convert.ToInt32(mySqlReader["ApprxmtLngth"]),
        InstallMethod = Convert.ToString(mySqlReader["InstallMthd"]),
        Origin = Convert.ToString(mySqlReader["ME.EquipmentNumber"]),
        Destination = Convert.ToString(mySqlReader["ME2.EquipmentNumber"]),
        Status = Convert.ToString(mySqlReader["CblStatus"])
     });
}

因为 ME.EquipmentNumber 和 ME2.EquipmentNumber 不是 header 的实际名称。

Answer 1

描述

只需在查询中为列指定不同的别名(EquipmentNumber1 和 EquipmentNumber2)，并使用 as 运算符进行编码。您还必须删除代码中的 ME1 和 ME2。

示例

SELECT CableNumber, CblType, ApprxmtLngth, ME.EquipmentNumber as EquipmentNumber1, ME2.EquipmentNumber as EquipmentNumber2, CblStatus, InstallMthd, S.PrdtCd FROM CableId C
 INNER JOIN MajorEquipment ME ON C.FromLoc = ME.MEId
 INNER JOIN MajorEquipment ME2 ON C.ToLoc = ME2.MEId
 INNER JOIN SupplierInfo S ON C.SupplierId = S.SupplierId WHERE
 (@CblType IS NULL OR CblType LIKE @CblType)
 AND (@CblStatus IS NULL OR CblStatus LIKE @CblStatus)
 AND (@FromLoc IS NULL OR ME.EquipmentNumber LIKE @FromLoc)
 AND (@ToLoc IS NULL OR ME2.EquipmentNumber LIKE @ToLoc);

while (mySqlReader.Read())
{
    CableID_Controller.CList.Add(new CableID_Model
    {
        CableNumber = Convert.ToString(mySqlReader["CableNumber"]),
        CableType = Convert.ToString(mySqlReader["CblType"]),
        SupplierPart = Convert.ToString(mySqlReader["PrdtCd"]),
        CableLength = Convert.ToInt32(mySqlReader["ApprxmtLngth"]),
        InstallMethod = Convert.ToString(mySqlReader["InstallMthd"]),
        Origin = Convert.ToString(mySqlReader["EquipmentNumber1"]),
        Destination = Convert.ToString(mySqlReader["EquipmentNumber2"]),
        Status = Convert.ToString(mySqlReader["CblStatus"])
     });
}

更多信息

• How to MySQL - ALIAS