Creating nonexistent database tables in code

Question

Until that moment, my company had an aversion to databases and it kept all the data in files separated by commas. I came across a unique client application that I believe would benefit from using a relational database. The application requires storage of “summary” data on the production process and “detailed” data for several subprocesses. Subprocesses should be related to the process summary.

My question is: is it normal / necessary to check for a table before writing to this table and create it if the table does not exist? Now this can lead to a bigger question when I type this; if the database does not exist, should i create a database and any tables that it requires?

Answer 1

With SQLite, creating the tables themselves is a good approach. You do not need to make your installation instructions for your clients more complex than they are, and if the database is completely internal to your application, they probably don’t even care what format it is in, as long as your software is reliable and fast.

Firefox currently uses SQLite for its internal storage, and they never bother the user about creating databases. And users like it. :)

: , , . CSV ? , , ? ?

Answer 2

, . , . .

, ... http://www.databasedev.co.uk/design_basics.html

(, , .)

Answer 3

, - -. , - , (, tblUsers), , .

, , , - , , , , . .

, , , . , , ... , ( ) " " ? , .

Answer 4

SubSonic SimpleRepository ( ).

, / , .

Answer 5

Sqlite "create table if not exists" . (check http://www.sqlite.org/lang_createtable.html) sqlite , .

Answer 6

.

, .

, Windows (), -, DAL.

, .

Answer 7

.

, do'nt .

DataSet - . , .

Xml , BinaryFormatter :

RemotingFormat = SerializationFormat.Binary

.

- " ". .

Answer 8

.

, . , .

, " ", , . SQL . " " - , , .

Answer 9

Many RDBMSs support SQL DDL statements. ADOX can be used with some databases to do things like create new tables. I do not think this is particularly exotic, especially when the program uses some kind of "built-in" file database as an internal data store or even as an output format.

No problems creating indexes, constraints, relationships, etc. as needed. Example:

Private Const WG_CONNSTRING As String = _
      "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;" _
    & "Jet OLEDB:Create System Database=True;" _
    & "Data Source='$MDB$.mdw'"
Private Const DB_CONNSTRING As String = _
      "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;" _
    & "Jet OLEDB:System Database='$MDB$.mdw';" _
    & "Data Source='$MDB$.mdb'"

'Exits with new MDB created, populated from initial data
'in text files, and cnDB left open.

Dim catDB As Object 'Don't early-bind ADOX objects.

Set catDB = CreateObject("ADOX.Catalog")
catDB.Create Replace$(WG_CONNSTRING, "$MDB$", MDB_NAME)
catDB.Create Replace$(DB_CONNSTRING, "$MDB$", MDB_NAME)

Set cnDB = catDB.ActiveConnection
With cnDB
    .Execute "CREATE TABLE Fruits (" _
           & "FruitID IDENTITY NOT NULL CONSTRAINT PK_FruitID PRIMARY KEY," _
           & "Fruit TEXT(50) WITH COMPRESSION NOT NULL UNIQUE" _
           & ")", _
             , adCmdText
    .Execute "CREATE TABLE Pies (" _
           & "PieID IDENTITY NOT NULL CONSTRAINT PK_PieID PRIMARY KEY," _
           & "Pie TEXT(50) WITH COMPRESSION NOT NULL," _
           & "FruitID INTEGER NOT NULL CONSTRAINT FK_FruitID " _
           & "REFERENCES Fruits (FruitID)" _
           & ")", _
             , adCmdText
    .Execute "CREATE VIEW PiesView (ID, Pie, Fruit) AS " _
           & "SELECT PieID AS ID, Pie, Fruit " _
           & "FROM Pies LEFT OUTER JOIN Fruits " _
           & "ON Pies.FruitID = Fruits.FruitID", _
             , adCmdText
    .Execute "CREATE PROC InsertPie(NewPie TEXT(50), FruitName TEXT(50)) AS " _
           & "INSERT INTO Pies (Pie, FruitId) " _
           & "SELECT NewPie, Fruits.FruitId FROM Fruits " _
           & "WHERE Fruit = FruitName", _
             , adCmdText
End With