|
Hello,
I have a windows application that uses a sql server remote database.
Which is the best method to deploy an application that has to connect to a remote server on an another computer, that is part of the LAN Network.
I have been told that during installation the connection will be setup.
At the moment I am using the sqlclient connection. I have my connection string in the app.config. But when the customer installs that want to be able to browse to the server that they will have to connect to.
Well as I have never deployed, I will never know what is best.
Give you could give a short example, i would be most grateful.
Thanks
|
|
|
|
|
The best thing to do would be to create a dialog window the allows the user to choose the server and database. Eamonn Murray posted an article on CodeProject that can be used as a good tutorial on how to do this. (http://www.codeproject.com/cs/database/SubmitSQLInfoEnumerator.asp[^])
You'll probably need to modify his code to use Windows Authentication if that is what you need to support. I'm assuling that you'll also want to store the resulting connection string in a file so that the user doesn't need to select the database every time the application runs, but those are requirement details. Anyway, the above article should tell you everything you need to know.
|
|
|
|
|
Hi guys, I'm designing a sql server database for a recruitment site, I have a resumes table that stores pieces of information about every jobseeker, among this information is the employment history which includes(Job title, company name, from, to), of course one jobseeker can have multiple previous jobs. Now, I want to know how this structure would be applied? this structure suggests a field within the resume table that is by itself a table! So, any ideas how can I design this database
|
|
|
|
|
One table for JobSeekers with 1 row each and a primary key JSID (autonumber long should suffice)
Another table for JobsHeld with a foreign key field whose value is the pk (JSID) from the jobseekers table, and a jobnum field that is just the order of the jobs held (then the pk for this table can be the pk from jobseekers = the jobnum, since the combination would be unique.) The JobsHeld table has 0-n rows for each row in the JobSeekers table, they can always be retrieved and associated with the JobSeeker by a Join on the JSID field. A rather classic example of a one to many relationship.
|
|
|
|
|
Hello Rob, thanks for your reply.
Rob Graham wrote: The JobsHeld table has 0-n rows for each row in the JobSeekers table
I don't get this.
I was wondering what about using xml as the data type for a field named say(Employment history), so I can nest an xml table in this field for every record in the resumes table...any suggestion/comment?
|
|
|
|
|
That will be a mess to implement and maintain. The solution I proposed is quite common in database applications, sometimes called master-detail:
<font>JobSeekers
Jsid | lastname | firstname | Address 1 | address 2 | Telephone
---------------------------------------------------------------
1 | Smith | Robert | 123 22nd St| Apt 35 | 555-9898
----------------------------------------------------------------
2 | Jones | Bill | 1532 1st St| <null> | 555-9987
-----------------------------------------------------------------
3 | Green | James | 12 Maple St| <null> | 555-8906
JobHistory
JSID | JobNum | Start | End | Employer
------------------------------------------------------------
1 | 1 | May 1985 | June 1986 | Acme tools
------------------------------------------------------------
1 | 2 | July 1986 | March 2006 | Big Gun Software
------------------------------------------------------------
2 | 1 | march 2000| March 2006 | Acme Componets
</null></null></font>
The Jobseeker with JSID 1 had two prior jobs, the 1 with JSID 2 had only 1, the one with Jsid 3 had no prior jobs.
I can always easily get the job history for any JSID by
" Select * from JobHistory Where JSID = 2 "
I store no more and no less data than I need, It's easy to correct the JobHistory by adding or removing rows.
[added comment:]
More importantly, If I later discover that i want more data in the JobHistory Table, I can just
Add fields to contain it. With your approach, the xml structure would change, and you would have to
Query and correct each instance.
Also if I want some results based on job history, say all applicants who previously worked at Acme,
it's a simple query. With your approch, you must once again examine every record's job history xml -
a real pain.
Last modified: 26mins after originally posted --
|
|
|
|
|
Thank you very much Rob for the clarification, that was really helpful.
I'm sorry Rob just one more question:
This recruitment site is part of a large portal which has other services, so the whole portal has its own database apart from the recruitment's. Now I want to authenticate the user once I mean if this user(JobSeeker) had already been logged in from somewhere on the portal modules, I don't want to ask'em again to sign in to search jobs or modify his resume. Now, with that said I would create a FK say UserID in the resumes table in the (jobs) database whose value is the PK of the Users table of the (portal) database. So, is that possible??
Thanking you in advance..
-- modified at 21:33 Thursday 21st September, 2006
|
|
|
|
|
Sure, you can add however many FK's you want to a table. Using a single userId key from the main login table as the FK in all related table seems quite sensible.
|
|
|
|
|
Rob,
This another login table is from another database..So can this still be done? referencing a PK from a table in another database??
|
|
|
|
|
Hi guys
I've got a design question regarding how something should be modeled in SQL. I have an object hierarchy like this:
class Foo
- string
- bool
- int
- Guid
class FooInheritor : Foo
- double
- int
- int
class AnotherFooInheritor : Foo
- float
- string
And so on, with another 15 or so classes deriving from Foo.
In my SQL database, I have a Foo table that stores all Foo types (including inheritors), then another FooInheritorTable that stores FooInheritor-specific data, a AnotherFooInheritor table that stores AnotherFooInheritor-specific data, and so on.
My question is:
when doing SQL queries, what is an acceptable way to query for a Foo instance? If I do SELECT * FROM FooTable WHERE @ID = someID, that will give me all Foo-specific data, but if the Foo being returned is actually a FooInheritor, what then? I would need to do another query on the FooInheritorTable to get the FooInheritor-specific data. And if the Foo returned is actually AnotherFooInheritor, then I need to do another query on the AnotherFooInheritorTable, and so on.
Should I:
- not model the tables after the real objects?
- create a view that returns all possible data for all 15 or so tables?
- query the FooTable, then when I get the results back, do another query on the table specific for that type?
I'd love to hear any suggestions. Thanks for taking the time to read this long post.
|
|
|
|
|
I'm not sure you gave us enough information to answer this question well. Are you looking for a single resultset that shows EVERY Foo with its Foo specific data, or are you just querying one Foo at a time? How dynamic do you want the final solution to be? Are you more concerned with the results being returned quickly, or do you want the design to look real clean (which seems to be a hangup most of us have)? Are the tables changed frequently, or is the data static? How often do you retrieve the data? How many records are we talking about here? What are the relationships between the tables (i.e. do you have a field in the Foo table that has the name of the table the FooInheritor specific data is found in)?
Nonetheless, here are a couple thoughts:
Judah Himango wrote: - not model the tables after the real objects?
Speed wise, you would probably be better off with denormalization. Especially if the tables rarely change, yet you query them frequently. It isn't pretty, but redundant data isn't always a bad thing.
Judah Himango wrote: - create a view that returns all possible data for all 15 or so tables?
I tend to not like this kind of stuff. It usually looks like a mess, is not easily scaleable, and is difficult to maintain. You have to remember to change the view whenever you add a new FooInheritor type.
Judah Himango wrote: - query the FooTable, then when I get the results back, do another query on the table specific for that type?
This is probably easiest to write, along with keeping things normalized. But, two queries aren't as fun as one.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
EricDV wrote: Judah Himango wrote:
- create a view that returns all possible data for all 15 or so tables?
I tend to not like this kind of stuff. It usually looks like a mess, is not easily scaleable, and is difficult to maintain. You have to remember to change the view whenever you add a new FooInheritor type.
We initially used this solution, only to find out it is a mess (views returning some 30 fields or more! Ugh!) and a pain to maintain (every time you add a new Foo type or change an existing one, you have to update the view accordingly! Ugh!).
EricDV wrote: This is probably easiest to write, along with keeping things normalized. But, two queries aren't as fun as one.
This is the cleanest solution from the consuming code side (C#) where I can still keep everything as objects. It just requires lots of sub-queries.
Let me explain my situation a little more: these tables are queried frequently. I expect something between 5 and 100 rows to come back, although there is no hard-coded limit right now.
Each row returned is a Foo instance, but it could be a regular Foo, a FooInheritor, or a AnotherFooInheritor. Going with the clean solution, it would look like this:
FooRow[] rows = fooTable.GetResults();
for(int i = 0; i < rows.Length; i++)
{
Foo theFoo;
if(rows[i].Type == Foo)
{
theFoo = new Foo(rows[i]);
}
else if(rows[i].Type == FooInheritor)
{
theFoo = QueryFooInheritorTable(rows[i].ID);
}
else if(rows[i].Type == AnotherFooInheritor)
{
theFoo = QueryAnotherFooInheritorTable(rows[i].ID);
}
...
}
What I'm worried about is performance. If I'm returning 100 results, I can potentially make 101 queries: 1 query to the FooTable, then another query for each row returned if that row is FooInheritor, AnotherFooInheritor, or some other type that inherits from Foo.
What do you think?
|
|
|
|
|
Even though they are slow, cursors are sometimes the best option. Especially when compared to running a hundred queries.
You could dynamically create an SQL statement by doing something like this:
create procedure GetFooInfo
as
DECLARE @nFooID int,@cType varchar(50),@cSQL varchar(2000)
set @cSQL = ''
DECLARE fooBase CURSOR FOR
SELECT distinct Type
FROM fooTable
OPEN fooBase
FETCH NEXT FROM fooBase INTO @cType
WHILE @@FETCH_STATUS = 0
BEGIN
if len(@cType) > 0
set @cSQL = @cSQL + ' LEFT JOIN ' + @cType + ' ON fooTable.FooID = ' + @cType + '.FooInheritorID '
FETCH NEXT FROM fooBase INTO @cType
END
CLOSE fooBase
DEALLOCATE fooBase
exec('SELECT * FROM fooTable ' + @cSQL)
go
ps. This is what I did to set things up to be similar to your description:
CREATE TABLE fooTable (
FooID int IDENTITY (1, 1) NOT NULL,
Type varchar (50),
FooBaseInfo varchar(50)
) ON [PRIMARY]
GO
insert into fooTable (Type,FooBaseInfo) values ('','this is a basic foo')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor1','FooInheritor1 foo 1')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor2','FooInheritor2 foo 1')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor3','FooInheritor3 foo 1')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor4','FooInheritor4 foo 1')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor2','FooInheritor2 foo 2')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor4','FooInheritor4 foo 2')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor4','FooInheritor4 foo 3')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor2','FooInheritor2 foo 3')
insert into fooTable (Type,FooBaseInfo) values ('FooInheritor4','FooInheritor4 foo 4')
CREATE TABLE FooInheritor1 (
FooInheritorID int,
Foo1Specific varchar(50)
) ON [PRIMARY]
INSERT INTO FooInheritor1 select FooID,'Specific to 1' as Specific FROM fooTable where Type = 'FooInheritor1'
CREATE TABLE FooInheritor2 (
FooInheritorID int,
Foo2Specific varchar(50)
) ON [PRIMARY]
INSERT INTO FooInheritor2 select FooID,'Specific to 2' as Specific FROM fooTable where Type = 'FooInheritor2'
CREATE TABLE FooInheritor3 (
FooInheritorID int,
Foo3Specific varchar(50)
) ON [PRIMARY]
INSERT INTO FooInheritor3 select FooID,'Specific to 3' as Specific FROM fooTable where Type = 'FooInheritor3'
CREATE TABLE FooInheritor4 (
FooInheritorID int,
Foo4Specific varchar(50)
) ON [PRIMARY]
INSERT INTO FooInheritor4 select FooID,'Specific to 4' as Specific FROM fooTable where Type = 'FooInheritor4'
GO
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
What about something like this:
FooTypes[] fTypes = fooTable.Query("SELECT DISTINCT FooInheritorType FROM fooTable");
for(int i = 0; i < fTypes.Length; i++)
{
string cQuery;
if fTypes[i] == "")
cQuery = "SELECT * FROM fooTable ft WHERE FooInheritorType = ''";
else
cQuery = "SELECT * FROM fooTable ft INNER JOIN " + fTypes[i] + " fi ON ft.id = fi.id";
FooRow[] rows = fooTable.Query(cQuery);
for(int i2 = 0; i2 < rows.Length; i2++)
{
Foo theFoo = new Foo(rows[i2]);
...
}
}
Then you will only need to make 15 or 16 trips to the DB (or one trip for how many Inheritor Types there are).
-- modified at 9:55 Friday 22nd September, 2006
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Ok. So it's essentially like a view that returns all the possible data, or all the data that can be returend by that particular query, right? We had gone the view route, where the view returns all possible data for all the Foo inheritors...but it gets really messy, ugly, and is a pain to maintain.
|
|
|
|
|
Judah Himango wrote: So it's essentially like a view that returns all the possible data
The first suggestion I made was like that (the one using a stored procedure). Except it would be better than a view in that it wouldn't have to be maintained, since it is dynamic.
But, the nested loop suggestion is even better. It would first return a resultset of all possible Inheritors. Looping through each of those, you get a resultset for each Inheritor type that has records for all Inheritors of that type along with their Foo data and FooInheritor specific data. This resultset can be used to create the Foo objects.
I believe you said that there are about 15 different inheritor types. So, if you add an inheritor type, you will not need to alter this code. Just have a field in the Foo table that tells what child table the FooInheritor specific information should be gotten from. (In the pseudo code I submitted earlier, I assumed that the FooInheritorType field would have the name of the child table. In the case of a Foo that is not an inheritor, the field would be empty and only the Foo table would be queried - hence the IF (fTypes[i] == "") .)
Or...I'm in over my head here, and I have no idea what the hell you're trying to do.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
You are understanding my problem correctly. Thanks for the suggestions.
|
|
|
|
|
Sometimes I use this kind of schema if I need something highly changeable (usually only when data columns are user-configurable):
Have your main Foo table, then have 1 table for foo-inheritor values. In the foo-inheritor table, you have a foreign key to the Foo table, a FieldName field and a Value field. Your stored procedure would return 1 result set for Foo, and another for the foo-inheritor. Your business object would load the individual field values from the foo-inheritor result set into the appropriate fields.
This model works best when only 1 Foo is queried at a time, but can also be used to return more than 1 (with slightly more effort in both the stored proc and the .net code). It is also more difficult if the values cannot be stored as character types, but you can add a Type field and store everything in varbinary if necessary, then convert when loading the objects.
I would definitely go with Eric's solutions if the tables aren't very dynamic, but this solution works when many foo-inheritors are expected to be added over time.
Scott
|
|
|
|
|
Scott Serl wrote: our stored procedure would return 1 result set for Foo, and another for the foo-inheritor.
Oh man, there are so many foo inheritors though; combine them all together and you've got something like 35-40 fields. Needless to say, this is F-UGLY on the consuming code side, where I basically say Foo instance = ParseFooFromRow("idField", "textField", ....[all 40 fields])
Not only is that code ugly, but it's a nightmare to maintain; everytime you add a new foo type, or add a change an existing type, you have to go in and modify that huge thing. Nasty stuff.
|
|
|
|
|
Yes, you have to store the schema somewhere, and you can build a factory that can build any foo-inheritor. I usually build a UI to create new foo types, then store the schema in the database. When I go get the data, I grab and return the schema so the object knows how to load the data. Each foo-type specific data item is a single record in the result set, and is loaded by name from the schema. It's not too dificult to maintain, as the code is generic for any foo-inheritor.
It is ugly, but is the easiest way I know to design end-user-configurable objects. If the foo-types aren't going to change much, it is much easier to use seperate tables, then you can have stored procs for getting each foo-type.
Scott Serl
|
|
|
|
|
Interesting, thanks. Sounds like a lot of work, but hey.
What do you think of the solution of 1 initial query (to get all common Foo data), then an extra query each time you want to retrieve a FooInheritor?
|
|
|
|
|
If the data is for display to a human and the database calls are not across the internet via dialup, then the performance should be OK (and I am talking about the case of maybe dozens of individual foo-inheritor queries). If it is for some kind of automated data collection and reporting, then the performance could be an issue if you end up making a hundred queries each second.
As always, the best solution depends on all the little details, so it is not easy to recommend one solution over another. Each has its own implications for maintenance and performance. Think about it, then choose one way to do it, and just do it. Try to burry the details in an assembly and access it through an interface. If the implementation starts to cause problems, redesign it later, and the code accessing the interface will not need to be changed; just recompile the implementation assembly, convert the database, and you are done.
Scott
|
|
|
|
|
It is for display for a human.
I'm thinking I will go with the multiple queries thing and see how it goes.
Thanks for your in-depth answers, much appreciated.
|
|
|
|
|
Hi All;
I have a dataGridView binding to a dataView. I change the row filter of dataview on textChange of a textbox and it works very fine but the problem arises when i add the datatable (from which the dataview has been created) in a dataset, RowFilter stops working... any clue?
sorry for my bad English.
|
|
|
|
|
Why you have a DataTable without a DataSet? It does not matter. I dont know, why the RowFilter stops working, but try to use the Select-Method from DataTable instead a DataView.RowFilter.
Regards
Stephan
\\\| \\ - -
( @ @ )
+---------------oOOo-(_)-oOOo-----------------+
| Stephan Pilz stephan.pilz@stephan-pilz.de |
| <a href=www.stephan-pilz.de>www.stephan-pilz.de</a> |
| ICQ#: 127823481 |
+-----------------------Oooo------------------+
oooO ( )
( ) ) /
\ ( (_/
\_)
|
|
|
|
|