将DAL函数转换为mysql中的存储过程

Question

您好，我的数据访问层有一个类，其中包含一个名为 GetCitiesByLocation 的函数，它看起来像这样，

public DataTable GetCitiesByLocation(long pStateID, string pKeyword)
{

    //create database object
    Database db = DBHelper.CreateDatabase();

    //create SELECT sql statement to be executed using string builder
    //add WHERE 1 = 1 at the end

    //add where [optional] statements
    if (String.IsNullOrEmpty(pKeyword) == false)
    {
        //Create AND statement for ?Keyword
    }

    //add group by order by queries
    sql.Append(" GROUP BY c.city_id ");
    sql.Append(" ORDER BY city_name ");

    //Convert SQL String To DbCommand Object
    DbCommand comm = db.GetSqlStringCommand(sql.ToString());

    //Map Variables to mysql ?Parameters
    db.AddInParameter(comm, "?StateID", DbType.Int64, pStateID);
    db.AddInParameter(comm, "?Keyword", DbType.String, pKeyword);

    try
    {
        //execute sql statement and return results
    }

    catch (Exception ex)
    {
        throw ex;
    }
}

请在此处查看完整版本：http://friendpaste.com/6ZGJHRNxQ9J57ntFD5XZi1

我想将其转换为 MYSQL 存储过程

这是我朋友将其转换为 MSSQL 存储过程的失败尝试

ALTER PROCEDURE [dbo].[sp_Search]
@Keyword varchar(30)

AS

SELECT count(cc.course_id) as 'course_count',  c.*, con.* FROM city c
LEFT JOIN countries con on con.country_id = c.country_id
LEFT JOIN courses cc on cc.city_id = c.city_id
WHERE 1 = 1
AND CASE @Keyword WHEN (@Keyword <> NULL) THEN (AND c.state_name like @Keyword)  END
AND CASE @Keyword WHEN (pStateID > 0) THEN (AND c.state_id = @StateID AND c.country_id = 69 AND c.province_id = 0) END
AND CASE @Keyword WHEN (pProvinceID  > 0) THEN (AND c.province_id = @ProvinceID AND c.country_id = 52 AND c.state_id = 0) END

我也有我想要的事务 DAL转换为mysql存储过程

Answer 1

我不是 MySQL 专家，但您似乎试图以与执行参数化查询相同的方式执行存储过程，但这是行不通的。

如果您的 MSSQL Proc 编译为我期望的那样，我也会感到非常惊讶，您的 proc 将以

  ALTER PROCEDURE [dbo].[sp_Search]
    @Keyword varchar(30),
    @StateID int = null,
    @ProvinceID int = null

Or 开头，在 MySQL 中

CREATE PROCEDURE sp_Search(Keyword varhar(30),
StateID int,
ProviceId int)

您最希望必须通过以下方式更改您的 DAL

1. 您现有的 DAL 调用
   获取SqlString命令。你需要
   将其从 GetSqlStringCommand 更改为 GetStoredProcCommand。
2. 你的参数名称必须
   更改
3. 文本为 sp_search 而不是“Select count(cc.course_id) as 'course_count...”