ASP.NET Tutorial/ADO.net Database/OleDBCommand — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 14:56, 26 мая 2010
Содержание
Call stored procedure and pass in parameters (VB.net)
<source lang="csharp">
<%@ 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 />
<asp:Panel id="Panel1" runat="server">
First and last name: |
<asp:TextBox id="tbFName" runat="server" /> <asp:TextBox id="tbLName" runat="server" /> |
</asp:Panel> </form>
</body></html></source>
Create OleDbDataReader object from OleDbCommand
<source lang="csharp">
<%@ 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 + "
"; description += "City : " + this.City + "
"; description += "Contact : <a href=mailto:" + this.Email + ">" + this.Email + "</a>
"; description += "Homesite : <a href="" + this.Website + "">" + this.Website + "</a>
"; 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></source>
Execcute delete statement
<source lang="csharp">
<%@ 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> </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></source>
Pass OleDbParameter to OleDbCommand (VB.net)
<source lang="csharp">
<%@ 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 />
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></source>
Use OleDBCommand to read data returned from a select statement (C#)
<source lang="csharp">
<%@ 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) + "
"); } objConn.Close(); }
</script> <html><body> </body></html></source>