Sql server: запросы с несколькими вставками обновления
Здравствуйте, я разрабатываю приложение c#, которое работает на Sql Server 2014 с C#, у меня есть ошибка в коде, который см. ниже, затем этот код импортирует список txt внутри базы данных, вставка работает правильно до конца вставок в таблицу ImportazioneElettroveneta, в конце операций вставки в эту таблицу я генерирую исключение ниже, не могли бы вы объяснить мне, в чем проблема?
Exception(error):<br /> System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'ImportazioneElettroveneta'.<br /> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)<br /> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
Что я уже пробовал:
Мой исходный код:
public Boolean ImportaListinoElettroveneta(String PercorsoFile) { GC.Collect(); GC.WaitForPendingFinalizers(); Boolean ret = true; SqlConnection conn = Database.apriconnessione(); SqlCommand command = conn.CreateCommand(); command.CommandTimeout = 0; SqlTransaction transaction = conn.BeginTransaction("TransactionImportElettroveneta"); try { String EliminaTabellaTemporanea = "drop table ImportazioneElettroveneta"; command = new SqlCommand(EliminaTabellaTemporanea, conn, transaction); command.CommandTimeout = 0; command.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine("Errore la tabella temporanea non esiste: " + ex); } String Query_Creazione_Tabella_Temporanea = "create table ImportazioneElettroveneta( CodiceNumericoEV varchar(5000),DescrizioneArticolo varchar(5000),CodiceArticoloEV varchar(5000),MarcaEV varchar(5000),UM varchar(5000),PrezzoListino money,Sconto1 money,Sconto2 money,Sconto3 money,Sconto4 money,PrezzoNetto money,CodiceBarreMetel bigint,IVA varchar(5000),MarcaMetel varchar(5000),ArticoloMetel varchar(5000),DescrizioneMarca varchar(5000)) "; command = new SqlCommand(Query_Creazione_Tabella_Temporanea, conn, transaction); command.CommandTimeout = 0; command.ExecuteNonQuery(); command.CommandText = "INSERT Into ImportazioneElettroveneta(CodiceNumericoEV,DescrizioneArticolo,CodiceArticoloEV,MarcaEV,UM,PrezzoListino,Sconto1,Sconto2,Sconto3,Sconto4,PrezzoNetto,CodiceBarreMetel,IVA,MarcaMetel,ArticoloMetel,DescrizioneMarca) Values(@CodiceNumericoEV,@DescrizioneArticolo,@CodiceArticoloEV,@MarcaEV,@UM,@PrezzoListino,@Sconto1,@Sconto2,@Sconto3,@Sconto4,@PrezzoNetto,@CodiceBarreMetel,@IVA,@MarcaMetel,@ArticoloMetel,@DescrizioneMarca)"; try { using (FileStream fs = File.Open(PercorsoFile, FileMode.Open, FileAccess.Read, FileShare.Read)) { using (BufferedStream bs = new BufferedStream(fs, System.Text.ASCIIEncoding.Unicode.GetByteCount("271"))) using (StreamReader sr = new StreamReader(bs)) { string s; while ((s = sr.ReadLine()) != null) { command.Parameters.Clear(); try { command.Parameters.AddWithValue("@CodiceNumericoEV", s.Substring(startIndex: 1, length: 13)); command.Parameters.AddWithValue("@DescrizioneArticolo", s.Substring(startIndex: 13, length: 45)); command.Parameters.AddWithValue("@CodiceArticoloEV", s.Substring(startIndex: 58, length: 25)); command.Parameters.AddWithValue("@MarcaEV", s.Substring(startIndex: 83, length: 6)); command.Parameters.AddWithValue("@UM", s.Substring(startIndex: 89, length: 2)); command.Parameters.AddWithValue("@PrezzoListino", decimal.Parse(s.Substring(startIndex: 106, length: 15))); command.Parameters.AddWithValue("@Sconto1", decimal.Parse(s.Substring(startIndex: 122, length: 5))); command.Parameters.AddWithValue("@Sconto2", decimal.Parse(s.Substring(startIndex: 127, length: 5))); command.Parameters.AddWithValue("@Sconto3", decimal.Parse(s.Substring(startIndex: 132, length: 5))); command.Parameters.AddWithValue("@Sconto4", decimal.Parse(s.Substring(startIndex: 137, length: 5))); command.Parameters.AddWithValue("@PrezzoNetto", decimal.Parse(s.Substring(startIndex: 142, length: 15))); command.Parameters.AddWithValue("@CodiceBarreMetel", s.Substring(startIndex: 156, length: 13)); command.Parameters.AddWithValue("@IVA", s.Substring(startIndex: 169, length: 2)); command.Parameters.AddWithValue("@MarcaMetel", s.Substring(startIndex: 171, length: 3)); command.Parameters.AddWithValue("@ArticoloMetel", s.Substring(startIndex: 174, length: 16)); command.Parameters.AddWithValue("@DescrizioneMarca", s.Substring(startIndex: 190, length: 25)); command.CommandTimeout = 0; command.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine("Errore riga: " + ex); } } } } command.Parameters.Clear(); String QueryAggiornaNonDisponbili = "Update Articolo set Stato='Nondisponibile' where Importato='ELETTROVENETA' "; command = new SqlCommand(QueryAggiornaNonDisponbili, conn, transaction); command.CommandTimeout = 0; command.ExecuteNonQuery(); command.Parameters.Clear(); String QueryInserimentoNuoviArticoli = "Insert into Articolo(CodArt,Descrizione,CodMarca,CodEAN,Prezzo,PrezzoListino,UM,Fornitore,Importato) SELECT ArticoloMetel as CodArt,DescrizioneArticolo as Descrizione,MarcaMetel as CodMarca,CodiceBarreMetel as CodEAN,PrezzoNetto,PrezzoListino,UM,MarcaMetel as Fornitore,'ELETTROVENETA' as Importato FROM ImportazioneElettroveneta where ArticoloMetel not in ( select CodArt from Articolo where Importato = 'ELETTROVENETA' ) and MarcaMetel not in ( select CodMarca from Articolo where Importato = 'ELETTROVENETA' ) "; command = new SqlCommand(QueryInserimentoNuoviArticoli, conn, transaction); command.CommandTimeout = 0; command.ExecuteNonQuery(); command.Parameters.Clear(); String QueryAggiornamentoArticoliPresenti = " Update Articolo set Stato = 'Disponibile', Descrizione = i.Descrizione, CodEAN = i.CodEAN, Prezzo = i.PrezzoNetto, PrezzoListino = i.PrezzoListino, UM = i.UM, DataAggiornamento = getdate() from( SELECT ArticoloMetel as CodArt, DescrizioneArticolo as Descrizione, MarcaMetel as CodMarca, CodiceBarreMetel as CodEAN, PrezzoNetto, PrezzoListino, UM, MarcaMetel as Fornitore, 'ELETTROVENETA' as Importato FROM ImportazioneElettroveneta where ArticoloMetel in (select CodArt from Articolo where Importato = 'ELETTROVENETA') and MarcaMetel in (select CodMarca from Articolo where Importato = 'ELETTROVENETA'))i where Articolo.CodArt = i.CodArt and i.CodMarca = Articolo.CodMarca and Articolo.Importato = 'ELETTROVENETA' and Articolo.Fornitore = i.Fornitore"; command = new SqlCommand(QueryAggiornamentoArticoliPresenti, conn, transaction); command.CommandTimeout = 0; command.ExecuteNonQuery(); try { String EliminaTabellaTemporanea = "drop table ImportazioneElettroveneta"; command = new SqlCommand(EliminaTabellaTemporanea, conn, transaction); command.CommandTimeout = 0; command.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine("Errore la tabella temporanea non esiste: " + ex); } transaction.Commit(); } catch (Exception ex) { ret = false; transaction.Rollback(); Console.WriteLine("Errore Importazione Listino Elettroveneta: " + ex); Managementerror.SendError("Errore: " + ex); } conn.Close(); return ret; }