存儲增删查改
一、查
//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 [email protected]";
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;
}
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)