sql，access连接数据库，读数据库数据
SqlAccessConnection.aspx前台
Code<%@ Page Language="C#" AutoEventWireup="true" CodeFile="SqlAccessConnection.aspx.cs" Inherits="SqlAccessConnection" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
1、<asp:Button ID="Button1" runat="server" Text="sql连接3个样子的写法，配置文件1,配置文件2" OnClick="Button1_Click"/><br /><br />
2、<asp:Button ID="Button2" runat="server" Text="sql连接，绑定，DataTable--->gridview" OnClick="Button2_Click"/>
<asp:GridView ID="gvTest" runat="server"></asp:GridView>
3、sql，配置文件2，调用ConnectionString（access没有这一项功能）<br />
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="SqlDataSource1" Height="100px">
<Columns>
<asp:BoundField DataField="name" HeaderText="姓名" SortExpression="name" />
<asp:BoundField DataField="age" HeaderText="年龄" SortExpression="age" />
<asp:BoundField DataField="sex" HeaderText="性别" SortExpression="sex" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:SqlConnectionStrings %>"
SelectCommand="SELECT * FROM [students]">
</asp:SqlDataSource>
4、access,配置文件1,配置文件2，AccessAppSettings<asp:Button ID="Button3" runat="server" OnClick="Button3_Click" Text="Button" /><br />
<br />
5、access用vs配置数据源，自定义sql
<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="AccessDataSource1" Height="100px">
<Columns>
<asp:BoundField DataField="name" HeaderText="姓名" SortExpression="name" />
<asp:BoundField DataField="age" HeaderText="年龄" SortExpression="age" />
<asp:BoundField DataField="sex" HeaderText="性别" SortExpression="sex" />
</Columns>
</asp:GridView>
<asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="~/App_Data/test.mdb"
SelectCommand="select * from student" >
</asp:AccessDataSource>
6、access用vs配置数据源，指定来自表（原来和4一样的）
<asp:GridView ID="GridView3" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="AccessDataSource1" Height="100px">
<Columns>
<asp:BoundField DataField="name" HeaderText="姓名" SortExpression="name" />
<asp:BoundField DataField="age" HeaderText="年龄" SortExpression="age" />
<asp:BoundField DataField="sex" HeaderText="性别" SortExpression="sex" />
</Columns>
</asp:GridView>
<asp:AccessDataSource ID="AccessDataSource2" runat="server" DataFile="~/App_Data/test.mdb"
SelectCommand="SELECT * FROM [student]"></asp:AccessDataSource>
</div>
</form>
</body>
</html>
后台：
Codeusing System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.OleDb;
public partial class SqlAccessConnection : System.Web.UI.Page
{
protected string CONN_STRING_NON_DTC = System.Web.Configuration.WebConfigurationManager.AppSettings["SqlAppSettings"];
protected void Page_Load(object sender, EventArgs e)
{ }
protected void Button1_Click(object sender, EventArgs e)
{
//连接字符串3个样子
string str1="server=(local);Database=test;Integrated Security=true;";//连接字符串1
string str2="Data Source=(local);Initial Catalog=test;Integrated Security=True;User Id=sa;Password=sa"; //连接字符串2
string str3 = System.Web.Configuration.WebConfigurationManager.AppSettings["SqlAppSettings"]; //使用Web.Config文件中的
string str4 = ConnectionString;
SqlConnection conn = new SqlConnection(ConnectionString);//-------str1,str2,str3,str4,都可以
conn.Open();
//cmd两种写法
//SqlCommand cmd = new SqlCommand();
//cmd.CommandText = "Select top 3 * from students";
//cmd.Connection = conn;
SqlCommand cmd = new SqlCommand("Select * from students", conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Response.Write(reader["name"] + " ");
}
conn.Close();
}
protected void Button2_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Server=(local);Database=test;Integrated Security=true;";
SqlCommand cmd = new SqlCommand("Select top 3 * from students", conn);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
conn.Open();
DataTable table = new DataTable();
DataSet dsDataSet = new DataSet();
adapter.Fill(dsDataSet, "table");
conn.Close();
table = dsDataSet.Tables["table"];
this.gvTest.DataSource = table.DefaultView;
this.gvTest.DataBind();
}
protected void Button3_Click(object sender, EventArgs e)
{
//两种方式都可以
string CONN_STRING_NON_DTC_access = System.Web.Configuration.WebConfigurationManager.AppSettings["AccessAppSettings"];//数据库连接字定义
string ConnectionString_access = ConfigurationManager.ConnectionStrings["AccessConnectionStrings"].ConnectionString;
OleDbConnection objConnection = null; // 连接对象
OleDbCommand selectCommand = null; //OleDbCommand对象
OleDbDataReader selectReader = null; // OleDbDataReader对象
string selectSqlString1 = null; //声明查询语句
selectSqlString1 = "select * from student";//定义查询语句
//两种方式都可以
objConnection = new OleDbConnection(ConnectionString_access); //建立数据链接对象objConnection
//objConnection = new OleDbConnection(CONN_STRING_NON_DTC_access); //建立数据链接对象objConnection
selectCommand = new OleDbCommand(selectSqlString1, objConnection);//创建OleDbCommand 对象并保存sql语句
objConnection.Open();//打开数据连接
selectReader = selectCommand.ExecuteReader();//创建OleDbDataReader对象并执行sql语句；
while(selectReader.Read()) //读取查询的结果并且显示在页面上
{
Response.Write(selectReader["name"].ToString());
}
objConnection.Close();
}
}
配置文件：
Code<?xml version="1.0"?>
<configuration>
<connectionStrings>
<add name="SqlConnectionStrings" connectionString="Data Source=(local);Initial Catalog=test;Integrated Security=True" providerName="System.Data.SqlClient"/>
<add name="AccessConnectionStrings" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\test.mdb;Persist Security Info=True" providerName="System.Data.OleDb"/>
</connectionStrings>
<location>
<appSettings>
<add key="SqlAppSettings" value="Data Source=(local);Initial Catalog=test;Integrated Security=True;User Id=sa;Password=sa"/>
<add key="AccessAppSettings" value="provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|\test.mdb;"/>
</appSettings>
</location>
<system.web>
<compilation debug="true"/></system.web></configuration>
