EnterPrise Lbrary学习笔记（四） 数据访问模块

1.获取数据库连接

db = DatabaseFactory.CreateDatabase();

db = DatabaseFactory.CreateDatabase("DataAccessQuickStart");

二者的差异在于是否在配置文件中指定默认的数据库连接字符串，若未指定默认数据库连接，则根据名称读取对应连接字符串

<connectionStrings>
  <add
          name="DataAccessQuickStart"
          providerName="System.Data.SqlClient"
          connectionString="server=.;database=EntLibQuickStarts;Integrated Security=true" />
</connectionStrings>
<dataConfiguration defaultDatabase="DataAccessQuickStart"/>

2.执行sql

1)执行Sql语句

string sqlCommand = "Select CustomerID, Name, Address, City, Country, PostalCode " +
    "From Customers";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);

2）执行存储过程

string sqlCommand = "GetProductsByCategory";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

3）添加输入输出参数

db.AddInParameter(dbCommand, "ProductID", DbType.Int32, productID);
db.AddOutParameter(dbCommand, "ProductName", DbType.String, 50);
db.AddOutParameter(dbCommand, "UnitPrice", DbType.Currency, 8);

3.执行操作并返回值

1）返回datareader列表

StringBuilder readerData = new StringBuilder();
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
    while (dataReader.Read())
    {
        readerData.Append(dataReader["Name"]);
        readerData.Append(Environment.NewLine);
    }
}

2）返回dataset

DataSet productsDataSet = null;
productsDataSet = db.ExecuteDataSet(dbCommand);

3）执行不返回值

db.ExecuteNonQuery(dbCommand);

返回受影响的行数

例子

string sqlCommand = "GetProductDetails";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

db.AddInParameter(dbCommand, "ProductID", DbType.Int32, productID);
db.AddOutParameter(dbCommand, "ProductName", DbType.String, 50);
db.AddOutParameter(dbCommand, "UnitPrice", DbType.Currency, 8);

db.ExecuteNonQuery(dbCommand);

string results = string.Format(CultureInfo.CurrentCulture, "{0}, {1}, {2:C} ",
                               db.GetParameterValue(dbCommand, "ProductID"),
                               db.GetParameterValue(dbCommand, "ProductName"),
                               db.GetParameterValue(dbCommand, "UnitPrice"));

有输出参数的可以返回查询结果第一行输出参数对应列

4）返回查询结果的值

string productName = (string) db.ExecuteScalar(dbCommand);