SQL Server - último valor de un autoincremento en transacción

Buenas!!

Explico mi problema:

Utilizo Visual Studio 2005, SQL server 2005, Entlib

Tengo dos tablas, "Foto", "Justificador"

La tabla Foto tiene 3 columnas:
"Numero" (Clave, tipo int, IDENTITY, será el que nos interese en el problema)
"fk_numerodocumento" (Clave externa de documento, tipo int)
"imagen"

La tabla Justificador tiene 2 columnas;
"fk_numeroFoto_Foto" (tipo int, Clave)
"justificacion_de" (tipo varchar(), Clave también, tiene valores predefinidos)

Quiero insertar una foto en la tabla "Foto" de tal manera que cuando se inserte se autoincremente el valor de "Numero", me arrastre dicho numero a la tabla "Justificador" y pueda rellenar "justificacion_de".

Es necesario hacer todo esto dentro de una transacción. Mi pseudocódigo es algo así

Begin Transaction
try
{
//Introducimos la foto y nº de doc al que pertenece
//Ahora tenemos que saber que Numero tiene esa foto en la tabla foto, asique lo que hacemos es buscar
//el numero más alto y devolverlo

string buscar_num_mayor_sql = "SELECT max(numero) FROM Foto";

//Aquí queda esperando hasta que el tiempo expira, lo cual provoca error y hace saltar el //Rollback();
Ejecutar(buscar_num_mayor_sql);

Commit();
}
catch
{
Rollback();
}

Agradecería una Solución ya que no se si la que planteo sería correcta, o si existe alguna herramienta o método para poder solucionarlo.

Muchas gracias de antemano

Salu2

Debes utilizar la funcion SCOPE_IDENTITY(), esta te regresa el ultimo numero IDENTITY, insertado por tu transaccion.

Claro, esto deberias manejarlo en un PROCEDIMIENTO ALMACENADO.

Ok perfecto, tengo la solución implementada, pego los pasos y el código con deseo de que le sirva a alguien en un futuro:

1º) Crear un PROCEDIMIENTO ALMACENADO en la base de datos, en mi caso en particular es:

CREATE PROCEDURE dbo.InsertaFoto
(
@fk_numerodocumento_documento int,
@imagen image
)

AS
SET NOCOUNT ON

INSERT INTO Foto
(fk_numerodocumento_documento , imagen)
VALUES (@fk_numerodocumento_documento,@imagen)

RETURN SCOPE_IDENTITY()

2º) Desde código hacer la insercción y recoger el valor de retorno:

public void IntroducirFoto(int fk_numerodocumento_documento, Image img)
{
//Utilizo Entlib, por eso pongo DbCommand y no Command
DbCommand cmd = obj_conexion.CreateCommand();

try
{
MemoryStream ms = new System.IO.MemoryStream();
img.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);

SqlParameter par_numero = new SqlParameter("@fk_numerodocumento_documento", SqlDbType.Int);
SqlParameter par_imagen = new SqlParameter("@imagen", SqlDbType.Image);
par_numero.Value = fk_numerodocumento_documento;
par_imagen.Value = ms.GetBuffer();
cmd.Parameters.Add(par_numero);
cmd.Parameters.Add(par_imagen);

//Nombre del store procedure
cmd.CommandText = "InsertaFoto";
cmd.CommandType = CommandType.StoredProcedure;

//Hay que añadir un parámetro para que recoja el numero de foto
SqlParameter retValue = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
//Se especifíca que es de retorno
retValue.Direction = ParameterDirection.ReturnValue;
//Se agrega al comando
cmd.Parameters.Add(retValue);

DbDataReader dr = cmd.ExecuteReader();

int num = Convert.ToInt32(retValue.Value);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}

Bien, hay muchos detalles omitidos como por ejemplo crear la conexión con la base de datos etc, no nos atañe en el problema.

Muchísimas gracias Isaias por haberme orientado hacia la solución final ;)

Salu2

De nada, para eso estamos en al foros, para compartir conocimientos e ideas.

Saludos