//open the sql connection
conn.Open();
Just before the switch statement I call conn.Open which opens the connection to the CIP database 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");
}
In the section above I'm parsing that AML request to see if there are query options set in it. In clearer terms I'm looking to see if the user tried to run a search instead of listing all the results. When you're in Innovator and you have selected say parts, you'll see a blue highlighted line at the top of the part table in the right hand pane. If you go in to one of those fields, say part number and type CT-CAP* and hit enter that will return all the parts with the part number like CT-CAP*. Like CT-CAP-000001, CT-CAP-000002. So by searching the AML query for all those fields we can create a query that will return only the parts the user is searching for. So for each of the query options above I add some SQL to the variable where. Later I'll tie that variable into my main SQL request.
Now comes the fun part, the main SQL request that is going to get all these parts for us. I ran into a little trouble at first because CIP keeps part descriptions in separate tables for each part type. So capacitor descriptions are in the capacitor table, switch descriptions in the switch table etc. To make my life easier I went into SQL Express manager and created a view to do a union of all the tables. Someone on stack overflow suggested this approach to me.
Creating a view is pretty easy, just go into MS SQL Server Managment studio and login. Then find the CIP_E database, select the view tab, right click and create new. Then give it a name and use this as the SQL that describes it.
SELECT PART_NUMBER, [Part Type], Description, RowID FROM dbo.Capacitors WHERE PART_NUMBER LIKE 'CT-%' UNION SELECT PART_NUMBER, [Part Type], Description, RowID FROM dbo.Connectors WHERE PART_NUMBER LIKE 'CT-%' UNION SELECT PART_NUMBER, [Part Type], Description, RowID FROM dbo.[Crystals and Oscillators] WHERE PART_NUMBER LIKE 'CT-%' UNION SELECT PART_NUMBER, [Part Type], Description, RowID FROM dbo.Diodes WHERE PART_NUMBER LIKE 'CT-%' UNION SELECT PART_NUMBER, [Part Type], Description, RowID FROM dbo.ICS WHERE PART_NUMBER LIKE 'CT-%' UNION SELECT PART_NUMBER, [Part Type], Description, RowID FROM dbo.inductors WHERE PART_NUMBER LIKE 'CT-%' UNION SELECT PART_NUMBER, [Part Type], Description, RowID FROM dbo.misc WHERE PART_NUMBER LIKE 'CT-%' UNION SELECT PART_NUMBER, [Part Type], Description, RowID FROM dbo.Relays WHERE PART_NUMBER LIKE 'CT-%' UNION SELECT PART_NUMBER, [Part Type], Description, RowID FROM dbo.Resistors WHERE PART_NUMBER LIKE 'CT-%' UNION SELECT PART_NUMBER, [Part Type], Description, RowID FROM dbo.Switches WHERE PART_NUMBER LIKE 'CT-%' UNION SELECT PART_NUMBER, [Part Type], Description, RowID FROM dbo.Transformers WHERE PART_NUMBER LIKE 'CT-%' UNION SELECT PART_NUMBER, [Part Type], Description, RowID FROM dbo.Transistors WHERE PART_NUMBER LIKE 'CT-%'
Now if you want you can select the new view and select show top 1000 rows and you'll see that it combines all of the tables into one for our query.
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 ";
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(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;
//damn you for having no LIMIT function MS SQL
reader = cmd.ExecuteReader();
The first thing I do above is run a count query to find out how many records our query will return. I'll use this to let the user interface know how many more pages of results there are. After that query you'll see that I use that data to calculate the start, end and pagemax values.
Let's skip ahead to the main query and try to break it down. Here I'll take a moment to say how much I did not like working with MS SQL. Compared to say MySQL with PHP it felt clumsy, and more difficult than it need to be. Especially since there's no LIMIT clause to limit the number of results. Instead I have to do what you see above, using a main query to select the row numbers and then using a Between statement to limit the returned results. Such a pain, and if you want to know more just google MS SQL Limit function...
Anyway the query says Select everything from the CIP AllParts table, which contains all of the part numbers in our CIP system. Then join that with the Component Manufacturer Parts table which has all the manufacturer data. Then finally join that with the view we just made to get the part descriptions. The whole where 'monkey' = 'monkey' thing is just a statement that will always be true so I can just insert those where clauses we generated a few steps ago without having to do anything fancy.
Next we select from Parts which is not a real table we just created it in the query by saying WITH PARTS AS(....). We use the Between statement combined with the start and end values we previously calculated to just get the few rows that we want.
Finally we set the connection to our database connection and execute the query using ExecuteReader. That will return a reader that we'll use to read the result data from the query.
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));
}
Almost Done! In the above code I repeatedly call the reader.Read() function to get the result rows from the CIP database. For each result I create a new Item, and since CIP has 32 bit IDs but uses several '-' characters between them I strip out the dash character and use that for the ID. Then I apply the item, which I'll be honest I'm not sure I have to do but I saw it in the docs and it seems to work. Next I just set the properties for each CIP PART. Finally I use resultND to append this new item to res, the result set.
If you don't know already, this setProperty, setAttribute is basically setting up the AML ( XML) inside this item. If you print out the res you'll see that you've just created a large AML response containing all of the CIP PARTS you requested.
//close db connection
conn.Close();
return res;
As a last step we return the res result set, and that's it! Now you can go into innovator, select Design from the TOC menu. Now select CIP_PART, right click it and choose list all. If everything went well you should see a list of all of your parts.
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
No comments:
Post a Comment