By Chan - 2/14/2007
Hi,I created purchase invoice data entry form for stock in. I want to allow user to able to transfer item by selecting item records in purchase invoice line items. My design is that, user can select line item record, click on "Transfer" button. Customized Transfer form will be shown as below: Location Cost Qty
----------------------------------- The fields above is in un-normalized form. However, my tables are designed in normalized form. How to have temporary table like VFP cursor for temporary editing? Thank you
|
By Keith Chisarik - 2/14/2007
This was like the second thing I wanted to know when I started .NET, I LOVED cursors.
I use datatables or data views now for lightweight temporary data manipulation.
|
By Trent L. Taylor - 2/15/2007
As Keith mentioned, you can create a temp cursor by using a BOs GetDataTable method or just manually creating a DataTable and populating as you like.Dim loTable As New DataTable("MyCursor") DIm loRow As DataRow '-- Create your columns
loTable.COlumns.Add("MyFirstColumn",GetType(System.STring)) '-- Add new rows
loRow = loTable.NewRow()
loRow.Item("MyFirstColumn") = "Your value"
loTable.Rows.Add(loRow) |
By Hugo R. Figueroa - 9/11/2008
well how can achieve this code (VFP) in C#SELECT date as xdate,cdesc as Service,SUM(num_pax) FROM tempo1 GROUP BY xdate,Service INTO CURSOR tempo2 SELECT CAST(CDOW(xdate)as character(10)) as xday,xdate,Service,sum_num_pax as pax FROM tempo2 INTO CURSOR tempo3 browse I need to use several "cursors" to get the data in the way I want and then use the last one as a source of a datagrid.
|
By Trent L. Taylor - 9/11/2008
Well, you can take several approaches. Approach #1 - Standard Query
The first approach would just be to execute the query and bring it into a BO. Remember, you are dealing with disconnected data so you really don't have to add this to a temp cursor as BO (or DataTable within the BO) is your temp cursor. A query doesn't have to match the structure of the BO...you can bring back any query you want. So it would look something like this: MyBO.FillDataTable("SELECT date as xdate,cdesc as Service,SUM(num_pax) FROM tempo1 GROUP BY xdate,Service")
You can then access the populated table via the strong-typed properties...or custom properties...or just by accessing the MyBo.CurrentRow.Item("MyField")Approach #2 - Multiple Result Sets
Remember that there is a new method on the BusinessLayer that allows you to bring back as many result sets as you need...this is very nice when you need to execute multiple queries:
Dim cmd As New SqlCommand("SELECT date as xdate,cdesc as Service,SUM(num_pax) FROM tempo1 GROUP BY xdate,Service;" & _ "SELECT CAST(CDOW(xdate)as character(10)) as xday,xdate,Service,sum_num_pax as pax FROM tempo2;") MicroFour.StrataFrame.Business.BusinessLayer.FillMultipleDataTables(cmd, _
MyBO1, _
MyBO2) Approach #3 - True Temp Cursors in SQL Server
If you really need to create a temp cursor, then this would be done within a stored procedure but ultimately you would be bringing back a result set like above. The only difference would be that you have done all of the query work on the SQL Sproc side prior to coming back over to the client. There are a lot of articles on creating temp tables in SQL Server. The quick and dirty version is that when you create a table with a # it is a local cursor and when you create a table with a ## it is a global cursor. But I really don't think that this is the approach that you need so I will not expand much on this.
|
By Hugo R. Figueroa - 9/12/2008
Thank you Trent for your answer (It is very helpfull to have code examples besides the explanations :cool . Really.The thing that I don't know how to accomplish is after selecting into a BO the data , how can I "select" from that BO (to group the data or change the field names) into another BO ? I feel dumb asking this questions, but if I don't ask , how can I learn?
|
By Edhy Rijo - 9/12/2008
Hi Hugo,I understand that you want to do do 2 SELECTs and the 2nd one will be against the result of the first one. I think that for what you need, you will be better off using a SP, this way you run all the SQL you need and the last one will be returned to the BO. Or maybe you could use LINQ to query the data in the 1st BO, but I think that may be more hard to do if you are not familiar with LINQ. The StrataFlix sample application have some SP sample that may help you get started.
|
By Hugo R. Figueroa - 9/12/2008
Hi, EdhyThe problem is I am providing web access of an application working with DBFs, not SQL server. I know I would be better using SQL server, but that will be the second step, ahead in the future. For now I have to work with VFP tables. I was thinking of Linq, but it seems hard for now .
|
By Trent L. Taylor - 9/13/2008
First of all, thanks for your comments Edhy...all good stuff. But I would really caution you from using LINQ...it is a nice concept, but the larger your database gets and your codebase becomes things can become very sluggish. Even on an empty database, LINQ is significantly slower than straight T-SQL. The problem is that when playing around with LINQ and creating a couple of queries, you don't notice it all that much...but the more code built with LINQ and the more complex queries become, then it really starts to show it's poor performance. But to each his own, I just thought I would caution you against using LINQ. At present, it is totally impossible for LINQ to perform as fast as straight T-SQL.OK...so now back to the question. You can either apply a filter on the BO that you bring in or call the Select() on the CurrentDataTable then pass the filtered BO into another BO via the CopyDataFrom method. MyBO.CurrentDataTable.Select("MyField = 1234") OR MyBo.Filter = "MyField = 1234"
MySecondBO.CopyDataFrom(MyBO, ClearAndFillFromDefaultView)
|
By Edhy Rijo - 9/13/2008
Hi Trent, Thanks for your comments about LINQ, and mostly thanks for the "How to" in getting the data from another BO with the filter trick. The more I work with SF and try to understand the power in their BO logic, the more I get amazed of how simple the solution to a task is with SF, still the ListView is still my favorite control .
|
By Trent L. Taylor - 9/14/2008
Thanks for your comments, Edhy! And I am really glad that you have enjoyed the new and improved ListView!!!
|
By Hugo R. Figueroa - 9/14/2008
Thank you so much for your explanations (both Edhy and Trent). I think now I can go on.This is one of the best forums (if not the best  ). You people know a lot of .net, but instead of try to impress others with your knowledge as occurs in many other .net forums, you make others really learn. And that is something I really appreciate.
|
By Trent L. Taylor - 9/14/2008
Thanks for your comments, Hugo. I agree with you...we are very fortunate to have a lot of solid developers out here that participate for the benefit of all who visit versus just trying to, "show how smart they are."
|
By Hugo R. Figueroa - 9/16/2008
Edhy, I implemented what you suggested about stored procedures, (I thinked that couldn't access vfp stored procedures from .net , in fact I never used a custom vfp stored procedure :w00t . Now it is working like a charm. With vfp stored procedures I think I have resolved most of my "problems"  Thank you
|
By Edhy Rijo - 9/16/2008
Hi Hugo, I am glad you've seen some light at the end of the tunnel. I thinked that couldn't access vfp stored procedures from .net , in fact I never used a custom vfp stored procedure) Well, I have never used a SP at all, but sounded like SP would be a choice here and I am happy you where able to think about it and find some used for them.  Now it is working like a charm. With vfp stored procedures I think I have resolved most of my "problems" Guess now I know where to go if I need to use SP in VFP It is all about share, problems and solutions!!!!
|
|