ASP.NET调用类连接Access数据库执行sql语句并以GridView方式读取表中数据

上篇文章是通过页面直接访问数据库,这篇是通过调用一个通用的AccessDb类来实现对数据的操作,这样可以节省以后的工作量,创建的类需要放在App_Code文件夹下(创建类时vs2005会提示)

新建类AccessDB

using System;

using System.Data;

using System.Configuration;

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.OleDb;

/// <summary>

/// AccessDB 的摘要说明

/// </summary>

public class AccessDB

{

//变量声明处#region 变量声明处

public OleDbConnection Conn;

public string ConnString;//连接字符串

public AccessDB()

{

//ConnString = ConfigurationManager.ConnectionStrings["DB"].ConnectionString + System.AppDomain.CurrentDomain.BaseDirectory + ConfigurationManager.ConnectionStrings["DBpath"].ConnectionString;

string datapath = "DataBase/SampleDatabase.mdb";

string strConnection = "Provider=Microsoft.Jet.OleDb.4.0;Data Source= ";

strConnection += System.Web.HttpContext.Current.Server.MapPath(datapath);

ConnString = strConnection;

Conn = new OleDbConnection(ConnString);

Conn.Open();

}

//构造函数与连接关闭数据库#region 构造函数与连接关闭数据库

/// <summary>

/// 构造函数

/// </summary>

/// <param name="Dbpath">ACCESS数据库路径-webconfig中自定义</param>

public AccessDB(string DBpath)

{

//ConnString = ConfigurationManager.ConnectionStrings["DB"].ConnectionString + System.AppDomain.CurrentDomain.BaseDirectory + ConfigurationManager.ConnectionStrings["DBpath"].ConnectionString;

string datapath = "DataBase/SampleDatabase.mdb";

string strConnection = "Provider=Microsoft.Jet.OleDb.4.0;Data Source= ";

strConnection += System.Web.HttpContext.Current.Server.MapPath(datapath);

Conn = new OleDbConnection(strConnection);

Conn.Open();

}

/// <summary>

/// 打开数据源链接

/// </summary>

/// <returns></returns>

public OleDbConnection DbConn()

{

Conn.Open();

return Conn;

}

/// <summary>

/// 请在数据传递完毕后调用该函数，关闭数据链接。

/// </summary>

public void Close()

{

Conn.Close();

}

/// 数据库基本操作#region 数据库基本操作

/// <summary>

/// 根据SQL命令返回数据DataTable数据表,

/// 可直接作为dataGridView的数据源

/// </summary>

/// <param name="SQL"></param>

/// <returns></returns>

public DataTable SelectToDataTable(string SQL)

{

OleDbDataAdapter adapter = new OleDbDataAdapter();

OleDbCommand command = new OleDbCommand(SQL, Conn);

adapter.SelectCommand = command;

DataTable Dt = new DataTable();

adapter.Fill(Dt);

return Dt;

}

/// <summary>

/// 根据SQL命令返回数据DataSet数据集，其中的表可直接作为dataGridView的数据源。

/// </summary>

/// <param name="SQL"></param>

/// <param name="subtableName">在返回的数据集中所添加的表的名称</param>

/// <returns></returns>

public DataSet SelectToDataSet(string SQL, string subtableName)

{

OleDbDataAdapter adapter = new OleDbDataAdapter();

OleDbCommand command = new OleDbCommand(SQL, Conn);

adapter.SelectCommand = command;

DataSet Ds = new DataSet();

Ds.Tables.Add(subtableName);

adapter.Fill(Ds, subtableName);

return Ds;

}

/// <summary>

/// 在指定的数据集中添加带有指定名称的表，由于存在覆盖已有名称表的危险，返回操作之前的数据集。

/// </summary>

/// <param name="SQL"></param>

/// <param name="subtableName">添加的表名</param>

/// <param name="DataSetName">被添加的数据集名</param>

/// <returns></returns>

public DataSet SelectToDataSet(string SQL, string subtableName, DataSet DataSetName)

{

OleDbDataAdapter adapter = new OleDbDataAdapter();

OleDbCommand command = new OleDbCommand(SQL, Conn);

adapter.SelectCommand = command;

DataTable Dt = new DataTable();

DataSet Ds = new DataSet();

Ds = DataSetName;

adapter.Fill(DataSetName, subtableName);

return Ds;

}

/// <summary>

/// 根据SQL命令返回OleDbDataAdapter，

/// 使用前请在主程序中添加命名空间System.Data.OleDb

/// </summary>

/// <param name="SQL"></param>

/// <returns></returns>

public OleDbDataAdapter SelectToOleDbDataAdapter(string SQL)

{

OleDbDataAdapter adapter = new OleDbDataAdapter();

OleDbCommand command = new OleDbCommand(SQL, Conn);

adapter.SelectCommand = command;

return adapter;

}

/// <summary>

/// 执行SQL命令，不需要返回数据的修改，删除可以使用本函数

/// </summary>

/// <param name="SQL"></param>

/// <returns></returns>

public bool ExecuteSQLNonquery(string SQL)

{

OleDbCommand cmd = new OleDbCommand(SQL, Conn);

try

{

cmd.ExecuteNonQuery();

return true;

}

catch

{

return false;

}

/// <summary>

/// 执行SQL命令，数据是否存在，返回bool

/// </summary>

/// <param name="SQL"></param>

/// <returns></returns>

public bool Exists(string SQL)

{

OleDbCommand cmd = new OleDbCommand(SQL, Conn);

int count = Convert.ToInt32(cmd.ExecuteScalar());

if (count > 0)

{

return true;

}

else

{

return false;

}

/// <summary>

/// 执行SQL命令，查询记录总数，返回int

/// </summary>

/// <param name="SQL"></param>

/// <returns></returns>

public int MaxCount(string SQL)

{

OleDbCommand cmd = new OleDbCommand(SQL, Conn);

int count = Convert.ToInt32(cmd.ExecuteScalar());

if (count > 0)

{

return count;

}

else

{

return 0;

}

这是摘自网上一个朋友写的类,做了一些修改

在写一个页面AccessData,源码如下:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AccessData.aspx.cs" Inherits="AccessData" %>

<%@Import Namespace="System.Data"%>

<%@Import Namespace="System.Data.SqlClient"%>

<%@Import Namespace="System.Data.OleDb"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

protected void Page_Load(object sender, EventArgs e)

{

DataSet accessdata = new DataSet();

//调用类连接数据库

AccessDB accessdb = new AccessDB();

//执行语句

string insertsql = "insert into zbinfo values (1,1,1,1)";

accessdb.ExecuteSQLNonquery(insertsql);

//GridView方式读取数据

string str = "select * from zbinfo";

accessdb.SelectToDataSet(str, "zbinfo", accessdata);

this.GridView1.DataSource = accessdata.Tables["zbinfo"].DefaultView;

this.GridView1.DataBind();

}

</script>

<title>监控对象位置</title>

</head>

<body>

<div>

<asp:Label ID="Label1" runat="server" Text="监控对象:"></asp:Label>

<asp:Label ID="zbid" runat="server" Text="Label"></asp:Label>

<br />

<asp:Label ID="Label3" runat="server" Text="坐标信息:"></asp:Label>

<asp:Label ID="zbinfo" runat="server" Text="Label"></asp:Label>

<asp:Label ID="Label_count" runat="server" Text="Label"></asp:Label><br />

<br />

<asp:GridView ID="GridView1" runat="server">

</asp:GridView>

<br />

 <asp:DataList ID="DataList1" runat="server">

</asp:DataList><br />

<asp:TextBox ID="TextBox1" runat="server" Height="46px" Width="316px"></asp:TextBox></div>

</form>

</body>

</html>

ASP.NET调用类连接Access数据库执行sql语句并以GridView方式读取表中数据

继续阅读

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

GridView终极用法(一)

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

SequoiaDB巨杉数据库C++驱动概述