c# Microsoft Dynamics GP向taCreateEmployee添加新员工
本文关键字：taCreateEmployee 添加 新员工 GP Microsoft Dynamics | 更新日期: 2023-09-27 18:10:57
如何从taCreateEmployee获得下一个可用的员工id ?
我构建了一个小的windows窗体程序，使用eConnect工具将新员工添加到Microsoft Dynamics Great Plains数据库中。我能够成功地构建xml文档并将其发送到服务器，但我得到一个错误消息:
'taCreateEmployee' '期望参数'@I_vEMPLOYID'，该参数未提供。
所以我想我需要在尝试插入记录之前获得员工id，但我如何获得员工id?
<?xml version="1.0" encoding="utf-8"?>
<eConnect xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<UPRCreateEmployeeType><eConnectProcessInfo xsi:nil="true" />
<taRequesterTrxDisabler_Items xsi:nil="true" />
<taCreateEmployee>
<EMPLOYID /> //HAVE ALSO TRIED <EMPLOYID></EMPLOYID> AND <EMPLOYID> </EMPLOYID> with a space
<EMPLCLAS>SEASONAL</EMPLCLAS>
<LASTNAME>Lastname</LASTNAME>
<FRSTNAME>Firstname</FRSTNAME>
<ADRSCODE>PRIMARY</ADRSCODE>
<ADDRESS1>111 Main St</ADDRESS1>
<CITY>City</CITY>
<STATE>State</STATE>
<ZIPCODE>66000</ZIPCODE>
<PHONE1>5730000000</PHONE1>
<SOCSCNUM>00000000</SOCSCNUM>
<BRTHDATE>1930-10-30 0:00:00.000</BRTHDATE>
<LOCATNID>PITS</LOCATNID>
<SUTASTAT>HI</SUTASTAT>
<BIRTHDAY>129</BIRTHDAY>
<BIRTHMONTH>10</BIRTHMONTH>
</taCreateEmployee>
<taCreateInternetAddresses_Items xsi:nil="true"/>
</UPRCreateEmployeeType>
</eConnect>
private void SerializeObject(AllASEmlpoyees employeeList)
{
try
{
eConnectType econnect = new eConnectType();
UPRCreateEmployeeType[] value = new UPRCreateEmployeeType[employeeList.Candidates.Count()];
var count = 0;
foreach (var item in employeeList.Candidates)
{
UPRCreateEmployeeType employee = new UPRCreateEmployeeType();
//employee record
taCreateEmployee employeerecord = new taCreateEmployee();
//Console.WriteLine("First Name: " + item.FirstName + " Last Name " + item.LastName);
var _with1 = employeerecord;
_with1.EMPLOYID = "";
_with1.EMPLCLAS = item.GPEmloyeeClass;
_with1.INACTIVE = 0;
_with1.FRSTNAME = item.FirstName;
_with1.LASTNAME = item.LastName;
//_with1.MIDLNAME = "";
_with1.ADRSCODE = item.GPAddressCode;
_with1.ADDRESS1 = item.Address1;
_with1.ADDRESS2 = item.Address2;
_with1.CITY = item.City;
_with1.STATE = item.GPStateFullName;
_with1.ZIPCODE = item.Zip;
_with1.PHONE1 = item.Phone;
_with1.SOCSCNUM = item.SSNum;
_with1.BIRTHDAY = (short)item.GPBirthday.Day;
_with1.BIRTHMONTH = (short)item.GPBirthday.Month;
_with1.BRTHDATE = item.GPBirthdayAsString;
_with1.LOCATNID = item.GPLocationId;
_with1.SUTASTAT = item.GPStateAbbreviation;
_with1.EMPLOYMENTTYPE = item.GPEmployeementType;
_with1.UpdateIfExists = 1;
employee.taCreateEmployee = employeerecord;
value[count] = employee;
//add array to xml table
count++;
}
econnect.UPRCreateEmployeeType = value;
FileStream fs = new FileStream(directory + @"'" + file, FileMode.Create);
XmlTextWriter writer = new XmlTextWriter(fs, new UTF8Encoding());
XmlSerializer serializer = new XmlSerializer(typeof(eConnectType));
serializer.Serialize(writer, econnect);
writer.Close();
}
catch (ApplicationException ex)
{
Console.WriteLine("Exception: " + ex.ToString());
}
}
这里是我使用连接工具进行调用的地方:
public void eConnectSend(AllASEmlpoyees employeeList)
{
//Serialized XML File
string xmldocument = null;
//Connection String
string connectString = null;
//Result
string xmlobject = null;
using (eConnectMethods eConCall = new eConnectMethods())
{
try
{
SerializeObject(employeeList);
System.Xml.XmlDocument xmldoc = new System.Xml.XmlDocument();
xmldoc.Load(directory + @"'" + file);
xmldocument = xmldoc.OuterXml;
connectString = "thisismyconnectionstringwhichisworking";
//send data to Great Plains
xmlobject = eConCall.CreateTransactionEntity(connectString, xmldocument);
Console.WriteLine("Object returned: " + xmlobject.ToString());
}
catch (eConnectException exp)
{
Console.WriteLine("Exception: " + exp.ToString());
//Interaction.MsgBox(exp.ToString);
}
catch (System.Exception ex)
{
Console.WriteLine("Exception: " + ex.ToString());
//Interaction.MsgBox(ex.ToString());
}
finally
{
//eConCall.Dispose();
Console.WriteLine("Done");
Console.ReadLine();
this.cleanUpObjectsOnComplete();
}
}
}
任何帮助我将是伟大的!
我不知道这是否是一个工作，但这就是我如何解决这个问题。我向数据库添加了三个存储过程。
存储过程:
- 使用select查询检查员工是否存在，如果存在则返回id
- 查询UPR40201表获取下一个可用id
- 记录添加成功后更新UPR40201表
第一个，接受ss#作为输入参数。使用传递的参数，我在表上运行查询，查找已经存在的雇员。如果是，那么我返回id并在taCreateEmployee上调用UpdateIfExits。
ALTER PROCEDURE [dbo].[MY_Emloyee_GetEmployeeIdBySocialSecurityNumber]
-- Add the parameters for the stored procedure here
@ssnum AS varchar(255),
@EmployeeID As varchar(10) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
-- Insert statements for procedure here
SELECT @EmployeeID=taCreateEmployee.EMPLOYID FROM taCreateEmployee WHERE taCreateEmployee.SOCSNUM = @ssnum
return 1
END TRY
BEGIN CATCH
DECLARE
@ErrorSeverity INT,
@ErrorNumber INT,
@ErrorMessage NVARCHAR(4000),
@ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
IF @ErrorState = 0
SET @ErrorState = 1
RAISERROR ('ERROR OCCURED:%d',
@ErrorSeverity,
@ErrorState,
@ErrorNumber)
IF XACT_STATE() < 0
RETURN @ErrorState
END CATCH
END
END
如果不存在，则调用第二个存储过程，该存储过程从表UPR40201列NEXTEMPID获取下一个可用的雇员id。该表只保存一个值，即下一个可用的id。
ALTER PROCEDURE [dbo].[MY_Employee_GetNewEmployeID]
-- Add the parameters for the stored procedure here
@EmployeeID AS varchar(10) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
-- Insert statements for procedure here
SELECT @EmployeeID=NEXTEMPID FROM UPR40201
RETURN 1
END TRY
BEGIN CATCH
DECLARE
@ErrorSeverity INT,
@ErrorNumber INT,
@ErrorMessage NVARCHAR(4000),
@ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
IF @ErrorState = 0
SET @ErrorState = 1
RAISERROR ('ERROR OCCURED:%d',
@ErrorSeverity,
@ErrorState,
@ErrorNumber)
IF XACT_STATE() < 0
RETURN @ErrorState
END CATCH
END
一旦我有了一个有效的员工Id，我就使用eConnect工具更新记录。成功后，我通过调用最终存储过程并增加id来更新表UPR40201列NEXTEMPID中的值。
ALTER PROCEDURE [dbo].[MY_Employee_UpdateNextEmployeeId]
-- Add the parameters for the stored procedure here
@NEXTEMPID AS varchar(40)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--BEGIN COMMANDS
BEGIN TRY
BEGIN TRANSACTION
-- Insert statements for procedure here
DELETE FROM UPR40201 WHERE NEXTEMPID != '0';
INSERT INTO UPR40201 (NEXTEMPID) VALUES (@NEXTEMPID);
COMMIT TRANSACTION
RETURN 1
END TRY
BEGIN CATCH
DECLARE
@ErrorSeverity INT,
@ErrorNumber INT,
@ErrorMessage NVARCHAR(4000),
@ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
IF @ErrorState = 0
SET @ErrorState = 1
RAISERROR ('ERROR OCCURED:%d',
@ErrorSeverity,
@ErrorState,
@ErrorNumber)
IF XACT_STATE() < 0
ROLLBACK TRANSACTION
RETURN @ErrorState
END CATCH
END