Stored Procedure C# - "IF EXISTS UPDATE ELSE INSERT"

Question

5.00/5 (1 vote)

See more:

VS2010

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.

C#

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)
        {
            
        }
       
    }
}

Posted 20-Mar-13 7:13am

JagzKrish

Updated 20-Mar-13 7:15am

v3

Add a Solution

Comments

RedDk 20-Mar-13 13:32pm

I think this might be a double post ...

JagzKrish 20-Mar-13 13:36pm

RedDK - yes it might be, but with a different question as suggested by one of the members, in response to my previous question, member wanted me to post another question in specific to Stored procedure, which I have no idea about, added my source code so that one canunderstand what I am actually looking fwd to.
Thanks

RedDk 20-Mar-13 13:44pm

I could be wrong about this but isn't it possible to just a add tag to the OP by clicking on "Improve question"? Not arguing, just a suggestion for next time? Anyway ...

JagzKrish 20-Mar-13 13:46pm

I will sure keep that in mind, I am all new here on Code Project, so have not much knowledge on stuff, Sorry for my ignorance and thank you for the advice :)

RedDk 20-Mar-13 14:46pm

Just a thought but have a look at:
http://code.msdn.microsoft.com/vstudio/site/search?query=SQL&f%5B4%5D.Value=SQL&f%5B4%5D.Type=SearchText&f%5B0%5D.Value=9.0&f%5B0%5D.Type=VisualStudioVersion&f%5B0%5D.Text=Visual%20Studio%202008&f%5B1%5D.Value=C%23&f%5B1%5D.Type=ProgrammingLanguage&f%5B1%5D.Text=C%23&f%5B2%5D.Value=10.0&f%5B2%5D.Type=VisualStudioVersion&f%5B2%5D.Text=Visual%20Studio%202010&f%5B3%5D.Value=Data&f%5B3%5D.Type=Platform&f%5B3%5D.Text=Data&ac=4

4 solutions

Solution 3

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[^]

Posted 20-Mar-13 9:42am

Maciej Los

Comments

Prasad_Kulkarni 21-Mar-13 1:50am

5'ed :-D

Maciej Los 21-Mar-13 2:49am

Thank you, Prasad ;)

Solution 4

Refer to this sp i think u got the solution if u have any query let me know............

SQL

create proc sp_dpsdata( @Dispatcher datatype, @DPSNum datatype, @DPSType datatype, @DT datatype, @Queue datatype)
as begin
declare count int
count=(select count(*) from dps_data where DispatchNumber=@DPSNum)
note:DispatchNumber should be primary key column or instead of DispatchNumber mention primary key column

if(count>0)
{
update dpsdata table query
}
else
{
insert dpsdata table query
}
end

Posted 20-Mar-13 19:55pm

skstcs

Updated 20-Mar-13 20:26pm

v2

Comments

Member 11344289 18-Jan-15 9:24am

Dear sir,
Can u help me plz- code through store procedure , its a registration form there are 4 column like Empname,Empsalary,address, qualification -condition - check if Empname is already exist then update all record of that employee otherwise insert new record of Employee it should be through store procedure in SQL 2008 R2. if this possible plz send help on my Email-kaushalsinghsviim@gmail.com thanks&Regards -kaushal

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pheonyx · Accepted Answer · 2013-03-20T07:19:00

Solution 1

This is a rough sample of what you require.

SQL

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

Posted 20-Mar-13 7:19am

Pheonyx

Comments

Maciej Los 20-Mar-13 15:45pm

It should works, my 5!

JagzKrish 8-Apr-13 20:25pm

Pheonyx: This does the Job for me perfectly fine. However, challenging part is, I want the Application to throw a pop up or message box, "Data already exists do you want to update?" on clicking Ok it should be updated, if data doesn't exist, it will just insert without any pop up. Any solution for this Lame man?

Pheonyx 9-Apr-13 4:04am

Hi JagzKrish,

Basically, if you are passing in the uid, and the user is not able to set that value (which they shouldnt be as it is unique) in the main application code just check to see if uid > 0, if it is >0 then it should exist so ask them if they want to update it. If it is = 0 then just save it.

JagzKrish 9-Apr-13 13:06pm

Pheonyx: I am sorry, still don't understand that, in which part of the code should I use the IF ELSE statement? so that I can throw a message box before actually updating the Row in the table. Kindly help, I am a NooB...

Pheonyx 9-Apr-13 14:57pm

You can't throw a message box from a stored procedure. You need to do some logic in your main program that checks to see if the entry exists. I am assuming your table has a unique primary key column. But without knowing your actual table structure and the stored procedure you have produced so far it is quote hard to advise.

JagzKrish 9-Apr-13 15:17pm

Pheonyx: Here is the stored procedure that I have used.

CREATE PROCEDURE [dbo].[dpssp_InsertOrUpdate]
(
@Dispatcher char(30),
@DPSNum char(10),
@DPSType char(10),
@DT datetime,
@Queue char(10)
)
AS BEGIN

IF EXISTS (SELECT * FROM dbo.dps_data WHERE [DispatchNumber]=@DPSNum)

BEGIN

UPDATE dbo.dps_data SET [DispatcherName] = @Dispatcher, [DispatchNumber] = @DPSNum, [DPSType]=@DPSType,[DateTime]=@DT,[Queue]=@Queue
WHERE DispatchNumber=@DPSNum

END

ELSE

BEGIN

INSERT INTO dps_data(DispatcherName, DispatchNumber, DPSType, DateTime, Queue) VALUES( @Dispatcher, @DPSNum, @DPSType, @DT, @Queue)
SET @DPSNum=SCOPE_IDENTITY()
END

END

Pheonyx 10-Apr-13 4:17am

In your table, how is the column DPSNum configured? It appears to be a char(10) from what you have shown. Is it the Primary Key column of the table? Is it guaranteed to be unique?

JagzKrish 10-Apr-13 8:08am

Pheonyx: DPSNum is the Primary Key, so this is what is happening when I execute the Application with the stored procedure. Here is an example, lets say there is a Number 123 in the Column DPSNum and rest four columns have 'A' 'B' 'C' 'D' respectively. When I again insert the values 'E' 'F' 'G' 'H' and Value '123' in the DPSNum Box in the application, The values ABCD in the table gets replaced by EFGH, is that right?

Pheonyx 10-Apr-13 8:19am

Yes that is correct.
If that is the case, then you can take two approaches to checking if data exists.
a) you can perform a select query on the database passing in the DPSNum, if this returns a count value > 0 then prompt to ask if they wish to update.
b) Check the DPSNum field to see if it has been left blank, if it is blank then you can be sure it is a new DPSNum, if it is not blank then again, ask if they wish to update it.

How you go about this depends alot on your current program design and what is easier.
If DPSNum can be typed in by a user then take the first approach, if DPSNum is pre-populated based on a selection or creation of a new object then take the second approach.

At least that is my advice.

José Amílcar Casimiro · Accepted Answer · 2013-03-20T07:21:00

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.

SQL

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:

C#

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.

Stored Procedure C# - "IF EXISTS UPDATE ELSE INSERT"

4 solutions

Solution 1

Solution 3

Solution 2

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us