BBMPT1/MESClassLibrary/DAL/BasicInfo/BomDAL.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using MESClassLibrary.BLL.Log;
using MESClassLibrary.Model;

namespace MESClassLibrary.DAL.BasicInfo
{
    public class BomDAL
    {
        public DataTable SearchBom(string PartNo)
        {
            try
            {
                string sql = @"SELECT dbo.tb_Product.ProductName, dbo.tb_ProductType.ProductTypeName, dbo.tb_ProductType.ProductTypeNo, 
                        dbo.tb_Product.PartNo, dbo.tb_Bom.PartNo2, dbo.tb_Product.StockNo
                        FROM dbo.tb_Bom RIGHT OUTER JOIN
                        dbo.tb_Product ON dbo.tb_Bom.PartNo1 = dbo.tb_Product.PartNo LEFT OUTER JOIN
                        dbo.tb_ProductType ON dbo.tb_Product.ProductTypeID = dbo.tb_ProductType.ProductTypeID
                        WHERE PartNo=@PartNo";

                SqlParameter[] param = new SqlParameter[1];
                param[0] = new SqlParameter("@PartNo", SqlDbType.VarChar);
                param[0].Value = PartNo;

                return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, param).Tables[0];

            }
            catch (Exception ex)
            {
                LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
                return null;
            }
        }

        public DataTable Search(string partNo1, string partNo2)
        {
            try
            {
                string sql = "select * from tb_Bom where PartNo1=@partNo1,PartNo2=@partNo2";

                SqlParameter[] param = new SqlParameter[2];
                param[0] = new SqlParameter("@@partNo1", SqlDbType.VarChar);
                param[0].Value = partNo1;

                param[1] = new SqlParameter("@@partNo2", SqlDbType.VarChar);
                param[1].Value = partNo2;

                return SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, param).Tables[0];
            }
            catch (Exception ex)
            {
                LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
                return null;
            }
        }

        public bool AddInfo(BomModel md)
        {
            string sql = "";
            SqlParameter[] param = null;

            try
            {
                sql = "insert into tb_Bom(ID,,PartNo1，PartNo2) values(@ID,@partNo1,@partNo2)";

                param = new SqlParameter[3];
                param[0] = new SqlParameter("@ID", SqlDbType.VarChar);
                param[0].Value = md.BomID;

                param[1] = new SqlParameter("@partNo1", SqlDbType.VarChar);
                param[1].Value = md.PartNo1;

                param[2] = new SqlParameter("@partNo2", SqlDbType.VarChar);
                param[2].Value = md.PartNo2;

                SqlHelper.ExecuteNonQuery(SqlHelper.GetConnSting(), CommandType.Text, sql, param);

                return true;
            }
            catch (Exception ex)
            {
                LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
                return false;
            }
        }

        public bool updateInfo(BomModel md)
        {
            string sql = "";
            SqlParameter[] param = null;

            try
            {
                sql = "update tb_Bom set PartNo2=@partNo2 where  PartNo1=@partNo1";

                param = new SqlParameter[2];

                param[0] = new SqlParameter("@partNo1", SqlDbType.VarChar);
                param[0].Value = md.PartNo1;

                param[1] = new SqlParameter("@partNo2", SqlDbType.VarChar);
                param[1].Value = md.PartNo2;

                SqlHelper.ExecuteNonQuery(SqlHelper.GetConnSting(), CommandType.Text, sql, param);

                return true;
            }
            catch (Exception ex)
            {
                LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
                return false;
            }
        }

        public bool DelInfo(BomModel md)
        {
            string sql = "";
            SqlParameter[] param = null;

            try
            {
                sql = "delete from  tb_Bom  where  PartNo1=@partNo1 and PartNo2=@partNo2 ";

                param = new SqlParameter[2];

                param[0] = new SqlParameter("@partNo1", SqlDbType.VarChar);
                param[0].Value = md.PartNo1;

                param[1] = new SqlParameter("@partNo2", SqlDbType.VarChar);
                param[1].Value = md.PartNo2;

                SqlHelper.ExecuteNonQuery(SqlHelper.GetConnSting(), CommandType.Text, sql, param);

                return true;
            }
            catch (Exception ex)
            {
                LogErrBLL.AddInfo(ex.ToString(), MethodBase.GetCurrentMethod());
                return false;
            }
        }

    }
}