ASP.NET / Dataset og datagrid objektet / Database som grundlag

Dataset, datagrid og database

Alle de øvelser vi har lavet med dataset, datagrid og XML dokumenter kan man naturligvis også lave med en "rigtig" database i bunden. Til det formål skal vi bruge dataadapter objektet. Nedenfor har jeg valgt at vise "ret post" eksemplet, da det giver en god illustration af, hvordan man kan bruge dataadapteren.

Eksemplet er baseret på Andeby informationerne, som jeg har lagt ind i en Access database i stedet. Hent den her.

Der er ændringer i Page_Load, UpdateCommand og GetDataset:

VB.NET

Imports System.Data
Imports System.Data.OleDb

    Dim objAdapter As OleDbDataAdapter

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim strConnection As String
        Dim strSQL As String
        Dim objConnection As OledbConnection
        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Server.MapPath("Andeby.mdb")
        objConnection = New OledbConnection(strConnection)
        strSQL = "SELECT * FROM Person"
        objAdapter = New OleDbDataAdapter(strSQL, objConnection)
        If Not IsPostBack Then
            DoDatabind()
        End If
    End Sub

    Private Sub dgridAndeby_UpdateCommand(ByVal ...
        Dim objRows() As DataRow
        Dim dsetAndeby As New DataSet
        Dim strSelect As String
        strSelect = "ID='" + CStr(dgridAndeby.DataKeys(e.Item.ItemIndex)) + "'"
        dsetAndeby = GetDataset()
        objRows = dsetAndeby.Tables(0).Select(strSelect)
        objRows(0).Item("Fornavn") = CType(e.Item.Cells(2).Controls(0), TextBox).Text
        ' Opdater database
        Dim ObjBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(objAdapter)
        objAdapter.UpdateCommand = ObjBuilder.GetUpdateCommand()
        objAdapter.InsertCommand = ObjBuilder.GetInsertCommand()
        objAdapter.DeleteCommand = ObjBuilder.GetDeleteCommand()
        objAdapter.Update(dsetAndeby, "Andeby")
        dgridAndeby.EditItemIndex = -1
        DoDatabind()
    End Sub

    Private Function GetDataset() As DataSet
        Dim dsetAndeby As New DataSet
        objAdapter.Fill(dsetAndeby, "Andeby")
        Return dsetAndeby
    End Function

C#

using System.Data;
using System.Data.OleDb;

  OleDbDataAdapter objAdapter;
  private void Page_Load(object sender, System.EventArgs e)
  {
   String strConnection;
   String strSQL;
   OleDbConnection objConnection;
   strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Server.MapPath("Andeby.mdb");
   objConnection = new OleDbConnection(strConnection);
   strSQL = "SELECT * FROM Person";
   objAdapter = new OleDbDataAdapter(strSQL, objConnection);
   if (!IsPostBack)
   {
    DoDatabind();
   }
  }

  private DataSet GetDataset()
  {
   DataSet dsetAndeby = new DataSet();
   objAdapter.Fill(dsetAndeby, "Andeby");
   return dsetAndeby;
  }

  private void UpdateEdit(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
  {
   DataRow []objRows;
   DataSet dsetAndeby = new DataSet();
   String strSelect;
   strSelect = "ID=" + (int)dgridAndeby.DataKeys[e.Item.ItemIndex];
   dsetAndeby = GetDataset();
   objRows = dsetAndeby.Tables[0].Select(strSelect);
   objRows[0]["Fornavn"] = ((TextBox)e.Item.Cells[2].Controls[0]).Text;
   // Opdater database
   OleDbCommandBuilder ObjBuilder = new OleDbCommandBuilder(objAdapter);
   objAdapter.UpdateCommand = ObjBuilder.GetUpdateCommand();
   objAdapter.InsertCommand = ObjBuilder.GetInsertCommand();
   objAdapter.DeleteCommand = ObjBuilder.GetDeleteCommand();
   objAdapter.Update(dsetAndeby, "Andeby");
   dgridAndeby.EditItemIndex = -1;
   DoDatabind();
  }
 }
}

Sådan gemmer du din connectionstring i web.config

<configuration>
  <appSettings>
    <add key="ConnectionString"
      value="server=localhost;database=Northwind;uid=sa;password=secret;" />
  </appSettings>

</configuration> 

strConnection = ConfigurationSettings.AppSettings("ConnectionString")
strConnection = ConfigurationSettings.AppSettings["ConnectionString"];

imports System.Configuration
using System.Configuration;

Cdkatalog