我正在使用Microsoft Visual Studio的DAL，使用常规的方法从网站数据库的ItemDetails表检索数据来显示网站上列出的项目的评论，并创建ItemDetails.aspx文件。我添加了一个DropDownList Control以显示其类别中的所有项目。在从下拉列表中选择类别后，它会显示该类别中的所有项目，并附有一个带有链接"Show Details"的超链接以在网格视图中显示详细信息。我是新手，不知道如何为asp.net网站创建DAL。需要简单易懂的指南来创建asp.net网站的DAL。除SQLadapter之外还有哪些其他创建DAL的方法。
为ASP.NET网站创建数据访问层（DAL）
5
- Sunishtha Singh
3
2个回答
1
以下是一个参考样本............
public List<T> GetRequests(string strNo)
{
List<T> objlstMapping = null;
Mapping objMapping = null;
try
{
Database objDbInstance = CreateSQLDatabase(DbConnection.MF);
using (DbCommand objDbCommand = objDbInstance.GetStoredProcCommand(Constants.SP_QUESTS))
{
DALBase.AddDbParam(objDbInstance, objDbCommand, "@No", DbType.AnsiString, ParameterDirection.Input, strFolioNo);
objDbCommand.Connection = objDbInstance.CreateConnection();
objDbCommand.Connection.Open();
using (DbDataReader dr = objDbCommand.ExecuteReader(CommandBehavior.CloseConnection))
{
objMapping = new List<T>();
if (dr.HasRows)
{
while (dr.Read())
{
objMapping = new BrokerFolioMapping();
objMapping .Brok_Code = SProposedValue(dr, "Code");
objMapping .Active = SProposedValue(dr, "Status");
objMapping .AccStmt_Active = SProposedValue(dr, "PortfolioStatus");
objlstFolioMapping.Add(objMapping );
}
}
}
}
}
catch (Exception ex)
{
}
return objlstFolioMapping;
}
- Mangal
1
例如，这里是我以前用过的一个DAL，用于调用存储过程。
它允许您执行存储过程并返回数据集、数据表、成功响应等。
实际上，这取决于您打算如何访问数据，您会编写存储过程还是在代码中使用查询。您也可以选择使用Entity Framework/LINQ。
using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
public class _DataInteraction
{
#region "Stored Procedures"
public static DataTable stdReturnDataTableQuery(string procedureName, string db)
{
DataTable myDataTable;
SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[db].ConnectionString);
SqlCommand cmd = new SqlCommand();
SqlDataAdapter myDataAdapter = new SqlDataAdapter();
cmd.CommandText = procedureName;
cmd.CommandType = CommandType.Text;
cmd.Connection = myConnection;
//-----------------------------------------------------------------------
// make our datatable to return
//-----------------------------------------------------------------------
myDataTable = new DataTable();
//-----------------------------------------------------------------------
// fill the datatable with the stored procedure results
//-----------------------------------------------------------------------
try
{
myConnection.Open();
myDataAdapter.SelectCommand = cmd;
myDataAdapter.Fill(myDataTable);
}
catch (Exception ex)
{
//flag as error happened
throw ex;
}
finally
{
myConnection.Close();
if ((myDataAdapter != null))
myDataAdapter.Dispose();
if ((cmd != null))
cmd.Dispose();
}
return myDataTable;
}
// Return a datatable from the database
public static DataTable stdReturnDataTable(string procedureName, List<SqlParameter> myParameters, string db)
{
SqlConnection myConnection = default(SqlConnection);
SqlCommand myCommand = default(SqlCommand);
SqlDataAdapter myDataAdapter = default(SqlDataAdapter);
DataTable myDataTable = default(DataTable);
string connString = null;
// -----------------------------------------------------------------------
// create instance of connection
// -----------------------------------------------------------------------
connString = ConfigurationManager.ConnectionStrings[db].ConnectionString;
myConnection = new SqlConnection();
myConnection.ConnectionString = connString;
//-----------------------------------------------------------------------
// create instance of command and dataadapter
//-----------------------------------------------------------------------
myCommand = new SqlCommand(procedureName, myConnection);
myDataAdapter = new SqlDataAdapter(myCommand);
//-----------------------------------------------------------------------
// say its a stored procedure command
//-----------------------------------------------------------------------
myCommand.CommandType = CommandType.StoredProcedure;
//-----------------------------------------------------------------------
// add any parameters?
//-----------------------------------------------------------------------
if ((myParameters != null))
{
foreach (SqlParameter myParm in myParameters)
{
// add the parameter to the command
myCommand.Parameters.Add(myParm);
}
}
//-----------------------------------------------------------------------
// make our datatable to return
//-----------------------------------------------------------------------
myDataTable = new DataTable();
//-----------------------------------------------------------------------
// fill the datatable with the stored procedure results
//-----------------------------------------------------------------------
try
{
myConnection.Open();
myDataAdapter.Fill(myDataTable);
}
catch (Exception ex)
{
//flag as error happened
throw ex;
}
finally
{
myConnection.Close();
if ((myDataAdapter != null))
myDataAdapter.Dispose();
if ((myCommand != null))
myCommand.Dispose();
}
return myDataTable;
}
// Return a dataset from the database
public static DataSet stdReturnDataset(string procedureName, List<SqlParameter> myParameters, string db)
{
SqlConnection myConnection = default(SqlConnection);
SqlCommand myCommand = default(SqlCommand);
SqlDataAdapter myDataAdapter = default(SqlDataAdapter);
DataSet ds = new DataSet();
string connString = null;
//-----------------------------------------------------------------------
// create instance of connection
//-----------------------------------------------------------------------
connString = ConfigurationManager.ConnectionStrings[db].ConnectionString;
myConnection = new SqlConnection();
myConnection.ConnectionString = connString;
//-----------------------------------------------------------------------
// create instance of command and dataadapter
//-----------------------------------------------------------------------
myCommand = new SqlCommand(procedureName, myConnection);
myDataAdapter = new SqlDataAdapter(myCommand);
//-----------------------------------------------------------------------
// say its a stored procedure command
//-----------------------------------------------------------------------
myCommand.CommandType = CommandType.StoredProcedure;
//-----------------------------------------------------------------------
// add any parameters?
//-----------------------------------------------------------------------
if ((myParameters != null))
{
foreach (SqlParameter myParm in myParameters)
{
// add the parameter to the command
myCommand.Parameters.Add(myParm);
}
}
//-----------------------------------------------------------------------
// fill the datatable with the stored procedure results
//-----------------------------------------------------------------------
try
{
myConnection.Open();
myDataAdapter.Fill(ds);
}
catch (Exception ex)
{
//flag as error happened
throw ex;
}
finally
{
myConnection.Close();
if ((myDataAdapter != null))
myDataAdapter.Dispose();
if ((myCommand != null))
myCommand.Dispose();
}
return ds;
}
// Return success from a query from the database
public static bool db_NonQuerySuccessResponse(string strCommandText, List<SqlParameter> myParameters, string db)
{
SqlConnection SQLConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[db].ConnectionString);
SqlCommand SQLCommand = new SqlCommand();
DataSet ds = new DataSet();
string Value = "";
bool success = false;
try
{
SQLCommand.CommandText = strCommandText;
SQLCommand.CommandType = CommandType.StoredProcedure;
SQLCommand.Parameters.Clear();
if ((myParameters != null))
{
foreach (SqlParameter myParm in myParameters)
{
// add the parameter to the command
SQLCommand.Parameters.Add(myParm);
}
}
SQLCommand.Connection = SQLConnection;
SQLConnection.Open();
SQLCommand.ExecuteNonQuery();
SQLConnection.Close();
success = true;
}
catch (Exception ex)
{
success = false;
return success;
}
return success;
}
// General non query, no results no success
public static bool db_NonQuery(string strCommandText, List<SqlParameter> myParameters, string db)
{
SqlConnection SQLConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[db].ConnectionString);
SqlCommand SQLCommand = new SqlCommand();
DataSet ds = new DataSet();
try
{
SQLCommand.CommandText = strCommandText;
SQLCommand.CommandType = CommandType.StoredProcedure;
SQLCommand.Parameters.Clear();
if ((myParameters != null))
{
foreach (SqlParameter myParm in myParameters)
{
// add the parameter to the command
SQLCommand.Parameters.Add(myParm);
}
}
SQLCommand.Connection = SQLConnection;
SQLConnection.Open();
SQLCommand.ExecuteNonQuery();
SQLConnection.Close();
}
catch (Exception ex)
{
return false;
}
return true;
}
//// Execute scalar on db
//public static string db_Scalar(string strCommandText, ref List<SqlParameter> myParameters, string db)
//{
// SqlConnection SQLConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[db].ConnectionString);
// SqlCommand SQLCommand = new SqlCommand();
// string Value = "";
// SQLCommand.CommandText = strCommandText;
// SQLCommand.CommandType = CommandType.StoredProcedure;
// SQLCommand.Parameters.Clear();
// if ((myParameters != null))
// {
// foreach (SqlParameter myParm in myParameters)
// {
// // add the parameter to the command
// SQLCommand.Parameters.Add(myParm);
// }
// }
// SQLCommand.Connection = SQLConnection;
// SQLConnection.Open();
// Value = SQLCommand.ExecuteScalar;
// SQLConnection.Close();
// return Value;
//}
#endregion
}
- Ryan McDonough
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
User user = UserRepo.Single(x=>x.Username=="Bob");再加入UnitOfWork模式来保存更改，这是一个非常优雅而相对高效的ORM。需要注意的是，其主要缺点是批量更新可能会很慢 - 当您一次性更新数十万条记录时，最好退回到Sql适配器。 - BasicUPDATE A set B=C WHERE Id = 1，UPDATE A set B=C WHERE Id = 2等，而不是UPDATE A set B=C WHERE Id IN (1,2)，但除非您同时对数千行执行相同的更新，否则这不是问题。 - Basic