Visualizza il feed RSS

Appunti a perdere

Gestire i parametri su MySql con Visual Studio Express

Valuta questo inserimento
di pubblicato il 20-02-2011 alle 18:17 (4433 Visite)
Nel presente esempio troviamo la gestione di:
[LIST][*]Parametri per il comando sql insert[*]Recupero dell’ultimo id assegnato automaticamente dall’inserimento[*]Utilizzo di una stored procedure con recupero del valore di ritorno della funzione[/LIST] [B][U][SIZE=4]Stored Procedure:[/SIZE][/U][/B]
CREATE DEFINER=`root`@`%` FUNCTION `Incrementa`(valore int, incremento int) RETURNS int(11)
BEGIN
declare retValue INTEGER DEFAULT 0;
set retValue = valore + incremento;
RETURN retValue;
END
[FONT=Calibri][SIZE=4][B][U]Esempio VB.NET [/U][/B][/SIZE][/FONT]
[FONT=Calibri] [COLOR=#0000ff]Private[/COLOR] cntxt [COLOR=#0000ff]As[/COLOR] [COLOR=#6f002f]String[/COLOR] = "User ID=utente;Password=password;Host=localhost;Port=3306;Database=dbprova;Protocol=TCP;Compress=false;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;"
[COLOR=#0000ff]Private[/COLOR] [COLOR=#0000ff]Sub[/COLOR] eseguiButton_Click([COLOR=#0000ff]ByVal[/COLOR] sender [COLOR=#0000ff]As[/COLOR] System.[COLOR=#6f002f]Object[/COLOR], [COLOR=#0000ff]ByVal[/COLOR] e [COLOR=#0000ff]As[/COLOR] System.EventArgs) [COLOR=#0000ff]Handles[/COLOR] eseguiButton.Click
[COLOR=#008000]'Definizione connessione per mysql[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] cn [COLOR=#0000ff]As[/COLOR] [COLOR=#8515ea]New[/COLOR] MySqlConnection(cntxt)
[COLOR=#008000]'Oggetto command per mysql[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] cm [COLOR=#0000ff]As[/COLOR] [COLOR=#8515ea]New[/COLOR] MySqlCommand("INSERT INTO TableProva (Descrizione, Valore) VALUES(?Descrizione,?Valore)", cn)
[COLOR=#0000ff]Try[/COLOR]
[COLOR=#008000]'Apertura della connessione[/COLOR]
cn.Open()
[COLOR=#008000]'Aggiunta parametri[/COLOR]
cm.Parameters.AddWithValue("?Descrizione", [COLOR=#6f002f]String[/COLOR].Format("{0}-{1}", "Prova", DateTime.Now.ToString()))
cm.Parameters.AddWithValue("?Valore", [COLOR=#8515ea]New[/COLOR] Random().NextDouble * 1000)
[COLOR=#008000]'Esecuzione della query di inserimento[/COLOR]
cm.ExecuteNonQuery()
[COLOR=#008000]'Recupero dell'ultimo id inserito[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] i [COLOR=#0000ff]As[/COLOR] [COLOR=#6f002f]Integer[/COLOR] = [COLOR=#0000ff]CType[/COLOR](cm.LastInsertedId, [COLOR=#6f002f]Integer[/COLOR])
[COLOR=#008000]'Definizione del nuovo command che "punta" ad una stored procedure[/COLOR]
cm = [COLOR=#8515ea]New[/COLOR] MySqlCommand("Select Incrementa(?valore, ?incremento)", cn)
[COLOR=#008000]'Aggiunta parametri al command[/COLOR]
[COLOR=#0000ff]Dim[/COLOR] prm [COLOR=#0000ff]As[/COLOR] [COLOR=#8515ea]New[/COLOR] MySqlParameter
prm.ParameterName = "?valore"
prm.DbType = DbType.Int32
prm.Value = i
cm.Parameters.Add(prm)
prm = [COLOR=#8515ea]New[/COLOR] MySqlParameter
prm.ParameterName = "?incremento"
prm.DbType = DbType.Int32
prm.Value = 100 [COLOR=#008000]'incremento di 100 il numero di id[/COLOR]
cm.Parameters.Add(prm)
[COLOR=#0000ff]Dim[/COLOR] o [COLOR=#0000ff]As[/COLOR] [COLOR=#6f002f]Object[/COLOR] = cm.ExecuteScalar
MessageBox.Show(o.ToSTring())
[COLOR=#0000ff]Catch[/COLOR] ex [COLOR=#0000ff]As[/COLOR] Exception
MessageBox.Show([COLOR=#6f002f]String[/COLOR].Format("{0}{1}{2}", ex.Message, Environment.NewLine, ex.StackTrace))
[COLOR=#0000ff]Finally[/COLOR]
[COLOR=#0000ff]If[/COLOR] cn.State = ConnectionState.Open [COLOR=#0000ff]Then[/COLOR]
cn.Close()
[COLOR=#0000ff]End[/COLOR] [COLOR=#0000ff]If[/COLOR]
[COLOR=#0000ff]End[/COLOR] [COLOR=#0000ff]Try[/COLOR][/FONT]
[FONT=Calibri][B][U][SIZE=4]Esempio C#[/SIZE][/U][/B] [/FONT]
[FONT=Calibri][B][COLOR=#0000ff]private[/COLOR][/B] [COLOR=#ff0000]string[/COLOR] cntxt = [COLOR=#0000ff]"User ID=utente;Password=password;Host=localhost;Port=3306;Database=dbprova;Protocol=TCP;Compress=false;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;"[/COLOR][COLOR=#006400];[/COLOR]
[B][COLOR=#0000ff]private[/COLOR][/B] [COLOR=#ff0000]void[/COLOR] [B][COLOR=#191970]eseguiButton_Click[/COLOR][/B][COLOR=#006400]([/COLOR]System[COLOR=#006400].[/COLOR]Object sender[COLOR=#006400],[/COLOR] System[COLOR=#006400].[/COLOR]EventArgs e[COLOR=#006400])[/COLOR]
[COLOR=#006400]{[/COLOR]
[COLOR=#008000]//Definizione connessione per mysql[/COLOR]
MySqlConnection cn = [B][COLOR=#008b8b]new[/COLOR][/B] [B][COLOR=#191970]MySqlConnection[/COLOR][/B][COLOR=#006400]([/COLOR]cntxt[COLOR=#006400]);[/COLOR]
[COLOR=#008000]//Oggetto command per mysql[/COLOR]
MySqlCommand cm = [B][COLOR=#008b8b]new[/COLOR][/B] [B][COLOR=#191970]MySqlCommand[/COLOR][/B][COLOR=#006400]([/COLOR][COLOR=#0000ff]"INSERT INTO TableProva (Descrizione, Valore) VALUES(?Descrizione,?Valore)"[/COLOR][COLOR=#006400],[/COLOR] cn[COLOR=#006400]);[/COLOR]
[B][COLOR=#008080]try[/COLOR][/B] [COLOR=#006400]{[/COLOR]
[COLOR=#008000]//Apertura della connessione[/COLOR]
cn[COLOR=#006400].[/COLOR][B][COLOR=#191970]Open[/COLOR][/B][COLOR=#006400]();[/COLOR]
[COLOR=#008000]//Aggiunta parametri[/COLOR]
cm[COLOR=#006400].[/COLOR]Parameters[COLOR=#006400].[/COLOR][B][COLOR=#191970]AddWithValue[/COLOR][/B][COLOR=#006400]([/COLOR][COLOR=#0000ff]"?Descrizione"[/COLOR][COLOR=#006400],[/COLOR] [COLOR=#ff0000]string[/COLOR][COLOR=#006400].[/COLOR][B][COLOR=#191970]Format[/COLOR][/B][COLOR=#006400]([/COLOR][COLOR=#0000ff]"{0}-{1}"[/COLOR][COLOR=#006400],[/COLOR] [COLOR=#0000ff]"Prova"[/COLOR][COLOR=#006400],[/COLOR] DateTime[COLOR=#006400].[/COLOR]Now[COLOR=#006400].[/COLOR][B][COLOR=#191970]ToString[/COLOR][/B][COLOR=#006400]()));[/COLOR]
cm[COLOR=#006400].[/COLOR]Parameters[COLOR=#006400].[/COLOR][B][COLOR=#191970]AddWithValue[/COLOR][/B][COLOR=#006400]([/COLOR][COLOR=#0000ff]"?Valore"[/COLOR][COLOR=#006400],[/COLOR] [B][COLOR=#008b8b]new[/COLOR][/B] [B][COLOR=#191970]Random[/COLOR][/B][COLOR=#006400]().[/COLOR]NextDouble [COLOR=#006400]*[/COLOR] [COLOR=#00008b]1000[/COLOR][COLOR=#006400]);[/COLOR]
[COLOR=#008000]//Esecuzione della query di inserimento[/COLOR]
cm[COLOR=#006400].[/COLOR][B][COLOR=#191970]ExecuteNonQuery[/COLOR][/B][COLOR=#006400]();[/COLOR]
[COLOR=#008000]//Recupero dell'ultimo id inserito[/COLOR]
[B][COLOR=#ff0000]int[/COLOR][/B] i = Convert[COLOR=#006400].[/COLOR][B][COLOR=#191970]ToInt32[/COLOR][/B][COLOR=#006400]([/COLOR]cm[COLOR=#006400].[/COLOR]LastInsertedId[COLOR=#006400]);[/COLOR]
[COLOR=#008000]//Definizione del nuovo command che "punta" ad una stored procedure[/COLOR]
cm = [B][COLOR=#008b8b]new[/COLOR][/B] [B][COLOR=#191970]MySqlCommand[/COLOR][/B][COLOR=#006400]([/COLOR][COLOR=#0000ff]"Select Incrementa(?valore, ?incremento)"[/COLOR][COLOR=#006400],[/COLOR] cn[COLOR=#006400]);[/COLOR]
[COLOR=#008000]//Aggiunta parametri al command[/COLOR]
MySqlParameter prm = [B][COLOR=#008b8b]new[/COLOR][/B] [B][COLOR=#191970]MySqlParameter[/COLOR][/B][COLOR=#006400]();[/COLOR]
prm[COLOR=#006400].[/COLOR]ParameterName = [COLOR=#0000ff]"?valore"[/COLOR][COLOR=#006400];[/COLOR]
prm[COLOR=#006400].[/COLOR]DbType = DbType[COLOR=#006400].[/COLOR]Int32[COLOR=#006400];[/COLOR]
prm[COLOR=#006400].[/COLOR]Value = i[COLOR=#006400];[/COLOR]
cm[COLOR=#006400].[/COLOR]Parameters[COLOR=#006400].[/COLOR][B][COLOR=#191970]Add[/COLOR][/B][COLOR=#006400]([/COLOR]prm[COLOR=#006400]);[/COLOR]
prm = [B][COLOR=#008b8b]new[/COLOR][/B] [B][COLOR=#191970]MySqlParameter[/COLOR][/B][COLOR=#006400]();[/COLOR]
prm[COLOR=#006400].[/COLOR]ParameterName = [COLOR=#0000ff]"?incremento"[/COLOR][COLOR=#006400];[/COLOR]
prm[COLOR=#006400].[/COLOR]DbType = DbType[COLOR=#006400].[/COLOR]Int32[COLOR=#006400];[/COLOR]
prm[COLOR=#006400].[/COLOR]Value = [COLOR=#00008b]100[/COLOR][COLOR=#006400];[/COLOR]
[COLOR=#008000]//incremento di 100 il numero di id[/COLOR]
cm[COLOR=#006400].[/COLOR]Parameters[COLOR=#006400].[/COLOR][B][COLOR=#191970]Add[/COLOR][/B][COLOR=#006400]([/COLOR]prm[COLOR=#006400]);[/COLOR]
[COLOR=#ff0000]object[/COLOR] o = cm[COLOR=#006400].[/COLOR]ExecuteScalar[COLOR=#006400];
MessageBox[COLOR=#006400].[/COLOR][B][COLOR=#191970]Show[/COLOR][/B](o.ToString());[/COLOR]

[COLOR=#006400]}[/COLOR] [B][COLOR=#008080]catch[/COLOR][/B] [COLOR=#006400]([/COLOR]Exception ex[COLOR=#006400])[/COLOR] [COLOR=#006400]{[/COLOR]
MessageBox[COLOR=#006400].[/COLOR][B][COLOR=#191970]Show[/COLOR][/B][COLOR=#006400]([/COLOR][COLOR=#ff0000]string[/COLOR][COLOR=#006400].[/COLOR][B][COLOR=#191970]Format[/COLOR][/B][COLOR=#006400]([/COLOR][COLOR=#0000ff]"{0}{1}{2}"[/COLOR][COLOR=#006400],[/COLOR] ex[COLOR=#006400].[/COLOR]Message[COLOR=#006400],[/COLOR] Environment[COLOR=#006400].[/COLOR]NewLine[COLOR=#006400],[/COLOR] ex[COLOR=#006400].[/COLOR]StackTrace[COLOR=#006400]));[/COLOR]
[COLOR=#006400]}[/COLOR] [B][COLOR=#008080]finally[/COLOR][/B] [COLOR=#006400]{[/COLOR]
[B][COLOR=#0000ff]if[/COLOR][/B] [COLOR=#006400]([/COLOR]cn[COLOR=#006400].[/COLOR]State == ConnectionState[COLOR=#006400].[/COLOR]Open[COLOR=#006400])[/COLOR] [COLOR=#006400]{[/COLOR]
cn[COLOR=#006400].[/COLOR][B][COLOR=#191970]Close[/COLOR][/B][COLOR=#006400]();[/COLOR]
[COLOR=#006400]}[/COLOR][/FONT]

Commenti