Microsoft.Practices.EnterpriseLibrary.Data 使用笔记

最新推荐文章于 2021-04-05 05:05:42 发布

原创最新推荐文章于 2021-04-05 05:05:42 发布 · 4.1k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#table #statistics #cmd #null #database #dataset

本文介绍了如何使用C#进行数据库操作，包括基本的SQL查询、存储过程的调用及参数处理、事务处理等内容，并提供了具体的代码实现。

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