September 23, 2008 at 9:32 am
Hi there,
I am currently attempting to write my first CLR Assembly.
The purpose of this assembly is to check the availability of systems to fill sales orders.
There is a DataTable (tblCurr) which contains the Current Sales Orders. I have added this to a DataTable object.
I need to update this with the details of either Systems from stock or current Works Orders to build systems. Furthermore, the systems each need a sensor either from stock or a seperate Works Order. Four further DataTable obejects to hold System and Sensor Stock and Works Orders have also been created and populated.
Before I add the logic to update the Current Sales Orders table with details of systems and sensors, I want to figure out how to return the completed table to SQL Server once it's updated. My current code is show below. Can anyone advise me how to return the result set to SQL Server from the DataTable when the procedure is executed?
Any general advice as well as specific examples would be appreciated as I really am at the start of the learning curve with this. 🙂
Thanks.
Option Explicit On
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Partial Public Class StoredProcedures
_
Public Shared Sub sp_FillDataTables()
' Execute SQL Statements and add the results to DataTables in a DataSet
' Open a conn to SQL Server using Connection defined at VS Project Level
Using cn As New SqlConnection("context connection=true")
Dim tblCurr As New DataTable
Dim tblSysStock As New DataTable
Dim tblSysWO As New DataTable
Dim tblSensStock As New DataTable
Dim tblSensWO As New DataTable
' Load Current Orders into a DataTable
Dim adptCurrent As SqlDataAdapter = New SqlDataAdapter("SELECT c.COR_OUR_NUMBER_N AS OrderNum, " & _
"CASE c.COR_CUSTOMER_CODE WHEN 'FCST' THEN '' ELSE cu.CUS_INV_NAME END AS CustName, " & _
"CASE c.COR_CUSTOMER_CODE WHEN 'FCST' THEN '' ELSE c.COR_CUSTOMER_ORDER END AS CustPO, " & _
"CASE c.COR_CUSTOMER_CODE WHEN 'FCST' THEN '' ELSE c.COR_NARR END AS MKSIssue, " & _
"CAST(CASE c.COR_CUSTOMER_CODE WHEN 'FCST' THEN 0 ELSE 1 END AS BIT) AS Allocated, " & _
"c.COR_EXPECTED_DATE AS ManufDueDate, c.COR_CUSTOMER_DUE AS SalesDueDate, c.COR_PART_ONLY AS PartNum, " & _
"p.PMA_OTHER_FIELD AS PartDesc, CAST(c.COR_QTY_ORDERED AS INTEGER) AS OrdQty, NULL AS SysLot, " & _
"NULL AS SysWO, NULL AS SysDue, NULL AS SenLot, NULL AS SenWO, NULL AS SenDue " & _
"FROM dbo.COR_TBL c INNER JOIN dbo.CUS_TBL cu ON cu.CUS_CODE = c.COR_CUSTOMER_CODE " & _
"INNER JOIN dbo.PMA_TBL p ON c.COR_PART_ONLY = p.PMA_PART_ONLY AND c.COR_PART_REV = p.PMA_PART_REV " & _
"WHERE ((p.PMA_ANAL_FIELD10 = 10 AND p.PMA_ANAL_FIELD9 IN (9, 10)) OR c.COR_PART_ONLY IN ( " & _
"SELECT b.BOM_PARENT_PART_ONLY FROM BOM_TBL b " & _
"INNER JOIN PMA_TBL p ON b.BOM_OWNED_PART_ONLY = p.PMA_PART_ONLY " & _
"WHERE(p.PMA_ANAL_FIELD10 = 10) AND p.PMA_ANAL_FIELD9 = 10)) AND (c.COR_QTY_DELIVERED < c.COR_QTY_ORDERED) " & _
"ORDER BY c.COR_EXPECTED_DATE, " & _
"CASE c.COR_CUSTOMER_CODE WHEN 'FCST' THEN 1 ELSE 0 END," & _
"c.COR_OUR_NUMBER_N", cn)
adptCurrent.Fill(tblCurr)
' Load Systems in Stock into a DataTable
Dim adptSysStock As SqlDataAdapter = New SqlDataAdapter("SELECT l.LOT_PART_ONLY AS PartNum, " & _
"l.LOT_PART_REV AS PartRev, l.LOT_NUM AS LotNum, l.LOT_PHYSICAL AS LotQty " & _
"FROM dbo.LOT_TBL l JOIN dbo.PMA_TBL p ON l.LOT_PART_ONLY = p.PMA_PART_ONLY " & _
"AND l.LOT_PART_REV = p.PMA_PART_REV AND p.PMA_ANAL_FIELD10 = 10 AND p.PMA_ANAL_FIELD9 = 9 " & _
"ORDER BY l.LOT_NUM", cn)
adptSysStock.Fill(tblSysStock)
' Add Allocated Boolean Column
tblSysStock.Columns.Add("Allocated", GetType(Boolean))
' Load System Works Orders into a DataTable
Dim adptSysWO As SqlDataAdapter = New SqlDataAdapter("SELECT w.WOR_ITEM_ONLY AS PartNum, " & _
"w.WOR_ITEM_REV AS PartRev, w.WOR_ORDER AS OrderNum, w.WOR_ORIG_QTY AS OrdQty, " & _
"w.WOR_DATE_SCHED AS DueDate FROM dbo.WOR_TBL w JOIN dbo.PMA_TBL p " & _
"ON w.WOR_ITEM_ONLY = p.PMA_PART_ONLY AND w.WOR_ITEM_REV = p.PMA_PART_REV " & _
"AND p.PMA_ANAL_FIELD10 = 10 AND p.PMA_ANAL_FIELD9 = 9 " & _
"WHERE(w.WOR_ORIG_QTY - w.WOR_GOOD_QTY > 0) ORDER BY w.WOR_ORDER", cn)
adptSysWO.Fill(tblSysWO)
tblSysWO.Columns.Add("Allocated", GetType(Boolean))
' Load Sensors in Stock into a DataTable
Dim adptSensStock As SqlDataAdapter = New SqlDataAdapter("SELECT DISTINCT l.LOT_NUM, " & _
"l.LOT_PART_ONLY AS PartNum, l.LOT_PART_REV AS PartRev, l.LOT_PHYSICAL AS LotQty " & _
"FROM dbo.LOT_TBL l INNER JOIN dbo.BOM_TBL b ON l.LOT_PART_ONLY = b.BOM_OWNED_PART_ONLY " & _
"AND l.LOT_PART_REV = b.BOM_OWNED_PART_REV JOIN dbo.PMA_TBL p ON l.LOT_PART_ONLY = p.PMA_PART_ONLY " & _
"AND l.LOT_PART_REV = p.PMA_PART_REV AND p.PMA_ANAL_FIELD10 = 10 AND p.PMA_ANAL_FIELD9 = 10 " & _
"WHERE b.BOM_PARENT_PART_ONLY = 'xxxxx' -- REPLACE THIS WITH PARAMETER " & _
"ORDER BY l.LOT_NUM", cn)
adptSensStock.Fill(tblSensStock)
tblSensStock.Columns.Add("Allocated", GetType(Boolean))
' Load Sensor Works Orders into a DataTable
Dim adptSensWO As SqlDataAdapter = New SqlDataAdapter("SELECT w.WOR_ORDER, " & _
"w.WOR_ITEM_ONLY AS PartNum, w.WOR_ITEM_REV AS PartRev, w.WOR_ORDER AS OrderNum, " & _
"w.WOR_ORIG_QTY AS OrdQty, w.WOR_DATE_SCHED AS DueDate " & _
"FROM dbo.BOM_TBL b JOIN PMA_TBL p ON b.BOM_OWNED_PART_ONLY = p.PMA_PART_ONLY " & _
"AND b.BOM_OWNED_PART_REV = p.PMA_PART_REV AND p.PMA_ANAL_FIELD10 = 10 AND p.PMA_ANAL_FIELD9 = 10 " & _
"JOIN dbo.WOR_TBL w ON b.BOM_OWNED_PART_ONLY = w.WOR_ITEM_ONLY AND b.BOM_OWNED_PART_REV = w.WOR_ITEM_REV " & _
"WHERE w.WOR_ORIG_QTY - w.WOR_GOOD_QTY > 0 AND b.BOM_PARENT_PART_ONLY = 'xxxxx' -- REPLACE THIS WITH PARAMETER " & _
"ORDER BY w.WOR_ORDER", cn)
adptSensWO.Fill(tblSensWO)
tblSensWO.Columns.Add("Allocated", GetType(Boolean))
' Declare a DataTableReader to examine Current Orders DataTable
Dim r As DataTableReader = tblCurr.CreateDataReader
' Return Data to SQL Server
' Add Code Here
r.Close()
cn.Close()
cn.Dispose()
End Using
End Sub
End Class
September 23, 2008 at 11:55 am
My advice on this would be to not use CLR for it. TSQL is best suited for this operation. You will lose performance pulling the data into the CLR stack, and then pushing it back to the TSQL side for something this simple. I would look at how to do this using derived table joins, or a single set based TSQL select operation, both of which should be possible and both of which will outperform any CLR implementation you can create.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
September 25, 2008 at 3:15 am
Hi Jonathan,
Thanks for your reply.
Unfortunately, I couldn't find a way to make the logic work in T-SQL.
Basically I need to update the main table with rows from the 4 sub-tables, comsuming the rows from them as they are used. They way in which they are used is dependent on each other. For example, once I add a system from stock to an order there is no need to look for further System works orders or sensors but if I use a System works order I then also need to add a sensor.
The only way I could see to do this was to use multiple cursors on a row by row basis and since RBAR is required VB seemed appropriate.
If you can suggest a way I could acheive my objective in T-SQL I would be more than happy as it would save me a lot of work. 🙂
September 25, 2008 at 6:56 am
I guess I am not getting somthing from your logic that is probably important that makes it RBAR. Even if the correct implementation in TSQL was a RBAR solution, unless you pull a full DataSet into CLR and do heavy complex mathematic processing almost in double recursive type looping, TSQL will likely be faster still, it is extremely hard to get ahead with SQLCLR in my experience. What is the total work flow for what you are trying to accomplish here? It sounds to me like maybe the expected processing could probably be done in a different manner, but I could be wrong. What would some sample data for each of the tables be and how would that data look on output.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
September 25, 2008 at 10:08 am
Here is the complete sequence of tasks in the workflow:
1. Get list of current Sales Order from db
2. Get list of current System Stock from db
3. Get list of current System Works Orders from db
4. Get list of current Sensor Stock from db
5. Get list of current Sensor Works Orders from db
6. For each Order, see if there is a System in stock
7. If there is, assign it to the order and remove it from stock
8. If there isn't, check if there is a works order to make a system
9. If there is, assign it to the Sales Order and remove it from the list of available system works orders
10. If a stock system was assigned, move to the next order.
11. If a system works order was assigned, check if there is a sensor in stock.
12. If there is assign it to the Sales Order and remove it from stock
13. If there isn't, check if there is a works order to make a sensor
14. If there is, assign it to the Sales Order and remove it from the list of available sensor works orders
15. If there are still any remaining Systems, Sensors or Works Orders, move to the next Sales Order
16. If not, write updated list of Sales Orders with Systems and Sensors back to db
Attached is an example in MS Excel 2003 spreadsheet format of how the output needs to look post-processing
Thanks for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply