C# 如果不存在，则插入 SQL 服务器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9648934/
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
INSERT INTO if not exists SQL server
提问by Simon Kiely
I have a database structured as follows:
我有一个结构如下的数据库：
users
用户
userid (Primary Key)
username
group
团体
groupid (PK)
groupName
user_groups
用户组
userid (Foreign Key)
groupid (Foreign Key)
The first time a user logs in I would like their info to be added to the users table. So essentially the logic I would like to have if
用户第一次登录时，我希望将他们的信息添加到用户表中。所以基本上我想要的逻辑如果
if (//users table does not contain username)
{
INSERT INTO users VALUES (username);
}
How can I do this intelligently using SQL Server/C# ?
如何使用 SQL Server/C# 智能地执行此操作？
采纳答案by a_horse_with_no_name
Or using the new MERGE syntax:
或者使用新的 MERGE 语法：
merge into users u
using (
select 'username' as uname
) t on t.uname = u.username
when not matched then
insert (username) values (t.uname);
回答by user194076
Basically you can do it like this:
基本上你可以这样做：
IF NOT EXISTS (SELECT * FROM USER WHERE username = @username)
INSERT INTO users (username) VALUES (@username)
But seriously, how you're going to know if user visited your website for the first time? You have to insert records in table user, when somebody register on your website, not login.
但是说真的，您如何知道用户是否是第一次访问您的网站？当有人在您的网站上注册时，您必须在表 user 中插入记录，而不是登录。
回答by Sascha
IF NOT EXISTS (select * from users where username = 'username')
BEGIN
INSERT INTO ...
END
回答by ron tornambe
Here is a (probably oversimplified) example that addresses your issue. Note that it is always best to use parameters to prevent injection attacks, especially when verifying users.
这是一个解决您的问题的（可能过于简化）示例。请注意，最好使用参数来防止注入攻击，尤其是在验证用户时。
CREATE PROCEDURE AddUser
@username varchar(20)
AS
IF NOT EXISTS(SELECT username FROM users WHERE username=@username)
INSERT INTO users(username) VALUES (@username)
回答by Amen Ayach
The following code is a method that returns 0 if user already exists and returns the new user IDthat just added :
以下代码是一个方法，如果用户已存在则返回 0 并返回刚刚添加的新用户ID：
private int TryToAddUser(string UserName)
{
int res = 0;
try
{
string sQuery = " IF NOT EXISTS (select * from users where username = @username) \n\r" +
" BEGIN \n\r" +
" INSERT INTO users values (@username) \n\r" +
" SELECT SCOPE_IDENTITY() \n\r " +
" END \n\r " +
" ELSE SELECT 0";
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
{
cmd.CommandText = sQuery;
cmd.Parameters.AddWithValue("@username",UserName);
cmd.Connection = new System.Data.SqlClient.SqlConnection("SomeSqlConnString");
cmd.Connection.Open();
res = (int)cmd.ExecuteScalar();
cmd.Connection.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return res;
}
回答by Jason
I would first create a stored proc on the db to do the check and insert if necessary:
我会首先在数据库上创建一个存储过程来进行检查并在必要时插入：
CREATE PROCEDURE AddNewUserProc
(
@username VarChar(50) -- replace with your datatype/size
)
AS
IF NOT EXISTS (SELECT * FROM users WHERE username = @username)
BEGIN
INSERT INTO users
VALUES (@username)
END
Then a method on the app that will call this procedure
然后应用程序上的一个方法将调用此过程
public void AddNewUserMethod(string userName)
{
SqlConnection connection = new SqlConnection("connection string");
SqlCommand command = new SqlCommand("AddNewUserProc", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("username", SqlDbType.VarChar, 50).Value = userName;
try
{
connection.Open();
command.ExecuteNonQuery();
}
finally
{
if (connection.State == ConnectionState.Open) { connection.Close(); }
}
}
Noteleaving this as alternative/historical, but for purpose of correctness the correct way is using the Mergestatement, see answer https://stackoverflow.com/a/9649040/167304or checkout MS doc https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15
请注意将此作为替代/历史记录，但为了正确性，正确的方法是使用Merge语句，请参阅答案https://stackoverflow.com/a/9649040/167304或结帐 MS doc https://docs.microsoft.com /en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15
回答by Steam
@gbnanswer needs SQL server 2008 or higher. I tried a non-merge way to do it. Ugly perhaps, but it works.
@gbn答案需要 SQL Server 2008 或更高版本。我尝试了一种非合并的方式来做到这一点。也许丑陋，但它有效。
declare @user varchar(50)
set @user = 'username'
insert into users (username)
select @user
where not exists (
select username
from users
where username = @user
);
If you want to test any of the answers, here is the SQL for the table -
如果你想测试任何答案，这里是表的 SQL -
CREATE TABLE [dbo].[users](
[userid] [int] NULL,
[username] [varchar](50) NULL
)
INSERT [dbo].[users] ([userid], [username]) VALUES (1, N'John')
INSERT [dbo].[users] ([userid], [username]) VALUES (2, N'David')
INSERT [dbo].[users] ([userid], [username]) VALUES (3, N'Stacy')
INSERT [dbo].[users] ([userid], [username]) VALUES (4, N'Arnold')
INSERT [dbo].[users] ([userid], [username]) VALUES (5, N'Karen')
回答by levkaster
There is a simple solution! I Found it in this post!
有一个简单的解决方案！我在这篇文章中找到了它！
INSERT INTO users (Username)
SELECT ('username')
WHERE NOT EXISTS(SELECT * FROM users WHERE Username= 'username')
In my project I needed to do it with 2 values. So my code was:
在我的项目中，我需要用 2 个值来完成。所以我的代码是：
INSERT INTO MyTable (ColName1, ColName2)
SELECT 'Value1','Value2'
WHERE NOT EXISTS
(SELECT * FROM MyTable WHERE ColName1 = 'Value1' AND ColName2= 'Value2')
Hope this helps!
希望这可以帮助！
