sql，access连接数据库，读数据库数据

最新推荐文章于 2018-01-25 22:39:33 发布

dianlixiao2608

最新推荐文章于 2018-01-25 22:39:33 发布

阅读量115

点赞数

文章标签：数据库 xhtml ui

原文链接：http://www.cnblogs.com/jianlinglo/archive/2009/05/21/1486502.html

版权

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>

后台：

Code
using 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>

转载于:https://www.cnblogs.com/jianlinglo/archive/2009/05/21/1486502.html