存储增删查改

存储增删查改

一、查
//1、无参
//1、sql
public DataTable getidentall()
{
   DataTable ds = new DataTable();
   WBSQLHelper.ExecuteDataset(ds, "dbo.proc_getidentall");
   return ds;
 }

//2、proc

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[proc_getenable_OR]
 @source_code  nvarchar(20)
as
begin

select Enable,product from source where source_code=@source_code

end
//2、有参
//1、sql
        public DataSet getshipment(string contract)
        {
            //string strsql = @"select shipments from contract where contractId=@contract";
            SqlParameter[] parameters = {
                    new SqlParameter("@contract", SqlDbType.NVarChar,20)
            };
            parameters[0].Value = contract;

            DataSet ds = new DataSet();
            WBSQLHelper.ExecuteDataset(ds, "dbo.proc_getshipment", parameters);
            return ds;
        }
//2、proc
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[proc_getinfobydocket_OR]
 @docket_no  nvarchar(20)
as
begin
select dt.docket_no 记录编号,dt.orderNo 当前排队序号,dt.truck 车号,dr.drivername 驾驶员,dt.team 运输公司,dt.billofladingid 订单编号,dt.VIPbillofladingid VIP订单号,
                              customer.name 客户名称,site.name 收货地址,p.name 产品规格,dt.ratedload 额定装载量,
                              dt.inbridgedate 第一次称重时间,dt.inweight 第一次称重,dt.registtime 登记时间,t.cardid RFID,dt.source 仓号,
                              case dt.status WHEN '3' THEN '等待装料' when '4' then '等待入库' when '5' then '装料中' when '6' then '完成装料' when '90' then '90.等待空车计量' ELSE '空闲' END 状态
                              from  Document dt
                              left join Driver dr on dt.driverid=dr.driverid
                              left join customer on dt.customer =customer.code                                
                              left join site on dt.site =site.code 
                              left join product p on dt.product=p.code
                              left join truck t on t.code=dt.truck
                              where 
                              (dt.status between 3 and 6) AND dt.docket_no =@docket_no
                              order by left(dt.orderNo,2) desc,dt.orderNo asc
end

//二、增删改
//1、增
//1、sql
public bool insertInregister(Model.Document d)
{
            try
            {
                SqlParameter[] parameters ={
                                           new SqlParameter ("@docket_no",SqlDbType.NVarChar,50           ),
                                           new SqlParameter ("@company",SqlDbType.NVarChar,50           ),
                                           new SqlParameter ("@plant",SqlDbType.NVarChar,50           ),
                                           new SqlParameter ("@truck",SqlDbType.NVarChar,50           ),
                                           new SqlParameter ("@driverid",SqlDbType.Int           ),
                                           new SqlParameter ("@contract",SqlDbType.NVarChar,50           ),
                                           new SqlParameter ("@billofladingid",SqlDbType.NVarChar,50           )
                                           };
                parameters[0].Value = d.docket_no;
                parameters[1].Value = d.company;
                parameters[2].Value = d.plant;
                parameters[3].Value = d.truck;
                parameters[4].Value = d.driverid;
                parameters[5].Value = d.contract;
                parameters[6].Value = d.billofladingid;
                WBSQLHelper.RunProcedure("proc_addInregister", parameters);
            }

            catch (Exception ex)
            {
                throw;
            }
            return true;
        }
//2、proc
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_addLogDetails_EG] (
	@LogDate datetime,   
	@LogLogger varchar(50),  
	@LogActionClick varchar(50),  
	@LogMessage varchar(4000),
	@UserName varchar(50),
	@UserIP varchar(50)
)
as 
begin
		--不存在添加记录
 INSERT 
 INTO LogDetails(
   LogDate
  ,LogLogger
  ,LogActionClick
  ,LogMessage
  ,UserName
  ,UserIP
  ) 
  VALUES
  (
    @LogDate,
    @LogLogger,
    @LogActionClick,
    @LogMessage,
    @UserName,
    @UserIP
    
  )
end

//2、 改
//1、sql
public bool cancelorderno(Model.Document d)
{
            //string sql = string.Format(@"update Document set status='0' where docket_no='{0}'", d.docket_no);
            //int i = WBSQLHelper.ExceQuery(sql);
            //return i > 0;
            try
            {
                SqlParameter[] parameters ={
                                           new SqlParameter ("@docket_no",SqlDbType.NVarChar,50           ),
                                           };
                parameters[0].Value = d.docket_no;
                WBSQLHelper.RunProcedure("proc_updatecancelorderno", parameters);
            }

            catch (Exception ex)
            {
                throw;
            }
            return true;
        }


//2、proc
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_updatecancelorderno] (
 	@docket_no varchar(50)
)
as 
begin
		--不存在添加记录
 update 
       Document
 set	
       status='0' 
 where 
       docket_no=@docket_no
end



//返回错误码
//一
  public string  btnsaveedit(Model.Document d)
        {
            try
            {
                SqlParameter[] parameter ={
                                           new SqlParameter {ParameterName="@billofladingid",SqlDbType=SqlDbType.NVarChar,Value=d.billofladingid },
                                           new SqlParameter {ParameterName="@ratedload",SqlDbType=SqlDbType.Decimal  ,Value=  d.ratedload },
                                           new SqlParameter {ParameterName="@team" ,SqlDbType=SqlDbType.NVarChar   ,Value= d.team      },
                                           new SqlParameter {ParameterName="@upd_host" ,SqlDbType=SqlDbType.NVarChar   ,Value=   d.upd_host    },
                                           new SqlParameter {ParameterName="@erro_message",SqlDbType=SqlDbType.Int   ,Direction= ParameterDirection.Output         },
                                           new SqlParameter {ParameterName="@upd_by",SqlDbType=SqlDbType.NVarChar   ,Value=  d.upd_by         },
                                           new SqlParameter {ParameterName="@docket_no",SqlDbType=SqlDbType.NVarChar  ,Value= d.docket_no        }
                                           };
                string msg = WBSQLHelper.ExeProc("proc_EditeDocumentD", parameter, "@erro_message");//msg为输出得值

                return msg;
            }
            catch (Exception ex)
            {

                throw;
            }
        }
//二
  public static string GetTruckQueue(TruckQueue d)
        {
            SqlParameter[] parameter = {
                new SqlParameter { ParameterName="@company",SqlDbType=SqlDbType.VarChar,Value=d.Plantid},
                new SqlParameter { ParameterName="@truck",SqlDbType=SqlDbType.VarChar,Value=d.truckName },
                new SqlParameter { ParameterName="@drivername",SqlDbType=SqlDbType.VarChar,Value= d.drivername },
                new SqlParameter { ParameterName="@billofladingid",SqlDbType=SqlDbType.VarChar,Value= d.billofladingid},
                new SqlParameter { ParameterName="@team",SqlDbType=SqlDbType.VarChar,Value=d.transportname},
                new SqlParameter { ParameterName="@VIPbillofladingid",SqlDbType=SqlDbType.VarChar,Value= d.VIPbillofladingid },
                new SqlParameter { ParameterName="@ratedload",SqlDbType=SqlDbType.Decimal,Value=d.ratedload },
                new SqlParameter { ParameterName="@dayplanid",SqlDbType=SqlDbType.VarChar,Value=d.dayplanid},
                new SqlParameter { ParameterName="@telephone",SqlDbType=SqlDbType.VarChar,Value=d.telephone},
                new SqlParameter { ParameterName="@erro_message", SqlDbType=SqlDbType.NVarChar,Size=300,Direction= ParameterDirection.Output },
            };
            WBSQLHelper.ExecuteScalar(CommandType.StoredProcedure, "pro_TruckQueue", parameter);
            //WBSQLHelper.ExecuteNonQuery(CommandType.StoredProcedure, "pro_TruckQueue", parameter);
            //string msg = WBSQLHelper.ExeProc("pro_TruckQueue", parameter, "@erro_message");//msg为输出得值
            string msg = parameter[9].Value.ToString();

            return msg;

        }