Data Access Application实现任务
|
Task |
Methods |
|---|---|
|
Filling a DataSet and updating the database from a DataSet. |
ExecuteDataSet. Creates, populates, and returns a DataSet. LoadDataSet. Populates an existing DataSet. UpdateDataSet. Updates the database using an existing DataSet. |
|
Reading multiple data rows. |
ExecuteReader. Creates and returns a provider-independent DbDataReader instance. |
|
Executing a Command. |
ExecuteNonQuery. Executes the command and returns the number of rows affected. Other return values (if any) appear as output parameters. ExecuteScalar. Executes the command and returns a single value. |
|
Retrieving data as a sequence of objects. |
ExecuteSprocAccessor. Returns data selected by a stored procedure as a sequence of objects for client-side querying. ExecuteSqlStringAccessor. Returns data selected by a SQL statement as a sequence of objects for client-side querying. |
|
Retrieving XML data (SQL Server only). |
ExecuteXmlReader. Returns data as a series of XML elements exposed through an XmlReader. Note that this method is specific to the SqlDatabase class (not the underlying Database class). |
|
Creating a Command. |
GetStoredProcCommand. Returns a command object suitable for executing a stored procedure. GetSqlStringCommand. Returns a command object suitable for executing a SQL statement (which may contain parameters). |
|
Working with Command parameters. |
AddInParameter. Creates a new input parameter and adds it to the parameter collection of a Command. AddOutParameter. Creates a new output parameter and adds it to the parameter collection of a command. AddParameter. Creates a new parameter of the specific type and direction and adds it to the parameter collection of a command. GetParameterValue. Returns the value of the specified parameter as an Object type. SetParameterValue. Sets the value of the specified parameter. |
|
Working with transactions. |
CreateConnection. Creates and returns a connection for the current database that allows you to initiate and manage a transaction over the connection. |
Data Access Application的使用
一、Data Access Application Block的配置
二、建立Database实例
// Resolve the default Database object from the container. // The actual concrete type is determined by the configuration settings. Database defaultDB = EnterpriseLibraryContainer.Current.GetInstance<Database>(); // Resolve a Database object from the container using the connection string name. Database namedDB = EnterpriseLibraryContainer.Current.GetInstance<Database>("ExampleDatabase");
Some features are only available in the concrete types for a specific database. For example, the ExecuteXmlReader method is only available in the SqlDatabase class. If you want to use such features, you must cast the database type you instantiate to the appropriate concrete type. The following code creates an instance of the SqlDatabase class.
// Resolve a SqlDatabase object from the container using the default database.
SqlDatabase sqlServerDB
= EnterpriseLibraryContainer.Current.GetInstance<Database>() as SqlDatabase;
In addition to using configuration to define the databases you will use, the Data Access block allows you to create instances of concrete types that inherit from the Database class directly in your code, as shown here. All you need to do is provide a connection string that specifies the appropriate ADO.NET data provider type (such as SqlClient).
// Assume the method GetConnectionString exists in your application and
// returns a valid connection string.
string myConnectionString = GetConnectionString();
SqlDatabase sqlDatabase = new SqlDatabase(myConnectionString);
三、不带参数的数据查询
Simple queries consisting of an inline SQL statement or a stored procedure, which take no parameters, can be executed using the ExecuteReader method overload that accepts a CommandType value and a SQL statement or stored procedure name as a string.
The following code shows the simplest approach for a stored procedure, where you can also omit the CommandType parameter. The default is CommandType.StoredProcedure (unlike ADO.NET, where the default is CommandType.Text.)
// Call the ExecuteReader method by specifying just the stored procedure name.
using (IDataReader reader = namedDB.ExecuteReader("MyStoredProcName"))
{
// Use the values in the rows as required.
}
To use an inline SQL statement, you must specify the appropriate CommandType value, as shown here.
// Call the ExecuteReader method by specifying the command type
// as a SQL statement, and passing in the SQL statement.
using (IDataReader reader = namedDB.ExecuteReader(CommandType.Text,
"SELECT TOP 1 * FROM OrderList"))
{
// Use the values in the rows as required - here we are just displaying them.
DisplayRowValues(reader);
}
The example named Return rows using a SQL statement with no parameters uses this code to retrieve a DataReader containing the first order in the sample database, and then displays the values in this single row. It uses a simple auxiliary routine that iterates through all the rows and columns, writing the values to the console screen.
void DisplayRowValues(IDataReader reader)
{
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.WriteLine("{0} = {1}", reader.GetName(i), reader[i].ToString());
}
Console.WriteLine();
}
}
四、带数例参数的数据查询
// Call the ExecuteReader method with the stored procedure
// name and an Object array containing the parameter values.
using (IDataReader reader = defaultDB.ExecuteReader("ListOrdersByState",
new object[] { "Colorado" }))
{
// Use the values in the rows as required - here we are just displaying them.
DisplayRowValues(reader);
}
五、带命名参数的数据查询
// Read data with a SQL statement that accepts one parameter prefixed with @.
string sqlStatement = "SELECT TOP 1 * FROM OrderList WHERE State LIKE @state";
// Create a suitable command type and add the required parameter.
using (DbCommand sqlCmd = defaultDB.GetSqlStringCommand(sqlStatement))
{
defaultDB.AddInParameter(sqlCmd, "state", DbType.String, "New York");
// Call the ExecuteReader method with the command.
using (IDataReader sqlReader = namedDB.ExecuteReader(sqlCmd))
{
DisplayRowValues(sqlReader);
}
}
// Now read the same data with a stored procedure that accepts one parameter.
string storedProcName = "ListOrdersByState";
// Create a suitable command type and add the required parameter.
using (DbCommand sprocCmd = defaultDB.GetStoredProcCommand(storedProcName))
{
defaultDB.AddInParameter(sprocCmd, "state", DbType.String, "New York");
// Call the ExecuteReader method with the command.
using (IDataReader sprocReader = namedDB.ExecuteReader(sprocCmd))
{
DisplayRowValues(sprocReader);
}
}
六、返回数据转化为对象
通对数据访问层的ORM技术返回对象，使得客户端数据查询可以充分利用Linq查询的便利。Data Access block并不包含ORM解决方案，但可以使用sql查询结果返回的对象系列数据实现 IEnumerable接口
Accessor
The block provides two core classes for performing this kind of query: the SprocAccessor and the SqlStringAccessor. You can create and execute these accessors in one operation using the ExecuteSprocAccessor and ExecuteSqlAccessor methods of the Database class, or create a new accessor directly and then call its Execute method.
Accessors use two other objects to manage the parameters you want to pass into the accessor (and on to the database as it executes the query), and to map the values in the rows returned from the database to the properties of the objects it will return to the client code
创建和执行Accessor
// Create an object array and populate it with the required parameter values.
object[] paramArray = new object[] { "%bike%" };
// Create and execute a sproc accessor that uses the default
// parameter and output mappings.
var productData = defaultDB.ExecuteSprocAccessor<Product>("GetProductList",
paramArray);
// Perform a client-side query on the returned data. Be aware that
// the orderby and filtering is happening on the client, not in the database.
var results = from productItem in productData
where productItem.Description != null
orderby productItem.Name
select new { productItem.Name, productItem.Description };
// Display the results
foreach (var item in results)
{
Console.WriteLine("Product Name: {0}", item.Name);
Console.WriteLine("Description: {0}", item.Description);
Console.WriteLine();
}
创建和使用 Mappers
七、XML data
// Resolve a SqlDatabase object from the container using the default database.
SqlDatabase sqlServerDB
= EnterpriseLibraryContainer.Current.GetInstance<Database>() as SqlDatabase;
// Specify a SQL query that returns XML data.
string xmlQuery = "SELECT * FROM OrderList WHERE State = @state FOR XML AUTO";
// Create a suitable command type and add the required parameter
// NB: ExecuteXmlReader is only available for SQL Server databases
using (DbCommand xmlCmd = sqlServerDB.GetSqlStringCommand(xmlQuery))
{
xmlCmd.Parameters.Add(new SqlParameter("state", "Colorado"));
using (XmlReader reader = sqlServerDB.ExecuteXmlReader(xmlCmd))
{
// Iterate through the elements in the XmlReader
while (!reader.EOF)
{
if (reader.IsStartElement())
{
Console.WriteLine(reader.ReadOuterXml());
}
}
}
}
八、ExecuteScalar
// Create a suitable command type for a SQL statement.
// NB: For efficiency, aim to return only a single value or a single row.
using (DbCommand sqlCmd
= defaultDB.GetSqlStringCommand("SELECT [Name] FROM States"))
{
// Call the ExecuteScalar method of the command.
Console.WriteLine("Result using a SQL statement: {0}",
defaultDB.ExecuteScalar(sqlCmd).ToString());
}
// Create a suitable command type for a stored procedure.
// NB: For efficiency, aim to return only a single value or a single row.
using (DbCommand sprocCmd = defaultDB.GetStoredProcCommand("GetStatesList"))
{
// Call the ExecuteScalar method of the command.
Console.WriteLine("Result using a stored procedure: {0}",
defaultDB.ExecuteScalar(sprocCmd).ToString());
}
九、异步数据访问
十、更新数据
执行更新查询语句
Datasets更新
十一、管理数据库连接
十二、处理事务
https://docs.microsoft.com/en-us/previous-versions/msp-n-p/ff953187(v=pandp.50)
标签：type,SQL,Library,Access,Application,command,reader,using,parameter From： https://www.cnblogs.com/5x19/p/16584837.html