SQL helper

<strong>SQL Server</strong>

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using MyDAL;
using System.Text;
using System.IO;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DBContrl
/// </summary>
public class DBContrl
{
     #region var
    private String sConnString = "";
    #endregion

    public DBContrl()
	{
        sConnString = System.Configuration.ConfigurationManager.ConnectionStrings["ToolingConnectionString"].ConnectionString;
    }

    #region Location
  
    /// <summary>
    /// 删除Location
    /// </summary>
   
    public bool Del_LocID(String sSite, String sStep, String sType, String sLocID, ref String sErrMsg)
    {
        bool bResullt = false;

        try
        {
            #region 查询是否还有存货
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select * from TMS_Sparepart_BasicInfo");
            sbQuery.AppendFormat(" where  LocID='{0}'",  sLocID);
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            DataSet dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
            if (myDB.IsSucc)
            {
                if (dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
                {
                    String sSparePart = dsResult.Tables[0].Rows[0]["ToolID"].ToString().Trim();
                    sErrMsg = String.Format("Current Location-{0} exist spare part:{1}, please move it first", sLocID, sSparePart);
                    return bResullt;
                }
            }
            else
            {
                sErrMsg = myDB.ErrMsg;
                return bResullt;
            }
            #endregion

            #region 删除Location
            StringBuilder sbDelete = new StringBuilder();
            sbDelete.Append("delete from dbo.TMS_Setting_Location");
            sbDelete.AppendFormat(" where Site='{0}' and Step='{1}' and Type='{2}' and LocID='{3}'", sSite, sStep, sType, sLocID);
            dtParams = myDB.GetDataTable_ParaSchema();
            myDB.ExecNoQuery(sbDelete.ToString(), ref dtParams, CommandType.Text);
            if (myDB.IsSucc)
            {
                bResullt = true;
            }
            else
            {
                sErrMsg = myDB.ErrMsg;
                return bResullt;
            }
            #endregion
        }
        catch (Exception ex)
        {
            bResullt = false;
            sErrMsg = String.Format("Occur excpeiton, info: {0}", ex.Message);
        }

        return bResullt;
    }

    /// <summary>
    /// 更新Location 信息
    /// </summary>   
    public bool Update_LocInfo(String sSite, String sStep, String sType, String sLocID, String sLocName, String CustCode, String sStatus, int iCapacity, ref String sErrMsg)
    {
        bool bResult = false;

        try
        {
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            myDB.CreateParameter("@Site", sSite, "", ref dtParams);
            myDB.CreateParameter("@Step", sStep, "", ref dtParams);
            myDB.CreateParameter("@Type", sType, "", ref dtParams);
            myDB.CreateParameter("@LocID", sLocID, "", ref dtParams);
            myDB.CreateParameter("@LocName", sLocName, "", ref dtParams);
            myDB.CreateParameter("@CustCode", CustCode, "", ref dtParams);
            myDB.CreateParameter("@Status", sStatus, "", ref dtParams);
            myDB.CreateParameter("@Capacity", iCapacity.ToString(), "", ref dtParams);

            myDB.ExecNoQuery("dbo.TMS_Location_Update", ref dtParams, CommandType.StoredProcedure);

            if (myDB.IsSucc)
            {
                bResult = true;
            }
            else
            {
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            bResult = false;
            sErrMsg = String.Format("Occur excepiton, info: {0}", ex.Message);
        }

        return bResult;
    }
        
    #endregion

    #region CustInfo 
  
    /// <summary>
    /// add new CustInfo
    public bool ADD_CustInfo(String sSite,String sStep , String sCustCode, String sCustName, String sDevice, String sPackage,String sPinQty,ref String sResult, ref String sErrMsg)
    {
        bool bResult = false;

        try
        {
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            myDB.CreateParameter("@Site", sSite, "", ref dtParams);
            myDB.CreateParameter("@Step", sStep, "", ref dtParams);
            myDB.CreateParameter("@CustCode", sCustCode, "", ref dtParams);
            myDB.CreateParameter("@CustName", sCustName, "", ref dtParams);
            myDB.CreateParameter("@Package",sPackage, "", ref dtParams);
            myDB.CreateParameter("@Device", sDevice, "", ref dtParams);
            myDB.CreateParameter("@PinQty", sPinQty, "", ref dtParams);
            myDB.CreateParameter("@sResult", sResult, "Output", ref dtParams);
            myDB.CreateParameter("@sErrMsg", sErrMsg, "Output", ref dtParams);
            myDB.ExecNoQuery("TMS_CustInfo_Update", ref dtParams, CommandType.StoredProcedure);
            if (myDB.IsSucc)
            {
                bResult = true;
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            bResult = false;
            sErrMsg = String.Format("Occur exception, info: {0}", ex.Message);
        }

        return bResult;
    }   
 
    /// <summary>
    /// 删除CustInfo信息
    /// </summary>
    public bool Delete_CustInfo(String sSite,String sStep ,String sCustCode, String sCustName, String sDevice, String sPackage,String sPinQty ,ref string sErrMsg)
    {
        bool bResult = false;

        #region 查询是否和Tool主表关联
        StringBuilder sbQuery = new StringBuilder();
        sbQuery.Append("select b.toolID from dbo.TMS_CustInfo a,dbo.TMS_Sparepart_BasicInfo b ");
        sbQuery.AppendFormat(" where b.CustInfoID=a.CustID ");
        if (sStep != "-" && !String.IsNullOrEmpty(sStep))
        {
            sbQuery.AppendFormat(" and a.Step='{0}' ", sStep);
        }
        if (sCustCode != "-" && !String.IsNullOrEmpty(sCustCode))
        {
            sbQuery.AppendFormat(" and a.CustCode='{0}' ", sCustCode);
        }
        if (sDevice != "-" && !String.IsNullOrEmpty(sDevice))
        {
            sbQuery.AppendFormat(" and a.Device='{0}' ", sDevice);
        }
        if (sPackage != "-" && !String.IsNullOrEmpty(sPackage))
        {
            sbQuery.AppendFormat(" and a.Package='{0}' ", sPackage);
        }
        if (sPinQty != "-" && !String.IsNullOrEmpty(sPinQty))
        {
            sbQuery.AppendFormat(" and a.PinQty='{0}' ", sPinQty);
        }
        DBAccess myDB = new DBAccess(sConnString);
        DataTable dtParams = myDB.GetDataTable_ParaSchema();
        DataSet dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
        if (myDB.IsSucc)
        {
            if (dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
            {
                String sSparePart = dsResult.Tables[0].Rows[0]["ToolID"].ToString().Trim();
                sErrMsg = String.Format("Current Customer Info exist in Tool table:{0}, please move it first",  sSparePart);
                return bResult;
            }
        }
        else
        {
            sErrMsg = myDB.ErrMsg;
            return bResult;
        }
        #endregion

        #region del the cust info
        StringBuilder sbDel = new StringBuilder();
        sbDel.Append("delete from dbo.TMS_CustInfo");
        sbDel.AppendFormat(" where Site='{0}' and Step='{1}' and CustCode='{2}'and CustName='{3}'and Device='{4}' and Package='{5}' and PinQty='{6}' ", sSite, sStep, sCustCode, sCustName, sDevice, sPackage, sPinQty);
        myDB.ExecNoQuery(sbDel.ToString(), ref dtParams, CommandType.Text);
        if (myDB.IsSucc)
        {
            bResult = true;
        }
        else
        {
            bResult = false;
            sErrMsg = myDB.ErrMsg;
        }

        #endregion
    
        return bResult;
    } 
    #endregion

   
    #region Spare Part Operation

    //ADD New Tool
    public bool ADD_NewTool(string sSite,string sStep,string sType,string sToolID,string sToolName,string  sCustomer,string sSataus,string sVender,string sComment,
        string sLocID, string sLUserID, string sLUserName, string sPackage ,string sDevice, string  sPinQty, ref String sErrMsg)
    {                                                    // string sMCID
        bool bResult = false;
        string sResult = "";
        try
        {
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            myDB.CreateParameter("@Site", sSite, "", ref dtParams);
            myDB.CreateParameter("@Step", sStep, "", ref dtParams);
            myDB.CreateParameter("@Type", sType, "", ref dtParams);
            myDB.CreateParameter("@ToolID", sToolID, "", ref dtParams);
            myDB.CreateParameter("@ToolName", sToolName, "", ref dtParams);
            myDB.CreateParameter("@CustCode", sCustomer, "", ref dtParams);
            myDB.CreateParameter("@Status", sSataus, "", ref dtParams);
            myDB.CreateParameter("@UserID", "", "", ref dtParams);
            myDB.CreateParameter("@UserName", "", "", ref dtParams);
            myDB.CreateParameter("@Vendor", sVender, "", ref dtParams);
            myDB.CreateParameter("@Comment", sComment, "", ref dtParams);
            myDB.CreateParameter("@LocID", sLocID, "", ref dtParams);
            myDB.CreateParameter("@LUserID", sLUserID, "", ref dtParams);
            myDB.CreateParameter("@LUserName", sLUserName, "", ref dtParams);
            myDB.CreateParameter("@MCID", "", "", ref dtParams);
            myDB.CreateParameter("@Package", sPackage, "", ref dtParams);
            myDB.CreateParameter("@Device", sDevice, "", ref dtParams);
            myDB.CreateParameter("@PinQty", sPinQty, "", ref dtParams);
            myDB.CreateParameter("@sResult", sResult, "Output", ref dtParams);
            myDB.CreateParameter("@sErrMsg", sErrMsg, "Output", ref dtParams);
            myDB.ExecNoQuery("dbo.TMS_Tool_new", ref dtParams, CommandType.StoredProcedure);
            if (myDB.IsSucc)
            {

                if (myDB.GetParaValue(dtParams, "@sResult") == "PASS" &&myDB.GetParaValue(dtParams, "@sErrMsg")=="")//获取输出参数
                {
                    sErrMsg = myDB.GetParaValue(dtParams, "@sErrMsg");
                    bResult = true;
                }
                else
                {
                    bResult = false;
                    sErrMsg = myDB.GetParaValue(dtParams, "@sErrMsg");
                }

            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return bResult;
    }

    public bool ADD_NewMaterial(string sSite, string sStep, string sType, string sToolID, string sToolName, string sCustomer,  string sVender, string sComment,
          string sLocID, string sLUserID, string sLUserName, int sQty, int sMinQty,  ref String sErrMsg)
    {                                                    
        bool bResult = false;
        String sResult = "";
        try
        {
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            myDB.CreateParameter("@Site", sSite, "", ref dtParams);
            myDB.CreateParameter("@Step", sStep, "", ref dtParams);
            myDB.CreateParameter("@Type", sType, "", ref dtParams);
            myDB.CreateParameter("@PartNo", sToolID, "", ref dtParams);
            myDB.CreateParameter("@PartName", sToolName, "", ref dtParams);
            myDB.CreateParameter("@CustCode", sCustomer, "", ref dtParams);
            myDB.CreateParameter("@Status", "", "", ref dtParams);
            myDB.CreateParameter("@UserID", "", "", ref dtParams);
            myDB.CreateParameter("@UserName", "", "", ref dtParams);
            myDB.CreateParameter("@Vendor", sVender, "", ref dtParams);
            myDB.CreateParameter("@Comment", sComment, "", ref dtParams);
            myDB.CreateParameter("@LocID", sLocID, "", ref dtParams);
            myDB.CreateParameter("@LUserID", sLUserID, "", ref dtParams);
            myDB.CreateParameter("@LUserName", sLUserName, "", ref dtParams);
            myDB.CreateParameter("@MCID", "", "", ref dtParams);
            myDB.CreateParameter("@ActionType", "NEW", "", ref dtParams);
            myDB.CreateParameter("@Quantity", sQty.ToString(), "", ref dtParams);
            myDB.CreateParameter("@MinQty", sMinQty.ToString(), "", ref dtParams);
            myDB.CreateParameter("@sResult", sResult, "Output", ref dtParams);
            myDB.CreateParameter("@sErrMsg", sErrMsg, "Output", ref dtParams);
            myDB.ExecNoQuery("dbo.TMS_Material_Opetation_BasicInfo", ref dtParams, CommandType.StoredProcedure);
            if (myDB.IsSucc)
            {

                if (myDB.GetParaValue(dtParams, "@sResult") == "PASS" && myDB.GetParaValue(dtParams, "@sErrMsg") == "")//获取输出参数
                {
                    bResult = true;
                }
                else
                {
                    bResult = false;
                    sErrMsg = myDB.GetParaValue(dtParams, "@sErrMsg");
                }

            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return bResult;
    }

   

    /// <summary>
    /// 删除Part No数据
    /// </summary>
    /// <returns></returns>
    public bool Del_ToolID(String Site,String Step,String Type,String sToolID,String sToolName, String sLUserID,String sLUserName, ref String sErrMsg )
    {
        bool bResult = false;
        String sResult = "";
        //@Site nvarchar(20),@Step nvarchar(20),@Type nvarchar(20),@ToolID nvarchar(50),@ToolName nvarchar(100),
        //@Status nvarchar(10),@UserID nvarchar(10),@UserName nvarchar(100),@Comment nvarchar(200),@LocID nvarchar(20),
        //@LUserID nvarchar(10),@LUserName nvarchar(100),@MCID nvarchar(30),@Vendor nvarchar(30),@CustCode nvarchar(10),
        //@Device nvarchar(50) ,@Package nvarchar(100),@ActionType nvarchar(10),@sResult nvarchar(10) output,@sErrMsg nvarchar(100) output  
        try
        {
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            myDB.CreateParameter("@Site", Site, "", ref dtParams);
            myDB.CreateParameter("@Step", Step, "", ref dtParams);
            myDB.CreateParameter("@Type", Type, "", ref dtParams);
            myDB.CreateParameter("@ToolID", sToolID, "", ref dtParams);
            myDB.CreateParameter("@ToolName", sToolName, "", ref dtParams);

            myDB.CreateParameter("@Status", "", "", ref dtParams);
            myDB.CreateParameter("@UserID", "", "", ref dtParams);
            myDB.CreateParameter("@UserName", "", "", ref dtParams);
            myDB.CreateParameter("@Comment", "", "", ref dtParams);
            myDB.CreateParameter("@LocID", "", "", ref dtParams);

            myDB.CreateParameter("@LUserID", sLUserID, "", ref dtParams);
            myDB.CreateParameter("@LUserName", sLUserName, "", ref dtParams);
            myDB.CreateParameter("@MCID", "", "", ref dtParams);
            myDB.CreateParameter("@Vendor", "", "", ref dtParams);
            myDB.CreateParameter("@CustCode", "", "", ref dtParams);

            myDB.CreateParameter("@Package", "", "", ref dtParams);
            myDB.CreateParameter("@Device", "", "", ref dtParams);
            myDB.CreateParameter("@PinQty", "", "", ref dtParams);
            myDB.CreateParameter("@ActionType", "Del", "", ref dtParams);
            myDB.CreateParameter("@sResult", sResult, "Output", ref dtParams);
            myDB.CreateParameter("@sErrMsg", sErrMsg, "Output", ref dtParams);
            myDB.ExecNoQuery("dbo.TMS_Tool_Maintenance", ref dtParams, CommandType.StoredProcedure);
            if (myDB.IsSucc)
            {

                if (myDB.GetParaValue(dtParams, "@sResult") == "PASS" && myDB.GetParaValue(dtParams, "@sErrMsg") == "")//获取输出参数
                {
                    bResult = true;
                    sErrMsg = myDB.GetParaValue(dtParams, "@sErrMsg");
                }
                else
                {
                    bResult = false;
                    sErrMsg = myDB.GetParaValue(dtParams, "@sErrMsg");
                }
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return bResult;
    }

    /// <summary>
    /// 维护ToolInfo
    /// </summary>
    /// <returns></returns>
    public bool Maintenance_PartNo(String sStep, String sType,String sToolID, String sToolName,String sComment,String sLocID,
        String sLUserID, String sLUserName, String sMCID,String sVendor,String sCustCode,String sPackage,String sDevice, String sPinQty,
        ref String sErrMsg  )
    {
        bool bResult = false;
        string sResult = "";
        //@Site nvarchar(20),@Step nvarchar(20),@Type nvarchar(20),@ToolID nvarchar(50),@ToolName nvarchar(100),
        //@Status nvarchar(10),@UserID nvarchar(10),@UserName nvarchar(100),@Comment nvarchar(200),@LocID nvarchar(20),
        //@LUserID nvarchar(10),@LUserName nvarchar(100),@MCID nvarchar(30),@Vendor nvarchar(30),@CustCode nvarchar(10),
        //@Device nvarchar(50) ,@Package nvarchar(100),@ActionType nvarchar(10),@sResult nvarchar(10) output,@sErrMsg nvarchar(100) output  
        
        //update
        //CategoryID =@CategoryID,CustID =@CustID ,ToolName=@ToolName,Comment=@Comment,LocID=@LocID,
	    //MCID=@MCID ,Vendor=@Vendor,ActionTime=@dtNow,LUserID=@LUserID,LUserName =@LUserName
        //  ToolID
        try
        {
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            myDB.CreateParameter("@Site", "SuZhou", "", ref dtParams);
            myDB.CreateParameter("@Step", sStep, "", ref dtParams);
            myDB.CreateParameter("@Type", sType, "", ref dtParams);
            myDB.CreateParameter("@ToolID", sToolID, "", ref dtParams);
            myDB.CreateParameter("@ToolName",sToolName, "", ref dtParams);

            myDB.CreateParameter("@Status", "", "", ref dtParams);
            myDB.CreateParameter("@UserID", "", "", ref dtParams);
            myDB.CreateParameter("@UserName", "", "", ref dtParams);
            myDB.CreateParameter("@Comment", sComment, "", ref dtParams);
            myDB.CreateParameter("@LocID", sLocID, "", ref dtParams);

            myDB.CreateParameter("@LUserID", sLUserID, "", ref dtParams);
            myDB.CreateParameter("@LUserName", sLUserName, "", ref dtParams);
            myDB.CreateParameter("@MCID", sMCID, "", ref dtParams);
            myDB.CreateParameter("@Vendor",sVendor, "", ref dtParams);
            myDB.CreateParameter("@CustCode", sCustCode, "", ref dtParams);

            myDB.CreateParameter("@Package", sPackage, "", ref dtParams);
            myDB.CreateParameter("@Device", sDevice, "", ref dtParams);
            myDB.CreateParameter("@PinQty", sPinQty, "", ref dtParams);

            myDB.CreateParameter("@ActionType", "Maintenance", "", ref dtParams);
            myDB.CreateParameter("@sResult", sResult, "Output", ref dtParams);
            myDB.CreateParameter("@sErrMsg", sErrMsg, "Output", ref dtParams);
            myDB.ExecNoQuery("dbo.TMS_Tool_Maintenance", ref dtParams, CommandType.StoredProcedure);
            if (myDB.IsSucc)
            {
                if (myDB.GetParaValue(dtParams, "@sResult") == "PASS" && myDB.GetParaValue(dtParams, "@sErrMsg") == "")//获取输出参数
                {
                    bResult = true;
                    sErrMsg = myDB.GetParaValue(dtParams, "@sErrMsg");
                }
                else
                {
                    bResult = false;
                    sErrMsg = myDB.GetParaValue(dtParams, "@sErrMsg");
                    sResult = myDB.GetParaValue(dtParams, "@sResult");
                }
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return bResult;
    }


    public bool CheckOut_PartNo(String sStep, String sType, String sCustCode,String sDevice, String sPackage, String sPinQty, String sToolID, String sLUserID, String sLUserName, String sUserID, String sUserName, String sStatus, String sMCID, ref String sErrMsg, ref String sResult)
    {
        bool bResult = false;

        //@Site nvarchar(20),@Step nvarchar(20),@Type nvarchar(20),@ToolID nvarchar(50),@ToolName nvarchar(100),
        //@Status nvarchar(10),@UserID nvarchar(10),@UserName nvarchar(100),@Comment nvarchar(200),@LocID nvarchar(20),
        //@LUserID nvarchar(10),@LUserName nvarchar(100),@MCID nvarchar(30),@Vendor nvarchar(30),@CustCode nvarchar(10),
        //@Device nvarchar(50) ,@Package nvarchar(100),@ActionType nvarchar(10),@sResult nvarchar(10) output,@sErrMsg nvarchar(100) output  

        //update
        //CategoryID =@CategoryID,CustID =@CustID ,ToolName=@ToolName,Comment=@Comment,LocID=@LocID,
        //MCID=@MCID ,Vendor=@Vendor,ActionTime=@dtNow,LUserID=@LUserID,LUserName =@LUserName
        //  ToolID
        try
        {
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            myDB.CreateParameter("@Site", "SuZhou", "", ref dtParams);
            myDB.CreateParameter("@Step", sStep, "", ref dtParams);
            myDB.CreateParameter("@Type", sType, "", ref dtParams);
            myDB.CreateParameter("@ToolID", sToolID, "", ref dtParams);
            myDB.CreateParameter("@ToolName", "", "", ref dtParams);
            
            myDB.CreateParameter("@Status", sStatus, "", ref dtParams);
            myDB.CreateParameter("@UserID", sUserID, "", ref dtParams);
            myDB.CreateParameter("@UserName", sUserName, "", ref dtParams);
            myDB.CreateParameter("@Comment","", "", ref dtParams);
            myDB.CreateParameter("@LocID", "", "", ref dtParams);

            myDB.CreateParameter("@LUserID", sLUserID, "", ref dtParams);
            myDB.CreateParameter("@LUserName", sLUserName, "", ref dtParams);
            myDB.CreateParameter("@MCID", sMCID, "", ref dtParams);
            myDB.CreateParameter("@Vendor", "", "", ref dtParams);
            myDB.CreateParameter("@CustCode", sCustCode,"", ref dtParams);

            myDB.CreateParameter("@Package", sPackage, "", ref dtParams);
            myDB.CreateParameter("@Device", sDevice, "", ref dtParams);
            myDB.CreateParameter("@PinQty", sPinQty,"", ref dtParams);

            myDB.CreateParameter("@ActionType", "CheckOut", "", ref dtParams);
            myDB.CreateParameter("@sResult", sResult, "Output", ref dtParams);
            myDB.CreateParameter("@sErrMsg", sErrMsg, "Output", ref dtParams);
            myDB.ExecNoQuery("dbo.TMS_Tool_Maintenance", ref dtParams, CommandType.StoredProcedure);
            if (myDB.IsSucc)
            {
                if (myDB.GetParaValue(dtParams, "@sResult") == "PASS" && myDB.GetParaValue(dtParams, "@sErrMsg") == "")//获取输出参数
                {
                    bResult = true;
                    sResult = myDB.GetParaValue(dtParams, "@sResult");
                }
                else
                {
                    bResult = false;
                    sErrMsg = myDB.GetParaValue(dtParams, "@sErrMsg");
                    sResult = myDB.GetParaValue(dtParams, "@sResult");
                }
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return bResult;
    }
    public bool CheckIn_PartNo(String sStep, String sType, String sCustCode ,String sDevice, String sPackage, String sPinQty, String sToolID, String sLUserID, String sLUserName, String sUserID, String sUserName, String sStatus, ref String sErrMsg, ref String sResult)
    {
        bool bResult = false;

        //@Site nvarchar(20),@Step nvarchar(20),@Type nvarchar(20),@ToolID nvarchar(50),@ToolName nvarchar(100),
        //@Status nvarchar(10),@UserID nvarchar(10),@UserName nvarchar(100),@Comment nvarchar(200),@LocID nvarchar(20),
        //@LUserID nvarchar(10),@LUserName nvarchar(100),@MCID nvarchar(30),@Vendor nvarchar(30),@CustCode nvarchar(10),
        //@Device nvarchar(50) ,@Package nvarchar(100),@ActionType nvarchar(10),@sResult nvarchar(10) output,@sErrMsg nvarchar(100) output  

        //update
        //CategoryID =@CategoryID,CustID =@CustID ,ToolName=@ToolName,Comment=@Comment,LocID=@LocID,
        //MCID=@MCID ,Vendor=@Vendor,ActionTime=@dtNow,LUserID=@LUserID,LUserName =@LUserName
        //  ToolID
        try
        {
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            myDB.CreateParameter("@Site", "SuZhou", "", ref dtParams);
            myDB.CreateParameter("@Step", sStep, "", ref dtParams);
            myDB.CreateParameter("@Type", sType, "", ref dtParams);
            myDB.CreateParameter("@ToolID", sToolID, "", ref dtParams);
            myDB.CreateParameter("@ToolName", "", "", ref dtParams);

            myDB.CreateParameter("@Status", sStatus, "", ref dtParams);
            myDB.CreateParameter("@UserID", sUserID, "", ref dtParams);
            myDB.CreateParameter("@UserName", sUserName, "", ref dtParams);
            myDB.CreateParameter("@Comment", "", "", ref dtParams);
            myDB.CreateParameter("@LocID", "", "", ref dtParams);

            myDB.CreateParameter("@LUserID", sLUserID, "", ref dtParams);
            myDB.CreateParameter("@LUserName", sLUserName, "", ref dtParams);
            myDB.CreateParameter("@MCID", "", "", ref dtParams);
            myDB.CreateParameter("@Vendor", "", "", ref dtParams);
            myDB.CreateParameter("@CustCode", sCustCode, "", ref dtParams);

            myDB.CreateParameter("@Device", sDevice, "", ref dtParams);
            myDB.CreateParameter("@Package", sPackage, "", ref dtParams);
            myDB.CreateParameter("@PinQty", sPinQty, "", ref dtParams);
            myDB.CreateParameter("@ActionType", "CheckIn", "", ref dtParams);
            myDB.CreateParameter("@sResult", sResult, "Output", ref dtParams);
            myDB.CreateParameter("@sErrMsg", sErrMsg, "Output", ref dtParams);
            myDB.ExecNoQuery("dbo.TMS_Tool_Maintenance", ref dtParams, CommandType.StoredProcedure);
            if (myDB.IsSucc)
            {
                if (myDB.GetParaValue(dtParams, "@sResult") == "PASS" && myDB.GetParaValue(dtParams, "@sErrMsg") == "")//获取输出参数
                {
                    bResult = true;
                    sResult = myDB.GetParaValue(dtParams, "@sResult");
                }
                else
                {
                    bResult = false;
                    sErrMsg = myDB.GetParaValue(dtParams, "@sErrMsg");
                    sResult = myDB.GetParaValue(dtParams, "@sResult");
                }
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return bResult;
    }

    public bool Operation_Material(string sSite, string sStep, string sType, string sToolID,string sMCID,string sQty,
                                  string sUserID, string sUserName, string sComment, string sLocID, string sLUserID, string sLUserName,
                                  string sActionType, ref String sErrMsg)
    {
        bool bResult = false;
        try
        {
            string sResult = "";
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            myDB.CreateParameter("@Site", sSite, "", ref dtParams);
            myDB.CreateParameter("@Step", sStep, "", ref dtParams);
            myDB.CreateParameter("@Type", sType, "", ref dtParams);
            myDB.CreateParameter("@PartNo", sToolID, "", ref dtParams);
            myDB.CreateParameter("@PartName", "", "", ref dtParams);
            myDB.CreateParameter("@CustCode", "", "", ref dtParams);
            myDB.CreateParameter("@Status", "", "", ref dtParams);
            myDB.CreateParameter("@UserID", sUserID, "", ref dtParams);
            myDB.CreateParameter("@UserName", sUserName, "", ref dtParams);
            myDB.CreateParameter("@Vendor", "", "", ref dtParams);
            myDB.CreateParameter("@Comment", sComment, "", ref dtParams);
            myDB.CreateParameter("@LocID", sLocID, "", ref dtParams);
            myDB.CreateParameter("@LUserID", sLUserID, "", ref dtParams);
            myDB.CreateParameter("@LUserName", sLUserName, "", ref dtParams);
            myDB.CreateParameter("@MCID", sMCID, "", ref dtParams);
            myDB.CreateParameter("@ActionType", sActionType, "", ref dtParams);
            myDB.CreateParameter("@Quantity", sQty.ToString(), "", ref dtParams);
            myDB.CreateParameter("@MinQty", "", "", ref dtParams);
            myDB.CreateParameter("@sResult", sResult, "Output", ref dtParams);
            myDB.CreateParameter("@sErrMsg", sErrMsg, "Output", ref dtParams);
            myDB.ExecNoQuery("dbo.TMS_Material_Opetation_BasicInfo", ref dtParams, CommandType.StoredProcedure);
            if (myDB.IsSucc)
            {

                if (myDB.GetParaValue(dtParams, "@sResult") == "PASS" && myDB.GetParaValue(dtParams, "@sErrMsg") == "")//获取输出参数
                {
                    bResult = true;
                }
                else
                {
                    bResult = false;
                    sErrMsg = myDB.GetParaValue(dtParams, "@sErrMsg");
                }

            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return bResult;
    }
    public bool Maintain_Material_PartNo(string sSite, string sStep, string sType, string sToolID,string sToolName, string sMCID, string sMinQty,
                                      string sCustCode,string sVendor , string sComment, string sLocID, string sLUserID, string sLUserName,
                                     string sActionType, ref String sErrMsg)
        
    {
        bool bResult = false;
        try
        {
            string sResult = "";
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            myDB.CreateParameter("@Site", sSite, "", ref dtParams);
            myDB.CreateParameter("@Step", sStep, "", ref dtParams);
            myDB.CreateParameter("@Type", sType, "", ref dtParams);
            myDB.CreateParameter("@PartNo", sToolID, "", ref dtParams);
            myDB.CreateParameter("@PartName", sToolName, "", ref dtParams);
            myDB.CreateParameter("@CustCode", sCustCode, "", ref dtParams);
            myDB.CreateParameter("@Status", "", "", ref dtParams);
            myDB.CreateParameter("@UserID", "", "", ref dtParams);
            myDB.CreateParameter("@UserName", "", "", ref dtParams);
            myDB.CreateParameter("@Vendor", sVendor, "", ref dtParams);
            myDB.CreateParameter("@Comment", sComment, "", ref dtParams);
            myDB.CreateParameter("@LocID", sLocID, "", ref dtParams);
            myDB.CreateParameter("@LUserID", sLUserID, "", ref dtParams);
            myDB.CreateParameter("@LUserName", sLUserName, "", ref dtParams);
            myDB.CreateParameter("@MCID", sMCID, "", ref dtParams);
            myDB.CreateParameter("@ActionType", sActionType, "", ref dtParams);
            myDB.CreateParameter("@MinQty", sMinQty.ToString(), "", ref dtParams);
            myDB.CreateParameter("@sResult", sResult, "Output", ref dtParams);
            myDB.CreateParameter("@sErrMsg", sErrMsg, "Output", ref dtParams);
            myDB.ExecNoQuery("dbo.TMS_Material_Maintain_BasicInfo", ref dtParams, CommandType.StoredProcedure);
            if (myDB.IsSucc)
            {

                if (myDB.GetParaValue(dtParams, "@sResult") == "PASS" && myDB.GetParaValue(dtParams, "@sErrMsg") == "")//获取输出参数
                {
                    bResult = true;
                }
                else
                {
                    bResult = false;
                    sErrMsg = myDB.GetParaValue(dtParams, "@sErrMsg");
                }

            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return bResult;
    }

    #endregion

    #region //insert pic
    public bool Material_InsertPic_byPartNo(String sSite, String sStep, String sType, String sPartNo, String sPicture,  ref String sErrMsg)
    {
        bool bResullt = false;
        string CategoryID="";
        try
        {
            #region 查询是否存在Part
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select a.* from TMS_Material_BasicInfo a,dbo.TMS_Category b where a.CategoryID=b.CategoryID ");
            sbQuery.AppendFormat(" and b.Class='Material' and b.Site='{0}' and b.Step='{1}'and a.PartNo=N'{2}' and b.Type='{3}' ", sSite, sStep, sPartNo,sType);
 
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            DataSet dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
            if (myDB.IsSucc)
            {
                if (dsResult != null && dsResult.Tables.Count == 1 && dsResult.Tables[0].Rows.Count == 1)
                {
                    CategoryID = dsResult.Tables[0].Rows[0]["CategoryID"].ToString();                    
                }
                else if (dsResult == null)
                {
                    sErrMsg = String.Format("Can't find the {0}, please add it first", sPartNo);
                    return bResullt;
                }
                else
                {
                    sErrMsg = String.Format("find the multiple {0}, please check it first", sPartNo);
                    return bResullt;
                }
            }
            else
            {
                sErrMsg = myDB.ErrMsg;
                return bResullt;
            }
            #endregion

            #region //insert the pic 若图片存在就覆盖
            sbQuery.Remove(0,sbQuery.Length);
            sbQuery.AppendFormat("select ToolID from TMS_Picture where CategoryID='{0}' and ToolID='{1}'", CategoryID, sPartNo); 
              dtParams = myDB.GetDataTable_ParaSchema();
              dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
              if (dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
              {
                  StringBuilder sbUpdate = new StringBuilder();
                  sbUpdate.AppendFormat("update TMS_Picture set picture='{0}' where CategoryID='{1}' and ToolID='{2}'  ",sPicture, CategoryID, sPartNo);
                  dtParams = myDB.GetDataTable_ParaSchema();
                  myDB.ExecNoQuery(sbUpdate.ToString(), ref dtParams, CommandType.Text);
                  if (myDB.IsSucc)
                  {
                      bResullt = true;
                  }
                  else
                  {
                      sErrMsg = myDB.ErrMsg;
                      return bResullt;
                  }
              }
              else
              {
                  StringBuilder sbDelete = new StringBuilder();
                  sbDelete.AppendFormat("Insert TMS_Picture values ({0},'{1}','{2}') ", CategoryID, sPartNo, sPicture);
                  dtParams = myDB.GetDataTable_ParaSchema();
                  myDB.ExecNoQuery(sbDelete.ToString(), ref dtParams, CommandType.Text);
                  if (myDB.IsSucc)
                  {
                      bResullt = true;
                  }
                  else
                  {
                      sErrMsg = myDB.ErrMsg;
                      return bResullt;
                  }
              }
          
            #endregion
        }
        catch (Exception ex)
        {
            bResullt = false;
            sErrMsg = String.Format("Occur excpeiton, info: {0}", ex.Message);
        }

        return bResullt;
    }
    public bool Tool_InsertPic_byPartNo(String sSite, String sStep, String sType, String sPartNo, String sPicture, ref String sErrMsg)
    {
        bool bResullt = false;
        string CategoryID = "";
        try
        {
            #region 查询是否存在Tool Or Part
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select a.* from TMS_Sparepart_BasicInfo a,dbo.TMS_Category b where a.CategoryID=b.CategoryID ");
            sbQuery.AppendFormat(" and b.Class='Tool' and b.Site='{0}' and b.Step='{1}' and a.ToolID=N'{2}' and b.Type='{3}'", sSite, sStep, sPartNo,sType);

            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            DataSet dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
            if (myDB.IsSucc)
            {
                if (dsResult != null && dsResult.Tables.Count == 1 && dsResult.Tables[0].Rows.Count == 1)
                {
                    CategoryID = dsResult.Tables[0].Rows[0]["CategoryID"].ToString().Trim();

                }
                else if (dsResult == null)
                {
                    sErrMsg = String.Format("Can't find the {0}, please add it first", sPartNo);
                    return bResullt;
                }
                else
                {
                    sErrMsg = String.Format("find the multiple {0}, please check it first", sPartNo);
                    return bResullt;
                }
            }
            else
            {
                sErrMsg = myDB.ErrMsg;
                return bResullt;
            }
            #endregion

            #region //insert the pic 若图片存在就覆盖
            sbQuery.Remove(0, sbQuery.Length);
            sbQuery.AppendFormat("select ToolID from TMS_Picture where CategoryID='{0}' and ToolID='{1}'", CategoryID, sPartNo);
            dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
            if (dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
            {
                StringBuilder sbUpdate = new StringBuilder();
                sbUpdate.AppendFormat("update TMS_Picture set picture='{0}' where CategoryID='{1}' and ToolID='{2}'  ", sPicture, CategoryID, sPartNo);
                dtParams = myDB.GetDataTable_ParaSchema();
                myDB.ExecNoQuery(sbUpdate.ToString(), ref dtParams, CommandType.Text);
                if (myDB.IsSucc)
                {
                    bResullt = true;
                }
                else
                {
                    sErrMsg = myDB.ErrMsg;
                    return bResullt;
                }
            }
            else
            {
                StringBuilder sbDelete = new StringBuilder();
                sbDelete.AppendFormat("Insert TMS_Picture values ({0},'{1}','{2}') ", CategoryID, sPartNo, sPicture);
                dtParams = myDB.GetDataTable_ParaSchema();
                myDB.ExecNoQuery(sbDelete.ToString(), ref dtParams, CommandType.Text);
                if (myDB.IsSucc)
                {
                    bResullt = true;
                }
                else
                {
                    sErrMsg = myDB.ErrMsg;
                    return bResullt;
                }
            }

            #endregion
        }
        catch (Exception ex)
        {
            bResullt = false;
            sErrMsg = String.Format("Occur excpeiton, info: {0}", ex.Message);
        }

        return bResullt;
    }

    #endregion

    #region Bin Check
    public bool Update_BinCheck(string Cust, string Device, string Location, ref string sErrMsg)
    {
        bool bResullt = false;
        try
        {
            StringBuilder sbDelete = new StringBuilder();
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            sbDelete.AppendFormat("Update dbo.TMS_BinCheck set Location='{0}'",Location );
            sbDelete.AppendFormat(" where CustCode='{0}' and Device='{1}' ", Cust, Device);
            dtParams = myDB.GetDataTable_ParaSchema();
            myDB.ExecNoQuery(sbDelete.ToString(), ref dtParams, CommandType.Text);
            if (myDB.IsSucc)
            {
                bResullt = true;
            }
            else
            {
                sErrMsg = myDB.ErrMsg;
                return bResullt;
            }
            return bResullt;
        }
        catch(Exception ex)
        {
            sErrMsg = ex.Message;
        }

        return bResullt;
    }

    #endregion

    #region//query for all web

  
    //Query History
    public DataSet Get_HistoryTool_ByToolNo(String ToolID, String Begintime, String Endtime, ref bool bResult, ref String sErrMsg)
    {
        DataSet dsResult = new DataSet();
        try
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select a.*,b.Step,b.Type from dbo.TMS_Sparepart_BasicInfo_history a ");
            sbQuery.AppendFormat("left join dbo.TMS_Category b on a.categoryID= b.categoryID ");
             //sbQuery.AppendFormat("left join dbo.TMS_CustInfo c on a.CustID=c.custID ");
            sbQuery.AppendFormat(" where 1=1 ");
            if (!String.IsNullOrEmpty(ToolID))
            { sbQuery.AppendFormat(" and toolID like N'%{0}%' ", ToolID); }
            if (!String.IsNullOrEmpty(Begintime) && !String.IsNullOrEmpty(Endtime))
            {
                sbQuery.AppendFormat(" and a.ActionTime> ='{0}' and a.ActionTime< ='{1}' ", Begintime, Endtime );
            }
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
            if (myDB.IsSucc)
            {
                bResult = true;
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return dsResult;
    }
    public DataSet Get_HistoryMaterial_ByPartNo(String PartNo, String Begintime, String Endtime, ref bool bResult, ref String sErrMsg)
    {
        DataSet dsResult = new DataSet();
        try
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select a.*,b.Step,b.Type from dbo.TMS_Material_BasicInfo_History a ");
            sbQuery.AppendFormat("left join dbo.TMS_Category b on a.categoryID= b.categoryID ");
            sbQuery.AppendFormat(" where 1=1 ");
            if (!String.IsNullOrEmpty(PartNo))
            { sbQuery.AppendFormat(" and PartNo like N'%{0}%' ", PartNo); }
            if (!String.IsNullOrEmpty(Begintime) && !String.IsNullOrEmpty(Endtime))
            { sbQuery.AppendFormat(" and a.ActionTime> ='{0}' and a.ActionTime< ='{1}' ", Begintime, Endtime); }
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
            if (myDB.IsSucc)
            {
                bResult = true;
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return dsResult;
    }
 
  
    /// <summary>
    /// Get tool Info
    /// </summary>
    /// <param name="InOut"></param>  1 ture -->Check Out   2 false -->Check In
    /// <returns></returns>
    public DataSet Get_ToolInfo(String sStep, String sType, String sCustCode, String sDevice, String sPackage, String sPinQty, String sToolID,String sToolName,String sStatus_my, ref bool bResult, ref String sErrMsg)
    {
        DataSet dsResult = new DataSet();
        try
        {
            StringBuilder sbQuery = new StringBuilder();

            sbQuery.Append(" select a.* ,b.site,b.step,b.Class,b.type,c.custcode,c.Device,c.Package,c.PinQty ");
            sbQuery.Append(" from dbo.TMS_Sparepart_BasicInfo a ,dbo.TMS_Category b,dbo.TMS_CustInfo c ");
            sbQuery.AppendFormat(" where a.categoryID=b.CategoryID and a.CustInfoID=c.CustID ");
            if (sStep != "-" && !String.IsNullOrEmpty(sStep))
            {
                sbQuery.AppendFormat(" and b.Step= '{0}' ", sStep);
            }
            if (sType != "-"&&!String.IsNullOrEmpty(sType))
            {
                sbQuery.AppendFormat(" and b.Type='{0}' ", sType);
            }
            if (sCustCode != "-" && !String.IsNullOrEmpty(sCustCode))
            {
                sbQuery.AppendFormat(" and c.CustCode='{0}' ", sCustCode);
            }
            if (sDevice != "-"&& !String.IsNullOrEmpty(sDevice))
            {
                sbQuery.AppendFormat(" and c.Device like N'%{0}%' ", sDevice);
            }
            if (sPackage != "-" && !String.IsNullOrEmpty(sPackage))
            {
                sbQuery.AppendFormat(" and c.Package='{0}' ", sPackage);
            }
            if (sPinQty != "-" && !String.IsNullOrEmpty(sPinQty))
            {
                sbQuery.AppendFormat(" and c.PinQty='{0}' ", sPinQty);
            }
            if (sToolID != "-" && !String.IsNullOrEmpty(sToolID))
            {
                sbQuery.AppendFormat(" and a.ToolID like N'%{0}%' ", sToolID);
            }
            if (sToolName != "-" && !String.IsNullOrEmpty(sToolName))
            {
                sbQuery.AppendFormat(" and a.ToolName like N'%{0}%' ", sToolName);
            }

            if (sStatus_my=="OUT")
            {  
                //check Out
                sbQuery.AppendFormat(" and a.Status='Idle' ");
            }
            if (sStatus_my == "IN")
            {    
                //Check In 
                sbQuery.AppendFormat(" and a.Status<>'Idle' ");
            }

            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
            if (myDB.IsSucc)
            {
                bResult = true;
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return dsResult;
    }

    //精确查找
    public DataSet Get_ToolInfo_Exact(String sStep, String sType, String sCustCode, String sDevice, String sPackage, String sPinQty, String sToolID, String sToolName, String sStatus_my, ref bool bResult, ref String sErrMsg)
    {
        DataSet dsResult = new DataSet();
        try
        {
            StringBuilder sbQuery = new StringBuilder();

            sbQuery.Append(" select a.* ,b.site,b.step,b.Class,b.type,c.custcode,c.Device,c.Package,c.PinQty ");
            sbQuery.Append(" from dbo.TMS_Sparepart_BasicInfo a ,dbo.TMS_Category b,dbo.TMS_CustInfo c ");
            sbQuery.AppendFormat(" where a.categoryID=b.CategoryID and a.CustInfoID=c.CustID ");
            if (sStep != "-" && !String.IsNullOrEmpty(sStep))
            {
                sbQuery.AppendFormat(" and b.Step= '{0}' ", sStep);
            }
            if (sType != "-" && !String.IsNullOrEmpty(sType))
            {
                sbQuery.AppendFormat(" and b.Type='{0}' ", sType);
            }
            if (sCustCode != "-" && !String.IsNullOrEmpty(sCustCode))
            {
                sbQuery.AppendFormat(" and c.CustCode='{0}' ", sCustCode);
            }
            if (sDevice != "-" && !String.IsNullOrEmpty(sDevice))
            {
                sbQuery.AppendFormat(" and c.Device = '{0}' ", sDevice);
            }
            if (sPackage != "-" && !String.IsNullOrEmpty(sPackage))
            {
                sbQuery.AppendFormat(" and c.Package='{0}' ", sPackage);
            }
            if (sPinQty != "-" && !String.IsNullOrEmpty(sPinQty))
            {
                sbQuery.AppendFormat(" and c.PinQty='{0}' ", sPinQty);
            }
            if (sToolID != "-" && !String.IsNullOrEmpty(sToolID))
            {
                sbQuery.AppendFormat(" and a.ToolID =N'{0}' ", sToolID);
            }
            if (sToolName != "-" && !String.IsNullOrEmpty(sToolName))
            {
                sbQuery.AppendFormat(" and a.ToolName like N'%{0}%' ", sToolName);
            }

            if (sStatus_my == "OUT")
            {
                //check Out
                sbQuery.AppendFormat(" and a.Status='Idle' ");
            }
            if (sStatus_my == "IN")
            {
                //Check In 
                sbQuery.AppendFormat(" and a.Status<>'Idle' ");
            }

            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
            if (myDB.IsSucc)
            {
                bResult = true;
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return dsResult;
    }

  
    //模糊查询
    public DataSet Get_Material_Info(String sStep, String sType,String sCustomer,String sVendor, String sPartNo,String sPartName,String sLocID,String sLocName,  ref bool bResult, ref String sErrMsg)
    {
        DataSet dsResult = new DataSet();
        try
        {
            StringBuilder sbQuery = new StringBuilder();

            sbQuery.Append("select  a.*,b.Site,b.Step,b.Type,c.LocName, ");
            sbQuery.Append(" (case   when a.quantity>MinQty then 'Hight' when a.quantity<=MinQty then 'Low' else 'UnKnow' end) as'State' ");
            sbQuery.Append(" from dbo.TMS_Material_BasicInfo a   ");
            sbQuery.Append(" left join dbo.TMS_Category b on a.CategoryID=b.CategoryID ");
            sbQuery.Append("  left join dbo.TMS_Setting_Location c on a.LocID=c.LocID ");
            sbQuery.AppendFormat(" where b.class='Material' ");
            if (sStep != "-" && !String.IsNullOrEmpty(sStep))
            {
                sbQuery.AppendFormat(" and b.Step= '{0}' ", sStep);
            }
            if (sType != "-"&&!String.IsNullOrEmpty(sType))
            {
                sbQuery.AppendFormat(" and b.Type='{0}' ", sType);
            }
            if (sCustomer != "-" && !String.IsNullOrEmpty(sCustomer))
            {
                sbQuery.AppendFormat(" and a.CustCode = '{0}' ", sCustomer);
            }
            if (sVendor != "-" && !String.IsNullOrEmpty(sVendor))
            {
                sbQuery.AppendFormat(" and a.Vendor = '{0}' ", sVendor);
            }
            if (sPartNo != "-" && !String.IsNullOrEmpty(sPartNo))
            {
                sbQuery.AppendFormat(" and a.PartNo like N'%{0}%' ",sPartNo );
            }
            if (sPartName != "-" && !String.IsNullOrEmpty(sPartName))
            {
                sbQuery.AppendFormat(" and a.PartName like N'%{0}%' ", sPartName);
            }
            if (sLocID != "-" && !String.IsNullOrEmpty(sLocID))
            {
                sbQuery.AppendFormat(" and a.LocID like N'%{0}%' ", sLocID);
            }
            if (sLocName != "-" && !String.IsNullOrEmpty(sLocName))
            {
                sbQuery.AppendFormat(" and c.LocName like N'%{0}%' ", sLocName);
            }
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
            if (myDB.IsSucc)
            {
                bResult = true;
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return dsResult;
    }
    
    //增加一个参数 是否显示低库存
    public DataSet Get_Material_Info(String sStep, String sType, String sCustomer, String sVendor, String sPartNo, String sPartName, bool IsShowLow,ref bool bResult, ref String sErrMsg)
    {
        DataSet dsResult = new DataSet();
        try
        {
            StringBuilder sbQuery = new StringBuilder();

            sbQuery.Append("select  a.*,b.Site,b.Step,b.Type, ");
            sbQuery.Append(" (case   when a.quantity>MinQty then 'Hight' when a.quantity<=MinQty then 'Low' else 'UnKnow' end) as'State' ");
            sbQuery.Append(" from dbo.TMS_Material_BasicInfo a left join dbo.TMS_Category b  ");
            sbQuery.Append(" on a.CategoryID=b.CategoryID ");
            sbQuery.AppendFormat(" where b.class='Material' ");
            if (IsShowLow)
            {
                sbQuery.AppendFormat(" and a.quantity<=MinQty ");
            }

            if (sStep != "-" && !String.IsNullOrEmpty(sStep))
            {
                sbQuery.AppendFormat(" and b.Step= '{0}' ", sStep);
            }
            if (sType != "-" && !String.IsNullOrEmpty(sType))
            {
                sbQuery.AppendFormat(" and b.Type='{0}' ", sType);
            }
            if (sCustomer != "-" && !String.IsNullOrEmpty(sCustomer))
            {
                sbQuery.AppendFormat(" and a.CustCode = '{0}' ", sCustomer);
            }
            if (sVendor != "-" && !String.IsNullOrEmpty(sVendor))
            {
                sbQuery.AppendFormat(" and a.Vendor = '{0}' ", sVendor);
            }
            if (sPartNo != "-" && !String.IsNullOrEmpty(sPartNo))
            {
                sbQuery.AppendFormat(" and a.PartNo like N'%{0}%' ", sPartNo);
            }
            if (sPartName != "-" && !String.IsNullOrEmpty(sPartName))
            {
                sbQuery.AppendFormat(" and a.PartName like N'%{0}%' ", sPartName);
            }

            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
            if (myDB.IsSucc)
            {
                bResult = true;
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return dsResult;
    }

    

    /// <summary>
    /// 查询 Custcode Info
    /// </summary>
    /// <param name="sCustCode"></param>
    /// <param name="bResult"></param>
    /// <param name="sErrMsg"></param>
    /// <returns></returns>
    public DataSet Get_CustInfo( String sStep,String sCustCode ,String sDevice, String sPackage,String sPinQty ,ref bool bResult, ref String sErrMsg)
    {
        DataSet dsResult = new DataSet();

        try
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select * from TMS_CustInfo where 1=1");

            if (string.IsNullOrEmpty(sStep) || sStep == "-")
            { }
            else
            {
                sbQuery.AppendFormat("and Step ='{0}'", sStep);
            }
            
            if (string.IsNullOrEmpty(sCustCode) || sCustCode=="-")
            {}
            else
            {
                sbQuery.AppendFormat("and CustCode ='{0}'",sCustCode);
            }

            if (string.IsNullOrEmpty(sDevice)||sDevice =="-" )
            {}
            else
            {
                sbQuery.AppendFormat("and Device Like '%{0}%' ", sDevice);
            }

            if ( string.IsNullOrEmpty(sPackage)||sPackage=="-")
            {}
            else
            {
                sbQuery.AppendFormat(" and Package='{0}' ", sPackage);
            }

            if (string.IsNullOrEmpty(sPinQty) || sPackage == "-")
            { }
            else
            {
                sbQuery.AppendFormat(" and PinQty='{0}' ", sPinQty);
            }
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
            if (myDB.IsSucc)
            {
                bResult = true;
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }

        }
        catch (Exception ex)
        {
            bResult = false;
            sErrMsg = String.Format("Occur exception, info: {0}", ex.Message);
        }

        return dsResult;
    }
   
    


    //模糊查询 Device ,Package,PinQty
    public DataSet Get_Device(String sStep, String sCustCode, String sDevice, String sPackage, String sPinQty, ref bool bResult, ref String sErrMsg)
    {
        DataSet dsResult = new DataSet();

        try
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select distinct Device from TMS_CustInfo where 1=1");

            if (string.IsNullOrEmpty(sStep) || sStep == "-")
            { }
            else
            {
                sbQuery.AppendFormat("and Step ='{0}'", sStep);
            }

            if (string.IsNullOrEmpty(sCustCode) || sCustCode == "-")
            { }
            else
            {
                sbQuery.AppendFormat("and CustCode ='{0}'", sCustCode);
            }

            if (string.IsNullOrEmpty(sDevice) || sDevice == "-")
            { }
            else
            {
                sbQuery.AppendFormat("and Device like '%{0}%' ", sDevice);
            }

            if (string.IsNullOrEmpty(sPackage) || sPackage == "-")
            { }
            else
            {
                sbQuery.AppendFormat(" and Package like '%{0}%' ", sPackage);
            }

            if (string.IsNullOrEmpty(sPinQty) || sPackage == "-")
            { }
            else
            {
                sbQuery.AppendFormat(" and PinQty '%{0}%' ", sPinQty);
            }
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
            if (myDB.IsSucc)
            {
                bResult = true;
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }

        }
        catch (Exception ex)
        {
            bResult = false;
            sErrMsg = String.Format("Occur exception, info: {0}", ex.Message);
        }

        return dsResult;
    }
    public DataSet Get_Package(String sStep, String sCustCode, String sDevice, String sPackage, String sPinQty, ref bool bResult, ref String sErrMsg)
    {
        DataSet dsResult = new DataSet();

        try
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select distinct Package from TMS_CustInfo where 1=1");

            if (string.IsNullOrEmpty(sStep) || sStep == "-")
            { }
            else
            {
                sbQuery.AppendFormat("and Step ='{0}'", sStep);
            }

            if (string.IsNullOrEmpty(sCustCode) || sCustCode == "-")
            { }
            else
            {
                sbQuery.AppendFormat("and CustCode ='{0}'", sCustCode);
            }

            if (string.IsNullOrEmpty(sDevice) || sDevice == "-")
            { }
            else
            {
                sbQuery.AppendFormat("and Device like '%{0}%' ", sDevice);
            }

            if (string.IsNullOrEmpty(sPackage) || sPackage == "-")
            { }
            else
            {
                sbQuery.AppendFormat(" and Package like '%{0}%' ", sPackage);
            }

            if (string.IsNullOrEmpty(sPinQty) || sPackage == "-")
            { }
            else
            {
                sbQuery.AppendFormat(" and PinQty '%{0}%' ", sPinQty);
            }
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
            if (myDB.IsSucc)
            {
                bResult = true;
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }

        }
        catch (Exception ex)
        {
            bResult = false;
            sErrMsg = String.Format("Occur exception, info: {0}", ex.Message);
        }

        return dsResult;
    }
    public DataSet Get_PinQty(String sStep, String sCustCode, String sDevice, String sPackage, String sPinQty, ref bool bResult, ref String sErrMsg)
    {
        DataSet dsResult = new DataSet();

        try
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select distinct PinQty from TMS_CustInfo where 1=1");

            if (string.IsNullOrEmpty(sStep) || sStep == "-")
            { }
            else
            {
                sbQuery.AppendFormat("and Step ='{0}'", sStep);
            }

            if (string.IsNullOrEmpty(sCustCode) || sCustCode == "-")
            { }
            else
            {
                sbQuery.AppendFormat("and CustCode ='{0}'", sCustCode);
            }

            if (string.IsNullOrEmpty(sDevice) || sDevice == "-")
            { }
            else
            {
                sbQuery.AppendFormat("and Device like '%{0}%' ", sDevice);
            }

            if (string.IsNullOrEmpty(sPackage) || sPackage == "-")
            { }
            else
            {
                sbQuery.AppendFormat(" and Package like '%{0}%' ", sPackage);
            }

            if (string.IsNullOrEmpty(sPinQty) || sPackage == "-")
            { }
            else
            {
                sbQuery.AppendFormat(" and PinQty '%{0}%' ", sPinQty);
            }
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
            if (myDB.IsSucc)
            {
                bResult = true;
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }

        }
        catch (Exception ex)
        {
            bResult = false;
            sErrMsg = String.Format("Occur exception, info: {0}", ex.Message);
        }

        return dsResult;
    }
   
 
     #region Local Info
    // 根据Customer Code和Type查询出Location列表
    public DataSet Get_Loc(String sSite, String sStep, String sType, String sCustCode,String sLocName ,String sLocID, ref bool bResult, ref String sErrMsg)
    {
        DataSet dsResult;
        bResult = false;

        try
        {
            StringBuilder sbFilter = new StringBuilder();
            sbFilter.Append("select * from dbo.TMS_Setting_Location");
            sbFilter.AppendFormat(" where Site='{0}' and Step='{1}' ", sSite, sStep);
            if (sType != "-" && sType != "")
            {
                sbFilter.AppendFormat(" and Type='{0}' ", sType);
            }
            if (sCustCode != "-" && sCustCode != "")
            {
                sbFilter.AppendFormat(" and CustCode='{0}' ", sCustCode);
            }
            if (sLocName != "-" && sLocName != "")
            {
                sbFilter.AppendFormat(" and LocName='{0}' ", sLocName);
            }
            if (sLocID != "-" && sLocID != "")
            {
                sbFilter.AppendFormat(" and LocID='{0}' ", sLocID);
            }
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbFilter.ToString(), ref dtParams, CommandType.Text);

            if (myDB.IsSucc)
            {
                bResult = true;
                sErrMsg = "";
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return dsResult;
    }

    public DataSet Get_Loc( String sStep, String sLocID, ref bool bResult, ref String sErrMsg)
    {
        DataSet dsResult;
        bResult = false;

        try
        {
            StringBuilder sbFilter = new StringBuilder();
            sbFilter.Append("select * from dbo.TMS_Setting_Location");
            sbFilter.AppendFormat(" where  Step='{0}' ",  sStep);
            if (sLocID != "-" && sLocID != "")
            {
                sbFilter.AppendFormat(" and LocID='{0}' ", sLocID);
            }

            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbFilter.ToString(), ref dtParams, CommandType.Text);

            if (myDB.IsSucc)
            {
                bResult = true;
                sErrMsg = "";
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return dsResult;
    }

    public DataSet Get_Loc(String sStep,  ref bool bResult, ref String sErrMsg)
    {
        DataSet dsResult;
        bResult = false;

        try
        {
            StringBuilder sbFilter = new StringBuilder();
            sbFilter.Append("select distinct LocID from dbo.TMS_Setting_Location");
            sbFilter.AppendFormat(" where  Step='{0}' ", sStep);        

            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbFilter.ToString(), ref dtParams, CommandType.Text);

            if (myDB.IsSucc)
            {
                bResult = true;
                sErrMsg = "";
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return dsResult;
    }
  #endregion
  
    public DataSet Get_MCModel_ByMCType(String sType, ref bool bResult, ref String sErrMsg)
    {
        DataSet dsResult;
        bResult = false;

        try
        {
            StringBuilder sbFilter = new StringBuilder();
            sbFilter.Append("select distinct ModelID from dbo.TMS_Machine where 1=1");

            if (sType != "-" && sType != "")
            {
                sbFilter.AppendFormat(" and Type='{0}' ", sType);
            }         
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbFilter.ToString(), ref dtParams, CommandType.Text);

            if (myDB.IsSucc)
            {
                bResult = true;
                sErrMsg = "";
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return dsResult;
    }
  
   // 寻找机台号
    public DataSet Get_MCInfo_ByModel(String sType, String sModel,  ref bool bResult, ref String sErrMsg)
    {
        DataSet dsResult;
        bResult = false;

        try
        {
            StringBuilder sbFilter = new StringBuilder();
            sbFilter.Append("select * from dbo.TMS_Machine where 1=1");
           
            if (sType != "-" && sType != "")
            {
                sbFilter.AppendFormat(" and Type='{0}' ", sType);
            }
            if (sModel != "-" && sModel != "")
            {
                sbFilter.AppendFormat(" and ModelID='{0}' ", sModel);
            }

            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbFilter.ToString(), ref dtParams, CommandType.Text);

            if (myDB.IsSucc)
            {
                bResult = true;
                sErrMsg = "";
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return dsResult;
    }
   

    public DataSet Get_BinCheck_ByDevice(String CustCode, String Device, ref bool bResult, ref String sErrMsg)
    {
        DataSet dsResult;
        bResult = false;

        try
        {
            StringBuilder sbFilter = new StringBuilder();
            sbFilter.Append("select * from dbo.TMS_BinCheck where 1=1");

            if (CustCode != "-" && CustCode != "")
            {
                sbFilter.AppendFormat(" and CustCode='{0}' ",CustCode);
            }
            if (Device != "-" && Device != "")
            {
                sbFilter.AppendFormat(" and Device Like '%{0}%' ", Device);
            }
            //sbFilter.AppendFormat(" and Location is not null");


            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbFilter.ToString(), ref dtParams, CommandType.Text);

            if (myDB.IsSucc)
            {
                bResult = true;
                sErrMsg = "";
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return dsResult;
    }

    public DataSet Get_Category_ByUserID(String UserID,String Class ,ref bool bResult, ref String sErrMsg)
    {
        DataSet dsResult;
        bResult = false;

        try
        {
            StringBuilder sbFilter = new StringBuilder();
            sbFilter.Append("select b.* from  dbo.TMS_User_Info a left join dbo.TMS_Category b on a.Step= b.Step where 1=1 ");

            //if (UserID != "-" && UserID != "")
            
                sbFilter.AppendFormat(" and a.UserID='{0}'  ", UserID);
            
            if (Class != "-" && Class != "")
            {
                sbFilter.AppendFormat("and b.Class='{0}' ",Class);
            }
            DBAccess myDB = new DBAccess(sConnString);
            DataTable dtParams = myDB.GetDataTable_ParaSchema();
            dsResult = myDB.ExecGetData(sbFilter.ToString(), ref dtParams, CommandType.Text);

            if (myDB.IsSucc)
            {
                bResult = true;
                sErrMsg = "";
            }
            else
            {
                bResult = false;
                sErrMsg = myDB.ErrMsg;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return dsResult;
    }

    public DataSet Get_PicByPicID(String sSite, string sClass,String sStep, String sType, String sPartID, ref string sErrMsg)
    {
        DataSet dsResult = new DataSet();
        try 
        {
            //get the  PicID
            string CategoryID = "";
            if(sClass=="Material")
            {
                 StringBuilder sbQuery = new StringBuilder();
                sbQuery.Append("select a.* from TMS_Material_BasicInfo a,dbo.TMS_Category b where a.CategoryID=b.CategoryID ");
                sbQuery.AppendFormat(" and b.Site='{0}' and b.Step='{1}' and b.class='{2}' and b.Type='{3}' and a.PartNo=N'{4}' ", sSite, sStep,sClass, sType, sPartID);
                DBAccess myDB = new DBAccess(sConnString);
                DataTable dtParams = myDB.GetDataTable_ParaSchema();
                 dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
                 if (myDB.IsSucc)
                 {
                     CategoryID  = dsResult.Tables[0].Rows[0]["CategoryID"].ToString().Trim() ;
                 }
                 else
                 {
                     sErrMsg = myDB.ErrMsg;
                     return dsResult;
                 }
            }
            else if (sClass == "Tool")
            {
                StringBuilder sbQuery = new StringBuilder();
                sbQuery.Append("select a.* from TMS_Sparepart_BasicInfo a,dbo.TMS_Category b where a.CategoryID=b.CategoryID ");
                sbQuery.AppendFormat(" and b.Site='{0}' and b.Step='{1}' and b.class='{2}' and b.Type='{3}' and a.ToolID=N'{4}' ", sSite, sStep, sClass, sType, sPartID);
                DBAccess myDB = new DBAccess(sConnString);
                DataTable dtParams = myDB.GetDataTable_ParaSchema();
                dsResult = myDB.ExecGetData(sbQuery.ToString(), ref dtParams, CommandType.Text);
                if (myDB.IsSucc)
                {
                    CategoryID =  dsResult.Tables[0].Rows[0]["CategoryID"].ToString().Trim();
                }
                else
                {
                    sErrMsg = myDB.ErrMsg;
                    return dsResult;
                }
            }


            //get picture 
            StringBuilder sbQuery_Pic = new StringBuilder();
            sbQuery_Pic.Append("select * from TMS_Picture ");
            sbQuery_Pic.AppendFormat(" where CategoryID='{0}' and ToolID='{1}'", CategoryID,sPartID);
            DBAccess myDB_Pic = new DBAccess(sConnString);
            DataTable dtParams_Pic = myDB_Pic.GetDataTable_ParaSchema();
            dsResult = myDB_Pic.ExecGetData(sbQuery_Pic.ToString(), ref dtParams_Pic, CommandType.Text);
            if (myDB_Pic.IsSucc)
            {
                  if (dsResult.Tables[0].Rows.Count < 1)
                     {
                         sErrMsg = "No find Picture";
                         return dsResult;
                     }
            }
            else
            {
                sErrMsg = myDB_Pic.ErrMsg;
                return dsResult;
            }
           
        }
        catch (Exception ex)
        {           
            sErrMsg = String.Format("Occur excpeiton, info: {0}", ex.Message);
        }
        return dsResult;
    }
    #endregion
        

   

}

Ora

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using System.Collections;

namespace MyDAL
{
    public partial class DBAccess
    {
        #region Parameter
        private ArrayList GetParams_Ora(DataTable dt)
        {
            ArrayList list = new ArrayList();
            foreach (DataRow dr in dt.Rows)
            {
                string paraName = dr["paraName"].ToString();
                string dataValue = dr["dataValue"].ToString();
                string paraDirect = dr["paraDirect"].ToString();
                OracleParameter p = new OracleParameter(paraName, OracleType.VarChar);
                p.Value = dataValue;
                switch (paraDirect.ToUpper())
                {
                    case "INPUT":
                        p.Direction = ParameterDirection.Input;
                        break;
                    case "INPUTOUTPUT":
                        p.Direction = ParameterDirection.InputOutput;
                        break;
                    case "OUTPUT":
                        p.Direction = ParameterDirection.Output;
                        break;
                    case "RETURNVALUE":
                        p.Direction = ParameterDirection.ReturnValue;
                        break;
                    case "CURSOR":
                        p.OracleType = OracleType.Cursor;
                        p.Direction = ParameterDirection.Output;
                        break;
                    default:
                        p.Direction = ParameterDirection.Input;
                        break;
                }
                if (p.Direction != ParameterDirection.Input)
                    p.Size = 2048;//为Output 参数设定一个比较大的空间
                list.Add(p);
            }
            return list;
        }
        #endregion

        /// <summary>
        /// 需要返回 DataSet 查询结果的
        /// </summary>
        /// <param name="sqlStr">SqlString</param>
        /// <param name="dt_paras">ParaSchema</param>        
        /// <returns>DataSet</returns>
        public DataTable ExecGetData_OraSqlStr(string sqlStr, ref DataTable dt_paras)
        {
            DataTable dt_result = new DataTable("dt_result");
            try
            {
                using (OracleConnection conn = new OracleConnection(ConnString))
                {
                    conn.Open();
                    using (OracleTransaction  tran = conn.BeginTransaction())
                    {
                        OracleCommand com = conn.CreateCommand();
                        OracleDataAdapter sd = new OracleDataAdapter(com);
                        try
                        {
                            com.CommandText = sqlStr;//.Replace(";","");
                            com.CommandType =  CommandType.Text ;
                            com.Transaction = tran;
                            com.CommandTimeout = 120;

                            ArrayList list = GetParams_Ora(dt_paras);

                            foreach (OracleParameter para in list)
                                com.Parameters.Add(para);

                            sd.Fill(dt_result);
                            IsSucc = true;
                            ErrMsg = "";
                        }
                        catch(Exception ex)
                        {
                            dt_result = null;
                            IsSucc = false;
                            ErrMsg = ex.Message;
                        }
                        finally
                        {
                            sd.Dispose();
                            com.Dispose();
                        }
                    }
                }
            }
            catch(Exception ex)
            {
                dt_result = null;
                IsSucc = false;
                ErrMsg = ex.Message;
            }

            return dt_result;
        }

        /// <summary>
        /// 不需要返回 DataSet 的查询
        /// </summary>
        /// <param name="text">proc_Name or SQL _tring</param>
        /// <param name="dt">ParaSchema or ResultDataTable</param>
        /// <param name="type">CommandType.StoredProcedure or CommandType.Text</param>
        /// <returns>void</returns>
        public bool ExecNoQuery_Ora(string procOrSql, ref DataTable dt_paras, CommandType type)
        {
            try
            {
                using (OracleConnection conn = new OracleConnection(ConnString))
                {
                    conn.Open();
                    using (OracleTransaction tran = conn.BeginTransaction())
                    {
                        try
                        {
                            OracleCommand com = conn.CreateCommand();
                            com.CommandText = procOrSql;
                            com.CommandType = type;
                            com.Transaction = tran;
                            com.CommandTimeout = 120;

                            ArrayList list = GetParams_Ora(dt_paras);

                            foreach (OracleParameter para in list)
                                com.Parameters.Add(para);

                            int count = com.ExecuteNonQuery();

                            DataRow dr = dt_paras.NewRow();
                            dr[0] = "rowcount";
                            dr[1] = "Int";
                            dr[3] = ParameterDirection.ReturnValue.ToString();
                            dr[4] = count.ToString();
                            dt_paras.Rows.Add(dr);

                            for (int i = 0; i < com.Parameters.Count; i++)
                            {
                                if (com.Parameters[i].Direction == ParameterDirection.InputOutput
                                    || com.Parameters[i].Direction == ParameterDirection.Output
                                    || com.Parameters[i].Direction == ParameterDirection.ReturnValue)
                                    dt_paras.Rows[i]["resultValue"] = com.Parameters[i].Value;
                            }
                            tran.Commit();
                            IsSucc = true;
                            ErrMsg = "";
                            return true;
                        }
                        catch(Exception ex)
                        {
                            tran.Rollback();
                            IsSucc = false;
                            ErrMsg = ex.Message;
                            return false;
                        }
                    }
                }
            }
            catch(Exception ex)
            {
                IsSucc = false;
                ErrMsg = ex.Message;
                return false;
            }
        }

    }
}