Корректная обработка ошибки SQL в С#

Евгенич

Добрый день! Никак не могу заставить процедуру работать в нужном режиме:Есть csv-файл. Есть sql-процедура, выполняющая insert в таблицу. Необходимо в цикле по всем строкам файла присваивать параметрам процедуры вставки в таблицу данные из считанной строки (чтение строки вне вопроса) и запускать процедуру.Проблема в том, что если в sql - процедуре возникает ошибка "Cannot insert dublicate value..." - (а она бывает периодически, т.к. файл создается по принципу append и мне нужно загрузить из него только новые строки) то валится код C# в блоке try.Думал сделать try catch в самой процедуре SQL. Но теперь ругачка "Uncommitable transaction is detected at the end of the batch"
//ищем csv файлы, начинающиеся на Z
string[] files = Directory.GetFiles(Common.import_folder, "Z*.csv");

foreach (string file in files)
{

 SqlTransaction trans = Common.conn.BeginTransaction();

 try
 {
 
 
 string[] order = new string[0];
 string[] orders = File.ReadAllLines(file, Encoding.GetEncoding(1251));
 for (int i = 0; i < orders.Length; i++)
 {


 if (!String.IsNullOrEmpty(orders[i]))
 {
 
 order = orders[i].Split('^'); 

 using (SqlCommand cm = new SqlCommand("spGisZhkh_OrderModify", Common.conn, trans))
 {
 cm.CommandType = CommandType.StoredProcedure;

 cm.Parameters.Add("@Case", SqlDbType.VarChar).Value = 0;
 cm.Parameters.Add("@SupplierID", SqlDbType.VarChar).Value = order[0];
 cm.Parameters.Add("@SupplierName", SqlDbType.VarChar).Value = order[1];
 

 cm.ExecuteNonQuery(); 


 }

 }

 }

 
 trans.Commit();
 File.Delete(file);
 }
 catch (Exception ex)
 { 
 trans.Rollback();
 }
Это процедура вставки в таблицу
create proc spGisZhkh_OrderModify
	 @case ******* --0-импорт,1-insert,2-modify,3-delete
	,@OrderID numeric(32,0) = null	
	,@SupplierID varchar(25) = null	
	…

as

BEGIN

--set XACT_ABORT ON;
set nocount on;

set @RowCount=0

if @case =0 --импорт
		
		
begin try 
		--формируем запись-уведомление
		insert tGisZhkh_Order	(
			 SupplierID 
			,SupplierName 
		…
		select 
							 
			@SupplierID 
			,@SupplierName 
		…
end try
begin catch

end catch
14 ответов

Евгенич

Евгенич,Убери хранимку. Она у тебя ничего не делает. Или учеба?


Евгенич

Petro123, хранимка используется во многих местах, хотелось иметь стандартный код по модификации этой таблицы.


Евгенич

Евгенич,Нельзя пустые try делать. На ветку субд за подробностями.


Евгенич

Евгенич,
//ищем csv файлы, начинающиеся на Z
string[] files = Directory.GetFiles(Common.import_folder, "Z*.csv");

foreach (string file in files)
{

 //SqlTransaction trans = Common.conn.BeginTransaction();

 try
 {
 
 
 string[] order = new string[0];
 string[] orders = File.ReadAllLines(file, Encoding.GetEncoding(1251));
 for (int i = 0; i < orders.Length; i++)
 {


 if (!String.IsNullOrEmpty(orders[i]))
 {
 
 order = orders[i].Split('^'); 

 using (SqlCommand cm = new SqlCommand("spGisZhkh_OrderModify", Common.conn)) //, trans))
 {
 cm.CommandType = CommandType.StoredProcedure;

 cm.Parameters.Add("@Case", SqlDbType.VarChar).Value = 0;
 cm.Parameters.Add("@SupplierID", SqlDbType.VarChar).Value = order[0];
 cm.Parameters.Add("@SupplierName", SqlDbType.VarChar).Value = order[1];
 

 cm.ExecuteNonQuery(); 


 }

 }

 }

 
 //trans.Commit();
 File.Delete(file);
 }
 catch (Exception ex)
 { 
 //trans.Rollback();
 }
create proc spGisZhkh_OrderModify
	 @case ******* --0-импорт,1-insert,2-modify,3-delete
	,@OrderID numeric(32,0) = null	
	,@SupplierID varchar(25) = null	
	…

as

BEGIN

--set XACT_ABORT ON;
set nocount on;

set @RowCount=0

if @case =0 --импорт
		
		
begin try 
		--формируем запись-уведомление
		insert tGisZhkh_Order	(
			 SupplierID 
			,SupplierName 
		…
		select 
							 
			@SupplierID 
			,@SupplierName
 where Not Exists ...-- инсерт - если такой записи не существует 
		…
end try
begin catch

end catch


Евгенич

Добрый день! Никак не могу
Имхую, что это - плохо catch (Exception ex) { trans.Rollback(); }как и само открытие транзакции для одной процедуры.Процедура сама должна открывать и обрабатывать транзакции.


Евгенич

Row by row is slow by slow.Здесь нужно:1. CSV залить в DataTable.2. На одном коннекте создать временную таблицу, залить туда данные из DataTable c помощью SqlBulkLoad, одним инсертом перелить данные из временной таблицы в рабочую. И никаких транзакций не потребуется.


Евгенич

Евгенич,Конкретно для вашего примера - транзакция мне кажется лишней.Ну обвалится инсерт - ну бог с ним. Мне, например, совершенно непонятно, что откатывать то взад если операция инсерт не прошла.Файл csv не удалится. Значит следующим проходом по csv файлам он пройдет.Если конечно запись с таким ключом не существует.


Евгенич

Всем спасибо! Убрал транзакцию, поставил where not exists условие в хранимку.


Евгенич

Я бы попробовал вот это. Хранимки с table-value параметрами из ADO.NET можно вызывать.


Евгенич

Я бы попробовал вот это. Хранимки с table-value параметрами из ADO.NET можно вызывать.
Table-valued параметр по сути - табличная переменная, со всеми её нюансами в плане оценки оптимизатором cardinality, row estimations, итд. Плюс такие нюансы, как невозможность проальтерить UDTT - только drop/create. В общем, я бы сто раз подумал, прежде чем связываться с этим хозяйством. В то время как обычные временные таблицы этих недостатков лишены.


Евгенич

Евгенич,Забыл написать, хорошая практика не удалять(File.Delete(file); файл после успешной передачи данны, а перенести в какую-нить папку, например, Archive. Еще, для полноты картины, завести папку Reject - и туда собирать не отработанные файлы. Это как бы дополнительное логирование в системе.


Евгенич

Table-valued параметр по сути - табличная переменная, со всеми её нюансами в плане оценки оптимизатором cardinality, row estimations, итд. Плюс такие нюансы, как невозможность проальтерить UDTT - только drop/create. В общем, я бы сто раз подумал, прежде чем связываться с этим хозяйством. В то время как обычные временные таблицы этих недостатков лишены.
Да, это понятно, я не бог весть какой DBA, точнее совсем не DBA. Я просто к тому, что TVP позволяют отправить кучу данных на сервер одним запросом вместо того чтобы дергать его отдельно для каждой записи. Мы как-то, в допотопные еще времена, когда TVP не было, XML параметры для этого использовали.


Евгенич

Да, это понятно, я не бог весть какой DBA, точнее совсем не DBA. Я просто к тому, что TVP позволяют отправить кучу данных на сервер одним запросом вместо того чтобы дергать его отдельно для каждой записи. Мы как-то, в допотопные еще времена, когда TVP не было, XML параметры для этого использовали.
Мы тоже использовали XML - на больших данных это сильно просаживает производительность. А как отправить кучу данных разом на сервер для последующей обработки, я писал выше: 21167520 - временная таблица +SqlBulkCopy. И по производительности это самый лучший вариант, т.к. SqlBulkCopy - это managed-обертка над bulk insert, делающая вставку данных с максимальной скоростью и минимальным журналированием. Плюс возможность потом обвесить эту временную таблицу нужными индексами (у табличных переменных, как и у UDTT, индексы возможны только как реализация primary key/unique constraint), плюс нормальное вычисление оптимизатором статистики по этой таблице, итд итп.


Евгенич

А как отправить кучу данных разом на сервер для последующей обработки, я писал выше: 21167520 - временная таблица +SqlBulkCopy. И по производительности это самый лучший вариант, т.к. SqlBulkCopy - это managed-обертка над bulk insert, делающая вставку данных с максимальной скоростью и минимальным журналированием. Плюс возможность потом обвесить эту временную таблицу нужными индексами (у табличных переменных, как и у UDTT, индексы возможны только как реализация primary key/unique constraint), плюс нормальное вычисление оптимизатором статистики по этой таблице, итд итп.
Да, посмотрел в доки, прикольно, буду теперь знать. Я просто раньше что-то думал, что BULK INSERT может только с локального (для сервера) файла загружать. Оказывается, по крайней мере с .NET клиента можно в него запульнуть сразу массив данных с клиента.