Microsoft.Practices.EnterpriseLibrary.Data 使用

Database db = null ;


#region 一般调用
db = DatabaseFactory.CreateDatabase( " Connection String " );
int count = ( int )db.ExecuteScalar(CommandType.Text, " SELECT Count(*) From cms_company " );
string message = string .Format( " There are {0} customers in the database " , count.ToString());
Response.Write(message);
#endregion


#region 带返回参数，返回值和返回数据集，一般参数的存储过程
// CREATE PROCEDURE [dbo].[kword]
// @kword varchar(250)='',
// @top int,
// @otop varchar(250) output
// As
// select top 10 * from Table1 where ntitle like '%'+@kword+'%' and id>@top
// declare @flag int
// select @flag=100
// SET @otop='返回值'
// return @flag
db = DatabaseFactory.CreateDatabase( " serverConnectionString " ); // 连接字符串变量名
DbCommand dbcomm = db.GetStoredProcCommand( " kword " ); // 存储过程名
db.AddInParameter(dbcomm, " @kword " , DbType.String, " 创业 " ); // 参数名 类型 值
db.AddInParameter(dbcomm, " top " , DbType.Int32, 2 ); // 参数名 类型 值
db.AddOutParameter(dbcomm, " otop " , DbType.String, 250 ); // output参数名 类型 长度
// 关键在这里，添加一个参数@RETURN_VALUE 类型为ReturnValue
db.AddParameter(dbcomm, " @RETURN_VALUE " , DbType.String, ParameterDirection.ReturnValue, "" , DataRowVersion.Current, null );
DataSet ds = db.ExecuteDataSet(dbcomm); // 必须有执行的动作后面才能获取值
// title = (string)db.ExecuteScalar(dbcomm);如果返回只有一个数据，这样也是可以的
GridView1.DataSource = ds;
GridView1.DataBind();

Response.Write( " <br>output输出参数值： " + db.GetParameterValue(dbcomm, " otop " ).ToString());
// int testvalue = (int)dbcomm.Parameters["@RETURN_VALUE"].Value; // 另一种获取值的方式
Response.Write( " <br />return返回参数值： " + db.GetParameterValue(dbcomm, " RETURN_VALUE " ).ToString());
#endregion



#region 使用事务记录操作数据库
// CREATE TABLE [dbo].[Table1](
// [id] [int] IDENTITY(1,1) NOT NULL,
// [ntitle] [varchar](250) NOT NULL,
// [valuea] [varchar](250) NULL,
// CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
// (
// [ntitle] ASC
// )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
// ) ON [PRIMARY]
db = DatabaseFactory.CreateDatabase( " serverConnectionString " );
using (IDbConnection conn = db.CreateConnection())
{
conn.Open();
IDbTransaction _trans = conn.BeginTransaction();
try
{
DbCommand _cmd = db.GetSqlStringCommand( " INSERT INTO [Table1]([ntitle],[valuea]) VALUES(@ntitle,@valuea) " );
db.AddInParameter(_cmd, " ntitle " , DbType.String, " AA " );
db.AddInParameter(_cmd, " valuea " , DbType.String, " AA " );
db.ExecuteNonQuery(_cmd, _trans as DbTransaction);
db.ExecuteNonQuery(_cmd, _trans as DbTransaction); // ntitle字段上建有唯一索引，故第二次插入同样记录时会报错
_trans.Commit();
}
catch
{
try
{
_trans.Rollback(); // 事务提交失败时，则回滚(是否回滚成功，可查看表中有无AA的记录即可)
}
catch { }
}
finally
{
conn.Close();
}
}
#endregion