mojoportal中的数据访问层
来源:互联网 发布:个人记账软件pc 编辑:程序博客网 时间:2024/06/15 11:04
mojoportal可以支持多个数据库,通过web.config来配置.这里我们用访问Sqlserver数据库为例.对数据库的访问一共用到了两个文件,他们是静态类,方法也是静态的.
sqlhelper.cs:这个文件包括了SqlHelper和SqlHelperParameterCache类的实现. sqlhelper类是用来对sqlclient进行高性能和可升级的封装.SqlHelperParameterCache类用来缓存存储过程参数的.
dbPortal.cs这个文件为业务罗基层服务,调用存储过程,实现了对数据库表的各种原子逻辑操作.
现在用代码描述执行一个具体的原子逻辑操作的例子
dbportal.cs
public static string SiteUser_LoginByEmail(int SiteID, string Email, string Password)
{
SqlConnection Connection = GetConnection();//创建新的连接
SqlParameter[] arParams = new SqlParameter[4];//创建sql参数
if (ConfigurationManager.AppSettings["CacheMSSQLParameters"].ToLower() == "true")//是否启用参数缓存,如果运行在mono则不能启用
{
arParams = SqlHelperParameterCache.GetSpParameterSet(GetConnectionString(),
"mp_Users_LoginByEmail"); //在参数缓存中读取参数 若没有则在参数缓存中添加
arParams[0].Value = SiteID;//参数赋值
arParams[1].Value = Email;
arParams[2].Value = Password;
}
else
{
arParams[0] = new SqlParameter("@SiteID", SqlDbType.Int);//若没有启用参数缓存则手工建立参数
arParams[0].Direction = ParameterDirection.Input;//参数类型为输入
arParams[0].Value = SiteID;
arParams[1] = new SqlParameter("@Email", SqlDbType.NVarChar, 100);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = Email;
arParams[2] = new SqlParameter("@Password", SqlDbType.NVarChar, 128);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = Password;
arParams[3] = new SqlParameter("@UserName", SqlDbType.NVarChar, 100);
arParams[3].Direction = ParameterDirection.Output;//参数类型为输出
}
try
{
SqlHelper.ExecuteNonQuery(Connection,
CommandType.StoredProcedure,
"mp_Users_LoginByEmail",
arParams);//使用sqlhelper类执行存储过程
Connection.Close(); //关闭连接
if(arParams[3] != null)
{
return arParams[3].Value.ToString();
}
else
{
return string.Empty;
}
}
catch(Exception ex)
{
Connection.Close();
throw ex;
}
}
dbportal.cs
public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
{
return GetSpParameterSet(connectionString, spName, false);
}
public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
{//includeReturnValueParameter表示参数列表中是否有返回值
if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
}
using(SqlConnection connection = new SqlConnection(connectionString))//运用数据库连接池
{
return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
}
/// <summary>
/// Retrieves the set of SqlParameters appropriate for the stored procedure
/// </summary>
/// <param name="connection">A valid SqlConnection object</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
/// <returns>An array of SqlParameters</returns>
private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter":"");
SqlParameter[] cachedParameters;
cachedParameters = paramCache[hashKey] as SqlParameter[];//查询该参数是否被缓存
if (cachedParameters == null)
{
SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);//自动检测存储过程中的参数
paramCache[hashKey] = spParameters;//缓存该参数
cachedParameters = spParameters;
}
return CloneParameters(cachedParameters);
}
private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
SqlCommand cmd = new SqlCommand(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlCommandBuilder.DeriveParameters(cmd);//自动检测cmd对象中的参数,并生成参数列表
connection.Close();
if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);//由于自动检测参数时,会在参数列表的首项加入@Return_Value.如果不需要它时,就要移去.
}
SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
// Init the parameters with a DBNull value
foreach (SqlParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return discoveredParameters;
}
private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
{
SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];
for (int i = 0, j = originalParameters.Length; i < j; i++)
{
clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
}
return clonedParameters;
}
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
// Finally, execute the command
int retval = cmd.ExecuteNonQuery();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
return retval;
}
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection )
{
if( command == null ) throw new ArgumentNullException( "command" );
if( commandText == null || commandText.Length == 0 ) throw new ArgumentNullException( "commandText" );
// If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}
// Associate the connection with the command
command.Connection = connection;
// Set the command text (stored procedure name or SQL statement)
command.CommandText = commandText;
// If we were provided a transaction, assign it
if (transaction != null)
{
if( transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
command.Transaction = transaction;
}
// Set the command type
command.CommandType = commandType;
// Attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
到这里全部函数执行完毕
存储过程:
CREATE Procedure [dbo].[mp_Users_LoginByEmail]
@SiteID int,
@Email nvarchar(100),
@Password nvarchar(20),
@UserName nvarchar(100) OUTPUT
AS
SELECT
@UserName = Name
FROM
mp_Users
WHERE
SiteID = @SiteID
AND Email = @Email
AND [Password] = @Password
GO
sqlhelper.cs:这个文件包括了SqlHelper和SqlHelperParameterCache类的实现. sqlhelper类是用来对sqlclient进行高性能和可升级的封装.SqlHelperParameterCache类用来缓存存储过程参数的.
dbPortal.cs这个文件为业务罗基层服务,调用存储过程,实现了对数据库表的各种原子逻辑操作.
现在用代码描述执行一个具体的原子逻辑操作的例子
dbportal.cs
public static string SiteUser_LoginByEmail(int SiteID, string Email, string Password)
{
SqlConnection Connection = GetConnection();//创建新的连接
SqlParameter[] arParams = new SqlParameter[4];//创建sql参数
if (ConfigurationManager.AppSettings["CacheMSSQLParameters"].ToLower() == "true")//是否启用参数缓存,如果运行在mono则不能启用
{
arParams = SqlHelperParameterCache.GetSpParameterSet(GetConnectionString(),
"mp_Users_LoginByEmail"); //在参数缓存中读取参数 若没有则在参数缓存中添加
arParams[0].Value = SiteID;//参数赋值
arParams[1].Value = Email;
arParams[2].Value = Password;
}
else
{
arParams[0] = new SqlParameter("@SiteID", SqlDbType.Int);//若没有启用参数缓存则手工建立参数
arParams[0].Direction = ParameterDirection.Input;//参数类型为输入
arParams[0].Value = SiteID;
arParams[1] = new SqlParameter("@Email", SqlDbType.NVarChar, 100);
arParams[1].Direction = ParameterDirection.Input;
arParams[1].Value = Email;
arParams[2] = new SqlParameter("@Password", SqlDbType.NVarChar, 128);
arParams[2].Direction = ParameterDirection.Input;
arParams[2].Value = Password;
arParams[3] = new SqlParameter("@UserName", SqlDbType.NVarChar, 100);
arParams[3].Direction = ParameterDirection.Output;//参数类型为输出
}
try
{
SqlHelper.ExecuteNonQuery(Connection,
CommandType.StoredProcedure,
"mp_Users_LoginByEmail",
arParams);//使用sqlhelper类执行存储过程
Connection.Close(); //关闭连接
if(arParams[3] != null)
{
return arParams[3].Value.ToString();
}
else
{
return string.Empty;
}
}
catch(Exception ex)
{
Connection.Close();
throw ex;
}
}
dbportal.cs
public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
{
return GetSpParameterSet(connectionString, spName, false);
}
public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
{//includeReturnValueParameter表示参数列表中是否有返回值
if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
}
using(SqlConnection connection = new SqlConnection(connectionString))//运用数据库连接池
{
return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
}
/// <summary>
/// Retrieves the set of SqlParameters appropriate for the stored procedure
/// </summary>
/// <param name="connection">A valid SqlConnection object</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param>
/// <returns>An array of SqlParameters</returns>
private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter":"");
SqlParameter[] cachedParameters;
cachedParameters = paramCache[hashKey] as SqlParameter[];//查询该参数是否被缓存
if (cachedParameters == null)
{
SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);//自动检测存储过程中的参数
paramCache[hashKey] = spParameters;//缓存该参数
cachedParameters = spParameters;
}
return CloneParameters(cachedParameters);
}
private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
SqlCommand cmd = new SqlCommand(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlCommandBuilder.DeriveParameters(cmd);//自动检测cmd对象中的参数,并生成参数列表
connection.Close();
if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);//由于自动检测参数时,会在参数列表的首项加入@Return_Value.如果不需要它时,就要移去.
}
SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
// Init the parameters with a DBNull value
foreach (SqlParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return discoveredParameters;
}
private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
{
SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];
for (int i = 0, j = originalParameters.Length; i < j; i++)
{
clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
}
return clonedParameters;
}
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
// Finally, execute the command
int retval = cmd.ExecuteNonQuery();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
return retval;
}
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection )
{
if( command == null ) throw new ArgumentNullException( "command" );
if( commandText == null || commandText.Length == 0 ) throw new ArgumentNullException( "commandText" );
// If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}
// Associate the connection with the command
command.Connection = connection;
// Set the command text (stored procedure name or SQL statement)
command.CommandText = commandText;
// If we were provided a transaction, assign it
if (transaction != null)
{
if( transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
command.Transaction = transaction;
}
// Set the command type
command.CommandType = commandType;
// Attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
到这里全部函数执行完毕
存储过程:
CREATE Procedure [dbo].[mp_Users_LoginByEmail]
@SiteID int,
@Email nvarchar(100),
@Password nvarchar(20),
@UserName nvarchar(100) OUTPUT
AS
SELECT
@UserName = Name
FROM
mp_Users
WHERE
SiteID = @SiteID
AND Email = @Email
AND [Password] = @Password
GO
- mojoportal中的数据访问层
- PetStore中的数据访问机制(Model层)
- 数据访问层中的Helper类
- .net中的数据访问层的封装
- mojoportal中的事件与委托
- 理解mojoportal中的layout.Master文件
- mojoportal中的layout.Master文件标签理解
- 收藏:数据访问层
- 数据访问层DataAccess
- 数据访问层学习
- C#数据访问层
- 数据访问层
- 数据访问层!
- 数据访问层
- C# 数据访问层
- 创建数据访问层
- 数据访问层
- 数据访问层sqlhelper
- mojoportal中的事件与委托
- Ajax 简介——转载自IBM
- 岁月安好
- 用java获得某个月份的天数
- mojoportal自定义控件简介
- mojoportal中的数据访问层
- 如何创建一个自定义的http modules
- 关于 网页嵌入字体 的问题
- http handlers简介
- HTTP Modules简介
- 在Windows XP用Apache和Subversion建立开发环境
- 在windows xp中使用Subversion
- 客户端TCP实现简介
- RSS.NET