Now you should be in the method editor, note if you can't edit anything you probably just double clicked it or selected view. Go back, right click the method name and select edit and you should be ok.
Now here's the full code for my method, it's straight out of my system and not cleaned up at all:
/**
* This function gets our Orcad CIP/CIS parts out of our database and returns them to
* Aras Innovator
*/
//This is the SQL connection string to connect to our SQL Express database.
//Note the login details are the default CIP internal user according to EMA's
//documentation.
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection ();
conn.ConnectionString =
"data source=MININT-PHRAUUK\\sqlexpress;User Id=CIP_E_Internal_User;Password=bra#ru5=phaT;" +
"initial catalog=CIP_E;";
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlDataReader reader;
//these two items are what is going to get returned
Item res = this.getInnovator().newResult("");
XmlElement resultNd = (XmlElement) res.dom.SelectSingleNode("/*/*/*");
//this is a handy debug statement htat saves the "Incoming AML Request" to the temp directory
//of the Innovator server. For us that is:
//C:\Program Files (x86)\Aras\Innovator\Innovator\Server\temp
//this.dom.Save(CCO.Server.MapPath("./temp/Incoming_AML.xml"));
/*StreamWriter sw = null;
sw = File.CreateText("C:/Program Files (x86)/Aras/Innovator/Innovator/Server/temp/loge.txt");
sw.WriteLine("--- BEGIN LOG ---");
sw.Flush();
*/
string action = "nothing";
//check if this is a List All
int iPage = 1;
int iPageSize = 25;
try{
XmlNode xPage = this.dom.SelectSingleNode("/Item/@page");
XmlNode xPageSize = this.dom.SelectSingleNode("/Item/@pagesize");
if(xPage.Value != "" && xPageSize.Value != ""){
iPage = int.Parse(xPage.Value);
iPageSize = int.Parse(xPageSize.Value);
//action = "list_all";
}
XmlNode xAction = this.dom.SelectSingleNode("/Item/@select");
string sAction = xAction.Value;
if(sAction != ""){
action = "list_all";
}
}catch (Exception ex){
}
//check if it is a single get
// <Item type="CIP_PART" levels="1" action="get" idlist="3FF86EECC96342C7825197321EFCC40D" />
string sID = "";
try{
XmlNode xAction = this.dom.SelectSingleNode("/Item/@action");
XmlNode xIdlist = this.dom.SelectSingleNode("/Item/@idlist");
if(xAction.Value == "get" && xIdlist.Value != "")
{
//convert ID
string sIdlist = xIdlist.Value;
if(sIdlist.Length == 32){
sID = sIdlist.Substring(0, 8) + sIdlist.Substring(8,4) + sIdlist.Substring(12,4) + sIdlist.Substring(16,4) + sIdlist.Substring(20,12);
action = "get_single";
}
}
}catch (Exception ex){
}
//open the sql connection
conn.Open();
//action = "list_all";
string where = "";
switch(action){
case "get_single":
//sw.WriteLine("action_single");
cmd.CommandText = "WITH PARTS AS(SELECT p.PART_NUMBER, p.RowID, des.description, b.manufacturer, b.[manufacturer PN], ROW_NUMBER() OVER (ORDER BY p.PART_NUMBER) AS 'RowNumber' FROM [CIP_E].[dbo].[AllParts] AS p "
+ "INNER JOIN [CIP_E].[dbo].[Component Manufacturer Parts] AS a ON p.RowID = a.PartGUID "
+ "INNER JOIN [CIP_E].[dbo].[Manufacturer Part Info] AS b ON a.ManufacturerGUID = b.RowID "
+ "Inner Join [CIP_E].[dbo].[CT_PART_DESCRIPTIONS] AS des ON des.RowID = p.RowID) "
+ "SELECT * FROM PARTS WHERE RowID = @rowid";
//WITH PARTS AS(SELECT p.PART_NUMBER, p.RowID, des.description, b.manufacturer, b.[manufacturer PN], ROW_NUMBER() OVER (ORDER BY p.PART_NUMBER) AS 'RowNumber' FROM [CIP_E].[dbo].[AllParts] AS p
//INNER JOIN [CIP_E].[dbo].[Component Manufacturer Parts] AS a ON p.RowID = a.PartGUID
//INNER JOIN [CIP_E].[dbo].[Manufacturer Part Info] AS b ON a.ManufacturerGUID = b.RowID
//Inner Join [CIP_E].[dbo].[CT_PART_DESCRIPTIONS] AS des ON des.RowID = p.RowID)
//SELECT * FROM PARTS WHERE RowID = '0BF585C8-14D8-4248-BDA0-4D7EDBB7BAB2'
//set the rowid
System.Data.SqlClient.SqlParameter param_rowid = new System.Data.SqlClient.SqlParameter();
param_rowid.ParameterName = "@rowid";
param_rowid.Value = "0BF585C8-14D8-4248-BDA0-4D7EDBB7BAB2";
cmd.Parameters.Add(param_rowid);
cmd.CommandType = CommandType.Text;
//sw.WriteLine("single sql: {0}", cmd.CommandText);
cmd.Connection = conn;
reader = cmd.ExecuteReader();
int i = 0;
while(reader.Read() && i < 100){
i++;
Item res5 = this.newItem("CIP_PART");
string newid = getNewID();
string id = reader["RowID"].ToString();
id = id.Replace("-", "");
res5.setID(id);
res5 = res5.apply();
res5.setProperty("part_number", reader["PART_NUMBER"].ToString());
res5.setProperty("manf_part_number", reader["Manufacturer PN"].ToString());
res5.setProperty("manufacturer", reader["Manufacturer"].ToString());
res5.setProperty("description", reader["description"].ToString());
resultNd.AppendChild(resultNd.OwnerDocument.ImportNode(res5.node, true));
}
break;
case "list_all":
/*
* Here we check to see if the user is searching for anything
*/
try{
XmlNode xPart = this.dom.SelectSingleNode("/Item/part_number/text()");
XmlNode xCond = this.dom.SelectSingleNode("/Item/part_number/@condition");
string sPart = xPart.Value;
System.Data.SqlClient.SqlParameter param_part = new System.Data.SqlClient.SqlParameter();
param_part.ParameterName = "@part";
if(xCond.Value == "like"){
//where += " AND p.PART_NUMBER LIKE '" + sPart.Replace("*", "%") + "'";
where += " AND p.PART_NUMBER LIKE @part";
param_part.Value = sPart.Replace("*", "%");
cmd.Parameters.Add(param_part);
}else{
//where += " AND p.PART_NUMBER = '" + sPart + "'";
where += " AND p.PART_NUMBER = @part";
param_part.Value = sPart;
cmd.Parameters.Add(param_part);
}
}catch (Exception ex){
}
try{
XmlNode xManfNum = this.dom.SelectSingleNode("/Item/manf_part_number/text()");
XmlNode xCond = this.dom.SelectSingleNode("/Item/manf_part_number/@condition");
string sManfNum = xManfNum.Value;
System.Data.SqlClient.SqlParameter param_manfnum = new System.Data.SqlClient.SqlParameter();
param_manfnum.ParameterName = "@manfnum";
if(xCond.Value == "like"){
//where += " AND b.[Manufacturer PN] LIKE '" + sManfNum.Replace("*", "%") + "'";
where += " AND b.[Manufacturer PN] LIKE @manfnum";
param_manfnum.Value = sManfNum.Replace("*", "%");
cmd.Parameters.Add(param_manfnum);
}else{
//where += " AND b.[Manufacturer PN] = '" + sManfNum + "'";
where += " AND b.[Manufacturer PN] = @manfnum";
param_manfnum.Value = sManfNum;
cmd.Parameters.Add(param_manfnum);
}
}catch (Exception ex){
}
try{
XmlNode xManf = this.dom.SelectSingleNode("/Item/manufacturer/text()");
XmlNode xCond = this.dom.SelectSingleNode("/Item/manufacturer/@condition");
string sManf = xManf.Value;
System.Data.SqlClient.SqlParameter param_manf = new System.Data.SqlClient.SqlParameter();
param_manf.ParameterName = "@manf";
if(xCond.Value == "like"){
//where += " AND b.[Manufacturer] LIKE '" + sManf.Replace("*", "%") + "'";
where += " AND b.[Manufacturer] LIKE @manf";
param_manf.Value = sManf.Replace("*", "%");
cmd.Parameters.Add(param_manf);
}else{
//where += " AND b.[Manufacturer] = '" + sManf + "'";
where += " AND b.[Manufacturer] = @manf";
param_manf.Value = sManf;
cmd.Parameters.Add(param_manf);
}
}catch (Exception ex){
//sw.WriteLine("No Manf found Found");
}
try{
XmlNode xDesc = this.dom.SelectSingleNode("/Item/description/text()");
XmlNode xCond = this.dom.SelectSingleNode("/Item/description/@condition");
string sDesc = xDesc.Value;
System.Data.SqlClient.SqlParameter param_desc = new System.Data.SqlClient.SqlParameter();
param_desc.ParameterName = "@desc";
if(xCond.Value == "like"){
//where += " AND des.[Description] LIKE '" + sDesc.Replace("*", "%") + "'";
where += " AND des.[Description] LIKE @desc";
param_desc.Value = sDesc.Replace("*", "%");
cmd.Parameters.Add(param_desc);
}else{
//where += " AND des.[Description] = '" + sDesc + "'";
where += " AND des.[Description] = @desc";
param_desc.Value = sDesc.Replace("*", "%");
cmd.Parameters.Add(param_desc);
}
}catch (Exception ex){
//sw.WriteLine("No description Found");
}
//cmd.CommandText = "SELECT COUNT(*) FROM dbo.AllParts WHERE PART_NUMBER LIKE 'CT%'" + where;
cmd.CommandText = "WITH PARTS AS(";
cmd.CommandText += "SELECT *, ROW_NUMBER() OVER (ORDER BY p.PART_NUMBER) AS 'RowNumber' FROM dbo.AllParts AS p WHERE p.PART_NUMBER LIKE 'CT%'";
cmd.CommandText += ")";
cmd.CommandText += "SELECT COUNT(*) FROM PARTS as p "
+ "INNER JOIN dbo.[Component Manufacturer Parts] AS a ON p.RowID = a.PartGUID "
+ "INNER JOIN dbo.[Manufacturer Part Info] AS b ON a.ManufacturerGUID = b.RowID "
+ "LEFT Join dbo.[CT_PART_DESCRIPTIONS] AS des ON des.RowID = p.RowID ";
//+ "WHERE 1" + where;
if(where != ""){
cmd.CommandText += "WHERE 'monkey' = 'monkey' " + where;
}
//sw.WriteLine("sql: {0}", cmd.CommandText);
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
int count = (int) cmd.ExecuteScalar();
//sw.WriteLine("Found: {0} parts", count);
//calculate the start and stop
int iStart = (iPage - 1) * iPageSize + 1;
int iEnd = iPage * iPageSize;
int iPageMax = count / iPageSize;
//is there an extra non full page?
if(iPageMax * iPageSize < count){
iPageMax++;
}
//looking for more records than exist
if(iEnd > count){
iEnd = count;
//iStart = iEnd - iPageSize;
if(iStart <0){
iStart = 0;
}
}
//now execute query for real
/* cmd.CommandText = "WITH PARTS AS(";
cmd.CommandText += "SELECT *, ROW_NUMBER() OVER (ORDER BY p.PART_NUMBER) AS 'RowNumber' FROM dbo.AllParts AS p WHERE p.PART_NUMBER LIKE 'CT%'";
cmd.CommandText += ")";
cmd.CommandText += "SELECT * FROM PARTS as p "
+ "INNER JOIN dbo.[Component Manufacturer Parts] AS a ON p.RowID = a.PartGUID "
+ "INNER JOIN dbo.[Manufacturer Part Info] AS b ON a.ManufacturerGUID = b.RowID "
+ "LEFT Join dbo.[CT_PART_DESCRIPTIONS] AS des ON des.RowID = p.RowID "
+ "WHERE RowNumber Between @low AND @high" + where;
*/
/* cmd.CommandText = "SELECT *, ROW_NUMBER() OVER (ORDER BY p.PART_NUMBER) AS 'RowNumber' FROM dbo.AllParts AS p ";
cmd.CommandText += ""
+ "INNER JOIN dbo.[Component Manufacturer Parts] AS a ON p.RowID = a.PartGUID "
+ "INNER JOIN dbo.[Manufacturer Part Info] AS b ON a.ManufacturerGUID = b.RowID "
+ "INNER Join dbo.[CT_PART_DESCRIPTIONS] AS des ON des.RowID = p.RowID "
+ "WHERE RowNumber Between @low AND @high" + where;*/
cmd.CommandText = "WITH PARTS AS(SELECT p.PART_NUMBER, p.RowID, des.description, b.manufacturer, b.[manufacturer PN], ROW_NUMBER() OVER (ORDER BY p.PART_NUMBER) AS 'RowNumber' FROM [CIP_E].[dbo].[AllParts] AS p "
+ "INNER JOIN [CIP_E].[dbo].[Component Manufacturer Parts] AS a ON p.RowID = a.PartGUID "
+ "INNER JOIN [CIP_E].[dbo].[Manufacturer Part Info] AS b ON a.ManufacturerGUID = b.RowID "
+ "Inner Join [CIP_E].[dbo].[CT_PART_DESCRIPTIONS] AS des ON des.RowID = p.RowID "
+ "WHERE 'monkey' = 'monkey' " + where + ") "
+ "SELECT * FROM PARTS WHERE RowNumber Between @low AND @high";
System.Data.SqlClient.SqlParameter param_low = new System.Data.SqlClient.SqlParameter();
param_low.ParameterName = "@low";
param_low.Value = iStart;
cmd.Parameters.Add(param_low);
System.Data.SqlClient.SqlParameter param_high = new System.Data.SqlClient.SqlParameter();
param_high.ParameterName = "@high";
param_high.Value = iEnd;
cmd.Parameters.Add(param_high);
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
//sw.WriteLine("Low: {0} High: {1}", iStart, iEnd);
//damn you for having no LIMIT function MS SQL
//sw.WriteLine("query: {0}", cmd.CommandText);
reader = cmd.ExecuteReader();
i = 0;
while(reader.Read() && i < iPageSize){
i++;
Item res5 = this.newItem("CIP_PART");
string newid = getNewID();
string id = reader["RowID"].ToString();
id = id.Replace("-", "");
res5.setID(id);
res5 = res5.apply();
res5.setProperty("part_number", reader["PART_NUMBER"].ToString());
res5.setProperty("start", iStart.ToString());
res5.setProperty("pageSize", iPageSize.ToString());
res5.setProperty("end", iEnd.ToString());
res5.setProperty("manf_part_number", reader["Manufacturer PN"].ToString());
res5.setProperty("manufacturer", reader["Manufacturer"].ToString());
res5.setProperty("description", reader["description"].ToString());
res5.setAttribute("page", iPage.ToString());
res5.setAttribute("pagemax", iPageMax.ToString());
res5.setAttribute("itemmax", count.ToString());
resultNd.AppendChild(resultNd.OwnerDocument.ImportNode(res5.node, true));
}
break;
default:
break;
}
////// I used to have everything in a try catch but it's hard to debug that way ;) So left this here
///// as a place holder.
try{
}
catch (Exception ex)
{
}
finally
{
//close db connection
conn.Close();
}
//sw.Flush();
//sw.Close();
return res;
Alright now I'll try to break it down a little bit to explain what I did here. The first part is the sql connection to the CIP database. For me the CIP database resides on the same MS SQL EXPRESS 2008 server as the CIP database does.
//This is the SQL connection string to connect to our SQL Express database.
//Note the login details are the default CIP internal user according to EMA's
//documentation.
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection ();
conn.ConnectionString =
"data source=MININT-PHRAUUK\\sqlexpress;User Id=CIP_E_Internal_User;Password=bra#ru5=phaT;" +
"initial catalog=CIP_E;";
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlDataReader reader;
The above code use the C# .net SqlConnection class to make a connection to our CIP database. Note the connection string which is the most important part. MINIT-PHRAUUK is the name IT gave my machine and sqlexpress is the instance of my sqlexpress server. The user ID and Password are the default user ID and password for the internal CIP user from EMAs setup docs. If you changed this you'll need to enter your info. Note this is not the same as the CIP user login, this is the login CIP itself uses. Finally initial catalog is set to CIP_E which is the default name of your CIP database.
The last two lines just create the sql command we'll use later, and the reader we'll use to read data out of the database.
//these two items are what is going to get returned
Item res = this.getInnovator().newResult("");
XmlElement resultNd = (XmlElement) res.dom.SelectSingleNode("/*/*/*");
The above two lines create a new Item, called res and an XmlElement that points to res. Res will be what we end up returning when we are call, and resultNd will help us write other Items into the Res result set.Before the next part I'll go off track a little in case you're totally new to Aras. Everything that happens in Aras happens with an AML request and response. Something that helped me a lot was when someone on the forum pointed out to me that you can save all the AML requests that Innovator sends, even the ones for the standard built in functions. Just go to Tools->Admin in the top menu bar and select "save all requests". Now all your requests will be saved in your working directly. Which incidentally you can set under Tools -> preferences
Now you can do something in Innovator and see what kind of requests it makes. As an example here's the request that gets sent when I try to List All CIP Parts in Innovator:
int iPage = 1;
int iPageSize = 25;
try{
XmlNode xPage = this.dom.SelectSingleNode("/Item/@page");
XmlNode xPageSize = this.dom.SelectSingleNode("/Item/@pagesize");
if(xPage.Value != "" && xPageSize.Value != ""){
iPage = int.Parse(xPage.Value);
iPageSize = int.Parse(xPageSize.Value);
//action = "list_all";
}
XmlNode xAction = this.dom.SelectSingleNode("/Item/@select");
string sAction = xAction.Value;
if(sAction != ""){
action = "list_all";
}
}catch (Exception ex){
}
So every time your onGet function gets called it is passed an AML request like we just talked about. The code above is using Xpath notation to parse that request and get the page and pagesize variables. These are sent from the UI if you choose to have say only 5 records returned per page or use the little up and down arrows to move through the page results.
string sID = "";
try{
XmlNode xAction = this.dom.SelectSingleNode("/Item/@action");
XmlNode xIdlist = this.dom.SelectSingleNode("/Item/@idlist");
if(xAction.Value == "get" && xIdlist.Value != "")
{
//convert ID
string sIdlist = xIdlist.Value;
if(sIdlist.Length == 32){
sID = sIdlist.Substring(0, 8) + sIdlist.Substring(8,4) + sIdlist.Substring(12,4) + sIdlist.Substring(16,4) + sIdlist.Substring(20,12);
action = "get_single";
}
}
}catch (Exception ex){
}
The code above is doing something similar, but here we're getting the action and id list variables. Then in a bit of a hack I'm checking to see if the id list only has one id in it and if it does I decide this is a single get request.
No comments:
Post a Comment