上篇文章是通过页面直接访问数据库,这篇是通过调用一个通用的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">
<script runat="server">
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>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>监控对象位置</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" runat="server" Text="监控对象:"></asp:Label>
<asp:Label ID="zbid" runat="server" Text="Label"></asp:Label>
<br />
<br />
<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 />
<br />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
<br />
<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>