学步园

程序中使用事务，处理无外键关系的表与表间的操作
                    //创建事务连接对象
                    SqlConnection myConn = new SqlConnection(ConfigurationSettings.AppSettings["conString"]);
                    try
                    {
                        myConn.Open();                            //打开连接                
                    }
                    catch(Exception ex)
                    {
                        labError.Text = "提示信息：连接数据库失败，请查看网络！" + ex.Message;
                        return;
                    }
                    SqlTransaction myTrans = myConn.BeginTransaction();     //主表事务
                    //将连接对象和事务对象传入用于处理
                    iSuccess = Test_Add(myConn,myTrans,iState,txtTopic.Text.Trim(),txtCont.Text.Trim());
                    if(iSuccess>0)
                    {
                        int iNum = 0;
                        //将附件存入数据库
                        for(int i=1;i<local.Length;i++)
                        {
                            //截取\User文件夹下的路径local[i]附件路径，多附件。
                            local[i]=local[i].Substring(local[i].IndexOf("\\User"));
                            iNum = Attachment(myConn,myTrans,iSuccess,local[i]);
                            if(iNum<1)
                            {
                                //
                                myTrans.Rollback();
                                myConn.Close();
                                myConn.Dispose();
                                labError.Text = "提示信息：附件加载失败，请查看网络！";
                                return;
                            }
                        }
                        iNum = 0;
                        //将接收邮件用户信息存入数据库
                        for(int i=0;i<ary_Email.Count;i++)
                        {
                            //添加邮件接收人的联系地址与用户名和议案调查的答题地址，因为不是议案调查所以答题地址为空
                            iNum = AddEmail(myConn,myTrans,iSuccess,ary_Name[i].ToString(),"",ary_Email[i].ToString());
                            if(iNum<1)
                            {
                                myTrans.Rollback();
                                myConn.Close();
                                myConn.Dispose();
                                labError.Text = "提示信息：接收邮件用户信息加载失败，请查看网络！";
                                return;
                            }
                        }

                        myTrans.Commit();    //事务结束
                        myConn.Close();        //关闭连接
                        myConn.Dispose();

                        iNum = 0;
                        //获取配置文件中的公司股票代码号
                        string tmpCode  = System.Configuration.ConfigurationSettings.AppSettings["Code"].ToString().Trim();
                        //写入分发中心审核信息表中
                        iNum = AddAuditing(tmpCode,iSuccess,txtTopic.Text.Trim(),txtCont.Text.Trim(),iCount,strUserName);
                        if(iNum<1)
                        {
                            labError.Text = "提示信息：写入失败，请查看网络！";
                            return;
                        }
                        
                        labError.Text = "提示信息：邮件已经发送成功！";
                    }
                    else
                    {
                        myTrans.Rollback();
                        myConn.Close();
                        myConn.Dispose();
                        labError.Text = "提示信息：发送失败，网络繁忙！";
                    }
                    
        //以下为处理函数*************
        public static int Attachment(SqlConnection myConnection,SqlTransaction myTrans,int iSendLogID, string strAttachment)
        {
            // Create Instance of Connection and Command Object
            //SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
            SqlCommand myCommand = new SqlCommand("MailAttachment_Add", myConnection);
            myCommand.Transaction = myTrans;
            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter SPSuccess = new SqlParameter("@iSuccess", SqlDbType.Int, 4);
            SPSuccess.Direction = ParameterDirection.Output;
            myCommand.Parameters.Add(SPSuccess);

            SqlParameter SPSendLogID = new SqlParameter("@iSendLogID", SqlDbType.Int);
            SPSendLogID.Value = iSendLogID;
            myCommand.Parameters.Add(SPSendLogID);

            SqlParameter SPAttachment = new SqlParameter("@Attachment", SqlDbType.VarChar, 200);
            SPAttachment.Value = strAttachment;
            myCommand.Parameters.Add(SPAttachment);

            // Open the database connection and execute the command
            int iMailAttachmentID = 0;
            try
            {
                //myConnection.Open();
                myCommand.ExecuteNonQuery();
                iMailAttachmentID = (int)SPSuccess.Value;
                //myConnection.Close();
            }
            catch(Exception ex)
            {
                Console.Write(ex.Message);
                return -1;
            }
            return iMailAttachmentID;
        }