- Create an object instance that represents a row in a database.
- The table must maintain an identity column that the users use to query the table.
- The instance will be created on the client side, but the id must come from the database.
- You may not use a GUID, because the customer refuses to use them.
Assume this table:
CREATE TABLE [dbo].[bars] (This C# can be used to insert and return the identity:
[id] [int] IDENTITY (1, 1) NOT NULL ,
[bar] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
SqlConnection connection = new SqlConnection("initial catalog=foo; data source=blah; uid=me; pwd=right");Drop me a line if you've successfully (or unsuccessfully) done something similar.
SqlCommand command = new SqlCommand("insert into bars values ('some value') select @@identity",connection);
connection.Open();
int identity = int.Parse(command.ExecuteScalar().ToString());
connection.Close();
In the current project I'm developing, using the EntLib, I use this code:
ReplyDeleteThe Table on SQL Server:
CREATE TABLE [dbo].[Utenti] (
[nUid] [int] IDENTITY (1, 1) NOT NULL ,
[uid] [smallint] NOT NULL ,
[name] [userName] NULL ,
[flgAttivo] [bit] NULL
) ON [PRIMARY]
GO
The Stored Procedure alway on SQL Server:
CREATE procedure dbo.Utenti_Insert
@uid smallint,
@name userName,
@flgAttivo bit,
@nUid int OUTPUT
As
Set @nUid = 0
INSERT INTO Utenti
(uid,name,flgAttivo)
VALUES
(@uid,@name,@flgAttivo)
Set @nUid = @@identity
GO
The C# code in the application (a Unit Test):
DBCommandWrapper insertCommand = databaseTest.GetStoredProcCommandWrapper("Utenti_Insert");
insertCommand.AddInParameter( "@uid", DbType.Int16, systemUserID);
insertCommand.AddInParameter( "@name", DbType.String, Name);
insertCommand.AddInParameter( "@flgAttivo", DbType.Boolean, FlagAttivo );
insertCommand.AddOutParameter( "@nUid",DbType.Int32, 4);
databaseTest.ExecuteNonQuery( insertCommand);
int internalUserID = (int)insertCommand.GetParameterValue("@nUid");
Bye and thanks for writing this blog I've found so many inspiration in it!
Antonio
I use a final SELECT, but I return SCOPE_IDENTITY() instead of @@IDENTITY, because @@IDENTITY can be wrong if you have triggers.
ReplyDelete