|
|
I'm developing an application using the GenericOLEDB dll accessing an access 2000-database. Mostly SELECTs are being used.
I don't know whether this is the correct approach, but I have chosen to create a new GenericOLEDB-object each time I access the database, and this has caused my application to be very slow. I guess this is because of opening a new connection each time.
What is the recommended approach? Having a global object leaving the connection open all the time seems like a bad solution to me, but I may be wrong?
All answers will be appreciated.
Sincerely,
Sjur Rondestveit
|
|
|
|
|
How to protect SQL databse not allowaning users to alter from enterprice manager.
|
|
|
|
|
Well, if they have an accout in SQL Server then they can log in and do what ever the account permits.
One thing that I am wondering: If they are not supposed to change things in the database with Enterprise Manager then why install it? Why do users have a local installation of the SQL Server client tools?
I am trying to work out your current set up because I could suggest something that could shut out something that is needed. The only users that I've ever come across that have Enterprise Manager (or any of the other database client tools) installed on their machines are DBAs, Developers and the System Admins.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Don't allow your users to connect using the sa account. SQL Server has strong security support and by default, no-one other than the database owner has access to anything. You should consider what you want your users to be able to do with a database and set the roles appropriately.
If you don't want users to be able to edit data, I suggest writing stored procedures that accept data in the valid form, grant EXEC permission on the stored procedures, and revoke INSERT, UPDATE and DELETE permission on the underlying data tables. Then users must use the stored procedures to make changes.
If you're trying to ensure that only consistent data is stored, consider using primary keys, UNIQUE constraints, foreign key constraints and CHECK constraints to enforce valid data entry. If you want to denormalise your data (e.g. keep an order total field against an order record rather than summing each order line for that order), write a trigger that updates the order total when order lines are added, updated or deleted.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi, I have two design objectives in mind:
1. to integrate NHibernate into data access layer of our applications
2. to maintain flexibility to switch from one OR tool (Object Relational mapping tool) to another (HNibernate to ADO.NET 2.0 ObjectSpace for instance).
To meet these requirements, it's neccessary to top our "OR" layer with an additional "OR broker" layer
class ObjBrokerFactory {<br />
<br />
IObjBroker GetObjBroker(ortype) {<br />
select ortype:<br />
case NHIBERNATE: return (IObjBroker) new NHibnernateObjBroker(...);<br />
case OBJSPACE: return (IObjBroker) new ObjSpaceBroker(...);<br />
}<br />
}<br />
<br />
class ObjHNibernateBroker {<br />
public New(...) {<br />
... initialized depending on OR type specified ...<br />
}<br />
<br />
public Finder(criteria) {...}<br />
public Insert(obj) {...}<br />
public Update(obj) {...}<br />
public Delete(obj) {...}<br />
}<br />
Then in client, instead of calling :
UserAccount acc = new UserAccount(...);<br />
acc.Email = "john.doe@hotmail.com";<br />
...<br />
IObjBroker broker = brokerFactory.GetObjBroker(ortype.NHIBERNATE);<br />
...<br />
broker.Insert(acc);<br />
...
Looks nice and clean, but, what if you need to persists a number of different object within one transaction? With NHibernate:
<br />
ISessionFactory factory = cfg.BuildSessionFactory();<br />
ISession session = factory.OpenSession();<br />
ITransaction transaction = session.BeginTransaction();<br />
User newUser = new User();<br />
newUser.Id = "joe_cool";<br />
newUser.UserName = "Joseph Cool";<br />
...<br />
session.Save(newUser);<br />
...<br />
transaction.Commit();<br />
session.Close();<br />
With ObjectSpace however:
<br />
IObjectSpace animal = (Animal) objectSpace.CreateObject(typeof(Animal), <br />
ID, Age, Weight);<br />
objectSpace.BeginTransaction();<br />
objectSpace.Update(animal);<br />
objectSpace.CommitTransaction();<br />
So much so for our "OR Broker" layer - any suggestion? Is there any good authoritative article on this subject? Design Pattern I'm not aware of?
Thanks in advance.
References:
1. NHibernate QuickStart http://nhibernate.sourceforge.net/quickstart.html
2. NHibernate - a more practical sample http://www.theserverside.net/articles/showarticle.tss?id=NHibernate[^]
3. ASP Appliance ADO.NET 2.0 ObjectSpace http://aspalliance.com/articleViewer.aspx?aId=248[^]
4. MSDN ObjectSpace http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/objectspaces.asp[^]
5. Sitepoint: http://www.sitepoint.com/blog-post-view.php?id=156266[^]
Norman Fung
|
|
|
|
|
I Want to Use Soundex in Dataset search But it gives "The expression contains undefined function call soundex()."
code for Like Function
dataset.Tables[0].Select("fname like '"+name+"%'");
and following Code I Want To Use For The Select
dataset.Tables[0].Select("Soundex(fname) =Soundex ("+name+")");
Any Solution
Please Give Reply As Early As Possible
|
|
|
|
|
When using webhits in the Index Service on IIS, I only see the ASP version of creating search forms. For the Record Set property, you have to set it to "Query Restriction" and then some other code to use the qFullHit.htw or qsumHit.htw.
My question is, does ADO.NET have a similar solution for using webhits in IIS?
|
|
|
|
|
I’ve been working on this quire for a day now and can’t seem to figure it out.
Given theses tables: (if there is an error in the create table disregard it. This code is the simplest way I can describe the tables)
<br />
Create table Problem as (PID int Primary Key, <br />
POpenDate DateTime Not Null,<br />
Pclosed DateTime Not Null<br />
)on primary<br />
Create table Notes as ( NID int PrimaryKey,<br />
PID int ForegnKey to Problem.PID,<br />
NEditDate<br />
)on primary
There is a one-to-many relationship between Problems and Notes.
I’m trying to select this:
Select p.PID, p.POpenDate, n.NEditDate as ‘LastEditDate’, p.PclosedDate <br />
Form Problems as p INNER JOIN Notes as n on p.PID = n.NID
Now because Notes keeps a record of all Edits to a Problem this select statement returns a record for every Edit since all edits must have a problem. I only want the most resent, or the closest date to now, returned.
A few things I’ve tried:
select DISTINCT ….. Doesn’t work they are all distinct because of the EditDate
select … group by p.PID Gave me: Server: Msg 8120, Level 16, State 1, Line 1
Column '_FieldName_' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Select top 1… Didn’t work only returns the Top one (well no kidding eh) I need the top 1 for each PID
I can’t change the design of the Db either.
Any Help would be Very much appreciated
Thank you,
Ronald Hahn, CNT - Computer Engineering Technologist
New Technologies Analyst
HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net
|
|
|
|
|
select *
from problems a
, tblnotes b
where a.id=b.id
and b.adddate =
(select max(adddate)
from tblnotes x
where x.id = a.id)
Ronald Hahn, CNT - Computer Engineering Technologist
New Technologies Analyst
HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net
|
|
|
|
|
I'm doing an web project about virtual stock exchange. With me that project is very important and i'm really worry about my database. I wonder is there anybody interested in my project 'n can help me improving my database better or somewhere can check my database? If you can, plz email to me : duc.to.ho@gmail.com
Thank you!
|
|
|
|
|
[WebMethod]
public DataSet GetEmployees()
{
.
.
.
}
or
[WebMethod]
public []Employee GetEmployees()
{
.
.
.
}
which Employee is business object and using dataReader is filled.
|
|
|
|
|
WDI wrote:
public []Employee GetEmployees()
you mean :
public Employee[] GetEmployees()
I think !!
I prefer the second method..especially when you use datareader to get the data.
|
|
|
|
|
I have this view :
VIEW
__________________________
ID Name TypeID
__________________________
1 | John | 1
1 | John | 2
1 | John | 3
2 | Mary | 2
2 | Mary | 3
3 | Betty | 1
3 | Betty | 3
__________________________
when I say :
select distinct ID from VIEW where TypeID in ( 1 , 3 )
I get :
1 , 2 , 3
But I Want to have only :
3
I dont know what should i do !
|
|
|
|
|
That is the correct result for the query you supplied. IN(...) means that the value can be any one of the values in the list.
First an explanation of why you are seeing the results you get.
Breaking this query down into smaller parts:
SELECT * FROM VIEW WHERE TypeID IN (1,3) will return
__________________________
ID Name TypeID
__________________________
1 | John | 1
1 | John | 3
2 | Mary | 3
3 | Betty | 1
3 | Betty | 3
__________________________ SELECT ID FROM... will return
__________________________
ID
__________________________
1
1
2
3
3
__________________________ And so SELECT DISTINCT ID FROM... will return
__________________________
ID
__________________________
1
2
3
__________________________
So, if you only want to get the IDs where there is a TypeID of 1 and 3.
The SQL you are looking for is:
SELECT DISTINCT ID FROM [VIEW]
WHERE TypeID IN (1,3)
AND ID NOT IN (SELECT ID FROM [VIEW]
WHERE TypeID NOT IN (1,3))
What this does is get a list of the IDs as you did before, but it also subtracts the IDs that have values NOT IN the list of TypeIDs that you specify
SELECT ID FROM [VIEW]
WHERE TypeID NOT IN (1,3)
returns a list of IDs that you are not interested in.
Does this help?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I altered VIEW as below and wrote your statement
VIEW
__________________________
ID Name TypeID
__________________________
1 | John | 1
1 | John | 2
1 | John | 3
2 | Mary | 2
2 | Mary | 3
3 | Betty | 1
3 | Betty | 3
4 | Michael | 1
_________________________
i get 3,4 ,but i want 3 only.
what should i do
|
|
|
|
|
Okay - I've added some extra SQL to the statement I wrote previously. I couldn't think of a very elegant solution to this so it is actually in two parts. First I create and populate a temporary table (actually a table variable) with similar results to the query that you have already. (I remove the distinct and add the TypeID column to the output). Secondly, I take the results of the first part and perform a self-join (in other words I join the table to itself) so that I can find only those that have all the relevant TypeIDs rather than just any of the typeIDs.
-- Create and populate the table variable
DECLARE @ids TABLE(ID int, TypeID int)
INSERT INTO @ids
SELECT DISTINCT ID, TypeID FROM [VIEW]
WHERE TypeID IN (1,3)
AND ID NOT IN (SELECT ID FROM [VIEW]
WHERE TypeID NOT IN (1,3))
-- The final result comes from the table variable.
SELECT DISTINCT a.ID
FROM @ids AS a
INNER JOIN @ids AS b on a.id = b.id -- self-join
WHERE a.TypeID = 1 AND b.TypeID = 3 -- Ensure that the ID has both desired typeIDs
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I have a wordier solution for you. It may be easier to understand and maintain. Only testing will tell you which is faster for your dataset. 2 tables scans verse 2 correlated sub queries/row. Mine might be faster if you have an index like (ID,TypeID) on the table. Then again, it might really suck  .
SELECT
DISTINCT v.ID
FROM
View v
WHERE
EXISTS (SELECT * FROM View where ID = v.ID AND TypeId = 1) AND
EXISTS (SELECT * FROM View where ID = v.ID AND TypeId = 3)
|
|
|
|
|
3rd solution. Let the server do straight set work. This might be the quickest on large datasets.
SELECT a.ID
FROM
(SELECT ID FROM View WHERE TypeId = 1 GROUP BY ID) a
INNER JOIN
(SELECT ID FROM View WHERE TypeId = 3 GROUP BY ID) b
ON (a.ID = b.ID)
|
|
|
|
|
The first solution is true but second and third ,both of theme are incorrect.
|
|
|
|
|
Sorry, I didn't read your question close enough.
|
|
|
|
|
How to call for update for one field e.g. 'ID' if we have 2 different databases that having different table name but same field name which is 'ID' ? If we want to update for field ID in table 1 database 1, then it should update for field ID table 2 database 2 as well..need help on this!
mijan
|
|
|
|
|
Hi,
you can update the table in a different database by referring it as <database>.<owner>.<table>
For eg.,
update a
set a.fielda = b.fieldb
from test1.dbo.t1 a, t2 b
where a.fielda = b.fieldb
|
|
|
|
|
I have no idea where to start. I have a table names Pages. A field PageID, PageNumber, etc.. (those only two matters). I delete some page and need to take the one at the end of the book(?) and bring it to the place where the page where deleted (Change the PageNumber). I need to do this in a stored proc of sql function. I don't know where to start ! 
sebastien.lachance.blogspot.com
|
|
|
|
|
Here is something to get you started. It really needs to be wrapped in a transaction with a 'SERIALIZABLE' isolation level. Brutal if you have a lot of users.
DECLARE PROCEDURE DeletePage
(
DECLARE @PageID INTEGER
)
AS
DECLARE @MaxPageNumber INTEGER
SELECT
@MaxPageNumber = MAX(PageNumber)
FROM
Pages
DECLARE @PageNumber INTEGER
SELECT
@PageNumber = PageNumber
FROM
Pages
WHERE
PageID = @PageID
IF @PageNumber IS NULL
BEGIN
-- Deal with Bad PageId
RAISERROR('Bad PageID',16,1)
RETURN 1
END
DELETE
FROM
Pages
WHERE
PageID = @PageID
IF @MaxPageNumber <> @PageNumber
BEGIN
UPDATE
Pages
SET
PageNumber = @PageNumber
WHERE
PageNumber = @MaxPageNumber
END
RETURN 0
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
