Key Learnings - Lily: March 2012

一个主从表，母子表更新示例

C#代码:
/// <summary>
/// Summary description for RelationalClass.
/// </summary>
class RelationalClass
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
//
// TODO: Add code to start application here
//
// Create the DataSet object
DataSet oDS = new DataSet();
SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Orders;Integrated Security=SSPI");
conn.Open();
// Create the DataTable "Orders" in the Dataset and the OrdersDataAdapter
SqlDataAdapter oOrdersDataAdapter = new SqlDataAdapter(new SqlCommand("SELECT * FROM Orders", conn));
oOrdersDataAdapter.InsertCommand = new SqlCommand("proc_InsertOrder", conn);
SqlCommand cmdInsert = oOrdersDataAdapter.InsertCommand;
cmdInsert.CommandType = CommandType.StoredProcedure;
cmdInsert.Parameters.Add(new SqlParameter("@OrderId", SqlDbType.Int));
cmdInsert.Parameters["@OrderId"].Direction = ParameterDirection.Output;
cmdInsert.Parameters["@OrderId"].SourceColumn = "OrderId";
cmdInsert.Parameters.Add(new SqlParameter("@CustomerName", SqlDbType.VarChar,50,"CustomerName"));
cmdInsert.Parameters.Add(new SqlParameter("@ShippingAddress", SqlDbType.VarChar,50,"ShippingAddress"));
oOrdersDataAdapter.FillSchema(oDS, SchemaType.Source);
DataTable pTable = oDS.Tables["Table"];
pTable.TableName = "Orders";
// Create the DataTable "OrderDetails" in the Dataset and the OrderDetailsDataAdapter
SqlDataAdapter oOrderDetailsDataAdapter = new SqlDataAdapter(new SqlCommand("SELECT * FROM OrderDetails", conn));

oOrderDetailsDataAdapter.InsertCommand = new SqlCommand("proc_InsertOrderDetails", conn);
cmdInsert = oOrderDetailsDataAdapter.InsertCommand;
cmdInsert.CommandType = CommandType.StoredProcedure;
cmdInsert.Parameters.Add(new SqlParameter("@OrderId", SqlDbType.Int));
cmdInsert.Parameters["@OrderId"].SourceColumn = "OrderId";
cmdInsert.Parameters.Add(new SqlParameter("@ProductId", SqlDbType.Int));
cmdInsert.Parameters["@ProductId"].SourceColumn = "ProductId";
cmdInsert.Parameters.Add(new SqlParameter("@ProductName", SqlDbType.VarChar,50,"ProductName"));
cmdInsert.Parameters.Add(new SqlParameter("@UnitPrice", SqlDbType.Decimal));
cmdInsert.Parameters["@UnitPrice"].SourceColumn = "UnitPrice";
cmdInsert.Parameters.Add(new SqlParameter("@Quantity", SqlDbType.Int ));
cmdInsert.Parameters["@Quantity"].SourceColumn = "Quantity";
oOrderDetailsDataAdapter.FillSchema(oDS, SchemaType.Source);
pTable = oDS.Tables["Table"];
pTable.TableName = "OrderDetails";

// Create the relationship between the two tables
oDS.Relations.Add(new DataRelation("ParentChild",
oDS.Tables["Orders"].Columns["OrderId"],
oDS.Tables["OrderDetails"].Columns["OrderId"]));
// Insert the Data
DataRow oOrderRow = oDS.Tables["Orders"].NewRow();
oOrderRow["CustomerName"] = "Customer ABC";
oOrderRow["ShippingAddress"] = "ABC street, 12345";
oDS.Tables["Orders"].Rows.Add(oOrderRow);
DataRow oDetailsRow = oDS.Tables["OrderDetails"].NewRow();
oDetailsRow["ProductId"] = 1;
oDetailsRow["ProductName"] = "Product 1";
oDetailsRow["UnitPrice"] = 1;
oDetailsRow["Quantity"] = 2;
oDetailsRow.SetParentRow(oOrderRow);
oDS.Tables["OrderDetails"].Rows.Add(oDetailsRow);
oOrdersDataAdapter.Update(oDS, "Orders");
oOrderDetailsDataAdapter.Update(oDS, "OrderDetails");
conn.Close();
}
}

在Entity Framework 4.0中（Ef4）中解决SaveChanges()前获取自增ID的问题
因为SaveChanges()是在事务内执行的，为了在其之间获取自增ID，赋值给另一个对象再保存，这里我们就得重新写事务：
    /// <summary>
    /// 用户数据交互
    /// </summary>
    public class User
    {
        private Entity.HappyOAEntities db = new Entity.HappyOAEntities();
        /// <summary>
        /// 注册新用户
        /// </summary>
        /// <param name="user"></param>
        /// <param name="login"></param>
        /// <returns></returns>
        public int Add(Entity.User user, Entity.Login login)
        {
            //特殊需求，自定义事务
            using (Entity.HappyOAEntities db2 = new Entity.HappyOAEntities())
            {
                db2.Connection.Open();
                using (var tran = db2.Connection.BeginTransaction())
                {
                    db2.Login.AddObject(login);
                    db2.SaveChanges();
                    user.ID = login.ID;
                    db2.User.AddObject(user);
                    db2.SaveChanges();
                    tran.Commit();
                    if (db2.Connection.State == System.Data.ConnectionState.Open)
                        db2.Connection.Close();
                }
            }
            return login.ID;
        }
    }

***********
   public int InsertOrder(OrderData order, List<OrderInfoData> orderinfolist)
{
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
SqlCommand cmd = new SqlCommand("insert into [order](Order_No, PurveyInfo_ID, User_ID, Order_Time, Down, Blank, YiTuiHui, ZaiTu, YiShouHuo) values(@order_no, @purveyinfo_id, @user_id, @order_time, 0, 0, 0, 0, 0)", conn);
SqlTransaction trans;
trans = conn.BeginTransaction();
cmd.Transaction = trans;
List<int> rlist = new List<int>();
try
{
cmd.Parameters.AddWithValue("@order_no", order.Order_No);
cmd.Parameters.AddWithValue("@purveyinfo_id", order.PurveyInfo_ID);
cmd.Parameters.AddWithValue("@user_id", order.User_ID);
cmd.Parameters.AddWithValue("@order_time", order.Order_Time);
cmd.ExecuteNonQuery();
cmd.CommandText = "select @@IDENTITY";
int i = Convert.ToInt32(cmd.ExecuteScalar());
foreach (OrderInfoData orderinfo in orderinfolist)
{
cmd.CommandText = "insert into orderInfo values(@order_id,@merchandiseinfo_id, @price, @quantity, 0)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@order_id", i);
cmd.Parameters.AddWithValue("@merchandiseinfo_id", orderinfo.MerchandiseInfo_ID);
cmd.Parameters.AddWithValue("@price", orderinfo.Price);
cmd.Parameters.AddWithValue("@quantity", orderinfo.Quantity);
cmd.ExecuteNonQuery();
}
trans.Commit();
return i;
}
catch (SqlException ex)
{
trans.Rollback();
throw ex;
}
finally
{
conn.Close();
}
}

***************
SaveChanges 在事务内进行操作。 SaveChanges 将回滚该事务，并且如果任何脏 ObjectStateEntry 对象无法继续，将引发异常。

我一直看不懂这句话的意思;; 一直以为savechanges就相当于一个事务!

然后我在网上查了下ado.net entity的事务大体上是通过DbTransaction和TransactionScope

所以就不懂了既然savechanges有事务的功能为什么还需要通过DbTransaction和TransactionScope

现在我有一个问题

我这里有两张表一张主表一张从表我要先添加主表再从主表中取它的id(自增型)加入从表中的一个属性这是一个事务

以前用ado.net时貌似是要这样写的
1 开始事务
2 添加主表数据
3 通过某一sql属性取主表的那个新id
4 添加另一张表数据
5 提交/回滚

现在我用ef来写这是我以前写过的一个成功添加了

C# code
public bool InsertOrder(OrderPri insertOrderPri,List insertOrderDetail)
{
try
{
smse = new SuperMarketSystemEntities();
smse.AddToOrderPris(insertOrderPri);//添加主表(此时insertOrderPri.OrderPriID无值)
foreach (var q in insertOrderDetail)
{
q.OrderPriID = insertOrderPri.OrderPriID;//给从表的OrderPriID添加值(此时已是生成的ID的值)
smse.AddToOrderDetails(q);
}
smse.SaveChanges();//操作成功了
return true;
}
catch
{
return false;
}
}

Key Learnings - Lily

Tuesday, March 27, 2012

Getting the identity of the most recently added record

一个主从表，母子表更新示例