数据访问层:
给你几个我的函数吧:
public
static
void
Open()
{
if
(con.State
==
ConnectionState.Closed)
{
con.Open()
}
}
public
static
void
Close()
{
if
(con.State
==
ConnectionState.Open)
{
con.Close()
}
}
public
static
SqlCommand
CreateCommand(string
sql)
{
return
new
SqlCommand(sql,
con)
}
public
static
List<string>
RunSQLReturnList(string
sql)
{
List<string>
info
=
new
List<string>()
SqlCommand
cmd
=
CreateCommand(sql)
Open()
SqlDataReader
sdr
=
cmd.ExecuteReader()
while
(sdr.Read())
{
for
(int
i
=
0
i
<
sdr.FieldCount
i++)
{
info.Add(sdr.GetName(i).ToString())
info.Add(sdr.GetValue(i).ToString())
}
}
Close()
return
info
}
public
static
Hashtable
RunSQLReturnHashTable(string
sql)
{
List<string>
lt
=
RunSQLReturnList(sql)
Hashtable
ht
=
new
Hashtable()
for
(int
i
=
0
i
<
lt.Count
i
+=
2)
{
ht[lt[i]]
=
lt[i
+
1]
}
return
ht
}
数据访问层返回给逻辑层HashTable对象hashTable1:
user.username=hashTable1["Name"].ToString()
.........
逻辑层返回给表示层实体对象user
label1.text
=
user.UserName
使用.net访问Access数据库：1.BL层：新增一个DataAccess类。
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
namespace Haley.FrameWork
{
/// <summary>
/// DataAccess 的摘要说明
/// </summary>
public class DataAccess
{
protected static OleDbConnection conn = new OleDbConnection()
protected static OleDbCommand comm = new OleDbCommand()
public DataAccess()
{
//init
}
/// <summary>
/// 打开数据库
/// </summary>
private static void openConnection()
{
if (conn.State == ConnectionState.Closed)
{
conn.ConnectionString = @"Provider=Microsoft.Jet.OleDb.4.0Data Source=" + ConfigurationManager.AppSettings["myconn"]//web.config文件里设定。
comm.Connection = conn
try
{
conn.Open()
}
catch (Exception e)
{ throw new Exception(e.Message)}
}
}
/// <summary>
/// 关闭数据库
/// </summary>
private static void closeConnection()
{
if (conn.State == ConnectionState.Open)
{
conn.Close()
conn.Dispose()
comm.Dispose()
}
}
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="sqlstr"></param>
public static void excuteSql(string sqlstr)
{
try
{
openConnection()
comm.CommandType = CommandType.Text
comm.CommandText = sqlstr
comm.ExecuteNonQuery()
}
catch (Exception e)
{
throw new Exception(e.Message)
}
finally
{ closeConnection()}
}
/// <summary>
/// 返回指定sql语句的OleDbDataReader对象，使用时请注意关闭这个对象。
/// </summary>
/// <param name="sqlstr"></param>
/// <returns></returns>
public static OleDbDataReader dataReader(string sqlstr)
{
OleDbDataReader dr = null
try
{
openConnection()
comm.CommandText = sqlstr
comm.CommandType = CommandType.Text
dr = comm.ExecuteReader(CommandBehavior.CloseConnection)
}
catch
{
try
{
dr.Close()
closeConnection()
}
catch { }
}
return dr
}
/// <summary>
/// 返回指定sql语句的OleDbDataReader对象,使用时请注意关闭
/// </summary>
/// <param name="sqlstr"></param>
/// <param name="dr"></param>
public static void dataReader(string sqlstr, ref OleDbDataReader dr)
{
try
{
openConnection()
comm.CommandText = sqlstr
comm.CommandType = CommandType.Text
dr = comm.ExecuteReader(CommandBehavior.CloseConnection)
}
catch
{
try
{
if (dr != null &&!dr.IsClosed)
dr.Close()
}
catch
{
}
finally
{
closeConnection()
}
}
}
/// <summary>
/// 返回指定sql语句的dataset
/// </summary>
/// <param name="sqlstr"></param>
/// <returns></returns>
public static DataSet dataSet(string sqlstr)
{
DataSet ds = new DataSet()
OleDbDataAdapter da = new OleDbDataAdapter()
try
{
openConnection()
comm.CommandType = CommandType.Text
comm.CommandText = sqlstr
da.SelectCommand = comm
da.Fill(ds)
}
catch (Exception e)
{
throw new Exception(e.Message)
}
finally
{
closeConnection()
}
return ds
}
/// <summary>
/// 返回指定sql语句的dataset
/// </summary>
/// <param name="sqlstr"></param>
/// <param name="ds"></param>
public static void dataSet(string sqlstr, ref DataSet ds)
{
OleDbDataAdapter da = new OleDbDataAdapter()
try
{
openConnection()
comm.CommandType = CommandType.Text
comm.CommandText = sqlstr
da.SelectCommand = comm
da.Fill(ds)
}
catch (Exception e)
{
throw new Exception(e.Message)
}
finally
{
closeConnection()
}
}
/// <summary>
/// 返回指定sql语句的datatable
/// </summary>
/// <param name="sqlstr"></param>
/// <returns></returns>
public static DataTable dataTable(string sqlstr)
{
DataTable dt = new DataTable()
OleDbDataAdapter da = new OleDbDataAdapter()
try
{
openConnection()
comm.CommandType = CommandType.Text
comm.CommandText = sqlstr
da.SelectCommand = comm
da.Fill(dt)
}
catch (Exception e)
{
throw new Exception(e.Message)
}
finally
{
closeConnection()
}
return dt
}
/// <summary>
/// 返回指定sql语句的datatable
/// </summary>
/// <param name="sqlstr"></param>
/// <param name="dt"></param>
public static void dataTable(string sqlstr, ref DataTable dt)
{
OleDbDataAdapter da = new OleDbDataAdapter()
try
{
openConnection()
comm.CommandType = CommandType.Text
comm.CommandText = sqlstr
da.SelectCommand = comm
da.Fill(dt)
}
catch (Exception e)
{
throw new Exception(e.Message)
}
finally
{
closeConnection()
}
}
/// <summary>
/// 返回指定sql语句的dataview
/// </summary>
/// <param name="sqlstr"></param>
/// <returns></returns>
public static DataView dataView(string sqlstr)
{
OleDbDataAdapter da = new OleDbDataAdapter()
DataView dv = new DataView()
DataSet ds = new DataSet()
try
{
openConnection()
comm.CommandType = CommandType.Text
comm.CommandText = sqlstr
da.SelectCommand = comm
da.Fill(ds)
dv = ds.Tables[0].DefaultView
}
catch (Exception e)
{
throw new Exception(e.Message)
}
finally
{
closeConnection()
}
return dv
}
}
}
2.Web.Config：
在Config文件中添加Access文件的路径：
<appSettings>
<!-- 这里是存放Access文件的地址。用Access文件存放的路径替换下边的路径。-->
<add key="myconn" value="D:\Test\Test.mdb"/>
</appSettings >
3.UI层。
在页面添加控件，在cs文件中使用上边的类。
protected void Button1_Click(object sender, EventArgs e)
{
string name = TextBox1.Text
string strSql = "insert into Table1(Name) values('" + name + "')"
DataAccess.excuteSql(strSql)
}
.net数据库访问技术是ADO.net体系。主要有三大部分。数据提供程序、DataSet、DataTable
数据提供程序接口有四个基类 Connection（连接数据库） Command（执行SQL命令） DataAdapter（提取或填充数据） DataReader（读取数据）
DataSet用来快速 *** 作大量的数据
DataTable表示一个表。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）