存储过程C# - “IF EXISTS UPDATE ELSE INSERT” [英] Stored Procedure C# - "IF EXISTS UPDATE ELSE INSERT"
问题描述
伙计们,请帮助..我是一个对编程知之甚少的新手,这里我有我的源代码,将数据插入SQL DB,想要为这段代码添加一个存储过程,以便验证条目是否存在在插入数据之前,如果EXISTS UPDATE ELSE INSERT,我不知道存储过程,请帮忙,需要在一夜之间完成这个项目,请协助。
< pre lang =c#> 使用系统;
使用 System.Collections.Generic;
使用 System.ComponentModel;
使用 System.Data;
使用 System.Drawing;
使用 System.Linq;
使用 System.Text;
使用 System.Windows.Forms;
使用 System.Data.SqlClient;
使用 System.Collections;
命名空间 Dispatch_Tracker
{
public partial class DispatchTracker:表单
{
public DispatchTracker()
{
InitializeComponent();
}
string connection = Data Source = WN7-4WVX1BS; + Initial Catalog = DispatchDB; Persist Security Info = True; + 用户ID = sa; + 密码= ***;
private void Save_Click(对象 sender,EventArgs e)
{
if (DispatcherName.Text == )
{
MessageBox.Show( < span class =code-string>你想失去生产力吗?\ n请选择Dispatcher名称。);
DispatcherName.Focus();
}
else if (DispatchNumber.Text == < span class =code-string> )
{
MessageBox.Show( 你真的处理了一个Dispatch吗?\我没有看到一个Dispatch Number。);
DispatchNumber.Focus();
}
else if (DPSType.Text == < span class =code-string> )
{
MessageBox.Show( 你刚忘记了什么?\ n请输入DPS类型);
DPSType.Focus();
}
else if (QG.Text == < span class =code-string> )
{
MessageBox.Show( 请选择DPS所属的队列。);
QG.Focus();
}
else
{
dateTimePickerDT.Value = System.DateTime.Now;
string sDispatcher = DispatcherName.Text;
string sDPSNum = DispatchNumber.Text;
string sDPSType = DPSType.Text;
DateTime dtDT = dateTimePickerDT.Value;
string sQueue = QG.Text;
string query = INSERT INTO dps_data(DispatcherName,DispatchNumber,DPSType,DateTime,Queue) + VALUES(@Dispatcher, @DPSNum,@ DPSType,@ DT,@ Queue);
SqlConnection conn = new SqlConnection(连接);
SqlCommand cmd = new SqlCommand(query,conn);
conn.Open();
cmd.Parameters.Add( new SqlParameter( @ Dispatcher,sDispatcher));
cmd.Parameters.Add( new SqlParameter( @DPSNum,sDPSNum));
cmd.Parameters.Add( new SqlParameter( @DPSType,sDPSType));
cmd.Parameters.Add( new SqlParameter( @DT,dtDT));
cmd.Parameters.Add( new SqlParameter( @Queue,sQueue));
int result = cmd.ExecuteNonQuery();
if (结果> 0 )
{
MessageBox.Show( 数据成功添加);
}
else
{
MessageBox.Show( 添加数据时出错);
}
conn.Close();
DispatchNumber.Text = String .Empty;
DPSType.Text = String .Empty;
QG.Text = String .Empty;
}
}
私有 void Form1_Load( object sender,EventArgs e)
{
}
private void DispatcherName_SelectedIndexChanged( object sender,EventArgs e)
{
}
private void QG_SelectedIndexChanged( object sender,EventArgs e)
{
}
}
}
这是您需要的粗略示例。
IF EXISTS ( SELECT * FROM [dbo]。[表] WHERE [uid] = @ uid)
BEGIN
- 更新现有记录
更新 [dbo]。[表] SET [Field1] = @ Parameter1,[Field2] = @ Parameter2
WHERE [uid] = @ uid
END
ELSE
BEGIN
- insert新记录
INSERT INTO [dbo]。[表]([Field1],[Field2])
VALUES ( @ Parameter1 , @ Parameter2 )
SET @ uid = SCOPE_IDENTITY ()
END
有关如何创建和执行存储过程的基本信息,你会在这里找到:
如何:创建和执行SQL语句返回无值 [ ^ ]
如何:执行返回行的存储过程 [ ^ ]
使用带命令的存储过程 [ ^ ]
我为你写了一个你需要的样本存储过程。
请注意dpssp_InsertOrUpdate是存储过程名称,我根据你的输入参数做出假设代码。
必须在相应的评论中写下插入和更新声明。
CREATE PROCEDURE dbo.dpssp_InsertOrUpdate
(
- 我对sql类型做出假设
@ Dispatcher nvarchar (max),
@ DPSNum nvarchar (max),
@ DPSType nvarchar (max),
@ DT datetime ,
@ Queue nvarchar (max)
)
AS
BEGIN
IF EXISTS (
SELECT Dispatcher
FROM dps_data
WHERE
DispatcherName = @ Dispatcher
AND DispatchNumber = @ DPSNum
AND DPSType = @ DPSType
AND [ DateTime ] = @ DT
AND Queue = @ Queue
)
BEGIN
- UPDATE SQL
END
ELSE
BEGIN
- INSERT SQL
结束
结束
您将需要使用像管理工作室这样的SQL客户端在数据库引擎中创建存储过程。
在代码中你需要将你的sql命令改为这样的:
SqlConnection conn = new SqlConnection(连接);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
// 假设解决方案sp名称
cmd.CommandText = dbo.dpssp_InsertOrUpdate;
cmd.Connection = conn;
conn.Open();
cmd.Parameters.Add( new SqlParameter( @ Dispatcher,sDispatcher));
cmd.Parameters.Add( new SqlParameter( @DPSNum,sDPSNum));
cmd.Parameters.Add( new SqlParameter( @DPSType,sDPSType));
cmd.Parameters.Add( new SqlParameter( @DT,dtDT));
cmd.Parameters.Add( new SqlParameter( @Queue,sQueue));
int result = cmd.ExecuteNonQuery();
// 也许你想处理sqlexception或异常类型。
conn.Close();
---------------------------------------------- ------------------------
祝你好运。
Guys, please help.. I am a novice with very little knowledge about programming, here I have my source code that inserts data into SQL DB, want to add a Stored procedure to this code so that it validates if the entry exists before inserting data, "IF EXISTS UPDATE ELSE INSERT", I have no idea about stored procedures, Kindly help, need to finish this project overnight, Please assist.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Collections;
namespace Dispatch_Tracker
{
public partial class DispatchTracker : Form
{
public DispatchTracker()
{
InitializeComponent();
}
string connection = "Data Source=WN7-4WVX1BS;" + "Initial Catalog=DispatchDB;Persist Security Info=True;" + "User ID=sa;" + "Password= ***";
private void Save_Click(object sender, EventArgs e)
{
if (DispatcherName.Text == "")
{
MessageBox.Show("Would you like to Lose out on Productivity?\nPlease select the Dispatcher name.");
DispatcherName.Focus();
}
else if (DispatchNumber.Text == "")
{
MessageBox.Show("Did you really process a Dispatch?\nI don't see a Dispatch Number.");
DispatchNumber.Focus();
}
else if (DPSType.Text == "")
{
MessageBox.Show("Did You Just Forget Something?\nPlease Enter the DPS Type");
DPSType.Focus();
}
else if (QG.Text == "")
{
MessageBox.Show("Please select the Queue which the DPS belongs to.");
QG.Focus();
}
else
{
dateTimePickerDT.Value = System.DateTime.Now;
string sDispatcher = DispatcherName.Text;
string sDPSNum = DispatchNumber.Text;
string sDPSType = DPSType.Text;
DateTime dtDT = dateTimePickerDT.Value;
string sQueue = QG.Text;
string query = "INSERT INTO dps_data(DispatcherName, DispatchNumber, DPSType, DateTime, Queue)" +"VALUES( @Dispatcher, @DPSNum, @DPSType, @DT, @Queue)";
SqlConnection conn = new SqlConnection(connection);
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
cmd.Parameters.Add(new SqlParameter("@Dispatcher", sDispatcher));
cmd.Parameters.Add(new SqlParameter("@DPSNum", sDPSNum));
cmd.Parameters.Add(new SqlParameter("@DPSType", sDPSType));
cmd.Parameters.Add(new SqlParameter("@DT", dtDT));
cmd.Parameters.Add(new SqlParameter("@Queue", sQueue));
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
MessageBox.Show("Data Added Successfully");
}
else
{
MessageBox.Show("Error in Adding Data");
}
conn.Close();
DispatchNumber.Text = String.Empty;
DPSType.Text = String.Empty;
QG.Text = String.Empty;
}
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void DispatcherName_SelectedIndexChanged(object sender, EventArgs e)
{
}
private void QG_SelectedIndexChanged(object sender, EventArgs e)
{
}
}
}
This is a rough sample of what you require.
IF EXISTS (SELECT * FROM [dbo].[Table] WHERE [uid]=@uid) BEGIN --update existing record UPDATE [dbo].[Table] SET [Field1]=@Parameter1, [Field2]=@Parameter2 WHERE [uid]=@uid END ELSE BEGIN --insert new record INSERT INTO [dbo].[Table] ([Field1], [Field2]) VALUES (@Parameter1, @Parameter2) SET @uid=SCOPE_IDENTITY() END
Basic information about how to create and execute stored procedure, you''ll find here:
How to: Create and Execute an SQL Statement that Returns No Value[^]
How to: Execute a Stored Procedure that Returns Rows[^]
Using Stored Procedures with a Command[^]
Hi,
I write you a sample stored procedure for what you need.
Please note that dpssp_InsertOrUpdate is the stored procedure name, and that i make assumptions about input parameters based on your code.
Is necessary to write the insert and update statement in the respective comment.
CREATE PROCEDURE dbo.dpssp_InsertOrUpdate ( --i make an assumption about sql types @Dispatcher nvarchar(max), @DPSNum nvarchar(max), @DPSType nvarchar(max), @DT datetime, @Queue nvarchar(max) ) AS BEGIN IF EXISTS( SELECT Dispatcher FROM dps_data WHERE DispatcherName = @Dispatcher AND DispatchNumber = @DPSNum AND DPSType = @DPSType AND [DateTime] = @DT AND Queue = @Queue ) BEGIN -- UPDATE SQL END ELSE BEGIN -- INSERT SQL END END
You will need to use a sql client like management studio for create the stored procedure in the database engine.
In the code you need to change your sql command to something like this:
SqlConnection conn = new SqlConnection(connection); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; //Assuming solution sp name cmd.CommandText = "dbo.dpssp_InsertOrUpdate"; cmd.Connection = conn; conn.Open(); cmd.Parameters.Add(new SqlParameter("@Dispatcher", sDispatcher)); cmd.Parameters.Add(new SqlParameter("@DPSNum", sDPSNum)); cmd.Parameters.Add(new SqlParameter("@DPSType", sDPSType)); cmd.Parameters.Add(new SqlParameter("@DT", dtDT)); cmd.Parameters.Add(new SqlParameter("@Queue", sQueue)); int result = cmd.ExecuteNonQuery(); //Maybe you want to handle sqlexception or exception types. conn.Close(); ----------------------------------------------------------------------
Good luck.
这篇关于存储过程C# - “IF EXISTS UPDATE ELSE INSERT”的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!