Key Learnings

Tuesday, March 27, 2012

Getting the identity of the most recently added record

http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

SqlCommand command = new SqlCommand(SQL, Conn, Trans);
                command.Parameters.Add(new SqlParameter("@Id",SqlDbType.Int));
                command.Parameters["@Id"].Direction = ParameterDirection.Output;
               // command.Parameters["@Id"].SourceColumn = "Id";
              // cmdInsert.Parameters.Add(new SqlParameter("@CustomerName", SqlDbType.VarChar, 50, "CustomerName"));
                command.Parameters.AddWithValue("@token", SqlDbType.VarChar).Value = account.Token;

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

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;
}
}

Wednesday, February 29, 2012

mail Asynchronously delegate callback

Public Sub sendMail(ByVal subject As String, ByVal message As String)

Try

Dim destinations As New ArrayList

destinations.Add(123@hotmail.com)

Dim errDesc As String

'mail is another function to send email

mail("mail server", "username", "password", "from", subject, message, destinations, Nothing, Nothing, errDesc)

Catch

End Try

End Sub

Public Class SendMailAsyn

Public Sub sendMailAsyn(ByVal subject As String, ByVal message As String)

Dim dlgt As New SendMailDelegate(AddressOf sendMail)

Dim cb As New AsyncCallback(AddressOf SendEmailResponse)

dlgt.BeginInvoke(subject, message, cb, dlgt)

End Sub

Public Delegate Sub SendMailDelegate(ByVal subject As String, ByVal message As String)

'callback method

Public Sub SendEmailResponse(ByVal ar As IAsyncResult)

Dim dlgt As SendMailDelegate = CType(ar.AsyncState, SendMailDelegate)

dlgt = ar.AsyncState

dlgt.EndInvoke(ar)

End Sub

End Class

'catch exception and send email

Dim mail As New SendMailAsyn

mail.sendMailAsyn("Exception", ex.Message)

Sunday, February 12, 2012

while
int n = 1;
      while (n < 6)
      {
         Console.WriteLine("Current value of n is {0}", n);
         n++;
      }
do
      int x;
      int y = 0;
      do
      {
         x = y++;
         Console.WriteLine(x);
      }
      while(y < 5);
for
for (int i = 1; i <= 5; i++)
         Console.WriteLine(i);

for each
public static void Main()
   {
      int odd = 0, even = 0;
      int[] arr = new int [] {0,1,2,5,7,8,11};
      foreach (int i in arr)
      {
         if (i%2 == 0)
            even++;
         else
            odd++;
      }
      Console.WriteLine("Found {0} Odd Numbers, and {1} Even Numbers.",
                        odd, even) ;
   }

Transaction

1.ADO.net
using(Sqlconnection con = new SqlConnection(constr))
{
SqlTransaction transaction;
try{
con.Open();
transactiom = con.BeginTransacton();
SqlCommand cmd = new SqlCommand();
com.Connection = con;
cmd.Transaction = transaction;
cmd.CommandText="Sql1";
cmd.ExecuteNonQuery();
transaction.Commit();
Console.Write("successeful");
}
catch {
transaction.Rollback();
Console.WriteLine("failed");
}
}

2.直接写入到sql 中使用 BEGIN TRANS, COMMIT TRANS, ROLLBACK TRANS 实现：
例如
BEGIN TRANS
DECLARE @orderDetailsError int, @productError int
DELETE FROM /"Order Details/" WHERE ProductID=42
SELECT @orderDetailsError = @@ERROR
DELETE FROM Products WHERE ProductID=42
SELECT @productError = @@ERROR
IF @orderDetailsError = 0 AND @productError = 0
COMMIT TRANS
ELSE
ROLLBACK TRANS
这种方法比较简单，具体可以查阅相关sql server 帮助

Sunday, February 5, 2012

try catch & DB Connection

SqlConnection conn = null;
SqlCommand cmd = null;

try
{
    conn = new SqlConnection(Settings.Default.qlsdat_extensionsConnectionString)
    cmd = new SqlCommand(reportDataSource, conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@Year", SqlDbType.Char, 4).Value = year;
    cmd.Parameters.Add("@startDate", SqlDbType.DateTime).Value = start;
    cmd.Parameters.Add("@endDate", SqlDbType.DateTime).Value = end;
        cmd.Open();

    DataSet dset = new DataSet();
    new SqlDataAdapter(cmd).Fill(dset);
    this.gridDataSource.DataSource = dset.Tables[0];
}
catch(Exception ex)
{
    Logger.Log(ex);
    throw;
}
finally
{
    if(conn != null)
        conn.Dispose();

        if(cmd != null)
        cmd.Dispose();
}

*****************
Try
Catch ex As Exception
'Exception handling here
Finally 'Clean Up
If Conn.State = ConnectionState.Open Then Conn.Close()
If Conn IsNot Nothing Then Conn.Dispose()
If cmd IsNot Nothing Then cmd.Dispose()
'AndAlso only tests 2nd condition if 1st is 'True'
If myReader IsNot Nothing AndAlso Not (myReader.IsClosed) Then myReader.Close()
End Try

************
Public Function ExecuteNonQuery(ByVal cmd As String, ByVal cmdType As CommandType, Optional ByVal parameters() As SqlParameter = Nothing) As Integer
            Dim connection As SqlConnection = Nothing
            Dim transaction As SqlTransaction = Nothing
            Dim command As SqlCommand = Nothing
            Dim res As Integer = -1
            Try
                connection = New SqlConnection(_connectionString)
                command = New SqlCommand(cmd, connection)
                command.CommandType = cmdType
                Me.AssignParameters(command, parameters)
                connection.Open()
                transaction = connection.BeginTransaction()
                command.Transaction = transaction
                res = command.ExecuteNonQuery()
                transaction.Commit()
            Catch ex As Exception
                If Not (transaction Is Nothing) Then
                    transaction.Rollback()
                End If
                Throw New SqlDatabaseException(ex.Message, ex.InnerException)
            Finally
                If Not (connection Is Nothing) AndAlso (connection.State = ConnectionState.Open) Then connection.Close()
                If Not (command Is Nothing) Then command.Dispose()
                If Not (transaction Is Nothing) Then transaction.Dispose()
            End Try
            Return res
        End Function

************
Finally
                'Lily on Feb 2012
                If Not (reader.IsClosed) Then
                    reader.Close()
                End If
                If reader IsNot Nothing Then
                    reader = Nothing
                End If
                If Not (ppResult Is Nothing) Then
                    ppResult = Nothing
                End If
                If Not (pp Is Nothing) Then
                    pp.Dispose()
                    pp = Nothing
                End If
                If cmd IsNot Nothing Then
                    cmd.Dispose()
                End If
                cmd = Nothing
                If conn.State = ConnectionState.Open Then
                    conn.Close()
                End If
                If conn IsNot Nothing Then
                    conn.Dispose()
                End If
                conn = Nothing
                If flagAR = False Then
                    Threading.Thread.CurrentThread.Sleep(timeReTry)
                End If
            End Try