|
I'm curios what's the best option to clear a database that contains a large number of tables?
Do we need to pick every table and issue delete statements (except those that have cascade rule set)?
Does anybody had any similar issue?
company, work and everything else @ netis
|
|
|
|
|
Another way is simply to extract the schema and create a new database from it.
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
There are three ways I would approach this:
The cleanest and fastest way if it's a database that contains a lot of data is to issue a drop statement against the entire database. I.E. remove the database completely and then recreate it.
The advantage of this is that it's clean and fast if you have to empty a database that already has a lot of data in it. The disadvantage is that you have to recreate the database from code, but it's usually a good idea to have code do that in any case which makes installations etc easier.
The second way depends on the database server itself many SQL servers have some kind of syntax for quickly emptying tables. In MS SQL server you can use something like "truncate table" (oracle too I think) it will delete much faster because it won't log each row deletion, however it will still respect referential integrity so if you have foreign keys etc you will have to either a) Be careful what order you remove the tables in always removing the ones at the outermost "branches" of the relationships first or b) drop the relationship then recreate it after you've truncated.
The third way is to issue delete statements against tables with the same caveats about referential integrity as the second way, but it's much slower than using truncate table.
In my own software I use the second *and* third way because for the areas that need to clean out the datbase they need to keep some of the data in some of the tables. And I have to support Firebird as well as mssql which doesn't have a truncate table command. If this weren't the case I'd go with option 1 every time, nothing is faster or cleaner than that.
|
|
|
|
|
I'm using Firebird and I also need to keep (in some cases) data in some of the tables.
I did go with the third option, however it is just getting more and more complicated 
(and I don't know if it's a good idea to reset the generators also)
company, work and everything else @ netis
|
|
|
|
|
Resetting the generators depends on a lot of things:
Let's say you are using them for a number that is visible to the user of the program and may print on reports etc. If they erase the data and start again it's possible that they might have old documents with the old number on them, in which case it's a bad idea to reset it as it won't be unique.
If they are only used internally and not to any of the unerased data then it's not a problem
I don't use generators for unique record ID's in Firebird because I support more than one type of database and my app generates it's own unique GUID values for records so in Firebird I just use the GUID's instead.
This stuff is always complicated, I have hundreds of tables in my app with some linked 4 deep.
If it was easy they wouldn't need anyone to write software. 
|
|
|
|
|
John Cardinal wrote: This stuff is always complicated, I have hundreds of tables in my app with some linked 4 deep.
If it was easy they wouldn't need anyone to write software.
Exactly 
I guess I'm going on with the 3rd option
company, work and everything else @ netis
|
|
|
|
|
For my code for the 3rd option I just made a helper method to delete the tables with the option of passing a generic List of ID's, then I can just pass that method a table name, it takes care of emptying it and optionally a list with one or more id's which it then constructs into a different format delete to keep the records id'd.
I know it's messy and not really programming to have to keep track of and work with the database schema, but there's no getting around it. You really do have to take the time to determine the correct order to delete those tables and always keep it in mind when adding new tables etc. It's easy to add a new table or feature to a program and then find out later from the end user that the erase database function is now broken. It helps to put it all in one place, the code that creates the schema updates and the code that does the table deletion and also make sure you have a schema level version number in your database itself so that you can handle properly database schema versionsing between your code and the database.
This means you can then put delete and table creation blocks in regions flagged with the db schema version number.
It means nothing at first release, but it's critical down the road for updates.
|
|
|
|
|
John Cardinal wrote: a schema level version number
I have, but I store only the last version of the DB.
John Cardinal wrote: It means nothing at first release, but it's critical down the road for updates.
story of my life  ...
company, work and everything else @ netis
|
|
|
|
|
Zoltan Balazs wrote: I have, but I store only the last version of the DB.
That's fine, that's what I do. When my user logs in, the required db schema set in my app is checked against the db's stored schema number.
If the db has a higher number my app exits with a warning that it's out of date.
If the db has a lower number my app triggers a schema update that goes through a big switch statement and executes each newer schema level update until it matches the program's required version.
|
|
|
|
|
Thanks, you've given a couple of ideas
company, work and everything else @ netis
|
|
|
|
|
I'm creating Winforms database application using VCS Express 2005
I have some large lookup tables (may be up to 500000 records) which
contains name and id and are stored in sql server.
I need to create single line combobox style control which:
1. Allows to type first characters in name
2. Auto-completes entered data by using first match
3. Allows to open picklist based by entered data and select name
I tried to use Combobox with lookup table.
I can set combobox autocomplete source to lookup table and autocomplete
window shows matches very well.
Lookup parts table is big, it takes a lot of time to load the
data source.
I think I need virtual combobox control with autocomplete and selection from
list.
Is is not reasonable to load whole table as combobox lookup table during
combobox
creation like ms doc sample recommends.
I have found 2 possibilities:
1. Add some code to combobox events to implement virtual mode. Is this
possible ?
2. Create textbox, selection button and use (virtual?) DataGridView to
emulate virtual dropdown list. In this case I must create UI in code
also.
Which way is better ?
Where to find more information about this ?
Andrus
|
|
|
|
|
Take a look at Ajax: Ajax AutoComplete[^]
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
I'm thinking about creating Winforms MDI application .
Browser is inconvenient for 8 hrs data entry and GUI report designer.
How to use autocomplete in winforms application ?
Andrus
|
|
|
|
|
I have implemented a similar function by loading all the data to memory when it's first called for - like you, the time to load if done every time just made the function useless to use.
To make sure the data is kept up to date, I have triggers to log updates to another table; the program then checks this table periodically and just reads the data for the updated records. This is not a good approach if a lot of records are frequently updated - the program then spends all it's time reading updates. It's a compromise, but it works for me.
|
|
|
|
|
Thank you.
I think it is better to avoid loading all data, since user actually looks only very small part of data.
I think I need a simpler approach: autocomplete reads first match from sql server.
Pressing dropdonw button reads first 10 matched from server.
Scrolling in dropdown list reads next 10 records.
This works probably fast even without caching.
Do you have anyidea how to implement such combo ?
My major issue is which event I should capture in combobox or how to use virtual grid for this.
I havent found any such sample.
Andrus
|
|
|
|
|
I don't use winforms so don't know how to do it with that. Certainly the windows messages can be processed for standard windows, so winforms probably has something similar.
|
|
|
|
|
hi,
how i can apply transaction on a dataset ?
Neeraj
|
|
|
|
|
You can't. A dataset is a disconnected set of data.
|
|
|
|
|
Hi everyone.
pls help,I want to display data from DB to the grid
but my data looks like this: Code Name Time
1 Me 2/15/2007 10:18:00 AM
and I want the grid to look like this: Code Name Time
1 Me 10:18
how do I display/formatt only that portion of time on my grid.
|
|
|
|
|
You usually don't try to format the data coming from your database in your code, instead you set a display format on the column in the grid itself.
|
|
|
|
|
hi
i'm developing a wizard that creates triggers and i need a couple of Create Trigger examples to map the query for different triggers(of type INSTEAD OF).
if u have any tiggers urself pleaze do post here
thanks fo![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif) r ur time an effort.
|
|
|
|
|
sorry that i can't help but did u search the web?
I Wish the Life Had CTRL-Z
|
|
|
|
|
|
I have a table in SQL Express 2005 that I would like to delete the first row after a specified number of rows have been added. The table contains four columns, one of the column contains the ID number starting at 1 and is autoincremented when a new row is added. Before a new row is added, I want to check if there are already 60 rows and delete the first row before adding the new row. I want new rows to be added, but want to keep the number of rows at 60 by deleting the oldest rows. I would really appreciate it if someone can provide with an example of this.
|
|
|
|
|
may u can use triggers like this:
CREATE TRIGGER myTrigger<br />
ON myTable<br />
FOR INSERT <br />
AS<br />
<br />
BEGIN<br />
DECLARE @count int;<br />
SELECT @count = COUNT(*) FROM myTable<br />
IF(@count>60)<br />
BEGIN<br />
DECLARE @first int;<br />
SELECT @first = MIN(ID) FROM myTable<br />
DELETE FROM myTable WHERE ID=@first<br />
END<br />
END<br />
and if you want to have your ID column Identity and only from 1-60 use IDENTITY_INSERT
check msdn and web for more
I Wish the Life Had CTRL-Z
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
