architecture question: not representing inheritance in the data mo

Hi
You may want to read the patterns and practices recommendations at
http://msdn.microsoft.com/library/d...
Spatterns.asp
such as
http://msdn.microsoft.com/library/d...Gag
.asp
If you are using user input to generate you SQL make sure you code against
SQL injection techniques. Also look at using sp_executesql to run the code
you generate.
John
"PJ6" wrote:

> First up, I know I'll get some good answers here, but in general, are ther
e
> any newsgroups devoted to overall application architecture?
> Second, sorry for cross-posting but in this case I think it's appropriate,
I
> want to hear answers from both sides.
> Instead of representing inheritance in the data model, in the particular
> project I'm working on now I've decided to do it in OO to keep it looser;
> each class gets its own table, because the details vary, but implements an
> interface. Each class serves a static collection of itself which it loads
> from the database. I get a master list of these items by putting all the
> collections together into one of the common interface type.
> Obtaining a master list in this way is a great simplification over the rou
te
> I would have normally chosen - I otherwise would have had a "base" table
> containing the properties of the common interface, and requiring that all
> "inheritor" tables have a foreign key to it. I already know that this is a
> little iffy since 1 to 1 relationships aren't exactly a good thing. Of
> course, I could have thrown everything into one table containing all the
> fields in every class, which poses its own problems and ugliness. Not liki
ng
> either of these options, I decided to dump representing inheritance at all
> in the database. And this led me in a new direction...
> Partially through the convenience of generics, I've noticed that since I'm
> caching everything locally already, it's easy to just do dictionary lookup
s
> through all this data to find subsets. I'm in effect sidestepping the norm
al
> chore of setting up stored procedures. Actually, I use attributes to speci
fy
> table names and fields to populate in each class, so I have no pre-written
> SQL at all in the entire application. I sort of like it, but this is
> something new for me - I normally push a lot of stuff as far as I can down
> into the data model; now I'm just using the database as a "dumb" container
.
> So to those other architects out there, what is your take on this approach
?
> Thanks,
> Paul
> P.S. - some additional information
> 1. yes, in this case, the user will often need access to all the data at
> once since there will be several different global analysis views
> 2. the data will change only wly through an automated process
>
>I've disagreed pretty sharply with MS on the subject of data access... but I
guess that's mostly just because of their examples of implementation. The
second article is interesting; thanks for making me take a another look.
Paul
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:B6CD35F3-6D60-453D-802F-906DD9F7CCE7@.microsoft.com...
> Hi
> You may want to read the patterns and practices recommendations at
> http://msdn.microsoft.com/library/d.../MSpatterns.asp
> such as
> http://msdn.microsoft.com/library/d...G
ag.asp
> If you are using user input to generate you SQL make sure you code against
> SQL injection techniques. Also look at using sp_executesql to run the code
> you generate.
> John
> "PJ6" wrote:
>

Applying the updates to SQL Mobile Server?!

Dear MSDN Support,

Here i am back with an inquiry about the last process of Merge Replication between SQL Mobile 2005 and SQL Server 2005.

Well i have performed all the steps found in the tutorial of the SQL Mobile Edition Books Online, and everything went on so fine and smooth. Now when i deploy my application on the Pocket PC emulator all possible SQL queries work and modify the data only in the emulator, i had installed ActiveSync 4.0 and it synchronizes the SQL Mobile data with the SQL Mobile server only when i copy the database from the emulator to my desktop, and this way is not efficient to my project, i need a better way to synchronize the data between the database on the emulator and that on the SQL Mobile Server.

I'll be looking forward to getting your help as soon as possible.

Thank you for your attention.

Best Regards;

Just FYI: there's no “MSDN support” on these forums, all answers are provided by

volunteers including your fellow developers, MVPs and Microsoft's employees.

|||

Thank you for your notice, and thank you for spotting at my problem!

|||

Mr Ahmad,

Just checking - did you figure out how to perform replication from your emulator to SQL Server? I couldn't tell from yor last post if you had solved your issue and are doing ok now. Let us know.

Thanks,

Darren

|||

Dear Darren,

I appreciate your reply alot, regarding the replication from the emulator to SQL Server, i had written the following code to my project and i am calling the function synchronize from a simple button.Here is the code i wrote for the Sync.

Dim repl As New SqlCeReplication()

repl.InternetUrl = "http://TOSHIBAMAN/Clinic/sqlcesa30.dll"

repl.Publisher = "TOSHIBAMAN"

repl.PublisherDatabase = "Clinic"

repl.PublisherSecurityMode = SecurityType.NTAuthentication

repl.Publication = "Clinic"

repl.Subscriber = "Clinic"

repl.SubscriberConnectionString = "Data Source=\My Documents\Clinic.sdf; Max Database Size=128;Default Lock Escalation =100;"

Try

repl.AddSubscription(AddOption.ExistingDatabase)

repl.Synchronize()

Catch err As SqlCeException

MessageBox.Show(err.ToString)

End Try

When clicking the button Synchronize the emulator fires the following message:

The SQL Mobile subscription already exists. Publisher, PublisherDatabase,and Publication for
this subscription should be different from any existing subscription

So what i do is to sync the database in the emulator and that in the SQL Mobile through the ActiveSync 4.0 then i connect to my database in the SQL Mobile and expand Replication right click on the subscription and choose to Synchronize the subscription.

But after i read some threads here, and the books online i do believe that there is a way which synchronizes the subscription from the emulator to the SQL Mobile to the original database engine!!

I'll be looking forward to getting your comments and advices.

Thank you alot for your attention!

Best Regards;

|||

Mr Ahmed,

Your code is very close - here are some corrections:

1. if you choose to use NT Authentication, you will have to provide the InternetLogin and InternetPassword properties as well. I suggest you start out by just using anonymous authentication until you have the replication working and then add more security afterwards.

2. in the connection string, it is not necessary to say max database size = 128 and Lock Escalation = 100 because these are the default values for SQL Mobile

3. if you are trying to dynamically create the Clinic.sdf SQL Mobile database upon the first merge replication synchronize, then you want to use AddOption.CreateDatabase.

So some sample code would look like this:

Public Function GetReplication() As SqlCeReplication

Dim repl As SqlCeReplication = Nothing

Try
repl = New SqlCeReplication()

repl.InternetUrl = Configuration.GetAppSetting("REPLICATION_URL")
repl.Publisher = Configuration.GetAppSetting("REPLICATION_PUBLISHER")
repl.PublisherDatabase = Configuration.GetAppSetting("REPLICATION_PUBLISHER_DB")
repl.PublisherSecurityMode = SecurityType.NTAuthentication
repl.Publication = Configuration.GetAppSetting("REPLICATION_PUBLICATION")
repl.Subscriber = Configuration.GetAppSetting("REPLICATION_SUBSCRIBER")
repl.SubscriberConnectionString = Configuration.GetAppSetting("REPLICATION_SUBSCRIBER_CONNECTION_STRING")

If Not System.IO.File.Exists(_dbmsFile) Then
repl.AddSubscription(AddOption.CreateDatabase)
End If

Catch ex As Exception
DisplaySQLCEErrors(ex)
Return Nothing
End Try

Return repl

End Function

Where the value of _dbmsFile (in your case) is "\My Documents\Clinic.sdf"

The point of the code sample above is to show you the minumum set of properties you need to set on the SqlCeReplication object in order for it to work, as well as how to handle dynamically creating the SQL Mobile database the first time (if it doesn't already exist)

Since you are using an emulator, you need to ensure that the emulator's network settings are configured to communicate with IIS and SQL Server on the server (which I can see is the same development machine you are running the emulator on). So open Pocket Internet Explorer on the emulator and try the URL http://TOSHIBAMAN/Clinic/sqlcesa30.dll to verify that you can get to the SQL Mobile server tools directory on IIS.

Darren

sql