ASP.NET程序设计与事务

Question

1、写一个方法生成一个出库单号,格式如下:DB0000000001、DB0000000002、其中的1、2分别是主表的自增ID,也就是获取下一个自增的ID,以字母DB+十位数字+id的方式来拼凑，比如获取的ID值为234，那么就是DB0000000234,这个方法如何写啊?2、往主从表里添加记录，先添加主表，主表的关键字段有id(自增)、出库单号(这是出库单主表，也就是上述DB0000000001)等等。现在的需求是获取主表的出库单号，比如DB0000000001，然后在将数据插入子表,请问这个事务如何写啊？

Answer 1

1、 string strId = "12";strId = "DB" + strId.PadLeft(10, '0');strId就是DB00000000122、可以在存储过程中加事务

Answer 2

PadLeft(多少位,补的数字)2
//执行事务处理
public void DoTran()
{ 　//建立连接并打开
　SqlConnection myConn=GetConn();
　myConn.Open();
　SqlCommand myComm=new SqlCommand();
　//SqlTransaction myTran=new SqlTransaction();
　//注意，SqlTransaction类无公开的构造函数
　SqlTransaction myTran;
　//创建一个事务
　myTran=myConn.BeginTransaction();
　try
　{
　　//从此开始，基于该连接的数据操作都被认为是事务的一部分
　　//下面绑定连接和事务对象
　　myComm.Connection=myConn;
　　myComm.Transaction=myTran; //定位到pubs数据库
　　myComm.CommandText="USE pubs";
　　myComm.ExecuteNonQuery();//操作1
　　myComm.CommandText=""; //操作2
　　myComm.ExecuteNonQuery();
　　　//提交事务
　　myTran.Commit();
　}
　catch(Exception err)
　{
  myTran.rollback();
　　throw new ApplicationException("事务操作出错，系统信息："+err.Message);
　 }
　finally
　{
　　myConn.Close();
　 }
}

Answer 3

1.先查出数据库最大的发那个
//max = Max(code)
return System.Text.RegularExpressions.Regex.Replace(
                    max.Trim(),
                    @"\d{10}$"),
                    m => (Convert.ToInt32(m.Value) + 1).ToString("D10))
2.
如果用的ADO.Net，可用ExecuteScalar()执行如下SQL
Insert into T1 ....; Select Scope_Identity(T1)  --分号是必需的
如果用的Linq可以用TransactionScope类对实现隐式事务

Answer 4

用的是asp.net做的B/S架构的程序

Answer 5

    declare @keyID as nvarchar(20)
                        set @keyID=(取最大值的单号)                     insert into T1(ID,keyID)values("ID",@keyID)
                     insert into T2(ID,keyID)values("ID",@keyID)

Answer 6

第一个就用PadLeft然后补零前面拼加个DB就好
第二个   2楼的方法就不错

Answer 7

给你一个事务的示例，中间的处理可以忽略/// <summary>
    /// 保存周计划
    /// </summary>
    /// <param name="dt">原数据dt</param>
    public static void MonthPlanSave(DataTable dt, SysUser sysUser, string Year, string Month)
    {
        // 事务准备
        OracleTransaction tran = null;
        OracleConnection conn = new OracleConnection();
        conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["CRMSDATAConnectionString"].ConnectionString;
        try
        {
            // 数据库操作
            conn.Open();
            tran = conn.BeginTransaction();
            DataSetWorkPlanTableAdapters.MONTHPLANHEADTableAdapter taMhead = new DataSetWorkPlanTableAdapters.MONTHPLANHEADTableAdapter();
            DataSetWorkPlanTableAdapters.MONTHPLANBODYTableAdapter taMbody = new DataSetWorkPlanTableAdapters.MONTHPLANBODYTableAdapter();
            DataSetWorkPlanTableAdapters.WEEKPLANHEADTableAdapter taWhead = new DataSetWorkPlanTableAdapters.WEEKPLANHEADTableAdapter();
            DataSetWorkPlanTableAdapters.WEEKPLANBODYTableAdapter taWbody = new DataSetWorkPlanTableAdapters.WEEKPLANBODYTableAdapter();
            DataTable weekDt = WeekPlan.WeekPlanBodyForMonth(sysUser, decimal.Parse(Month.ToString()), decimal.Parse(Year.ToString()));            OracleTableAdapterHelper.SetTransaction(taMbody, tran);//设置事务
            //查询月计划表体数据
            DataSetWorkPlan.MONTHPLANBODYDataTable dtmbody = new DataSetWorkPlan.MONTHPLANBODYDataTable();
            taMbody.FillMonthBody(dtmbody, dt.Rows[0]["MonthplanID"].ToString());
            //删除月计划表体数据
            foreach (DataRow dr in dtmbody.Rows)
            {
                taMbody.Delete(dr["MonthplanID"].ToString(), sysUser.ID, dr["IndicatorID"].ToString(), dr["IndicatorClass"].ToString());
            }            OracleTableAdapterHelper.SetTransaction(taMhead, tran);//设置事务
            //查询月计划表头数据
            DataSetWorkPlan.MONTHPLANHEADDataTable dtMhead = new DataSetWorkPlan.MONTHPLANHEADDataTable();
            taMhead.FillMonthHead(dtMhead, sysUser.ID, decimal.Parse(Month.ToString()), decimal.Parse(Year.ToString()));
            // 删除月计划表头数据
            foreach (DataRow dr in dtMhead.Rows)
            {
                taMhead.Delete(dr["MonthplanID"].ToString(), sysUser.ID);
            }            OracleTableAdapterHelper.SetTransaction(taWbody, tran);//设置事务
            //查询周计划表体数据
            DataSetWorkPlan.WEEKPLANBODYDataTable dtWbody = new DataSetWorkPlan.WEEKPLANBODYDataTable();
            taWbody.FillWeekBody(dtWbody, weekDt.Rows[0]["WEEKPLANID"].ToString());
            //删除周计划表体数据
            foreach (DataRow dr in dtWbody.Rows)
            {
                foreach (DataRow drMonth in weekDt.Rows)
                {
                    taWbody.DeleteWeekBody(drMonth["WEEKPLANID"].ToString(), sysUser.ID, dr["IndicatorID"].ToString(), dr["IndicatorClass"].ToString());
                }
            }            OracleTableAdapterHelper.SetTransaction(taWhead, tran);//设置事务
            //查询周计划表头数据
            DataSetWorkPlan.WEEKPLANHEADDataTable dtWhead = new DataSetWorkPlan.WEEKPLANHEADDataTable();
            taWhead.FillWeekHeadForMonth(dtWhead, sysUser.ID, decimal.Parse(Year.ToString()), decimal.Parse(Month.ToString()));
            //删除周计划表头数据
            foreach (DataRow dr in dtWhead.Rows)
            {
                foreach (DataRow drMonth in weekDt.Rows)
                {
                    taWhead.DeleteWeekHead(drMonth["WEEKPLANID"].ToString(), sysUser.ID);
                }
            }            //New周计划GUID
            string Week1 = Guid.NewGuid().ToString();
            string Week2 = Guid.NewGuid().ToString();
            string Week3 = Guid.NewGuid().ToString();
            string Week4 = Guid.NewGuid().ToString();
            string Week5 = Guid.NewGuid().ToString();            //分别添加数据到表
            foreach (DataRow dr in dt.Rows)
            {
                //新增月计划表头
                taMhead.Insert(dr["MonthplanID"].ToString(), sysUser.ID.ToString(), sysUser.Corp.ID.ToString(), Int16.Parse(Year.ToString()), Int16.Parse(Month.ToString()), sysUser.ID.ToString(), DateTime.Now, "", "");
                //新增月计划表体
                taMbody.Insert(dr["MonthplanID"].ToString(), sysUser.ID, dr["IndicatorID"].ToString(), dr["IndicatorClass"].ToString(), dr["IndicatorClassName"].ToString(), dr["IndicatorName"].ToString(), dr["IndicatorAmount"].ToString(), dr["ActionStep"].ToString(), dr["Assessor"].ToString(), dr["Result"].ToString(), dr["Re"].ToString());
                //新增周计划表头
                for (int ii = 0; ii < 5; ii++)
                {
                    switch (ii)
                    {
                        case 0:
                            taWhead.Insert(Week1, sysUser.ID, sysUser.Corp.ID, Int16.Parse(Year.ToString()), Int16.Parse(Month.ToString()), sysUser.ID.ToString(), DateTime.Now, "", "");
                            break;
                        case 1:
                            taWhead.Insert(Week2, sysUser.ID, sysUser.Corp.ID, Int16.Parse(Year.ToString()), Int16.Parse(Month.ToString()), sysUser.ID.ToString(), DateTime.Now, "", "");
                            break;
                        case 2:
                            taWhead.Insert(Week3, sysUser.ID, sysUser.Corp.ID, Int16.Parse(Year.ToString()), Int16.Parse(Month.ToString()), sysUser.ID.ToString(), DateTime.Now, "", "");
                            break;
                        case 3:
                            taWhead.Insert(Week4, sysUser.ID, sysUser.Corp.ID, Int16.Parse(Year.ToString()), Int16.Parse(Month.ToString()), sysUser.ID.ToString(), DateTime.Now, "", "");
                            break;
                        case 4:
                            taWhead.Insert(Week5, sysUser.ID, sysUser.Corp.ID, Int16.Parse(Year.ToString()), Int16.Parse(Month.ToString()), sysUser.ID.ToString(), DateTime.Now, "", "");
                            break;
                    }
                }
                //新增周计划表体
                foreach (DataColumn dc in dt.Columns)
                {
                    if (dc.Caption == "Week1")
                    {
                        taWbody.InsertWeekBody(Week1, sysUser.ID.ToString(), dr["IndicatorID"].ToString(), dr["IndicatorClass"].ToString(), dr["IndicatorClassName"].ToString(), dr["IndicatorName"].ToString(), dr["IndicatorAmount"].ToString(), "", "", "", "", "", "", "", "", decimal.Parse("1"));
                    }
                    if (dc.Caption == "Week2")
                    {
                        taWbody.InsertWeekBody(Week2, sysUser.ID.ToString(), dr["IndicatorID"].ToString(), dr["IndicatorClass"].ToString(), dr["IndicatorClassName"].ToString(), dr["IndicatorName"].ToString(), dr["IndicatorAmount"].ToString(), "", "", "", "", "", "", "", "", decimal.Parse("2"));
                    }
                    if (dc.Caption == "Week3")
                    {
                        taWbody.InsertWeekBody(Week3, sysUser.ID.ToString(), dr["IndicatorID"].ToString(), dr["IndicatorClass"].ToString(), dr["IndicatorClassName"].ToString(), dr["IndicatorName"].ToString(), dr["IndicatorAmount"].ToString(), "", "", "", "", "", "", "", "", decimal.Parse("3"));
                    }
                    if (dc.Caption == "Week4")
                    {
                        taWbody.InsertWeekBody(Week4, sysUser.ID.ToString(), dr["IndicatorID"].ToString(), dr["IndicatorClass"].ToString(), dr["IndicatorClassName"].ToString(), dr["IndicatorName"].ToString(), dr["IndicatorAmount"].ToString(), "", "", "", "", "", "", "", "", decimal.Parse("4"));
                    }
                    if (dc.Caption == "Week5")
                    {
                        taWbody.InsertWeekBody(Week5, sysUser.ID.ToString(), dr["IndicatorID"].ToString(), dr["IndicatorClass"].ToString(), dr["IndicatorClassName"].ToString(), dr["IndicatorName"].ToString(), dr["IndicatorAmount"].ToString(), "", "", "", "", "", "", "", "", decimal.Parse("5"));
                    }
                }
            }
            //提交事务
            tran.Commit();
        }
        catch (Exception ex)
        {
            // 事务回滚
            tran.Rollback();
            throw ex;
        }
        finally
        {
            OracleConnection.ClearPool(conn);
            conn.Close();
        }
    }