 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server CE / April 2008custom insert for databound datagridview
I don't know if this is the right forum to post this in, but I will start here.... I am writing a desktop database application. I created a simple SQL CE database. I then created a new form and dragged the DataGridView control from the toolbox to the form. I then created a new data source and pointed it to my desktop SQLCE database. I then dragged that data source onto the DataGridView. At this point I have a running application that can insert record into the database. Now comes the fun part. The first field is a Guid. I have read many things about the pros and cons of using Guids, but in my case I have decided I want to use them as I will be sharing data among several devices eventually. I made the ID column not visible and then, of course, started getting errors when I created a new row because the Guid was null. If fixed this by creating my own class that derived from the DataGridView and override the OnRowValidating. I did this because that is where I was getting the error. I then put in custom logic to check and see if the row guid is blank and if it is I create a new guid. This all works. I tell you about it in case there is a better way and it relates to my second question: I now want to keep track of changes in the database. I created a second table in the database that will contain the Guid of the record that was added / changed / deleted and the date / time it happened. I will use this to track changes. The problem is that I need to have a place somewhere that I do the insert / update of this meta data table when something changes in the main table. SQLCE doesn't support triggers, so that won't work. The benefits of SQLCE outweight this, however, and I am hoping there is a solution. So the question is in a data bound situation where can I hook into when the SQL statements get run? I put break points all over inside my data source class that got created and they never get called. I tried places like the Insert method and Update method. I can only assume those functions are for if I want to call them in my code, but the data bound stuff doesn't call them. SQLCE also doesn't support multiple statements in a single call so I can't just modify the INSERT INTO statement and put another after it. Ideas? have a look at Windows Mobile LOB 2008 sample, which implements managed triggers against a SQL Compact database. http://www.microsoft.com/downloads/details.aspx?FamilyId=428E4C3D-64AD-4A3D-85D2 -E711ABC87F04&displaylang=en  SignatureErik Ejlskov Jensen - MCTS: Mobile App Dev > I don't know if this is the right forum to post this in, but I will start > here.... [quoted text clipped - 46 lines] > > Ideas? The link you gave didn't work, but I did a google search and found the sample. Thank you. I will take a look. It sounds like just what I need. > have a look at Windows Mobile LOB 2008 sample, which implements managed > triggers against a SQL Compact database. [quoted text clipped - 51 lines] > > > > Ideas? I downloaded that sample. I then had to download the WM6 SDK and install it to look at the sample. I have been planing on doing that anyway. After looking at the sample this doesn't really answer my question. It is pretty cool the way they implemented the triggers and all. I am use something like that. In the sample they use list views and manually propulate them with data. This type of thing I already know how to do. I could easily write my own class to do something like the triggers and all. That still would not answer my question. My question is if I use the built in DataGridView and similar "auto generated" controls, forms and datasets, how do I hook into when SQL statements get run? When you use the auto generated stuff you never really have a chance to get those notifications as far as I can tell. This is what I need to know about. > have a look at Windows Mobile LOB 2008 sample, which implements managed > triggers against a SQL Compact database. [quoted text clipped - 51 lines] > > > > Ideas? You might look at the current changed event handler for the binding source that is bound to the DataGridView. I'm curious what you want to do with the changed data after you know what it is. For example would Sync Services for ADO.NET (which is designed for change tracking and updating) be a solution for you? Ginny >I don't know if this is the right forum to post this in, but I will start > here.... [quoted text clipped - 56 lines] > > Ideas? SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Software for Waste Management Syncing is exactly what I am doing. I am syncing between a PDA SQLCE database and a desktop SQLCE database. Unfortunately ADO.NET will only sync between something and SQL Server. It does not support syncing between a PDA and SQLCE or even SQLCE and SQLCE databases. I would love to just use something already out there like ADO.NET syncing. If you can tell me there is already something out there, I would LOVE to hear about it. I don't want to roll my own solution, I just seem to be stuck doing it. > You might look at the current changed event handler for the binding source > that is bound to the DataGridView. I'm curious what you want to do with the [quoted text clipped - 64 lines] > > > > Ideas? Eventually Sync Services for ADO.NET will support syncing to a variety of different relational databases, but the current version doesn't support your scenario, so at this point you'd be looking at writing your own sync adapter. Here's a sample for syncing with an Oracle database using a custom sync adapter that you might be able to adapt: http://www.syncguru.com/projects/SyncServicesDemoOracle.aspx Ginny > Syncing is exactly what I am doing. I am syncing between a PDA SQLCE > database [quoted text clipped - 98 lines] >> > >> > Ideas? SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Software for Waste Management I already have a solution for doing the sync part. The question I really need answered is the original one that I posted here: When I have used all of the built in drag and drop tools to build and application how do I get notified when an SQL statement gets called so that I can do something at that point? To recap: I created a form. I put a DataGridView on it. I dragged a data source onto the DataGridView. I told it to generate all the templates. Now when I use the auto generated UI to create a new record or edit a record or delete a record I can't seem to find anywhere to get notified of this. I put break points inside the data source and other places and nothing ever gets called. Everything just happens auto-magically. That is good because I wanted it to be auto-magic so I didn't have to write a bunch of code. It is bad because I can't find a place to insert my own logic. So, is there a way to insert my own logic in this case? > Eventually Sync Services for ADO.NET will support syncing to a variety of > different relational databases, but the current version doesn't support your [quoted text clipped - 107 lines] > >> > > >> > Ideas? There probably is a way to insert your own change tracking logic, but I think using Sync Services, which already provides change tracking logic inside the SQL Compact engine might be easier in the long run. There is a CTP you can download that handles syncing with a mobile device, but again the desktop piece is SQL Server so you'd still need to write a custom sync adapter to use SQL Compact as the desktop server. Here's the link if the CTP interests you: http://www.microsoft.com/Downloads/details.aspx?familyid=75FEF59F-1B5E-49BC-A21A -9EF4F34DE6FC&displaylang=en Since you're talking about DataGridView, I assume you want to track the changes on the desktop, but the desktop app doesn't have access to the data on the device. Are you perhaps considering getting the changed data onto the device in some other fashion? But in any case, the usual sequence of events for data bound controls like DataGridView is that moving to a new row in the grid calls EndEdit which in turn calls Update on the bound data adapter. Brian Noyes wrote a very good book called Data Binding with Windows Forms 2.0 that covers this sort of stuff in detail, some of which I've unfortunately forgotten, but the main thing I do remember is tha tthe BindingSource control is the bit that provides the glue between the GUI control and the underlying data, so look at the event handlers for BindingSource such as AddingNew, BindingComplete, CurrentChanged, CurrentItemChanged, PositionChanged, etc., to see which does what you want. HTH, Ginny >I already have a solution for doing the sync part. > [quoted text clipped - 157 lines] >> >> > >> >> > Ideas? SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Software for Waste Management Thank you for the comments. To answer your question I have to say that as soon as syncing supports SQLCE without having to have SQL Server and IIS I will use it. I have both desktop and PDA applications. They will sync their data together. It is such a simple application that I don't want to have to install SQL Server and IIS just to sync. The method I have taken is that I will use RAPI to copy the mobile database to the desktop and then I will simply use SQLCE on the desktop and sync them together. Again there is nothing built in to Sync that provides syncing 2 SQLCE database. They still require SQLS and IIS to do it (see comment above). I found the events you are talking about. I think this might be what I was looking for. Already I ran into a problem, however, in the AddingNew event the e.NewObject is null. I also handle CurrentChanged and CurrentItemChanged. I look at bindingSource.Current and can see the row data. I also tried looking at bindingSource.Current in AddingNew and it does show the item values. This is good. I should be able to use this. The one down side I found is that even though there are a couple of parameters in bindingSource.Current for IsNew and IsEdit they are always false even if I am adding or changing the value. Any ideas on that? The bottom line is that this answer my main question. Thanks a bunch! If you can answer why the IsEdit and IsNew are not set that would help too. For now I will just have to detect whether something has changed or now. I will also use the fact that AddingNew is the event to detect a new record. > There probably is a way to insert your own change tracking logic, but I > think using Sync Services, which already provides change tracking logic [quoted text clipped - 183 lines] > >> >> > > >> >> > Ideas? I understand very well why you use Rapi instead of a syncing solution involving IIS. I do the same! If you have more questions about data binding, you might want to ask on the WindowsForms newsgroup. They may not be as familiar with SQL Compact as we are here, but just don't tell them that's what you're using <g> and they can probably answer your specific binding questions better than I can at this point. My GUESS about IsEdit and IsNew is that the underlying objects don't set those to true for some reason, but that's only a guess and not very helpful. Ginny > Thank you for the comments. To answer your question I have to say that as > soon as syncing supports SQLCE without having to have SQL Server and IIS I [quoted text clipped - 251 lines] >> >> >> > >> >> >> > Ideas? SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Software for Waste Management Correction. The IsNew and IsEdit is being set in the CurrentChanged and CurrentItemChanged, but in ways I don't understand. The IsEdit is always set when moving from one record to the next even if nothing changed. I think I am getting the hang of this. There are other parameters in the classes that indicate things. I just realized another question however....none of these changes actually gets put into the database until you hit the save button on the navigation bar (again using the auto generated stuff). So the user might change stuff and then change it back and this might mean that I don't want to update my meta data at the binding source point. Is there a way to get at the actual point where SQL statements are being executed on the underlying database? > There probably is a way to insert your own change tracking logic, but I > think using Sync Services, which already provides change tracking logic [quoted text clipped - 183 lines] > >> >> > > >> >> > Ideas? EndEdit is the point at which the grid desides it's time to really update the changes, and I imagine that gets called by the Save button. The logic in the Save button event handler could look at the data adapter and call GetChanges to see what is different, and you could iterate through the changes, but there might be an easier way to go about it. I think the next step might be to step into everything that happens from the time the Save button is clicked and see where you might want to override something with your own logic. Ginny > Correction. The IsNew and IsEdit is being set in the CurrentChanged and > CurrentItemChanged, but in ways I don't understand. The IsEdit is always [quoted text clipped - 230 lines] >> >> >> > >> >> >> > Ideas? SignatureGinny Caughey Device Application Development MVP www.wasteworks.com Software for Waste Management Thanks again. I will try that. I will also try posting in the other forum as well. > EndEdit is the point at which the grid desides it's time to really update > the changes, and I imagine that gets called by the Save button. The logic in [quoted text clipped - 241 lines] > >> >> >> > > >> >> >> > Ideas? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.