ASP.Net/ADO.net Database/OleDBCommand

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

Create SQL command from OleDbCommand (C#)

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="c#" runat="server">
  void Page_Load(object sender, EventArgs e)
  {
    string strConnection, strSQL;
    DataSet objDataSet = new DataSet();
    OleDbConnection objConnection = null;
    OleDbDataAdapter objAdapter = null;
    OleDbCommand objCommand = null;
    OleDbCommandBuilder objBuilder = null;
    // Set the connection and query details
    strConnection = "Provider=Microsoft.Jet.OleDb.4.0;";
    strConnection += @"Data Source="+MapPath("EmployeeDatabase.mdb");
    strSQL = "SELECT ID, FirstName, LastName FROM Employee";
    // Open the connection and set the command
    objConnection = new OleDbConnection(strConnection);
    objAdapter = new OleDbDataAdapter(strSQL, objConnection);
    // Create the other commands
    objBuilder = new OleDbCommandBuilder(objAdapter);
    objAdapter.UpdateCommand = objBuilder.GetUpdateCommand();
    objAdapter.InsertCommand = objBuilder.GetInsertCommand();
    objAdapter.DeleteCommand = objBuilder.GetDeleteCommand();
    // Now display the CommandText property from each command
    lblSelectCommand.Text = objAdapter.SelectCommand.rumandText;
    lblUpdateCommand.Text = objAdapter.UpdateCommand.rumandText;
    lblInsertCommand.Text = objAdapter.InsertCommand.rumandText;
    lblDeleteCommand.Text = objAdapter.DeleteCommand.rumandText;
  }
</script>
<html>
 <body>
  <table border="1">
   <tr>
    <td>Command</td>
    <td>CommandText</td>
   </tr>
   <tr>
    <td>SelectCommand</td>
    <td><asp:Label id="lblSelectCommand" runat="server" />
   </tr>
   <tr>
    <td>UpdateCommand</td>
    <td><asp:Label id="lblUpdateCommand" runat="server" />
   </tr>
   <tr>
    <td>InsertCommand </td>
    <td><asp:Label id="lblInsertCommand" runat="server" />
   </tr>
   <tr>
    <td>DeleteCommand</td>
    <td><asp:Label id="lblDeleteCommand" runat="server" />
   </tr>
  </table>
 </body>
</html>


<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>


OleDBCommand Execute Reader (VB.net)

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="vb" runat="server">
  Sub Page_Load()
    dim strConnection as string = "Provider=Microsoft.Jet.OLEDB.4.0;"
      strConnection += "Data source=C:\northwind.mdb;"
    dim strSQL as string = "Select LastName, City, Country from employees;"
    dim objConnection as new OLEDBConnection(strConnection)
    dim objCommand as new OLEDBCommand(strSQL,objConnection)
    objConnection.Open()
    dgEmps.DataSource = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
    dgEmps.DataBind()
  end sub
</script>
<html>
  <body>
    <h2>Using ExecuteReader to create a table</h2>
    <asp:datagrid id="dgEmps" 
        runat="server" 
        CellPadding="3"
        Font-Name="arial"
        Font-Size="8pt"
        HeaderStyle-BackColor="#dcdcdc"
        HeaderStyle-ForeColor="blue"
    />
  </body>
</html>



Use direct SQL to insert (C#)

<%@ Page Language="C#" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script Language="c#" runat="server">
  void Page_Load(object sender, EventArgs e)
  {
    OleDbConnection objConnection = null;
    OleDbCommand objCmd = null;
    string strConnection, strSQL;
    strConnection = "Provider=Microsoft.Jet.OleDb.4.0;";
    strConnection += @"Data Source="+MapPath("EmployeeDatabase.mdb");
    objConnection = new OleDbConnection(strConnection);
    objConnection.Open();
    strSQL = "INSERT INTO Employee(FirstName,LastName) VALUES ("Beth", "Hart")";
    // Create the Command and set its properties
    objCmd = new OleDbCommand(strSQL, objConnection);
    // Execute the command
    objCmd.ExecuteNonQuery();
    lblStatus.Text = "Command run";
  }
</script>
<html>
  <body>
    <h2>Using SQL directly</h2>
    <asp:Label id="lblStatus" runat="server"/>
  </body>
</html>


<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>


Use SQL directly to insert data into database in VB.net

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">
  Sub Page_Load(Sender As Object, E As EventArgs)
    Dim objConnection As OleDbConnection
    Dim objCmd        As OleDbCommand
    Dim strConnection As String
    Dim strSQL        As String
    
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source="+MapPath("EmployeeDatabase.mdb")
  
    " Create and open the connection object
    objConnection = New OleDbConnection(strConnection)
    objConnection.Open()
    
    " set the SQL string
    strSQL = "INSERT INTO Employee(FirstName,LastName)" & _
                      " VALUES ( "Beth" , "Hart" )"
    " Create the Command and set its properties
    objCmd = New OleDbCommand(strSQL, objConnection)
    
    " execute the command
    objCmd.ExecuteNonQuery()
    lblStatus.Text = "Command run"
  End Sub
</script>
<html>
  <body>
    <h2>Using SQL directly</h2>
    <asp:Label id="lblStatus" runat="server"/>
  </body>
</html>


<A href="http://www.nfex.ru/Code/ASPDownload/EmployeeDatabase.zip">EmployeeDatabase.zip( 10 k)</a>

Источник — «http://nfex.ru/index.php?title=ASP.Net/ADO.net_Database/OleDBCommand&oldid=1635»