为SQLCommand生成带“@”的Insert语句？

If I use SQLCommand, I usually need something like:

如果我使用SQLCommand,我通常需要这样的东西:

INSERT INTO klant(klant_id,naam,voornaam) VALUES(@param1,@param2,@param3)";

Is there some easy way I can generate this string?

有一些简单的方法可以生成这个字符串吗?

SSMS generate something like this

SSMS生成这样的东西

           ,[years]
       ,[source]
       ,[TimeStamp])
 VALUES
       (<count, int,>
       ,<sex_male, bit,>
       ,<Ethnicity, tinyint,>

Can I use it somehow?

我能以某种方式使用它吗?

1 个解决方案

#1

If you need this frequently then consider SSMS tools pack. This generates CRUD according to user specified templates.

如果您经常需要这个,请考虑SSMS工具包。这会根据用户指定的模板生成CRUD。

An adhoc not particularly robust alternative is pretty simple with find and replace though.

虽然找到并替换,但是adhoc不是特别强大的替代方案非常简单。

SSMS generates something like

SSMS生成类似的东西

INSERT INTO [HumanResources].[Employee]
           ([BusinessEntityID]
           ,[NationalIDNumber]
           ,[LoginID]
           ,[CurrentFlag]
           ,[rowguid]
           ,[ModifiedDate])
     VALUES
           (<BusinessEntityID, int,>
           ,<NationalIDNumber, nvarchar(15),>
           ,<LoginID, nvarchar(256),>
           ,<CurrentFlag, Flag,>
           ,<rowguid, uniqueidentifier,>
           ,<ModifiedDate, datetime,>)

Then copy the top section down to the bottom

然后将顶部向下复制到底部

INSERT INTO [HumanResources].[Employee]
           ([BusinessEntityID]
           ,[NationalIDNumber]
           ,[LoginID]
           ,[CurrentFlag]
           ,[rowguid]
           ,[ModifiedDate])
     VALUES
           ([BusinessEntityID]
           ,[NationalIDNumber]
           ,[LoginID]
           ,[CurrentFlag]
           ,[rowguid]
           ,[ModifiedDate])

And select the bottom section and replace [ with @ and ] with an empty string.

然后选择底部并用[@和]替换为空字符串。

Or alternatively it would be fairly trivial to write something that queries sys.columns and generates the desired string.

或者,编写查询sys.columns并生成所需字符串的内容将非常简单。

(Again a not robust solution that assumes you are using column names conforming to the rules for standard identifiers- quotename would help with the column names but not the parameter names if you aren't.)

(同样是一个不太强大的解决方案,假设您使用的列名称符合标准标识符的规则 - quotename将有助于列名称,但如果不是,则有助于参数名称。)

DECLARE @QualifiedName NVARCHAR(500) = '[HumanResources].[Employee]';

WITH C
     AS (SELECT *
         FROM   sys.columns
         WHERE  object_id = object_id(@QualifiedName)
                AND is_computed = 0
                AND is_identity = 0)
SELECT '
INSERT INTO ' + @QualifiedName + '
           (' + SUBSTRING((SELECT ',' + name
                                  FROM   C
                                  ORDER  BY column_id
                                  FOR XML PATH('')), 2, 8000) + ')
     VALUES
           (' + SUBSTRING((SELECT ',@' + name
                                  FROM   C
                                  ORDER  BY column_id
                                  FOR XML PATH('')), 3, 8000) + ')

#1