Example #1
0
        //新建根物料模板
        public string CreateTemplet(string templetName, string creater)
        {
            string tmpId  = "";
            int    result = 0;
            string sql    = null;

            DbConnection  connection  = DbUtilities.GetConnection();
            DbTransaction transaction = connection.BeginTransaction();

            using (DbCommand command = connection.CreateCommand())
            {
                command.Transaction = transaction;

                //检查模板名称是否重复

                /* 李爱军要求不检查重复 20180930
                 * sql = $"SELECT COUNT(*) FROM TmpInfo WHERE TmpNm = '{templetName}' ";
                 * command.CommandText = sql;
                 * try
                 * {
                 *  result = Convert.ToInt32(command.ExecuteScalar());
                 * }
                 * catch (Exception e)
                 * {
                 *  log.Error(string.Format($"Select from table TmpInfo error!\nsql[{sql}]\nError[{e.StackTrace}]"));
                 *  connection.Close();
                 *  throw;
                 * }
                 *
                 * if (result > 0)
                 * {
                 *  log.Error(string.Format($"模板名称重复!Name=[{templetName}]\nsql[{sql}]\n"));
                 *  connection.Close();
                 *  throw new Exception("模板名称重复!");
                 * }
                 */

                //登记模板信息
                SeqNo seqNo = new SeqNo();
                tmpId = seqNo.GetBaseSeqNo();

                sql = $"INSERT INTO TmpInfo (TmpId, TmpNm, Root, LockCount, CrtDate, Crter, EditLock, DCM) VALUES ({tmpId}, '{templetName}', '1', 0, '{DateTime.Now}', '{creater}', 0, 0) ";
                command.CommandText = sql;
                try
                {
                    result = command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    log.Error(string.Format($"Insert table TmpInfo error!\nsql[{sql}]\nError[{e.StackTrace}]"));
                    transaction.Rollback();
                    connection.Close();
                    throw;
                }

                if (result == 0)
                {
                    log.Error(string.Format($"插入TmpInfo记录数为0\nsql[{sql}]\n"));
                    transaction.Rollback();
                    connection.Close();
                    throw new Exception("插入TmpInfo记录数为0!");
                }

                //登记根物料模板表Relation,TmpId='99',旧系统为'root'
                sql = $"INSERT INTO Relation (pid, TmpId, CTmpId, CTmpNm, CTmpNum, LockFlag, CrtDate, Crter, rlSeqNo) VALUES (99, 99, {tmpId}, '{templetName}',0, 0, '{DateTime.Now}', '{creater}', 0) ";
                command.CommandText = sql;
                try
                {
                    result = command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    log.Error(string.Format($"Insert table Relation error!\nsql[{sql}]\nError[{e.StackTrace}]"));
                    transaction.Rollback();
                    connection.Close();
                    throw;
                }

                if (result == 0)
                {
                    log.Error(string.Format($"Insert table Relation 0 record!\nsql[{sql}]\n"));
                    transaction.Rollback();
                    connection.Close();
                    throw new Exception("Insert table Relation 0 record!");
                }
            }
            transaction.Commit();
            connection.Close();
            return(tmpId);
        }
Example #2
0
        //新建物料信息(非根物料节点,无参考模板,上送父模板ID和新模板名称)
        public string CreateTemplet(long parentTempletId, string templetName, long pId, string creater)
        {
            int    result  = 0;
            int    rlSeqNo = -1;
            string sql     = null;
            string tmpId   = "";

            DbConnection  connection  = DbUtilities.GetConnection();
            DbTransaction transaction = connection.BeginTransaction();

            using (DbCommand command = connection.CreateCommand())
            {
                command.Transaction = transaction;

                //检查有无重复模板名称

                /* 李爱军要求不检查重复 20180930
                 * sql = $"SELECT COUNT(*) FROM TmpInfo WHERE TmpNm = '{templetName}' ";
                 * command.CommandText = sql;
                 * try
                 * {
                 *  result = Convert.ToInt32( command.ExecuteScalar());
                 * }
                 * catch (Exception e)
                 * {
                 *  log.Error(string.Format($"Select from table TmpInfo error!\nsql[{sql}]\nError[{e.StackTrace}]"));
                 *  connection.Close();
                 *  throw;
                 * }
                 *
                 * if (result > 0)
                 * {
                 *  log.Error(string.Format($"模板名称重复!Name=[{templetName}]\nsql[{sql}]\n"));
                 *  connection.Close();
                 *  throw new Exception("模板名称重复!");
                 * }
                 */

                //检查父模板是否存在
                sql = $"SELECT COUNT(*) FROM TmpInfo WHERE TmpId = '{parentTempletId}' ";
                command.CommandText = sql;
                try
                {
                    result = Convert.ToInt32(command.ExecuteScalar());
                }
                catch (Exception e)
                {
                    log.Error(string.Format($"Select from table TmpInfo error!\nsql[{sql}]\nError[{e.StackTrace}]"));
                    connection.Close();
                    throw;
                }

                if (result != 1)
                {
                    log.Error(string.Format($"父模板不存在!TmpId=[{parentTempletId}]\nsql[{sql}]\n"));
                    connection.Close();
                    throw new Exception("父模板不存在!");
                }

                //登记新模板信息
                SeqNo seqNo = new SeqNo();
                tmpId = seqNo.GetBaseSeqNo();
                sql   = $"INSERT INTO TmpInfo (TmpId, TmpNm, Root, LockCount, CrtDate, Crter, EditLock, DCM) VALUES ({tmpId}, '{templetName}', '0', 0, '{DateTime.Now}', '{creater}', 0, 0) ";
                command.CommandText = sql;
                try
                {
                    result = command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    log.Error(string.Format($"Insert table TmpInfo error!\nsql[{sql}]\nError[{e.StackTrace}]"));
                    transaction.Rollback();
                    connection.Close();
                    throw;
                }

                if (result == 0)
                {
                    log.Error(string.Format($"插入TmpInfo记录数为0\nsql[{sql}]\n"));
                    transaction.Rollback();
                    connection.Close();
                    throw new Exception("插入TmpInfo记录数为0!");
                }


                //登记父子模板关系表Relation
                //获取Relation.rlSeqNo的值
                if ("MySQL" == dbType)
                {
                    sql = $"SELECT IFNULL(MAX(rlSeqNo),-1) FROM RELATION WHERE TmpId = '{parentTempletId}' and CTmpId = '{tmpId}'";
                }
                else
                {
                    sql = $"SELECT ISNULL(MAX(rlSeqNo),-1) FROM RELATION WHERE TmpId = '{parentTempletId}' and CTmpId = '{tmpId}'";
                }
                command.CommandText = sql;
                try
                {
                    rlSeqNo = Convert.ToInt32(command.ExecuteScalar());
                }
                catch (Exception e)
                {
                    log.Error(string.Format($"Select Table Relation error!\nsql[{sql}]\nError[{e.StackTrace}]"));
                    connection.Close();
                    throw;
                }
                rlSeqNo++;

                //Insert Relation
                sql = $"INSERT INTO RELATION (pid,TmpId, CTmpId, CTmpNm, CTmpNum, LockFlag, CrtDate, Crter, rlSeqNo) VALUES ({pId},{parentTempletId}, {tmpId}, '{templetName}',0, 0, '{DateTime.Now}', '{creater}',{rlSeqNo})";
                command.CommandText = sql;
                try
                {
                    result = command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    log.Error(string.Format($"Insert table Relation error!\nsql[{sql}]\nError[{e.StackTrace}]"));
                    transaction.Rollback();
                    connection.Close();
                    throw;
                }
                if (result == 0)
                {
                    log.Error(string.Format($"插入Relation表记录数为0\nsql[{sql}]\n"));
                    transaction.Rollback();
                    connection.Close();
                    throw new Exception("插入Relation表记录数为0!");
                }


                //登记数据库表AttrPass

                sql = $"INSERT INTO AttrPass (TmpId, CTmpId, CAttrId, CAttrValue, ValueTp, PAttrId, Gt, Lt, Eq, Excld, Gteq, Lteq, CrtDate, Crter, rlSeqNo) VALUES ({parentTempletId},{tmpId},'_danyl', 0, '0', '0', '', '', '', '', '', '', '{DateTime.Now}','{creater}',{rlSeqNo})";
                command.CommandText = sql;
                try
                {
                    result = command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    log.Error(string.Format($"Insert table AttrPass error!\nsql[{sql}]\nError[{e.StackTrace}]"));
                    transaction.Rollback();
                    connection.Close();
                    throw;
                }
                if (result == 0)
                {
                    log.Error(string.Format($"Insert table Attrpass 0 record\nsql[{sql}]\n"));
                    transaction.Rollback();
                    connection.Close();
                    throw new Exception("Insert table Attrpass 0 record!");
                }
                transaction.Commit();
            }
            connection.Close();
            return(tmpId);
        }