运行 SqlCommand.ExecuteScalar 后，事务变为空

Question

我在SQL Server中有一个表，定义如下

create table Customer (ID varchar(10), Name varchar(200))
Insert into Customer 
values ('00001', 'Cust1'), ('ID00002', 'Cust2')

当我使用 ID 查询客户名称时(数据类型错误，它是varchar，但其值作为 int 传递(，该命令返回正确的名称，但其事务变为 null，并且在尝试提交时，我收到错误

无效操作异常
此 SqlTransaction 已完成;它不再可用。

我的代码：

using (var conn = new SqlConnection(connString))
{
    conn.Open();
    using (var trans = conn.BeginTransaction())
    {
        using (var cmd = new SqlCommand("SELECT Name FROM Customer WHERE ID = 1")) // After this command is executed, its transaction becomes null.
        //using (var cmd = new SqlCommand("SELECT Name FROM Customer  WHERE ID = '000001'")) // This runs fine.
        {
            cmd.Connection = conn;
            cmd.Transaction = trans;
            var res = cmd.ExecuteScalar();
        }
        trans.Commit(); 
    }
}

我能做些什么来检查这种情况，并在命令执行时失败，而不是在稍后阶段出现异常？

Answer 1

当输入表达式的计算结果为有效的数值数据类型时，ISNUMERIC 返回 1;否则返回 0。

你试试这个代码

  using (var conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["SchoolPlusDBContext"].ConnectionString))
        {
            conn.Open();
            using (var trans = conn.BeginTransaction())
            {
                using (var cmd = new SqlCommand("SELECT Name FROM Customer where  ISNUMERIC(id)=1 and Id=1")) // After this command is executed, its transaction becomes null.
                //using (var cmd = new SqlCommand("SELECT Name FROM Customer  WHERE ID = '000001'")) // This runs fine.
                {
                    cmd.Connection = conn;
                    cmd.Transaction = trans;
                    var res = cmd.ExecuteScalar();
                }
                trans.Commit();
            }
        }