ASP.NET Tutorial/ADO.net Database/Access

Материал из .Net Framework эксперт
Перейти к: навигация, поиск

Execute an insert statement on Access database

<%@ Page Language="C#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
    void Page_Load(object sender, EventArgs e) {
        string ConnectionString = ConfigurationSettings.AppSettings["AccessConnectString"];
        OleDbConnection myConnection = new OleDbConnection(ConnectionString);
    
        try{
            string CommandText = "INSERT INTO Publisher (PublisherName, PublisherCity, PublisherContact_Email, PublisherWebsite) VALUES ("New Publisher", "Newcastle", "bigcheese@newpublish.ru", "http://www.newpublish.ru")";
            OleDbCommand myCommand = new OleDbCommand(CommandText, myConnection);
    
            myConnection.Open();
    
            lblRecords.Text = Convert.ToString(myCommand.ExecuteNonQuery());
        } catch (Exception ex){
            throw(ex);
        } finally{
            myConnection.Close();
        }
    }
</script>
<html>
<head>
</head>
<body>
    <form runat="server">
        Records affected: <asp:Label id="lblRecords" runat="server"></asp:Label>
    </form>
</body>
</html>
File: Web.config
<configuration>
    <appSettings>
        <add key="AccessConnectString" value="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Books.mdb;" />
    </appSettings>
</configuration>


Execute an update statement against Access database

<%@ Page Language="C#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
    void Page_Load(object sender, EventArgs e) {
        string ConnectionString = ConfigurationSettings.AppSettings["AccessConnectString"];
        OleDbConnection myConnection = new OleDbConnection(ConnectionString);
    
        try{
            string CommandText = "UPDATE Publisher Set PublisherName="Old Publisher", PublisherCity = "AAA" WHERE PublisherID = 6";
            OleDbCommand myCommand = new OleDbCommand(CommandText, myConnection);
    
            myConnection.Open();
    
            lblRecords.Text = Convert.ToString(myCommand.ExecuteNonQuery());
        } catch (Exception ex){
            throw(ex);
        } finally{
            myConnection.Close();
        }
    }
</script>
<html>
<head>
</head>
<body>
    <form runat="server">
        Records affected: <asp:Label id="lblRecords" runat="server"></asp:Label>
    </form>
</body>
</html>
File: Web.config
<configuration>
    <appSettings>
        <add key="AccessConnectString" value="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Books.mdb;" />
    </appSettings>
</configuration>


Execute select statement against Access database

<%@ Page Language="C#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
    void Page_Load(object sender, EventArgs e) {
        string ConnectionString = ConfigurationSettings.AppSettings["AccessConnectString"];
        string CommandText = "select PublisherID, PublisherName, PublisherCity, PublisherWebsite FROM Publisher ORDER BY PublisherID";
    
        OleDbConnection myConnection = new OleDbConnection(ConnectionString);
        OleDbCommand myCommand = new OleDbCommand(CommandText, myConnection);
    
        myConnection.Open();
    
        DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
        DataGrid1.DataBind();
    }
</script>
<html>
<head>
</head>
<body>
    <form runat="server">
        <asp:datagrid id="DataGrid1" runat="server" EnableViewState="False">
            <HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></HeaderStyle>
            <ItemStyle backcolor="#DEDFDE"></ItemStyle>
        </asp:datagrid>
    </form>
</body>
</html>
File: Web.config
<configuration>
    <appSettings>
        <add key="AccessConnectString" value="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Books.mdb;" />
    </appSettings>
</configuration>


Iterating Through A DataSet for Access database

<%@ Page Language="C#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
    void Page_Load(object sender, EventArgs e)
    {
        string ConnectionString = Convert.ToString(ConfigurationSettings.AppSettings["AccessConnectString"]);
        string CommandText = "SELECT AuthorName, AuthorCity, AuthorContact_Email, AuthorWebsite FROM Author";
        OleDbConnection myConnection = new OleDbConnection(ConnectionString);
        OleDbCommand myCommand = new OleDbCommand(CommandText, myConnection);
        OleDbDataAdapter myAdapter = new OleDbDataAdapter();
        myAdapter.SelectCommand = myCommand;
        DataSet myDataSet = new DataSet();
        try {
          myConnection.Open();
          myAdapter.Fill(myDataSet, "Author");
        } catch (Exception ex) {
          throw (ex);
        } finally {
          myConnection.Close();
       }
       for (int i=0; i<=myDataSet.Tables["Author"].Rows.Count-1; i++)
       {
          Author p = new Author();
          p.Name = myDataSet.Tables["Author"].Rows[i]["AuthorName"].ToString();
          p.City = myDataSet.Tables["Author"].Rows[i]["AuthorCity"].ToString();
          p.Email = myDataSet.Tables["Author"].Rows[i]["AuthorContact_Email"].ToString();
          p.Website = myDataSet.Tables["Author"].Rows[i]["AuthorWebsite"].ToString();
          Label1.Text += p.ToString();
       }
    }
    public class Author
    {
        public string Name;
        public string City;
        public string Email;
        public string Website;
        public Author()
        {}
        public string ToString()
        {
            string description = "";
            description = "Name : " + this.Name + "<br />";
            description += "City : " + this.City + "<br />";
            description += "Contact : <a href=mailto:" + this.Email + ">" + this.Email + "</a><br/>";
            description += "Homesite : <a href="" + this.Website + "">" + this.Website + "</a><br/><br/>";
            return description;
        }
    }
</script>
<html>
<body>
    <asp:Label id="Label1" runat="server"></asp:Label>
</body>
</html>

File: Web.config
<configuration>
    <appSettings>
      <add key="AccessConnectString"
           value="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Books.mdb;" />
    </appSettings>
</configuration>


Read scalar data from Access database

<%@ Page Language="C#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
    void Page_Load(object sender, EventArgs e) {
        string ConnectionString = ConfigurationSettings.AppSettings["AccessConnectString"];
        string CommandText = "select PublisherID, PublisherName, PublisherCity, PublisherWebsite FROM Publisher ORDER BY PublisherID";
    
        OleDbConnection myConnection = new OleDbConnection(ConnectionString);
        OleDbCommand myCommand = new OleDbCommand(CommandText, myConnection);
    
        myConnection.Open();
    
        string CommandTextCount = "SELECT COUNT(*) FROM Publisher";
        OleDbCommand myCommandCount = new OleDbCommand(CommandTextCount, myConnection);
    
        lblTotal.Text = Convert.ToString(myCommandCount.ExecuteScalar());
    
        DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
        DataGrid1.DataBind();
    }
</script>
<html>
<head>
</head>
<body>
    
        Publishers in the database: <asp:Label id="lblTotal" runat="server"></asp:Label>
    
    <hr size="1" />
    <form runat="server">
        <asp:datagrid id="DataGrid1" runat="server" EnableViewState="False">
            <HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></HeaderStyle>
            <ItemStyle backcolor="#DEDFDE"></ItemStyle>
        </asp:datagrid>
    </form>
</body>
</html>
File: Web.config
<configuration>
    <appSettings>
        <add key="AccessConnectString" value="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Books.mdb;" />
    </appSettings>
</configuration>


Set Connection to Access database (VB.net)

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDB" %>
<script runat="server">
   sub Page_Load(Sender as Object, e as EventArgs) 
      dim myConnection as new OleDbConnection( _
            "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
            & "DATA SOURCE=" & Server.MapPath("EmployeeDatabase.mdb;"))
      dim myCommand as OleDbDataAdapter = new OleDbDataAdapter("select * from employee", myConnection)
       dim ds as DataSet = new DataSet()
       myCommand.Fill(ds, "employee")
    end sub
</script>
<html><body>
   <form runat="server">
   
   </form>
</body></html>
Источник — «http://nfex.ru/index.php?title=ASP.NET_Tutorial/ADO.net_Database/Access&oldid=2689»