中午看了一下SqlServerDAL,画了一个图先贴上来。
SQL数据访问层设计专为SQLServer数据库而设计的。该层是整个项目的最底层!
SQLHelper通过一组静态方法来封装数据的访问功能。
(DAAB.NET SQLHelper完整代码偶全打印出来了,删了一些注释后共21页。留着上课不想听课时候看。)
SQLHelper.cs类为 Account.cs 、Inventory.cs、Item.cs、Order.cs、Product.cs、Profile.cs 六个类的基类。他们都调用SQLHelper.cs类的方法进行功能操作。
如图:
(图好丑哦..)
继续看PetShop的SQLHelper.cs
..闪人..
[1]SQLServerDAL的核心-->SQLHelper.cs
今天软考祝露哥 网工考试顺利!~~
Date: 2005-11-5
继续看...
SQLHelper由以下静态方法组成。分别为:
首先看一下PrepareCommand()。因为ExecuteNonQuery() 、ExecuteReader()、ExecuteScalar()都调用了PrepareCommand()!
SqlCommand cmd, //要执行的sql语句或存储过程
SqlConnection conn, //数据库连接字符串
SqlTransaction trans, //数据库事务
CommandType cmdType, //执行数据库的类型 text or StoredProcedure
string cmdText, //SQL语句(存储过程和T-SQL命令)
SqlParameter[] cmdParms //SqlCommand的参数
PrepareCommand主要确定数据库状态是否打开,如果状态为关闭的则打开数据库。数据库事务trans是否不为空,如果不为空则执行事务操作。另外返回一些参数。
再来看看 CacheParameters()在SQLHelper类中首先申明了一个私有静态Hashtable域,该域主要用于存放SQL的参数(cmdParms)。它将在 CacheParameters()用到。
SQLHelper还定义了一个获取缓存参数方法 GetCacheParameters() 用于从Cache中获取缓冲的SQL参数。
public
static
SqlParameter[] GetCachedParameters(
string
cacheKey)
{
SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
//如果缓存中为nll则返回空值
if (cachedParms == null)
return null;
SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
//复制Cache中的参数
for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
return clonedParms;
}
简单的看了一下预先处理的方法再来看三个数据读取的方法咯~
ExecuteNonQuery()方法的主要功能是返回受影响的行。它有两个重载,下面列出所有ExecuteNonQuery的重载方法。
ExecuteNonQuery方法
public
static
int
ExecuteNonQuery(
string
connString, CommandType cmdType,
string
cmdText,
params
SqlParameter[] cmdParms)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
SqlCommand cmd = new SqlCommand();
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
using (SqlConnection conn = new SqlConnection(connString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
参数依次为:数据库连接字符串,执行命令类型,SQL语句和SQL参数数组。
那个using(...)表示数据库操作完成后,自动关闭数据库连接,释放系统资源。
ExecuteNonQuery方法(重载一)
public
static
int
ExecuteNonQuery(SqlConnection conn, CommandType cmdType,
string
cmdText,
params
SqlParameter[] cmdparms)
ExecuteNonQuery方法(重载二)
public
static
int
ExecuteNonQuery(SqlTransaction trans, CommandType cmdType,
string
cmdText,
params
SqlParameter[] cmdParms)
ExecuteReader方法
最后还有一个ExecuteScalar方法。该提供了一个重载。它的主要用途是返回影响数据库记录的行数对象。
public
static
object
ExecuteScalar(
string
connstring, CommandType cmdType,
string
cmdText,
params
SqlParameter[] cmdParms)
ExecuteScalar重载
public
static
object
ExecuteScalar(SqlConnection conn, CommandType cmdType,
string
cmdText,
params
SqlParameter[] cmdParms)
PetShop的SqlHelper.cs基本上看完了。在后面的调用中还会返回来学习SQLHelper.cs的。先闪...
SQL数据访问层设计专为SQLServer数据库而设计的。该层是整个项目的最底层!
SQLHelper通过一组静态方法来封装数据的访问功能。
(DAAB.NET SQLHelper完整代码偶全打印出来了,删了一些注释后共21页。留着上课不想听课时候看。)
SQLHelper.cs类为 Account.cs 、Inventory.cs、Item.cs、Order.cs、Product.cs、Profile.cs 六个类的基类。他们都调用SQLHelper.cs类的方法进行功能操作。
如图:
(图好丑哦..)
继续看PetShop的SQLHelper.cs
![](https://i-blog.csdnimg.cn/blog_migrate/24fed97ec5d1769ecd57e7eef56defc9.gif)
[1]SQLServerDAL的核心-->SQLHelper.cs
今天软考祝露哥 网工考试顺利!~~
Date: 2005-11-5
继续看...
SQLHelper由以下静态方法组成。分别为:
public
static
int
ExecuteNonQuery()
public static SqlDataReader ExecuteReader()
public static object ExecuteScalar()
public static void CacheParameters()
public static SqlParameter[] GetCachedParameters()
private static void PrepareCommand()
public static SqlDataReader ExecuteReader()
public static object ExecuteScalar()
public static void CacheParameters()
public static SqlParameter[] GetCachedParameters()
private static void PrepareCommand()
首先看一下PrepareCommand()。因为ExecuteNonQuery() 、ExecuteReader()、ExecuteScalar()都调用了PrepareCommand()!
private
static
void
PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType,
string
cmdText, SqlParameter[] cmdParms)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
//连接状态是否打开
if (conn.State != ConnectionState.Open)
conn.Open();
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
cmd.Connection = conn;
cmd.CommandText = cmdText;
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
//Transact-SQL 事务
if (trans != null)
cmd.Transaction = trans;
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
cmd.CommandType = cmdType;
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
PrepareCommand()有6个参数
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/b854634c0904529d4018c4c3336be836.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/b854634c0904529d4018c4c3336be836.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
SqlCommand cmd, //要执行的sql语句或存储过程
SqlConnection conn, //数据库连接字符串
SqlTransaction trans, //数据库事务
CommandType cmdType, //执行数据库的类型 text or StoredProcedure
string cmdText, //SQL语句(存储过程和T-SQL命令)
SqlParameter[] cmdParms //SqlCommand的参数
PrepareCommand主要确定数据库状态是否打开,如果状态为关闭的则打开数据库。数据库事务trans是否不为空,如果不为空则执行事务操作。另外返回一些参数。
再来看看 CacheParameters()在SQLHelper类中首先申明了一个私有静态Hashtable域,该域主要用于存放SQL的参数(cmdParms)。它将在 CacheParameters()用到。
public
static
void
CacheParameters(
string
cacheKey,
//
缓冲的标识关键字
params SqlParameter[] cmdParms) // SQL参数数组
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
parmCache[cacheKey] = cmdParms;
}
params SqlParameter[] cmdParms) // SQL参数数组
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/b854634c0904529d4018c4c3336be836.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
SQLHelper还定义了一个获取缓存参数方法 GetCacheParameters() 用于从Cache中获取缓冲的SQL参数。
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/b854634c0904529d4018c4c3336be836.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
ExecuteNonQuery()方法的主要功能是返回受影响的行。它有两个重载,下面列出所有ExecuteNonQuery的重载方法。
ExecuteNonQuery方法
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/b854634c0904529d4018c4c3336be836.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/b854634c0904529d4018c4c3336be836.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
那个using(...)表示数据库操作完成后,自动关闭数据库连接,释放系统资源。
ExecuteNonQuery方法(重载一)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/None.gif)
ExecuteReader方法
public
static
SqlDataReader ExecuteReader(
string
connString, CommandType cmdType,
string
cmdText,
params
SqlParameter[] cmdParms)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connString);
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
}catch
{
conn.Close();
throw;
}
}
ExecuteReader包含四个参数:数据库连接字符串、数据库执行类型、SQL语句和SQL参数数组。主要作用是提供一种从数据库读取只进的行流的一种方式。
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/b854634c0904529d4018c4c3336be836.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
// commandBehaviour.CloseConnection will not work
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/b854634c0904529d4018c4c3336be836.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/b854634c0904529d4018c4c3336be836.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
最后还有一个ExecuteScalar方法。该提供了一个重载。它的主要用途是返回影响数据库记录的行数对象。
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/None.gif)
![](http://lowkey.cnblogs.com/Images/OutliningIndicators/None.gif)
PetShop的SqlHelper.cs基本上看完了。在后面的调用中还会返回来学习SQLHelper.cs的。先闪...
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=818534