利用sqlhelper执行添加操作

 有一年没碰.net了，基本上所有的都忘记得差不多了。这几天重新熟悉这个，首先熟悉下用sqlhelper和Property属性类进行添加操作。

1、定义属性

 

using System; namespace shop.Model { [Serializable] /// <summary> /// 订单信息属性 订单实体 /// </summary> public class order { private int orderId; private string name; private int number; private DateTime date; public order() { // // TODO: 在此处添加构造函数逻辑 // } /// <summary> /// 自定义构造函数 /// </summary> /// <param name="orderId">订单ID</param> /// <param name="name">订单 名称</param> /// <param name="number">订单数量</param> /// <param name="dateTime">订单日期</param> public order(int orderId,string name,int number,DateTime date) { this.orderId = orderId; this.name = name; this.number = number; this.date = date; } //Properties public int OrderID { get { return orderId; } set { orderId = value; } } public string Name { get { return name; } set { name = value; } } public int Number { get { return number; } set { number = value; } } public DateTime Date { get { return date; } set { date = value; } } } } 

2、定义一个数据访问层接口。接口名为IOrder.代码如下

using System; using shop.Model; namespace shop.IDAL { /// <summary> /// IOder 的摘要说明 /// </summary> public interface IOrder { //添加订单 void InsertOrder(order orderInfo); /// <summary> /// 通过ID获取订单信息 /// </summary> /// <param name="orderID"></param> /// <returns></returns> order GetOrder(int orderID); } }

3、实现IOrder接口

using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Text; using System.Collections.Generic; using shop.DBUtility; using shop.Model; using shop.IDAL; namespace shop.SqlServerDAL { /// <summary> /// Order 的摘要说明 /// </summary> public class Order:IOrder { private const string SQL_INSERT_ORDER = "Declare @ID int; Declare @ERR int; INSERT INTO orderInfo VALUES (@orderID,@name,@number,@dateTime);SELECT @ID=@@IDENTITY;SELECT @ERR=@@ERROR; "; private const string SQL_SELECT_ORDER = "SELECT * FROM orderInfo";//SQL语句 public Order() { // // TODO: 在此处添加构造函数逻辑 // } //执行添加操作 public void InsertOrder(order orderInfo) { StringBuilder strSql = new StringBuilder(); SqlParameter[] orderParams = GetOrderParameter();//订单基本参数 SqlCommand cmd = new SqlCommand(); orderParams[0].Value = orderInfo.OrderID; orderParams[1].Value = orderInfo.Name; orderParams[2].Value = orderInfo.Number; orderParams[3].Value = orderInfo.Date; SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringOrderDistributedTransaction, CommandType.Text, SQL_INSERT_ORDER, orderParams); //用sqlhelper中定义的静态方法对数据的添加进行处理 } /// <summary> /// 获得订单参数 /// </summary> /// <returns></returns> public static SqlParameter[] GetOrderParameter() { SqlParameter[] parames = SqlHelperParameterCache.GetCachedParameterSet(SqlHelper.ConnectionStringOrderDistributedTransaction, SQL_INSERT_ORDER);//检验缓存中是否缓存参数，获取缓存中的参数 if (parames == null) { parames = new SqlParameter[]{ new SqlParameter("@orderID",SqlDbType.Int,10), new SqlParameter("@name",SqlDbType.VarChar,10), new SqlParameter("@number",SqlDbType.Int,10), new SqlParameter("@dateTime",SqlDbType.DateTime), }; //参数设置 SqlHelperParameterCache.CacheParameterSet(SqlHelper.ConnectionStringOrderDistributedTransaction, SQL_INSERT_ORDER, parames); }//设置缓存参数 return parames; } //获取订单数据 public order GetOrder(int orderID) { order orderInfo = new order(); SqlParameter pare = new SqlParameter("@orderID", SqlDbType.Int); pare.Value = orderID; using (SqlDataReader sd = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringOrderDistributedTransaction, CommandType.Text, SQL_SELECT_ORDER, pare)) { if (sd.Read()) { orderInfo = new order(orderID, sd.GetString(1), sd.GetInt32(2),sd.GetDateTime(3)); } } return orderInfo; } } }

4、web端实现操作(default.aspx.cs)

在该页面定义了个button按钮事件，对其实现添加。代码如下：

public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { order orderInfo = new order(2, "lee", 20,DateTime.Now);//对属性进行赋值 shop.SqlServerDAL.Order o = new shop.SqlServerDAL.Order(); o.InsertOrder(orderInfo);//调用添加方法。这样就成功添加完数据 } } 

5、利用sqlhelper.cs就执行完了对数据库的添加。这样执行，很好的处理了参数的问题。提高了安全性和性能。

写在这主要是怕以后又忘记了，做个备忘录。