- 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
转载于:https://www.cnblogs.com/jordan2009/archive/2011/04/18/2020070.html