C# 创建存储过程以添加自动增量作为其主要字段？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10614351/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create stored procedure to add with auto increment as its primary field?
提问by Pomster
I am trying to insert Data into my Sql-Server database though C#. I'm Calling a stored procedure and then would like it to add. I'm not sure what changes to make, but ultimately i would like it done in the stored procedure.
我试图通过 C# 将数据插入到我的 Sql-Server 数据库中。我正在调用一个存储过程，然后希望它添加。我不确定要进行哪些更改，但最终我希望在存储过程中完成。
My Stored procedure now:
我现在的存储过程：
CREATE PROCEDURE [dbo].[InsertTagProcdure]
@TagID int,
@Value nvarchar(200),
@TagCount nvarchar(200)
AS
IF NOT EXISTS (SELECT NULL FROM Tag
WHERE @TagID = @TagID)
BEGIN
INSERT INTO
Tag
(TagID,Value,TagCount)
VALUES
(@TagID,@Value,@TagCount)
END
And my C# Code:
还有我的 C# 代码：
int TagID = int.Parse(txtTagID.Text); //This should fall away so auto increment.
String Value = txtValue.Text;
int TagCount = int.Parse(txtCount.Text);
using (var conn = new SqlConnection(Properties.Settings.Default.DBConnectionString))
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "InsertTagProcdure";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TagID", TagID);
cmd.Parameters.AddWithValue("@Value", Value);
cmd.Parameters.AddWithValue("@TagCount", TagCount);
cmd.ExecuteNonQuery();
}
The Table Create i used: //Cant change this its what the boss gave me.
我使用的表创建：//不能改变这是老板给我的。
CREATE TABLE [dbo].[Tag](
[TagID] [int] IDENTITY(1,1) NOT NULL,
[Value] [varchar](200) NOT NULL,
[TagCount] [varchar](200) NULL,
CONSTRAINT [PK_Tag] PRIMARY KEY CLUSTERED
(
[TagID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
采纳答案by Dave
Ideally you would just make TagID an identity field by changing the table definition. If you can't do that, next best would be:
理想情况下，您只需通过更改表定义来使 TagID 成为身份字段。如果你不能这样做，下一个最好的方法是：
CREATE PROCEDURE [dbo].[InsertTagProcdure]
@Value nvarchar(200),
@TagCount nvarchar(200)
AS
BEGIN
BEGIN TRANSACTION
DECLARE @TagID int;
SELECT @TagID = coalesce((select max(TagID) + 1 from Tag), 1)
COMMIT
INSERT INTO
Tag
(TagID,Value,TagCount)
VALUES
(@TagID,@Value,@TagCount)
END
The transaction ensures that you don't end up with unique TagIDs and the coalesce handles the special case where the table is empty and gives an initial value of 1.
该事务确保您不会得到唯一的 TagID，并且合并处理表为空并给出初始值为 1 的特殊情况。
EDIT:
编辑：
Based on the change to your original question, the table already has an identity column so your stored procedure should be:
根据对原始问题的更改，该表已经有一个标识列，因此您的存储过程应该是：
CREATE PROCEDURE [dbo].[InsertTagProcdure]
@Value nvarchar(200),
@TagCount nvarchar(200)
AS
BEGIN
INSERT INTO Tag (Value,TagCount) VALUES (@Value,@TagCount)
END
and your C# code should be
你的 C# 代码应该是
int TagID = int.Parse(txtTagID.Text); //This should fall away so auto increment. String Value = txtValue.Text; int TagCount = int.Parse(txtCount.Text);
int TagID = int.Parse(txtTagID.Text); //这应该会消失，所以自动增量。字符串值 = txtValue.Text; int TagCount = int.Parse(txtCount.Text);
using (var conn = new SqlConnection(Properties.Settings.Default.DBConnectionString))
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "InsertTagProcdure";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Value", Value);
cmd.Parameters.AddWithValue("@TagCount", TagCount);
cmd.ExecuteNonQuery();
}
回答by Martin Clarke
You want to set the tag table up so that it uses the identity property. See here: http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx.
您想要设置标签表，以便它使用身份属性。请参阅此处：http: //msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx。
Then you can drop TagId from the procedure, the insert statement in the procedure and the c# code.
然后，您可以从过程中删除 TagId、过程中的插入语句和 c# 代码。
It then becomes something like this:
然后它变成了这样：
CREATE PROCEDURE [dbo].[InsertTagProcdure]
@Value nvarchar(200),
@TagCount nvarchar(200)
AS
BEGIN
INSERT INTO
Tag
(Value,TagCount)
VALUES
(@Value,@TagCount)
END
C# Code:
C# 代码：
String Value = txtValue.Text;
int TagCount = int.Parse(txtCount.Text);
using (var conn = new SqlConnection(Properties.Settings.Default.DBConnectionString))
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "InsertTagProcdure";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Value", Value);
cmd.Parameters.AddWithValue("@TagCount", TagCount);
cmd.ExecuteNonQuery();
}
回答by riffnl
the statement WHERE @TagID = @TagID will always be true, because your comparing the same values. I thinkyour looking for this (assuming TagID is your AUTO-ID field);
语句 WHERE @TagID = @TagID 将始终为真，因为您比较了相同的值。我认为您正在寻找这个（假设 TagID 是您的 AUTO-ID 字段）；
CREATE PROCEDURE [dbo].[InsertTagProcdure]
@TagID int,
@Value nvarchar(200),
@TagCount nvarchar(200)
AS
BEGIN
IF NOT EXISTS (SELECT TagID FROM Tag WHERE TagID = @TagID)
BEGIN
INSERT INTO
Tag
(Value,TagCount)
VALUES
(@Value,@TagCount)
SET @TagID = @@IDENTITY
END
ELSE
BEGIN
UPDATE Tag
SET Value=@Value,
TagCount=@TagCount
WHERE TagID = @TagID
END
RETURN @TagID
END
回答by M.Schenkel
You have to turn on 'Identity specification' in SQL Server on the appropriate column. Go to table design mode, select the column and in the bottom properties screen there is a property called 'Identity specification'.
您必须在 SQL Server 中的相应列上打开“身份规范”。转到表格设计模式，选择列，在底部属性屏幕中有一个名为“身份规范”的属性。
After this, you can omit the TagId in your stored procedure. Sql Server will automatically assign an id to a new record.
在此之后，您可以省略存储过程中的 TagId。Sql Server 会自动为新记录分配一个 id。
If you want to know what that newly inserted id is (which I assume is your next question), you can use the function SCOPE_IDENTITY() in your stored procedure, which returns the id of the newly inserted record.
如果您想知道新插入的 id 是什么（我假设这是您的下一个问题），您可以在存储过程中使用函数 SCOPE_IDENTITY()，它返回新插入记录的 id。
