Accesso al database Oracle tramite c#

Piccolo esempio che illustra un accesso al database Oracle tramite statement e una restituzione del numero di record presenti sulla tabella Authors in c#.
E' stato utilizzata la libreria OracleClient da referenziare nel progetto .Net in Visual Studio

//using System.Data.OracleClient;

private static void testConnectionDB()
{
OracleConnection conn = new OracleConnection(oraConn);
int intRecord = 0;
string str1="Florence";
string str2="Italy";
try
{
 conn.Open();
 OracleCommand cmd = conn.CreateCommand();

 cmd.CommandType = CommandType.Text;
 cmd.CommandText = "select count(*) as numAuthors from Authors where field1 =:strField1 and field2 =:strField2";

 cmd.Parameters.AddWithValue("strField1", str1);
 cmd.Parameters.AddWithValue("strField2", str2);

 intRecord = Convert.ToInt32(cmd.ExecuteScalar()); 
  
}
catch (Exception ex)
{
 log.Error(ex.Message);
}
finally
{
   cmd.Dispose();

   if (conn.State != ConnectionState.Closed)
   {
    conn.Close();
   }
}

Cercare una parola o espressione all'interno di un'altra espressione in SQL

In Microsoft SQL Server è possibile trovare una parola o un'espressione all'interno di un'altra espressione;
CHARINDEX(...,...,...) funzione utile ad esempio per eseguire aggiornamenti (update) o farsi restituire dei dati (select) a partire da alcuni caratteri non conoscendo l'intera parola.

Ad esempio, immaginando una tabella autori con alcuni campi (nome, cognome, città di residenza...) si possono modificare i nomi di alcuni elementi partendo da alcune parole.

-- Cambiare il nome in Alberto a tutti gli elementi con cognome che comprende
-- la parola Rossi e che non abbiano la residenza in una città con all'interno la parola Milano, esempi:

-- 'Mario Rossini', diventa, Alberto Rossini
-- 'Luca Marossi', diventa, Luca Marossi
UPDATE tab_autori SET nome='Alberto' where CHARINDEX('Rossi',cognome,1)>0

-- 'Mario Di Paoli' che vive a 'Romano d'Ezzelino' avrà il nome Marco
UPDATE tab_autori SET nome='Marco' where CHARINDEX('Paoli',cognome,1)>0 
 and CHARINDEX('Roma',citta_residenza,1)>0

MySQL e il dump di dati e struttura

E' possibile eseguire un export o dump dei dati e di una struttura di un database MySQL eseguendo una semplice riga di comando da diversi sistemi operativi.
Eccone un esempio:

Su sistema Windows:
andare nella directory di mysql \bin e accertarsi che ci sia il comando mysqldump.exe; a questo punto digitare (la password accanto a -p è attaccata senza spazi):

mysqldump -u root -paaaa dbtest > dbtest-$(date +%d-%m-%Y-%H.%M.%S).sql 

Su sistema Linux:
se mysql è staato installato correttamente digirare sul terminale:

sudo mysqldump -u root -paaaa dbtest > dbtest-$(date +%d-%m-%Y-%H.%M.%S).sql 

Risultato del nome del file:

dbtest-07-09-2015-11.15.15.sql

Cambio password di un utente con sql in MySQL

Con la riga di comando di mysql o da un client browser che punti su di un database MySQL è possibile cambiare la password ad un utente.
Ovviamente l'utenza abilitata alla shell mysql deve avere privilegi di amministratore (tipicamente l'utenza root).
Ad esempio sulla shell mysql, il comando per cambiare la password in 'mypassword' all utente 'root' del host 'localhost' è la seguente:

UPDATE mysql.user SET Password=PASSWORD('mypassword') WHERE User='root' AND Host='localhost';

Power Pivot di Microsoft come strumento di analisi

Uno strumento molto buono che ho utilizzato in fase di analisi e post-rilascio di un software gestionale, è senza dubbio Power Pivot di Microsoft.
Ha qualche limitazione ovviamente ma è buono per interfacciarsi con un solo click con SqlServer dalla versione 2008 oppure MS Access e ricavare qualche statistica.
Lo considero un ottimo strumento di base per creare tabelle, grafici a partire da "script sql" creati manualmente o da viste presenti su database e avere dati aggiornati senza dover ricorrere a tools particolari.
È installabile come plugin di Microsoft Excel dalla versione 2010, bisogna solamente scaricare la versione più idonea (32/64 bit, inglese/italiano, Excel 2010/2013...), chiudere Excel, installare il software e far ripartire Excel.
Nelle immagini successive è rappresentato il plugin correttamente installato.

Esempio di una select case in sql

Anche in sql è possibile utilizzare la clausola 'select case' come in altri linguaggi di programmazione.
Molto utile in fase di inserimento di dati in tabelle da altre tabelle o anche semplicemente per output di query.
Qui di seguito un esempio (test su MYSQL e MS SqlServer) che parte da una interrogazione sulla tabella 'car' e quando vengono trovati i codici 'A1' e 'A2' sono sostituiti con delle descrizioni.

Tabella car:
ID, CODE
-- ----
1, 'A1'
2, 'A2'
3, 'A3'

Sql:
select
  case code
    when 'A1' then 'FERRARI'
    when 'A2' then 'LAMBORGHINI'
    else code
  end
from car

Output:
'FERRARI'
'LAMBORGHINI'
'A3'

Esempio di più operazioni in una store procedure

Il codice seguente è stato testato su di un database Oracle e mostra come si possono eseguire più operazioni in una store procedure puntando su diverse tabelle.
Ci sono esempi di select, update, delete, condizioni e loop.
Si possono portare le seguenti istruzioni anche su di un altro database modificando opportunatamente la sintassi.
(Non sempre vengono utilizzate le stesse istruzioni su db diversi).

CREATE OR REPLACE PROCEDURE TSP.procedure_example(
 typeMarket  IN varchar2,
 inDayMarket   IN varchar2
)

is

selectCursor    sys_refcursor;
rowToUpdate     TSP.tbl_market_1%ROWTYPE;

begin

-- example of condition and select on first table

 IF procedure_example.typeMarket='CONSUMER' THEN
 open selectCursor for
 SELECT TSP.tbl_market_1.*
 FROM TSP.tbl_market_1
 WHERE ((tbl_market_1.field_example_1 = 1);

-- loop example: update for all records of 'select' on second table

LOOP
 fetch selectCursor into rowToUpdate;
 exit when selectCursor%NOTFOUND;

 update  TSP.tbl_market_2 set
 field_example_3=10,
 where   TSP.tbl_market_2.id = rowToUpdate.id;

END LOOP;

close selectCursor;

END IF;

-- example update on first table

update   TSP.tbl_market_1 set field_example_2 = 0
where   day_market = procedure_example.inDayMarket
and     market_ref = procedure_example.typeMarket;

-- example delete on second table

delete   from TSP.tbl_market_2
where   day_market = procedure_example.inDayMarket;

end;

Esempio di un trigger su tabella

L'esempio, creato e testato su db Oracle, permette di creare un trigger su di una tabella di un database.
In questo caso ogni volta che viene inserito un nuovo record, con la prima istruzione verranno copiati i dati dalla tabella "mytable" alla "mytable2", mentre con la seconda
istruzione saranno eliminati alcuni record presenti sulla prima tabella.
Eventualmente si possono inserire altre istruzioni all'interno del BEGIN/END

CREATE OR REPLACE TRIGGER myschema.MYTRIGGER
AFTER INSERT
ON myschema.mytable 
REFERENCING NEW AS New OLD AS Old
BEGIN

 insert into myschema.mytable2 (
     id,day_market,type_market) 
     select 
        id,day_market,type_market   
     from myschema.mytable where 
  to_date(myschema.mytable.day_market, 
  'yyyy/mm/dd')<sysdate-2;
     
 delete myschema.mytable where 
 to_date(myschema.mytable.day_market, 
 'yyyy/mm/dd')<sysdate-2;  

 -- ... other operations  
 
END ;

Diverse concatenazioni di stringhe

Spesso capita di concatenare delle stringhe in un caricamento dati, ma non sempre è buona la stessa sintassi nei diversi database.
Riporto alcuni esempi che ho utilizzato su diversi db (omettendo la versione precisa del database).

MySQL

SELECT CONCAT(first_name, ' ', last_name, ';') AS full_name
 FROM tbl_employee WHERE last_name = 'Brown'

SQL Server

SELECT first_name + ' ' + last_name + ';'
 FROM tbl_employee WHERE last_name = 'Brown'

Oracle

SELECT first_name || ' ' || last_name || chr(59)
 FROM tbl_employee WHERE last_name = 'Brown' 

Immaginando una tabella "tbl_employee" e due campi "first_name" e "last_name" l'output sarà:

John Brown;
Peter Brown;

Lettura dati con store-procedure

Con questo piccolo esempio di store-procedure vorrei che dalla tabella "tbl_course" venissero selezionati tutti i nomi passati in "inName" e fosse aggiornato il giorno d'esame "dayExam" con il valore 1.
In effetti potevo optare per una semplice "update...set" ma la reale procedura che ho utilizzato aveva al suo interno più parametri in input e la query era pìù complessa in ambiente Oracle.

CREATE OR REPLACE PROCEDURE OWN_NAME.proc_name(
    inName IN varchar2) 
is
selectCursor    sys_refcursor;
rowToUpdate     own_name.tbl_course%ROWTYPE;
begin
 open selectCursor for
  SELECT own_name.tbl_course.*
  FROM own_name.tbl_course
  WHERE tbl_course.inName = proc_name.inName;
 LOOP
  fetch selectCursor into rowToUpdate;
  exit when selectCursor%NOTFOUND;
   update  own_name.tbl_course set dayExam = 1
   where   own_name.tbl_course.id = rowToUpdate.id;
 END LOOP;
 close selectCursor;
end;

Query varie in sql: concatenazione, group by e inner join

Riporto alcune query che ho utilizzato in vari progetti collegandomi ad un database Oracle.

1 - concatenazione di campi sql

select ID_TIPO_PRATICA||'@'||ID_SERVIZIO||','||TIPO_DOC,DESCRIZIONE 
from TBL_EXAMPLE order by 2

2 - Numero di studenti maschi e femmine

SELECT Sesso, COUNT(*)
FROM Studente
GROUP BY Sesso

3 - Città in cui abitano almeno 3 studenti e il numero di studenti relativo

SELECT Città, COUNT(*)
FROM Studente
GROUP BY Città
HAVING COUNT(*)>=3

4 - Se prendo i campi da due tabelle e ho l'id uguale allora:

SELECT     dbo.Customers.CompanyName, dbo.Employees.FirstName
FROM         dbo.Customers INNER JOIN
dbo.Employees ON dbo.Customers.id = dbo.Employees.id