.net - tabla - recuperar id insertado sql server

¿Es posible recuperar el valor de la columna IDENTITY en insertar usando SqlCommandBuilder(sin usar Stored Proc)?

Question

FYI: me estoy ejecutando en dotnet 3.5 SP1

Estoy tratando de recuperar el valor de una columna de identidad en mi conjunto de datos después de realizar una actualización (usando un SqlDataAdapter y SqlCommandBuilder). Después de realizar SqlDataAdapter.Update (myDataset), quiero poder leer el valor myDataset.tables(0).Rows(0)("ID") de myDataset.tables(0).Rows(0)("ID") , pero es System.DBNull (a pesar del hecho de que la fila fue insertada).

(Nota: ¡No quiero escribir explícitamente un nuevo procedimiento almacenado para hacer esto!)

Un método que a menudo se publica http://forums.asp.net/t/951025.aspx modifica el SqlDataAdapter.InsertCommand y el UpdatedRowSource como sigue:

SqlDataAdapter.InsertCommand.CommandText += "; SELECT MyTableID = SCOPE_IDENTITY()"
InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

Aparentemente, esto pareció funcionar para muchas personas en el pasado, pero no funciona para mí.

Otra técnica: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=619031&SiteID=1 tampoco funciona para mí, ya que después de ejecutar SqlDataAdapter.Update, la colección SqlDataAdapter.InsertCommand.Parameters se restablece a el original (perdiendo el parámetro agregado adicional).

¿¿¿Alguien sabe la respuesta de esto???

Answer 1

Se puede indicar al comando de inserción que actualice el registro insertado utilizando los parámetros de salida o el primer registro devuelto (o ambos) utilizando la propiedad UpdatedRowSource ...

InsertCommand.UpdatedRowSource = UpdateRowSource.Both;

Si quisiera usar un procedimiento almacenado, estaría listo. Pero desea utilizar un comando sin procesar (también conocido como la salida del constructor de comandos), que no permite a) a) los parámetros de salida ob) devolver un registro. ¿Por qué es esto? Bueno, para a) esto es lo que se parecerá a su InsertCommand ...

INSERT INTO [SomeTable] ([Name]) VALUES (@Name)

No hay forma de ingresar un parámetro de salida en el comando. ¿Y qué hay de b)? Desafortunadamente, el DataAdapter ejecuta el comando Insert llamando a los comandos del método ExecuteNonQuery. Esto no devuelve ningún registro, por lo que no hay forma de que el adaptador actualice el registro insertado.

Por lo tanto, debe usar un proceso almacenado o renunciar al uso del DataAdapter.

Answer 2

Si esos otros métodos no le funcionaron, las herramientas proporcionadas por .Net (SqlDataAdapter) etc. realmente no ofrecen mucho más con respecto a la flexibilidad. Por lo general, debe llevarlo al siguiente nivel y comenzar a hacer cosas manualmente. El procedimiento almacenado sería una forma de seguir usando el SqlDataAdapter. De lo contrario, debe pasar a otra herramienta de acceso a datos, ya que las bibliotecas de datos .Net tienen límites, ya que el diseño es simple. Si su modelo no funciona con su visión, debe desplegar su propio código en algún punto / nivel.

Answer 3

Yo tuve el mismo problema. Fue resuelto cuando cloné el comando generado por el constructor de comandos. Parece que incluso cuando cambias el comandoTexto del comando Insertar, sigue obteniendo el comando generado por Commandbuilder ... Aquí está el código que usé para clonar el comando ...

        private static void CloneBuilderCommand(System.Data.Common.DbCommand toClone,System.Data.Common.DbCommand repository)
    {
        repository.CommandText = toClone.CommandText;
        //Copying parameters
        if (toClone.Parameters.Count == 0) return;//No parameters to clone? go away!
        System.Data.Common.DbParameter[] parametersArray= new System.Data.Common.DbParameter[toClone.Parameters.Count];
        toClone.Parameters.CopyTo(parametersArray, 0);
        toClone.Parameters.Clear();//Removing association before link to the repository one
        repository.Parameters.AddRange(parametersArray);            
    }

Answer 4

Si solo quiere (a) insertar un registro en la tabla, (b) usar un conjunto de datos y (c) no usar un procedimiento almacenado, entonces puede seguir esto:

1. Crea tu dataAdapter, pero en la instrucción select agrega WHERE 1 = 0, para que no tengas que descargar toda la tabla: paso opcional para el rendimiento

2. Cree una instrucción INSERT personalizada con instrucción de selección de identidad de ámbito y un parámetro de salida.

3. Haga el procesamiento normal, llene el conjunto de datos, agregue el registro y guarde la actualización de la tabla.

4. Ahora debería poder extraer la identidad del parámetro directamente.

Ejemplo:

    ''-- post a new entry and return the column number
    '' get the table stucture
    Dim ds As DataSet = New DataSet()
    Dim da As SqlDataAdapter = New SqlDataAdapter(String.Concat("SELECT * FROM [", fileRegisterSchemaName, "].[", fileRegisterTableName, "] WHERE 1=0"), sqlConnectionString)
    Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)

    '' since we want the identity column back (FileID), we need to write our own INSERT statement
    da.InsertCommand = New SqlCommand(String.Concat("INSERT INTO [", fileRegisterSchemaName, "].[", fileRegisterTableName, "] (FileName, [User], [Date], [Table]) VALUES (@FileName, @User, @Date, @Table); SELECT @FileID = SCOPE_IDENTITY();"))
    da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
    With da.InsertCommand.Parameters
        .Add("@FileName", SqlDbType.VarChar, 1024, "FileName")
        .Add("@User", SqlDbType.VarChar, 24, "User")
        .Add("@Date", SqlDbType.DateTime, 0, "Date")
        .Add("@Table", SqlDbType.VarChar, 128, "FileName")
        '' allow the @FileID to be returned back to us
        .Add("@FileID", SqlDbType.Int, 0, "FileID")
        .Item("@FileID").Direction = ParameterDirection.Output
    End With

    '' copy the table structure from the server and create a reference to the table(dt)
    da.Fill(ds, fileRegisterTableName)
    Dim dt As DataTable = ds.Tables(fileRegisterTableName)

    '' add a new record
    Dim dr As DataRow = dt.NewRow()
    dr("FileName") = fileName
    dr("User") = String.Concat(Environment.UserDomainName, "/", Environment.UserName)
    dr("Date") = DateTime.Now()
    dr("Table") = targetTableName
    dt.Rows.Add(dr)

    '' save the new record
    da.Update(dt)

    '' return the FileID (Identity)
    Return da.InsertCommand.Parameters("@FileID").Value

Pero eso es bastante largo para hacer lo mismo que esto ...

'' add the file record
    Dim sqlCmd As SqlCommand = New SqlCommand(String.Concat("INSERT INTO [", fileRegisterSchemaName, "].[", fileRegisterTableName, "] (FileName, [User], [Date], [Table]) VALUES (@FileName, @User, @Date, @Table); SELECT SCOPE_IDENTITY();"), New SqlConnection(sqlConnectionString))
    With sqlCmd.Parameters
        .AddWithValue("@FileName", fileName)
        .AddWithValue("@User", String.Concat(Environment.UserDomainName, "/", Environment.UserName))
        .AddWithValue("@Date", DateTime.Now())
        .AddWithValue("@Table", targetTableName)
    End With
    sqlCmd.Connection.Open()
    Return sqlCmd.ExecuteScalar

Answer 5

Encontré la respuesta que me funciona aquí: http://www.dotnetmonster.com/Uwe/Forum.aspx/dotnet-ado-net/4933/Have-soln-but-need-understanding-return-IDENTITY-issue

Código que funcionó (desde el sitio - atribuido a Girish)

//_dataCommand is an instance of SqlDataAdapter
//connection is an instance of ConnectionProvider which has a property called DBConnection of type SqlConnection
//_dataTable is an instance of DataTable

SqlCommandBuilder bldr = new SqlCommandBuilder(_dataCommand);
SqlCommand cmdInsert = new SqlCommand(bldr.GetInsertCommand().CommandText, connection.DBConnection);
cmdInsert.CommandText += ";Select SCOPE_IDENTITY() as id";

SqlParameter[] aParams = new
SqlParameter[bldr.GetInsertCommand().Parameters.Count];
bldr.GetInsertCommand().Parameters.CopyTo(aParams, 0);
bldr.GetInsertCommand().Parameters.Clear();

for(int i=0 ; i < aParams.Length; i++)
{
 cmdInsert.Parameters.Add(aParams[i]);
}

_dataCommand.InsertCommand = cmdInsert;
_dataCommand.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
_dataCommand.Update(_dataTable);

Answer 6

¿Has buscado utilizar LINQ en su lugar? Entiendo que esto no aborda su pregunta real, pero si está utilizando .NET 3.5, realmente debería intentar usar LINQ. De hecho, con la llegada de Code First EntityFramework, creo que podría elegir fácilmente LINQ to SQL o EF como alternativas relativamente livianas a su propio DAL.

Answer 7

Este es un problema con el que me he encontrado antes, el error parece ser que cuando llamas a da.Update (ds); la matriz de parámetros del comando insert se restablece a la lista inicial que se creó a partir de su generador de comandos, elimina los parámetros de salida agregados para la identidad.

La solución es crear un nuevo DataAdapter y copiar en los comandos, luego usar este nuevo para hacer su da.update (ds);

me gusta

SqlDataAdapter da = new SqlDataAdapter("select Top 0 " + GetTableSelectList(dt) + 
"FROM " + tableName,_sqlConnectString);
SqlCommandBuilder custCB = new SqlCommandBuilder(da);
custCB.QuotePrefix = "[";
custCB.QuoteSuffix = "]";
da.TableMappings.Add("Table", dt.TableName);

da.UpdateCommand = custCB.GetUpdateCommand();
da.InsertCommand = custCB.GetInsertCommand();
da.DeleteCommand = custCB.GetDeleteCommand();

da.InsertCommand.CommandText = String.Concat(da.InsertCommand.CommandText, 
"; SELECT ",GetTableSelectList(dt)," From ", tableName, 
" where ",pKeyName,"=SCOPE_IDENTITY()");

SqlParameter identParam = new SqlParameter("@Identity", SqlDbType.BigInt, 0, pKeyName);
identParam.Direction = ParameterDirection.Output;
da.InsertCommand.Parameters.Add(identParam);

da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

//new adaptor for performing the update                     
SqlDataAdapter daAutoNum = new SqlDataAdapter();
daAutoNum.DeleteCommand = da.DeleteCommand;
daAutoNum.InsertCommand = da.InsertCommand;
daAutoNum.UpdateCommand = da.UpdateCommand;

daAutoNum.Update(dt);

Answer 8

El problema para mí fue donde se colocó el código.

Agregue el código en el controlador de eventos RowUpdating, algo como esto:

void dataSet_RowUpdating(object sender, SqlRowUpdatingEventArgs e)
{
    if (e.StatementType == StatementType.Insert)
    {
        e.Command.CommandText += "; SELECT ID = SCOPE_IDENTITY()";
        e.Command.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
     }
}

Answer 9

En realidad, esto funciona para mí:

SqlDataAdapter.InsertCommand.CommandText += "; SELECT MyTableID = SCOPE_IDENTITY()" InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

Aclamaciones.

Answer 10

Lo que funciona para mí es configurar un MissingSchemaAction:

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(myDataAdapter);
myDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

Esto me permite recuperar la clave principal (si es una identidad, o autonumerador) después de una inserción.

Buena suerte.

Answer 11

En realidad, es aún más fácil, no hay necesidad en ningún comando personalizado. Cuando crea el compilador de tablas en el diseñador de conjuntos de datos, especifique "actualizar la tabla de datos" en Opciones avanzadas del asistente. Con eso, después de emitir dataadapter.update mydataset, encontrará la columna de identificación en la tabla de datos poblada con el nuevo valor de la base de datos.

Answer 12

Mi solución:

SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Mytable;" , sqlConnection);

SqlCommandBuilder cb = new SqlCommandBuilder(da);

da.DeleteCommand = (SqlCommand)cb.GetDeleteCommand().Clone();
da.UpdateCommand = (SqlCommand)cb.GetUpdateCommand().Clone();

SqlCommand insertCmd = (SqlCommand)cb.GetInsertCommand().Clone();

insertCmd.CommandText += ";SET @Id = SCOPE_IDENTITY();";
insertCmd.Parameters.Add("@Id", SqlDbType.Int, 0, "Id").Direction = ParameterDirection.Output;
da.InsertCommand = insertCmd;
da.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

cb.Dispose();

Answer 13

Otra forma de usar solo un objeto de comando.

Dim sb As New StringBuilder
sb.Append(" Insert into ")
sb.Append(tbl)
sb.Append(" ")
sb.Append(cnames)
sb.Append(" values ")
sb.Append(cvals)
sb.Append(";Select SCOPE_IDENTITY() as id") ''add identity selection

Dim sql As String = sb.ToString

Dim cmd As System.Data.Common.DbCommand = connection.CreateCommand
cmd.Connection = connection
cmd.CommandText = sql
cmd.CommandType = CommandType.Text
cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

''retrieve the new identity value, and update the object
Dim dec as decimal = CType(cmd.ExecuteScalar, Decimal)