@Эд Гинесс,
(Я знаю, что это старая тема, но для тех, кому это может понадобиться в будущем)
;tldr ответ: Не обошлось и без некоторых изменений кода (в основном неразрывных и разумных изменений ИМХО).
Очень странно, что Microsoft не раскрывает SPID после открытия объекта подключения. Я могу видеть, например, имя версии SQL и другие специфические свойства SQL Server, которые я могу получить из объекта SqlConnection, но не SPID.
Подробности:
Каждому SQLConnection назначается один SPID. Это достигается с помощью команды T-SQL @@SPID, но выполняется на стороне SQL Server. Хитрость заключается в том, чтобы передать это вместе с основной работой, выполняемой на стороне SQL Server, и прочитать ее на стороне C#.
Четыре возможных сценария, для которых необходим SPID.
- Вы выполняете хранимую процедуру, которая возвращает набор результатов (без spid)
- Вы выполняете INS/UPD/DEL хранимую процедуру.
- Вы запускаете подготовленные SQL stms «на лету» (встроенные) из ADO.Net (yikes !!) для выполнения операций CRUD (т. Е. Сценариев 1 и 2 без хранимых процедур)
- Вы вставляете данные непосредственно в таблицу, используя SqlBulkCopy
<сильный>1. Хранимая процедура, возвращающая набор результатов
Допустим, у вас есть SP (USP_GetDBDetails), который возвращает строки из главной базы данных. Нам нужно добавить строку кода в Existing SQL Stmt, чтобы вернуть SPID и получить его на стороне C#, используя тип Paramter для получения ReturnValue. Также можно прочитать основные наборы результатов.
Хранимые процедуры — прекрасная штука. Они могут одновременно возвращать возвращаемое значение И набор результатов И выходной параметр. В этом случае на стороне SP нам нужно только добавить дополнительное возвращаемое значение в конце SP, выполняемого ADO. .Net с использованием SqlConnection. Мы делаем это, как показано в коде T-SQL ниже:
CREATE Procedure [dbo].[USP_GetDBDetails]
AS
BEGIN
SELECT
database_id,
name,
create_date
FROM [sys].[databases]
Return @@SPID -- Line of Code that needs to be added to return the SPID
END
Теперь, чтобы захватить SPID на стороне C# (при необходимости измените строку подключения):
using (SqlConnection conn = new SqlConnection(@"Data Source=(local);Initial Catalog=master;Persist Security Info=True;Integrated Security =SSPI;"))
{
string strSql = "USP_GetDBDetails";
SqlCommand sqlcomm = new SqlCommand();
sqlcomm.CommandText = strSql;
sqlcomm.CommandType = CommandType.StoredProcedure;
sqlcomm.Connection = conn;
SqlParameter returnValueParam = sqlcomm.Parameters.Add("@ReturnValue", SqlDbType.Int);
returnValueParam.Direction = ParameterDirection.ReturnValue;
conn.Open();
**// Reader Section**
SqlDataReader rdr = sqlcomm.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(rdr); // Get the Reultset into a DataTable so we can use it !
rdr.Close(); // Important to close the reader object before reading the return value.
// Lets get the return value which in this case will be the SPID for this connection.
string spid_str = returnValueParam.Value.ToString();
int spid = (int)sqlcomm.Parameters["@ReturnValue"].Value; // Another Way to get the return value.
Console.WriteLine("SPID For this Conn = {0} ", spid);
// To use the Reult Sets that was returned by the SP:
foreach (DataRow dr in dt.Rows)
{
string dbName = dr["Name"].ToString();
// Code to use the Other Columns goes here
}
}
Вывод :
SPID For this Conn = 66
<сильный>2. Если объект Connection выполняет SP, который обрабатывает INS/UPS/DEL
Добавьте RETURN @@SPID в конце SP, отвечающего за INS/UPD/DEL, так же, как мы делали это для сценария 1.
А на стороне C#, чтобы получить SPID... все остается таким же, как и в Сценарии 1, за исключением раздела для чтения. Удалите 4 строки в разделе Reader и замените этой строкой ниже. (и, очевидно, цикл foreach для повторения DataTable dt не понадобится)
sqlcomm.ExecuteNonQuery();
<сильный>3. INS/UPD/DEL с использованием встроенного SQL
Переместите эти stmts в хранимую процедуру и выполните шаги для сценария 2. Могут быть способы выполнить некоторую акробатику T-SQL, чтобы внедрить @@SPID и вернуть его, возможно, используя параметр MultipleActiveResultSets, но не очень элегантный IMO.
<сильный>4. SqlBulkCopy.
Это потребует запроса таблицы, чтобы получить spid. Поскольку нет хранимой процедуры для возврата SPID от SqlServer, который можно захватить.
Нам нужно добавить дополнительный столбец типа INT для хранения значения SPID следующим образом:
ALTER TABLE dbo.TBL_NAME ADD
SPID int NOT NULL Default( @@SPID )
GO
При этом SQL Server автоматически вставит значение SPID во вновь добавленный столбец. На стороне C# ADO, обрабатывающей BulkCopy, не потребуется никаких изменений кода. Типичный код ADO Bulkcopy выглядит так, как показано ниже, и он должен продолжать работать после описанного выше шага ALTER TABLE.
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
DataTable dt = new DataTable();
dt.Columns.Add("Col1");
dt.Columns.Add("Col2");
string[] row = { "Col1Value", "Col2Value" };
dt.Rows.Add(row);
bulkCopy.DestinationTableName = "TBL_NAME_GOES_HERE"; //TBL_NAME
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(dt);
}
catch (SqlException ex)
{
// Handle Exception
}
}
}
Итак, чтобы проверить вывод, выберите отдельный SPID из dbo.TBL_NAME.
Вот и все . Надеюсь, это поможет кому-то.
person
objectNotFound
schedule
02.07.2016
