Torna al Thread
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=***\\SQLEXPRESS;Initial Catalog=Gestionario;Integrated Security=True";
SqlCommand com = new SqlCommand();
com.Connection = conn;
com.CommandText = "INSERT INTO Commesse (Tipodoc, Numerodoc, Datadoc, Dataconseg, Cod_Arca) SELECT tipodoc, numerodoc, datadoc, dataconseg, codicecf FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=percorso', [Foglio1$]) WHERE NOT EXISTS (SELECT * FROM Commesse WHERE Tipodoc = tipodoc AND Numerodoc = LTRIM(numerodoc) AND Datadoc = datadoc)";
conn.Open();
com.ExecuteNonQuery();
conn.Close();
SqlCommand com0 = new SqlCommand();
com0.Connection = conn;
com0.CommandText = "INSERT INTO Commesse (Numerodoc, Cod_Arca) SELECT Colonna_TS, Cod_ARCA FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=percorso', [Foglio1$]) WHERE NOT EXISTS (SELECT * FROM Commesse WHERE Numerodoc = Colonna_TS)";
conn.Open();
com0.ExecuteNonQuery();
conn.Close();
SqlCommand com2 = new SqlCommand();
com2.Connection = conn;
com2.CommandText = "Update Commesse Set Numerodoc = LTRIM(Numerodoc)";
conn.Open();
com2.ExecuteNonQuery();
conn.Close();
SqlCommand com3 = new SqlCommand();
com3.Connection = conn;
com3.CommandText = "Update Commesse Set Cod_Arca = Right(Cod_Arca, 5) WHERE Cod_Arca Like ('C%')";
conn.Open();
com3.ExecuteNonQuery();
conn.Close();
SqlCommand com4 = new SqlCommand();
com4.Connection = conn;
com4.CommandText = "Update Commesse Set Tipodoc = 'SE' Where Tipodoc is null";
conn.Open();
com4.ExecuteNonQuery();
conn.Close();
SqlCommand com5 = new SqlCommand();
com5.Connection = conn;
com5.CommandText = "Update Commesse Set ID_Commessa = '2009' + '000' + Numerodoc Where Datadoc Like ('%2009%') and Numerodoc Like ('_')";
conn.Open();
com5.ExecuteNonQuery();
conn.Close();
SqlCommand com6 = new SqlCommand();
com6.Connection = conn;
com6.CommandText = "Update Commesse Set ID_Commessa = '2009' + '00' + Numerodoc Where Datadoc Like ('%2009%') and Numerodoc Like ('__')";
conn.Open();
com6.ExecuteNonQuery();
conn.Close();
SqlCommand com7 = new SqlCommand();
com7.Connection = conn;
com7.CommandText = "Update Commesse Set ID_Commessa = '2009' + '0' + Numerodoc Where Datadoc Like ('%2009%') and Numerodoc Like ('___')";
conn.Open();
com7.ExecuteNonQuery();
conn.Close();
SqlCommand com8 = new SqlCommand();
com8.Connection = conn;
com8.CommandText = "Update Commesse Set ID_Commessa = 'SER0' + Numerodoc Where Numerodoc Like ('____') and ID_Commessa is null";
conn.Open();
com8.ExecuteNonQuery();
conn.Close();
SqlCommand com9 = new SqlCommand();
com9.Connection = conn;
com9.CommandText = "Update Commesse Set ID_Commessa = 'SER00' + Numerodoc Where Numerodoc Like ('___') and ID_Commessa is null";
conn.Open();
com9.ExecuteNonQuery();
conn.Close();
SqlCommand com10 = new SqlCommand();
com10.Connection = conn;
com10.CommandText = "Update Commesse Set ID_Commessa = 'SER000' + Numerodoc Where Numerodoc Like ('__') and ID_Commessa is null";
conn.Open();
com10.ExecuteNonQuery();
conn.Close();
SqlCommand com1 = new SqlCommand();
com1.Connection = conn;
com1.CommandText = "DELETE FROM Commesse WHERE Numerodoc IS NULL AND Datadoc IS NULL AND Dataconseg IS NULL AND Cod_Arca IS NULL AND ID_Commessa IS NULL AND Descrizione IS NULL";
conn.Open();
com1.ExecuteNonQuery();
conn.Close();