C# and SQL-Server Stored Procedures

[Guest]

Hello,

I have got a little problem with stored procedures and C#.

I have got a stored procedure which should only insert something in a table.
For example:

ALTER PROCEDURE DBO.PROC1
AS
insert into dbo.Test values ('test')

when I execute this procedure in my Visual Studio Envirment with right click
on it. It makes what it should The procedure inserts a row in my table.
But if I trie to execute it from C#, it doesn't.

SqlCommand cmd = new SqlCommand("proc1",conn);
cmd.ExecuteNonQuery();

I get the message that 1 row has been affekted - but there is no entry in
the table.

Does anyone has some ideas what I have done wrong?

Thanks!

Jacek

 

[Dave Sexton]

Hi Jacek,

Run Sql Profiler and see if the procedure is being called at all. Sql Profile
can be launched from Enterprise Manager (Sql Server 2000) or Sql Server
Management Studio (Sql Server 2005), both from the Tools menu, IIRC.

 

[Guest]

Hi Dave,

thanks for the very fast answer. I've forgotten to say, that I use the
Express Edition - I think there is no Profiler. But when I call a bigger
procedure with some more insert and update commands I get the message that XX
rows has been affekted. So I think the procedure has been executed, because
the count of rows that has been affekted is similar to the count of update
and insert-commands I use in the peocedure, but nothing happens.

As output parameter I have defined @@identity and the number I get is also
the next one which normaly would been uses for the new row.

 

[Dave Sexton]

Hi Jacek,

It sounds like it's working but your not looking at the right place to verify.
Make sure that you refresh the view if you have the table opened in Visual
Studio.

Can you programmatically extract the new values from the database?

 

[Guest]

Did you set Command.CommandType=CommandType.StoredProcedure?
You have 2 paths in Sql Server - the RPC path (stored procs) and the
Language path (e.g. "Text").
Peter

 

[Guest]

Hi Dave,

I have closed the view and opened it again and there is no entry. But when I
execute the prcedure from the Data Connections tree it works.

I can't extract the values programmatically, because they don't exists.
Every time I execute the procedure I get the same @@identity value. I am a
little bit confused now (-;

 

[Guest]

I have also tried in a query window "execute proc1" - this works.
But
cmd = new SqlCommand("Execute proc1");
and
cmd = new SqlCommand("proc1");
cmd.CommandType = CommandType.StoredProcedure;
won't work.

 

[Guest]

Hi Peter,

thanks for the answer.

Yes, I set cmd.CommandType = CommandType.StoredProcedure
I also tried with cmd = new SqlCommand("Execute proc1") without
CommandType.StoredProcedure, but this also won't work.

 

[Dave Sexton]

Hi Jacek,

Verify that the connection string used in the Data Connections window is the
exact same connection string that you are using in code.

The stored procedure code for proc1 that you posted doesn't use @@IDENTITY at
all. Are you retrieving the value of @@IDENTITY in a separate query or
connection? You might want to post the complete proc1 SQL if you haven't
already.

(You should really use the SCOPE_IDENTITY() function instead, unless you
understand the difference and require the value of @@IDENTITY. I doubt that
using SCOPE_IDENTITY() instead will help your current issue, however.)

 

[Guest]

Hi Dave,

the procedure I posted was noly an example. Here is the real procedure,
which I would like to use in my application. The sense is to update a
productdatabase with a new price. If an
productgroup/productfamily/productcategorie is existing, so the ID is taken.
If not it will be added to the table....
Whats realy funny - select commands works, only update and insert not.

ALTER PROCEDURE dbo.SpeichereArtikel
@ID int OUTPUT,
@Artikelnummer numeric(18,0),
@Herstellerartikelnummer varchar(50),
@Hersteller varchar(50),
@EAN varchar(50),
@Bezeichnung varchar(150),
@Bestand int,
@Preis money,
@Listenpreis money,
@Produktfamilie varchar(50),
@Produktkategorie varchar(50),
@Produktgruppe varchar(50),
@EOL bit,
@Gewicht decimal(18,4)
AS
declare @ProduktfamilieID as numeric(18,0)
declare @ProduktkategorieID as numeric(18,0)
declare @ProduktgruppeID as numeric(18,0)
declare @HerstellerID as numeric(18,0)

/* Produktfamilie einfügen */
if (select count(*) from UserProduktfamilie where
Bezeichnung=@Produktfamilie) = 0
begin
insert into UserProduktfamilie (Bezeichnung) values (@Produktfamilie)
end
SET @ProduktfamilieID = (select ID from UserProduktfamilie where
Bezeichnung=@Produktfamilie)

/* Produktkategorie einfügen */
if (select count(*) from UserProduktkategorie where
Bezeichnung=@Produktkategorie) = 0
begin
insert into UserProduktkategorie (Bezeichnung) values (@Produktkategorie)
end
SET @ProduktkategorieID = (select ID from UserProduktkategorie where
Bezeichnung=@Produktkategorie)

/* Produktgruppe einfügen */
if (select count(*) from UserProduktgruppe where
Bezeichnung=@Produktgruppe) = 0
begin
insert into UserProduktgruppe (Bezeichnung) values (@Produktgruppe)
end
SET @ProduktgruppeID = (select ID from UserProduktgruppe where
Bezeichnung=@Produktgruppe)

/* Hersteller einfügen */
if (select count(*) from UserHersteller where Bezeichnung=@Hersteller) = 0
begin
insert into UserHersteller (Bezeichnung) values (@Hersteller)
end
SET @HerstellerID = (select ID from UserHersteller where
Bezeichnung=@Hersteller)

/* prüfen, ob Artikel bereits eingetragen */
if (select count(*) from UserProdukte where Artikelnummer = @Artikelnummer)

0

BEGIN
/* Artikel vorhanden -> update */
update UserProdukte set
Herstellerartikelnummer = @Herstellerartikelnummer,
Hersteller = @HerstellerID,
EAN = @EAN,
Bezeichnung = @Bezeichnung,
Bestand = @Bestand,
Preis = @Preis,
Listenpreis = @Listenpreis,
Produktkategorie = @ProduktkategorieID,
Produktfamilie = @ProduktfamilieID,
Produktgruppe = @ProduktgruppeID,
EOL = @EOL,
Gewicht = @Gewicht
where Artikelnumer=@Artikelnummer
END
ELSE BEGIN
/* Artikel nicht vorhanden -> insert */
insert into UserProdukte (Artikelnummer, Herstellerartikelnummer,
Hersteller, EAN, Bezeichnung,
Bestand, Preis, Listenpreis, Produktkategorie, Produktfamilie,
Produktgruppe, EOL, Gewicht)
values
(@Artikelnummer, @Herstellerartikelnummer, @HerstellerID, @EAN,
@Bezeichnung,
@Bestand, @Preis, @Listenpreis, @ProduktkategorieID, @ProduktfamilieID,
@ProduktgruppeID, @EOL, @Gewicht)
end
SET @ID = @@IDENTITY
RETURN @@ERROR

 

[Guest]

Ok, now it works.
At the beginning I created a Database-File for my project in Visual Studio
2005.
I took the connection string from the properties window of the Database-File
in the Server Explorer Window. All works, only the procedure with the insert
and update command not. My connection string was" Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Datenbank.mdf;Integrated
Security=True;User Instance=True"

Now I have registered the database-file in the SQL Express Manager and made
a connection over tcp/ip. This works without any problems!

Is it possible that there is a bug?

Thanks for the help!

:

Jacek