如何使用ADO.NET来执行这个SQL查询？ [英] How to execute this SQL query using ADO.NET?

问题描述

由于这个确切的即席查询：

Given this EXACT ad-hoc query:

DECLARE @list AS TABLE (Name VARCHAR(20))
INSERT INTO @list(Name)
VALUES ('Otter', 'Lebron', 'Aaron', 'Brock')

SELECT *
FROM Users
WHERE FirstName in (SELECT Name from @list)

如何才能做到这一点使用C＃ADO.NET用的SqlParameter？

How can this be done using C# ADO.NET with a SqlParameter?

推荐答案

我用你的原单的SQL样品目的的修改。

I'm using a modification of your orginal SQL for sample purposes.

DECLARE @list AS TABLE (Name VARCHAR(20));
INSERT INTO @list(Name)
VALUES ('PROCEDURE'), 
        ('FUNCTION');

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE in (SELECT Name from @list)

您可以使用表值参数

下面是从表值参数SQL Server 2008中所采取的变形例（ADO .NET）

        using (SqlConnection cnn = new SqlConnection("Your connection string"))
        {

            var tableParam = new DataTable("names");
            tableParam.Columns.Add("Name", typeof(string));
            tableParam.Rows.Add(new object[] { "PROCEDURE" });
            tableParam.Rows.Add(new object[] { "FUNCTION')" });

            var sql = @"DECLARE @list AS TABLE (Name VARCHAR(20))
                       INSERT INTO @list(Name)
                       SELECT Name from @Names;

                       SELECT *
                       FROM INFORMATION_SCHEMA.ROUTINES
                       WHERE ROUTINE_TYPE in (SELECT Name from @list)";

            var sqlCmd = new SqlCommand(sql, cnn);
            var tvpParam = sqlCmd.Parameters.AddWithValue("@Names", tableParam);
            tvpParam.SqlDbType = SqlDbType.Structured;
            tvpParam.TypeName = "dbo.Names";

            cnn.Open();
            using(SqlDataReader rdr = sqlCmd.ExecuteReader() )
            {
                while (rdr.Read())
                    Console.WriteLine(rdr["SPECIFIC_NAME"]);                    

            }



        }

但你需要定义的类型dbo.Names才可以正常工作

But you need to define the type dbo.Names before it can work

下面的类型创建SQL

Here's the type creation SQL

CREATE TYPE dbo.Names AS TABLE 
( Name VARCHAR(Max));

另一种方法是使用 XML参数

Another option is to use an XML Parameter

 using (SqlConnection cnn = new SqlConnection("Your connection string"))
{



    var sql = @"DECLARE @list AS TABLE (Name VARCHAR(20))
                INSERT INTO @list(Name)
                SELECT t.name.value('.', 'varchar(MAX)')
                FROM   @Names.nodes('/Names/Name') as T(Name);

                SELECT *
                FROM INFORMATION_SCHEMA.ROUTINES
                WHERE ROUTINE_TYPE in (SELECT Name from @list)";

    var sqlCmd = new SqlCommand(sql, cnn);

    var s = new MemoryStream(ASCIIEncoding.Default.GetBytes("<Names><Name>PROCEDURE</Name><Name>FUNCTION</Name></Names>"));


    var xmlParam = new SqlXml(s);

    sqlCmd.Parameters.AddWithValue("@Names", xmlParam);


    cnn.Open();
    using(SqlDataReader rdr = sqlCmd.ExecuteReader() )
    {
        while (rdr.Read())
            Console.WriteLine(rdr["SPECIFIC_NAME"]);                    

    }



}

这篇关于如何使用ADO.NET来执行这个SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！