Here is an example showing how I would do it - input and output:
public static bool PostalAreaInsert( SqlConnection sqlConnection, ref Guid oid, ref DateTime? registeredTime, ref Guid? registeredBy, Guid? region, string name, DateTime? fromTime, DateTime? throughTime)
{
bool result = false;
SqlCommand sqlCommand = sqlConnection.CreateCommand( );
using( sqlCommand )
{
sqlCommand.CommandText = "PostalAreaInsert";
sqlCommand.CommandType = CommandType.StoredProcedure;
SqlParameter oidParameter =
sqlCommand.Parameters.Add(new SqlParameter("@Oid",
SqlDbType.UniqueIdentifier));
oidParameter.Direction = ParameterDirection.InputOutput;
oidParameter.Value = oid;
SqlParameter registeredTimeParameter =
sqlCommand.Parameters.Add(new SqlParameter
("@RegisteredTime", SqlDbType.DateTime));
registeredTimeParameter.Direction =
ParameterDirection.InputOutput;
if( registeredTime.HasValue )
{
registeredTimeParameter.Value =
registeredTime.Value;
}
else
{
registeredTimeParameter.Value = DBNull.Value;
}
SqlParameter registeredByParameter =
sqlCommand.Parameters.Add(
new SqlParameter("@RegisteredBy",
SqlDbType.UniqueIdentifier));
registeredByParameter.Direction =
ParameterDirection.InputOutput;
if( ( registeredBy.HasValue )
&& ( registeredBy.Value != Guid.Empty ) )
{
registeredByParameter.Value = registeredBy.Value;
}
else
{
registeredByParameter.Value = DBNull.Value;
}
SqlParameter objectTypeParameter =
sqlCommand.Parameters.Add(
new SqlParameter("@ObjectType", SqlDbType.Int));
objectTypeParameter.Direction =
ParameterDirection.InputOutput;
objectTypeParameter.Value = 2;
SqlParameter regionParameter =
sqlCommand.Parameters.Add(
new SqlParameter("@Region",
SqlDbType.UniqueIdentifier));
if( ( region.HasValue )
&& ( region.Value != Guid.Empty ) )
{
regionParameter.Value = region.Value;
}
else
{
regionParameter.Value = DBNull.Value;
}
SqlParameter nameParameter =
sqlCommand.Parameters.Add(
new SqlParameter("@Name",
SqlDbType.NVarChar, 255));
if(( name != null )
&& ( name.Length > 0 ))
{
nameParameter.Value = name;
}
else
{
nameParameter.Value = DBNull.Value;
}
SqlParameter fromTimeParameter =
sqlCommand.Parameters.Add(
new SqlParameter("@FromTime",
SqlDbType.DateTime));
if( fromTime.HasValue )
{
fromTimeParameter.Value = fromTime.Value;
}
else
{
fromTimeParameter.Value = DBNull.Value;
}
SqlParameter throughTimeParameter =
sqlCommand.Parameters.Add(
new SqlParameter("@ThroughTime",
SqlDbType.DateTime));
if( throughTime.HasValue )
{
throughTimeParameter.Value = throughTime.Value;
}
else
{
throughTimeParameter.Value = DBNull.Value;
}
int rowsAffected = sqlCommand.ExecuteNonQuery();
if(rowsAffected == 1)
{
Guid newOid = (Guid)oidParameter.Value;
DateTime regTime =
Convert.ToDateTime
(registeredTimeParameter.Value);
Guid regByOid = (Guid)registeredByParameter.Value;
oid = newOid;
registeredTime = regTime;
registeredBy = regByOid;
result = true;
}
}
return result;
}
The stored procedure:
CREATE PROCEDURE [PostalAreaInsert]
@Oid uniqueidentifier OUTPUT,
@RegisteredTime DateTime OUTPUT,
@RegisteredBy uniqueidentifier OUTPUT,
@ObjectType int OUTPUT,
@Region uniqueidentifier,
@Name nvarchar(255),
@FromTime DateTime,
@ThroughTime DateTime
AS
BEGIN
IF @Oid IS NULL
BEGIN
SET @Oid = NEWID()
END
IF @ObjectType IS NULL
BEGIN
SET @ObjectType = 2
END
IF @RegisteredBy IS NULL
BEGIN
SET @RegisteredBy = (SELECT Oid From SecurityLogin WHERE [Identity] = SYSTEM_USER)
END
IF @RegisteredTime IS NULL
BEGIN
SET @RegisteredTime = SYSDATETIME()
END
DECLARE @TranCounter INT;
SET @TranCounter = @@TRANCOUNT;
IF @TranCounter > 0
SAVE TRANSACTION SavePoint2;
ELSE
BEGIN TRANSACTION;
BEGIN TRY
INSERT INTO Element(Oid,OptimisticLockField,RegisteredBy,RegisteredTime,ObjectType)
VALUES(@Oid,0,@RegisteredBy,@RegisteredTime,@ObjectType);
INSERT INTO [PostalArea](Oid, [Region], [Name], [FromTime], [ThroughTime])
VALUES(@Oid, @Region, @Name, @FromTime, @ThroughTime);
IF @TranCounter = 0
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
IF @TranCounter = 0
ROLLBACK TRANSACTION;
ELSE
IF XACT_STATE() <> -1
ROLLBACK TRANSACTION SavePoint2;
RAISERROR(
@ErrorMessage,
@ErrorSeverity,
@ErrorState);
END CATCH
END
GO
Regards
Espen Harlinn