ASP.NET Tutorial/ADO.net Database/OleDBCommand
Содержание
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>
