Можно ли получить значение столбца IDENTITY для вставки с помощью SqlCommandBuilder (без использования хранимой процедуры)?

Ответ 1

Это проблема, с которой я столкнулся раньше, кажется, что ошибка при вызове da.Update(DS); массив параметров команды insert получает reset к встроенному списку, который был создан из вашего конструктора команд, он удаляет ваши добавленные выходные параметры для идентификации.

Решение состоит в том, чтобы создать новый dataAdapter и скопировать в командах, а затем использовать этот новый, чтобы выполнить da.update(ds);

как

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);

Ответ 2

Команда insert может быть проинструктирована об обновлении вставленной записи с использованием либо выходных параметров, либо первой возвращенной записи (или обоих) с использованием свойства UpdateRowSource...

InsertCommand.UpdatedRowSource = UpdateRowSource.Both;

Если вы хотите использовать хранимую процедуру, вы бы сделали. Но вы хотите использовать необработанную команду (так же, как вывод конструктора команд), которая не позволяет либо a) выводить параметры, либо b) возвращает запись. Почему это? Хорошо для a) это то, что ваш InsertCommand будет выглядеть...

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

Невозможно ввести в команду выходной параметр. Так как насчет б)? К сожалению, DataAdapter выполняет команду Insert, вызывая команды ExecuteNonQuery. Это не возвращает никаких записей, поэтому адаптер не может обновить вставленную запись.

Итак, вам нужно либо использовать хранимую процедуру, либо отказаться от использования DataAdapter.

Ответ 3

Что работает для меня - это настройка MissingSchemaAction:

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

Это позволяет мне получить первичный ключ (если он является идентификатором или автономом) после вставки.

Удачи.

Ответ 4

Собственно, это работает для меня:

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

Приветствия.

Ответ 5

У меня была та же проблема. Это было просто решено, когда я клонировал команду, сгенерированную командообразователем. Похоже, что даже когда вы меняете commandText команды insert, он продолжает получать команду, сгенерированную Commandbuilder... Вот он код, который я использовал для клонирования команды...

        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);            
    }

Ответ 6

Если вы просто хотите (a) вставить одну запись в таблицу, (b) использовать DataSet и (c) не использовать хранимую процедуру, то вы можете следовать этому:

• Создайте свой dataAdapter, но в инструкции select добавьте WHERE 1 = 0, так что вам не нужно загружать всю таблицу - необязательный шаг для производительности

• Создайте пользовательский оператор INSERT с оператором выбора видимости области и выходным параметром.

• Выполняйте обычную обработку, заполняя набор данных, добавляя запись и сохраняя обновление таблицы.

• Теперь вы можете напрямую извлечь идентификатор из параметра.

Пример:

    '-- 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

Но это довольно длительное время, чтобы сделать то же самое, что и это...

' 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

Ответ 7

Я нашел ответ, который работает для меня здесь: http://www.dotnetmonster.com/Uwe/Forum.aspx/dotnet-ado-net/4933/Have-soln-but-need-understanding-return-IDENTITY-issue

Код, который работал (с сайта - приписывается 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);

Ответ 8

Если эти другие методы не сработали для вас, предоставленные инструментами .Net(SqlDataAdapter) и т.д. действительно не предлагают ничего другого относительно гибкости. Обычно вам нужно перейти на следующий уровень и начать вручную делать вещи. Сохраненная процедура будет одним из способов сохранить SqlDataAdapter. В противном случае вам нужно перейти к другому инструменту доступа к данным, поскольку библиотеки данных .Net имеют ограничения, поскольку они проектируются как простые. Если ваша модель не работает с их видением, вам нужно перевернуть свой собственный код на определенном уровне/уровне.

Ответ 9

Проблема для меня заключалась в том, где был помещен код.

Добавьте код в обработчик события RowUpdating, примерно так:

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

Ответ 10

Другой способ использования только командного объекта.

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)

Ответ 11

На самом деле, это еще проще, нет необходимости в каких-либо пользовательских командах. Когда вы создаете tableadapter в дизайнере набора данных, укажите "обновить таблицу данных" в "Дополнительные параметры" мастера. При этом после того, как вы выпустите файл dataadapter.update mydataset, вы найдете столбец идентификации в datatable, заполненном новым значением из базы данных.

Ответ 12

Мое решение:

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();

Ответ 13

Вы изучали использование LINQ вместо этого? Я понимаю, что это не касается вашего фактического вопроса, но если вы используете .NET 3.5, вам действительно стоит попробовать LINQ. На самом деле, с появлением Code First EntityFramework, я думаю, вы могли бы легко выбрать LINQ to SQL или EF как относительно легкие альтернативы перелистыванию собственного DAL.