Ho visto in questo forum (e pure in altri) che ci sono diversi problemi a dover gestire database mySQL con ASP.NET,c'è chi utilizza ADO chi ODBC, in ogni caso la situazione è molto complessa (almeno per me).
Dopo giorni di ricerche e di tentativi vani, ho trovato un provider ADO.NET per mySQL ( http://crlab.com/mysqlnet/ ), a me è funzionato subito e non l'ho trovato molto complesso.
Posto un po' di codice (tratto da ASP.NET guida completa di Apogeo e debitamente modificato), sperando che possa risparmiare un po' di fatica a qualcuno di voi.
Per far funzionare l'esempio basta creare un database mySQL equivalente a quello usato dalla guida di Apogeo, lo potete scricare da qui ( http://www.apogeonline.com/libri/009.../databases.zip ), si chiama "banking.mdb".
Bisogna ovviamente registrare i nuovi componenti e usare le classi per mysql, comunque nell'esempio c'è tutto quello che serve.
codice:<%@ Page CompilerOptions=""/R:"C:\Programmi\CoreLab\MySQLDirect.NET\CoreLab.MySql.dll""" Debug="true" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.OleDb" %> <%@ import Namespace="CoreLab.MySql" %> <script runat="server"> Dim conn As New MySqlConnection("User=root; pwd=mypassword; Host=localhost; Port=3306; Database=banking") sub Page_Load(obj as Object, e as EventArgs) if Not Page.IsPostBack then FillDataGrid() end if end sub sub Submit(obj as object, e as eventargs) dim i, j as integer dim params(7) as string dim strText as string dim blnGo as boolean = true j = 0 for i = 0 to AddPanel.Controls.Count - 1 if AddPanel.controls(i).GetType Is GetType(TextBox) then strText = Ctype(AddPanel.Controls(i), TextBox).Text if strText <> "" then params(j) = strText else blnGo = false lblMessage.Text = lblMessage.Text & "You forgot to enter " & _ "a value for " & AddPanel.Controls(i).ID & " " lblMessage.Style("ForeColor") = "Red" end if j = j + 1 end if next if not blnGo then exit sub end if dim strSQL as string = "INSERT INTO tblUsers " & _ "(FirstName, LastName, Address, City, State, " & _ "Zip, Phone) VALUES (" & _ "'" & params(0) & "'," & _ "'" & params(1) & "'," & _ "'" & params(2) & "'," & _ "'" & params(3) & "'," & _ "'" & params(4) & "'," & _ "'" & params(5) & "'," & _ "'" & params(6) & "')" ExecuteStatement(strSQL) FillDataGrid() end sub sub dgData_Edit(obj as object, e as DataGridCommandEventArgs) FillDataGrid(e.Item.ItemIndex) end sub sub dgData_Delete(obj as object, e as DataGridCommandEventArgs) dim strSQL as string = "DELETE FROM tblUsers " & _ "WHERE UserID = " & e.Item.ItemIndex + 1 ExecuteStatement(strSQL) FillDataGrid() end sub sub dgData_Update(obj as object, e as DataGridCommandEventArgs) if UpdateDataStore(e) then FillDataGrid(-1) end if end sub sub dgData_Cancel(obj as object, e as DataGridCommandEventArgs) FillDataGrid(-1) end sub sub dgData_PageIndexChanged(obj as Object, e as DataGridPageChangedEventArgs) dgData.DataBind() end sub function UpdateDataStore(e as DataGridCommandEventArgs) _ as boolean dim i,j as integer dim params(7) as string dim strText as string dim blnGo as boolean = true j = 0 for i = 1 to e.Item.Cells.Count - 3 strText = Ctype(e.Item.Cells(i).Controls(0), TextBox).Text if strText <> "" then params(j) = strText j = j + 1 else blnGo = false lblMessage.Text = lblMessage.Text & "You forgot to enter " & _ "a value " end if next if not blnGo then return false exit function end if dim strSQL as string = "UPDATE tblUsers SET " & _ "FirstName = '" & params(0) & "'," & _ "LastName = '" & params(1) & "'," & _ "Address = '" & params(2) & "'," & _ "City = '" & params(3) & "'," & _ "State = '" & params(4) & "'," & _ "Zip = '" & params(5) & "'," & _ "Phone = '" & params(6) & "'" & _ " WHERE UserID = " & Ctype(e.Item.Cells(0).Controls(1), Label).text ExecuteStatement(strSQL) return blnGo end function sub FillDataGrid(Optional EditIndex as integer=-1) dim objCmd as new mysqlCommand _ ("select * from tblUsers", Conn) dim objReader as mysqlDataReader try objCmd.Connection.Open() objReader = objCmd.ExecuteReader() catch ex as Exception lblMessage.Text = "Error retrieving from the database. Please" & _ " make sure all values are correctly input" end try dgData.DataSource = objReader if not EditIndex.Equals(Nothing) then dgData.EditItemIndex = EditIndex end if dgData.DataBind() objReader.Close objCmd.Connection.Close() end sub function ExecuteStatement(strSQL) dim objCmd as new mysqlCommand(strSQL, Conn) try objCmd.Connection.Open() objCmd.ExecuteNonQuery() catch ex as Exception lblMessage.Text = "Error updating the database. Please" & _ " make sure all values are correctly input" end try objCmd.Connection.Close() end function </script> <html> <head> </head> <body> <asp:Label id="lblMessage" runat="server"></asp:Label> <form runat="server"> <asp:DataGrid id="dgData" runat="server" BorderColor="black" GridLines="Vertical" cellpadding="4" cellspacing="0" width="100%" AutoGenerateColumns="False" OnDeleteCommand="dgData_Delete" OnEditCommand="dgData_Edit" OnCancelCommand="dgData_Cancel" OnUpdateCommand="dgData_Update" OnPageIndexChanged="dgData_PageIndexChanged"> <Columns> <asp:TemplateColumn HeaderText="ID"> <ItemTemplate> <asp:Label id="Name" runat="server" Text='<%# Container.DataItem("UserID") %>' /> </ItemTemplate> </asp:TemplateColumn> <asp:BoundColumn HeaderText="FirstName" DataField="FirstName" /> <asp:BoundColumn HeaderText="LastName" DataField="LastName" /> <asp:BoundColumn HeaderText="Address" DataField="Address" /> <asp:BoundColumn HeaderText="City" DataField="City" /> <asp:BoundColumn HeaderText="State" DataField="State" /> <asp:BoundColumn HeaderText="Zip" DataField="Zip" /> <asp:BoundColumn HeaderText="Phone" DataField="Phone" /> <asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" HeaderText="Edit" /> <asp:ButtonColumn HeaderText="" text="Delete" CommandName="delete" /> </Columns> </asp:DataGrid> <asp:Panel id="AddPanel" runat="server"> <table> <tbody> <tr> <td valign="top" width="100"> First and last name: </td> <td valign="top" width="300"> <asp:TextBox id="tbFName" runat="server"></asp:TextBox> <asp:TextBox id="tbLName" runat="server"></asp:TextBox> </td> </tr> <tr> <td valign="top"> Address: </td> <td valign="top"> <asp:TextBox id="tbAddress" runat="server"></asp:TextBox> </td> </tr> <tr> <td valign="top"> City, State, ZIP: </td> <td valign="top"> <asp:TextBox id="tbCity" runat="server"></asp:TextBox> , <asp:TextBox id="tbState" runat="server" size="2"></asp:TextBox> <asp:TextBox id="tbZIP" runat="server" size="5"></asp:TextBox> </td> </tr> <tr> <td valign="top"> Phone: </td> <td valign="top"> <asp:TextBox id="tbPhone" runat="server" size="11"></asp:TextBox> </p> </td> </tr> <tr> <td valign="top" align="right" colspan="2"> <asp:Button id="btSubmit" onclick="Submit" runat="server" text="Add"></asp:Button> </td> </tr> </tbody> </table> </asp:Panel> </p> </form> </body> </html>
Rispondi quotando
