在Asp.net中有很多功能都需要与数据库绑定,使用数据库传递参数。为大家介绍一下在 .NET 中关于数据库提供数据的应用。(SQL.NET 数据提供程序 & OLEDB .NET 数据提供程序)
一、Connection 建立与数据库的连接
1、引用:
(1)SQL.NET 数据提供程序:
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
(2)OLEDB.NET数据提供程序:
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
2、连接数据库:
(1)SQL.NET 数据提供程序:
sqlcon = new SqlConnection("server=SQLDB;uid=sa;pwd=password;database=pubs");
(2)OLEDB.NET数据提供程序:
adocon = new OleDbConnection("Provider=SQLOLEDB;Data Source=SQLDB;Initial Catalog=pubs;User Id=sa;pwd=password");
二、Command 检索和操纵数据库中的数据
1、检索数据
(1)SQL.NET 数据提供程序:
SqlCommand sqlcom = new SqlCommand("select * from employee",sqlcon);
(2)OLEDB.NET数据提供程序:
OleDbCommand adocom = new OleDbCommand("select * from employee",adocon);
2、插入数据
(1)SQL.NET 数据提供程序:
SqlCommand inscom = new SqlCommand("insert into employee values ('ARD25437A','John','P','Baxter',7,100,0877,'1989-11-11T00:00:00')",sqlcon);
(2)OLEDB.NET数据提供程序:
OleDbCommand inscom = new OleDbCommand("insert into employee values ('ARD25437A','John','P','Baxter',7,100,0877,'1989-11-11T00:00:00')",adocon);
3、更新数据
(1)SQL.NET 数据提供程序:
SqlCommand mycommand = new SqlCommand("UPDATE employee SET job_id=11 WHERE fname='Pedro'",sqlcon);
(2)OLEDB.NET数据提供程序:
OleDbCommand mycommand = new OleDbCommand("UPDATE employee SET job_id=11 WHERE fname='Pedro'",adocon);
4、删除数据
(1)SQL.NET 数据提供程序:
SqlCommand delcom = new SqlCommand("delete from employee WHERE fname='Lesley'",sqlcon);
(2)OLEDB.NET数据提供程序:
OleDbCommand delcom = new OleDbCommand("delete from employee WHERE fname='Lesley'",adocon);
5、连接和执行命令
delcom.Connection.Open();
delcom.ExecuteNonQuery();
delcom.Connection.Close();
三、例子:
WebFrom1.aspx:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="selectTest.WebForm1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
省:<asp:DropDownList ID="ddlProvince" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlProvince_SelectedIndexChanged" style="height: 19px">
</asp:DropDownList>
<br />
<br />
市:<asp:DropDownList ID="ddlCity" runat="server">
</asp:DropDownList>
</form>
</body>
</html>
WebFrom1.aspx.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
namespace selectTest
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
//绑定省
SqlConnection con = DB.createConnection();
con.Open();
SqlCommand cmd = new SqlCommand("select * from province", con);
SqlDataReader sdr = cmd.ExecuteReader();
this.ddlProvince.DataSource = sdr;
this.ddlProvince.DataTextField = "proName";
this.ddlProvince.DataValueField = "proID";
this.ddlProvince.DataBind();
sdr.Close();
//绑定市
SqlCommand cmdCity = new SqlCommand("select * from city where proID=" + this.ddlProvince.SelectedValue, con);
SqlDataReader sdr1 = cmdCity.ExecuteReader();
this.ddlCity.DataSource = sdr1;
this.ddlCity.DataTextField = "cityName";
this.ddlCity.DataValueField = "cityID";
this.ddlCity.DataBind();
sdr1.Close();
//关闭连接
con.Close();
}
}
protected void ddlProvince_SelectedIndexChanged(object sender, EventArgs e)
{
string proID = this.ddlProvince.SelectedValue;
SqlConnection con = DB.createConnection();
con.Open();
SqlCommand cmd = new SqlCommand("select * from city where proID=" + proID, con);
SqlDataReader sdr = cmd.ExecuteReader();
this.ddlCity.DataSource = sdr;
this.ddlCity.DataTextField = "cityName";
this.ddlCity.DataValueField = "cityID";
this.ddlCity.DataBind();
sdr.Close();
con.Close();
}
}
}
DB.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
namespace selectTest
{
public class DB
{
public static SqlConnection createConnection()
{
SqlConnection con = new SqlConnection("server=.;database=dropDownTest;uid=sa;pwd=123456;");
return con;
}
}
}
效果: