c# - 测试 Table 是否至少有特定的存储过程，否则测试失败 c#

Question

此测试检查是否只有一个 SP 以插入、加载或保存结束，然后测试成功。

//Query to find SPs for a certain Table name
string check_SP =
    "SELECT ROUTINE_NAME, ROUTINE_DEFINITION " +
    "FROM INFORMATION_SCHEMA.ROUTINES " +
    "WHERE ROUTINE_DEFINITION LIKE '%CLNT_" + Table_Name + "%' " +
    "AND ROUTINE_TYPE='PROCEDURE'";

conn2.Open();
SqlCommand cmd_SP = new SqlCommand(check_SP, conn2);
list_SP = cmd_SP.ExecuteReader(); //Write 'check_SP' query results to 'list_SP'

while (list_SP.Read())
{
    if (list_SP[0].ToString().EndsWith("Insert")
        || list_SP[0].ToString().EndsWith("Load")
        || list_SP[0].ToString().EndsWith("Save"))
    {
        Console.WriteLine(list_SP[0]);
        test = true;
    }
    else
    {
        Console.WriteLine("Missing SP");
        test = false;
        break;
    }                    
}

我想做的是在没有以插入、加载或保存结尾的存储过程时中断 while 循环。 (即每张表至少要有这三个SP，否则测试会失败)

Answer 1

撇开这是否是正确的方法不谈，您需要更改逻辑以分别检查每种类型的过程是否存在:

bool insertExists = false;
bool loadExists = false;
bool saveExists = false;
while (list_SP.Read())
{
    if (list_SP[0].ToString().EndsWith("Insert"))
    {
        insertExists = true;
    }
    if (list_SP[0].ToString().EndsWith("Load"))
    {
        loadExists = true;
    }
    if (list_SP[0].ToString().EndsWith("Save"))
    {
        saveExists = true;
    }
    if (insertExists && loadExists && saveExists)
    {
        Console.WriteLine("All three exist");
        break;
    }
}

if (!insertExists)
{
    Console.WriteLine("Missing insert");
}
if (!loadExists)
{
    Console.WriteLine("Missing load");
}
if (!saveExists)
{
    Console.WriteLine("Missing save");
}