ASP.NET Tutorial/ADO.net Database/OleDBCommand

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

Call stored procedure and pass in parameters (VB.net)

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">
   dim Conn as new OleDbConnection( _
            "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
            & "DATA SOURCE=" _
            & Server.MapPath("EmployeeDatabase.mdb;"))
   
   sub InsertData(Sender as Object, e as EventArgs) 
      dim objCmd as OleDbCommand = new OleDbCommand _
         ("InsertUser", Conn)
      objCmd.rumandType = CommandType.StoredProcedure
      
      dim objParam as OleDbParameter
      objParam = objCmd.Parameters.Add("@FirstName", OleDbType.BSTR)
      objParam.Direction = ParameterDirection.Input
      objParam.Value = tbFName.Text
      
      objParam = objCmd.Parameters.Add("@LastName", OleDbType.BSTR)
      objParam.Direction = ParameterDirection.Input
      objParam.Value = tbLName.Text
      
      try
         objCmd.Connection.Open()
         objCmd.ExecuteNonQuery
      catch ex as OleDbException
         lblMessage.Text = ex.Message
      end try
      objCmd.Connection.Close()
      lblMessage.Text = "User successfully added."
   end sub
</script>
<html><body>
   <form runat="server">
      <asp:Label id="lblMessage" runat="server"
         maintainstate=false /><br>
      <asp:Panel id="Panel1" runat="server">
         <table>
         <tr>
            <td width="100" valign="top">
               First and last name:
            </td>
            <td width="300" valign="top">
               <asp:TextBox id="tbFName" runat="server" />
               <asp:TextBox id="tbLName" runat="server" />
            </td>
         </tr>
         </table>
      </asp:Panel>
   </form>
</body></html>


Create OleDbDataReader object from OleDbCommand

<%@ 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);
        try
        {
            myConnection.Open();
    
            OleDbDataReader myReader = myCommand.ExecuteReader();
                while (myReader.Read())
                {
                   Author p = new Author();
                   p.Name = myReader.GetString(0);
                   p.City = myReader.GetString(1);
                   p.Email = myReader.GetString(2);
                   p.Website = myReader.GetString(3);
                   Label1.Text += p.ToString();
                }
            myReader.Close();
        } catch (Exception ex) {
            throw (ex);
        } finally {
            myConnection.Close();
        }
    }
    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>


Execcute delete statement

<%@ 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 = "DELETE FROM Publisher 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>
        <!-- Insert content here -->
    </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>


Pass OleDbParameter to OleDbCommand (VB.net)

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">
   "declare connection
   dim Conn as new OleDbConnection( _
            "PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
            & "DATA SOURCE=" _
            & Server.MapPath("EmployeeDatabase.mdb;"))
   
   sub GetData(Sender as Object, e as EventArgs) 
      dim objCmd as OleDbCommand = new OleDbCommand _
         ("select * from employee where ID = @ID", Conn)
      dim objReader as OleDbDataReader
      
      dim objParam as OleDbParameter
      objParam = objCmd.Parameters.Add("@ID", OleDbType.Integer)
      objParam.Direction = ParameterDirection.Input
      objParam.Value = tbID.Text
           
      try
         objCmd.Connection.Open()
         objReader = objCmd.ExecuteReader
      catch ex as OleDbException
         Label1.Text = "Error retrieving from the database."
      end try
       
      DataGrid1.DataSource = objReader
      DataGrid1.DataBind()
       
      objReader.Close
      objCmd.Connection.Close()
   end sub
</script>
<html><body>
   <form runat="server">
      <asp:Label id="Label1" runat="server"
         maintainstate=false /><br>
      Enter an ID: <asp:TextBox id="tbID" runat="server"
         AutoPostBack=True
         OnTextChanged=GetData />
      <asp:DataGrid id="DataGrid1" runat="server" 
         BorderColor="black" 
         width="100%"
         AutoGenerateColumns="true" />
   </form>
</body></html>


Use OleDBCommand to read data returned from a select statement (C#)

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">
   void Page_Load(Object Sender, EventArgs e) {
      OleDbConnection objConn = new OleDbConnection(
      "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" +
       Server.MapPath("EmployeeDatabase.mdb;"));
      
      OleDbCommand objCmd = new OleDbCommand("select * from employee", objConn);
      OleDbDataReader objReader;
      
      objConn.Open();
      objReader = objCmd.ExecuteReader();
      
      while (objReader.Read()) {
         Response.Write(objReader.GetInt32(0) + "<br>");
      }
      objConn.Close();
   }
</script>
<html><body>
</body></html>
Источник — «http://nfex.ru/index.php?title=ASP.NET_Tutorial/ADO.net_Database/OleDBCommand&oldid=2721»