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++驅動概述