|
Hello friends,
I'm developing a .NET application using VS2005 and I want to use NHibernate to implement the persistence.
By the moment, in the hibernate web page, there are only two versions, the 1.0.2 version and the 1.2.0 version, which at this moment is only a beta version.
My question is: Are both versions compatible with .NET 2.0 and VS2005? Wich one should I use?
|
|
|
|
|
How can I back up data from 19 division servers to one main sql server once a day which are connected by VPN?
I read some articles about Replication/Publication function. But I never being use that function and not sure does it work in the way that I want????
Any one can help????
|
|
|
|
|
Hi,
I have a stored proc(below) which assigns tasks to an employee depending on which division they are in.
This is working fine, but is only selecting the first name in the database it comes to.
In order for the workload of task assigning to be spread evenly I need to come up with some way as to "sequentially" select and employee??
ie. each time a do a select I want to pick a different employee from the database until I get to the last employee in which I repeat the process.
I thought of doing a random generator function first but this would probably be unfair as some employees MAY get assigned more tasks than others.
Any ideas??
CREATE PROCEDURE sp_GetAssignedDetails<br />
@roleName nVarChar(50), <br />
@division nVarChar(50),<br />
@actorName nVarChar(50) OUTPUT,<br />
@actorLogon nVarChar(5) OUTPUT<br />
AS<br />
<br />
IF @division = 'North'<br />
BEGIN<br />
--Get ActorName and ActorLogon if division is North<br />
SELECT @ActorName = dbo.Actor.ActorName<br />
FROM dbo.Actor INNER JOIN<br />
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id<br />
WHERE dbo.ActorRole.RoleName = @roleName<br />
and dbo.ActorRole.North = '1'<br />
<br />
SELECT @actorLogon = dbo.Actor.ActorLogon<br />
FROM dbo.Actor INNER JOIN<br />
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id<br />
WHERE dbo.ActorRole.RoleName = @roleName<br />
and dbo.ActorRole.North = '1'<br />
END<br />
--Get ActorName and ActorLogon if division is South<br />
ELSE IF @division = 'South' <br />
BEGIN<br />
SELECT @actorName = dbo.Actor.ActorName<br />
FROM dbo.Actor INNER JOIN<br />
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id<br />
WHERE dbo.ActorRole.RoleName = @roleName<br />
and dbo.ActorRole.South = '1'<br />
<br />
SELECT @actorLogon = dbo.Actor.ActorLogon<br />
FROM dbo.Actor INNER JOIN<br />
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id<br />
WHERE dbo.ActorRole.RoleName = @roleName<br />
and dbo.ActorRole.South = '1'<br />
END<br />
--Get ActorName and ActorLogon if division is West<br />
ELSE IF @division = 'West' <br />
BEGIN<br />
SELECT @actorName = dbo.Actor.ActorName<br />
FROM dbo.Actor INNER JOIN<br />
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id<br />
WHERE dbo.ActorRole.RoleName = @roleName <br />
and dbo.ActorRole.West = '1'<br />
<br />
SELECT @actorLogon = dbo.Actor.ActorLogon<br />
FROM dbo.Actor INNER JOIN<br />
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id<br />
WHERE dbo.ActorRole.RoleName = @roleName <br />
and dbo.ActorRole.West = '1'<br />
END
-- modified at 16:05 Tuesday 31st October, 2006
|
|
|
|
|
I've had an idea (off the top of my head so bear with me  ).
What if I create a temp table.
Then everytime I do a select on the main table I insert the actor to the temp table then everytime I go to assign details I do a check
if actor exists in temp table
get next person from main table
repeat this until we get to the end of the table and do somekind of compare count on both tables to establish we have gone through everyone. Then drop the temp table, re-create it and start it all again?? Clear as mud!!
Anyone know best way to compare the count on the two tables so I can then clear the temp table and start over again.
Or if anyone has a better idea please let me know
|
|
|
|
|
I'm not sure I understand what you are trying to do regarding 'assigning tasks to an employee depending on which division they are in', but this code does the same thing as that which you provided...only it is a bit shorter.
CREATE PROCEDURE sp_GetAssignedDetails
@roleName nVarChar(50),
@division nVarChar(50),
@actorName nVarChar(50) OUTPUT,
@actorLogon nVarChar(5) OUTPUT
AS
SELECT @ActorName = dbo.Actor.ActorName,
@actorLogon = dbo.Actor.ActorLogon
FROM dbo.Actor INNER JOIN
dbo.ActorRole ON dbo.Actor.Id = dbo.ActorRole.Id
WHERE dbo.ActorRole.RoleName = @roleName
and '1' = case when @division = 'North' then dbo.ActorRole.North
when @division = 'South' then dbo.ActorRole.South
when @division = 'West' then dbo.ActorRole.West end
--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
|
|
|
|
|
Thanks for that EricDV.
Basically what I'm trying to do is......
If I have 20 employees in my Actor Table I need to select one that hasn't been selected before each time I go to the Actor Table.
This is required so that a different employee is assigned a task every time I call the sp_GetAssignedDetails from my application.
Once I get to the last employee and assign him/her a task, all employees have now had a task assigned so I then want to go back to employee No.1 and start all over again.
Any ideas?
|
|
|
|
|
You could have a datetime field (LastAssignment) in the Actor table. Then, assign the next task to the oldest LastAssignment Actor, and update their LastAssignment.
--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
|
|
|
|
|
Here is an idea:
- Create a new table PickOrder with 2 columns - division and chosenName.
- Iniitialize PickOrder with 4 records -
('North', 'a1')
('South', 'a1')
('East', 'a1')
('West, 'a1')
Now, in sp_GetAssignedDetails,
1- Get the chosenName value (cn) from the PickOrder table for the requested division.
2- Using your query, select the top 1 record WHERE actorName > cn ... ORDER BY actorName
3- if no record is found in step 2 and there are existing records for that division (i.e., we have exhuasted the list), set the chosenName to 'a1' and repeat step 2 to start with the first name.
4- Update the chosenName column value in PickOrder table with the actorName found in step 2, so that next time we can pick the next name.
-- modified (added ORDER BY clause) at 22:08 Thursday 2nd November, 2006
|
|
|
|
|
Hi
I have to insert new rows from a dataset wich has many tables (8), with the same schema, to an Sql server database. The dataset is filled from an xml file that is quite a bit large.
My question is which is the best solution to insert these new rows from the dataset with a transaction (to be updated all the tables or in case of fail rollback), to the Sql database?
Any ideea are welcome.
biglewy
|
|
|
|
|
hello everyone. I was wondering if someone could help me.
As a debugging aid i want to see how many times my stored-proc is called and i also want to record certain variables. I would like to write this information to a text file on c:\ of the SQL Server. I am using MS Sql Server 2000..
Can anyone point me in the right direction please.
Thanks
|
|
|
|
|
after some googling i have sorted my problem..
thanks
|
|
|
|
|
here is a stored-proc which writes to a file
<br />
IF OBJECT_ID('dbo.sp_TestWriteToFile') IS NOT NULL<br />
DROP PROC dbo.sp_TestWriteToFile<br />
GO<br />
CREATE PROCEDURE [dbo].[sp_TestWriteToFile]<br />
AS<br />
exec master..xp_cmdshell 'echo hello > c:\file.txt'<br />
exec master..xp_cmdshell 'echo blah de blah > c:\file.txt', no_output<br />
<br />
GO<br />
SET QUOTED_IDENTIFIER OFF <br />
GO<br />
SET ANSI_NULLS ON <br />
GO<br />
<br />
exec sp_TestWriteToFile
and here is one which appends data to a file:
<br />
IF OBJECT_ID('dbo.sp_TestWriteToFile') IS NOT NULL<br />
DROP PROC dbo.sp_TestWriteToFile<br />
GO<br />
CREATE PROCEDURE [dbo].[sp_TestWriteToFile]<br />
AS<br />
exec master..xp_cmdshell 'echo appended data >> c:\file.txt'<br />
GO<br />
SET QUOTED_IDENTIFIER OFF <br />
GO<br />
SET ANSI_NULLS ON <br />
GO<br />
<br />
exec sp_TestWriteToFile
-- modified at 11:40 Tuesday 31st October, 2006
|
|
|
|
|
hi guys
i have one insert query in which i am selecting values from another table and then insert it in first table
Query is like this.
table1 table2
userid group id
groupid group name
i have values of userid and groupname
insert into table1 (userid,groupid) values
(userid,select groupid from table2 where groupname="temp"))
it is not working so if anyone knows how to do it please help me out
DJ
|
|
|
|
|
insert into table1(userid,groupid)
select userid, groupid from table2 where groupname="temp"
cheers,
Neil
|
|
|
|
|
What would be the best way to store a strict hierarchy (like a "Help Contents" tree) in a database?
Is there a "standard solution"?
I'm more looking for alternatives, but here's what seems most natural to me: Storing just the parent in the same table as the items
id PKey, nchar name, id PKeyOfParent
e.g.
0, "Root", 0 (predefined ID)
1, "Child1", 0
2, "Child2", 0
3, "A GrandChild", 1
4, "Another GrandChild", 2
to get all immediate childs of MyID is simply WHERE PKeyOfParent=MyID
Advantage: No redundant data, orphaned items (parent does not exist are easily found.
Disadvantages: orphaned "cycles" are possible. can SQL Server can be forced to keep integrity itself (e.g. recursively deleting all descendants?). "IsDescendant" probably cannot be written as a single select (stored procedure maybe?)
The application I'm thinking of introduces a "secondary" M:N mapping anyway, but from my judgement, a more restrictive hierarchy would help the user a lot.
Also, for the amount of data I imagine it would be no problem to read the entire index, then build the tree in the client, but that sounds just wrong.
We are a big screwed up dysfunctional psychotic happy family - some more screwed up, others more happy, but everybody's psychotic joint venture definition of CP
Linkify! || Fold With Us! || sighist
|
|
|
|
|
I'm currently writing an application that has heirarchical data. We have a similar solution, the table contains an Id to its parent (null at the top of the heirarchy). The IsDecendant problem is one we also have, however we store a second, denormalised, table for that. The denormalised table has just two columns ID and decendantID.
Using your example data the denormalised table will look like this:
ID decendentID Comments
---------------------------------------------
0 0 -- [Root + Decendents]
0 1
0 2
0 3
0 4
1 1 -- [Child1 + Decendents]
1 3
2 2 -- [Child2 + Decendents]
2 4
3 3 -- [A GrandChild + Decedents]
4 4 -- [Another GrandChild + Decendents]
Then it is easy to query to see if something is a decendent of another thing:
SELECT *
FROM DenormalisedHeirarchy
WHERE decendentID = @decendentID
AND ID = @somethingID
The trick is keeping the denormalised set up to date. In our case we don't have too many inserts/updates so we can afford to have a trigger regenerate part of the denormalised table when changes occur.
Does this help?
|
|
|
|
|
The following approach may be simpler. Model your object with a parent and a list of children relationship. Then use an Object Relational Mapping product like NJDX to easily retrieve any objects along with all their children without writing a single line of SQL in your application. For example,
You may define a (C#) class MyEntity as follows:
public class MyEntity {<br />
int id; <br />
int pid;
ArrayList children;
String name;<br />
}
Then declaratively define the object relational mapping as follows:
CLASS MyEntity TABLE MYENTITY<br />
PRIMARY_KEY id<br />
RELATIONSHIP children REFERENCES ChildrenCollection WITH id<br />
;<br />
COLLECTION_CLASS ChildrenCollection COLLECTION_TYPE ARRAYLIST ELEMENT_TYPE MyEntity<br />
PRIMARY_KEY pid<br />
;
Now, in your application, you can do a query like:
<br />
String predicate = ....;
ArrayList entities = njdx.query("MyEntity", predicate, ...);
NJDX will get the top-level MyEntity objects along with its children, each child will be fetched with its own children, each grandchild will be fetched with its own children and so on ... all of this with just one query call to NJDX.
The nice thing about such an approach is that it is much easier to deal with object-oriented data in your application. Further, you don't have to write the invariably complicated SLQ/ADO.NET code to fetch and properly associate all the children objects in a long hierarchy. You can also use NJDX to easily insert, update, and delete objects. Even cascading deletes can be done by specifying the mapping appropriately.
|
|
|
|
|
IMHO the self referencing table structure you have chosen is well suited for a "Help Contents" tree. As for orphaned cycles, you could write a trigger to delete all descentants, or just write a function that returns a table of all descendants:
Given the following:
TableName=SelfRefTable
Fields=PKey [int] Name [varchar (50)] PKeyOfParent [int]
PKey Name PKeyOfParent
------------------------------------------------------------
0 Root NULL
1 Child1 0
2 Child2 0
3 A GrandChild 1
4 Another GrandChild 2
5 A Great GrandChild 3
6 A Great Great GrandChild 5
7 One More GrandChild 3
You can delete Node 3 and all of its descendants with a function call:
delete from SelfRefTable where PKey in(select PID from GetDescendants(3))
This will delete PKey 3,5,6,7 in the above data.
CREATE FUNCTION GetDescendants (@nID int)
RETURNS @tDescReturn table (PID int)
AS
begin
DECLARE @RowsAdded int
declare @tDesc TABLE (PKey int,PKeyOfParent int, processed tinyint default 0)
INSERT @tDesc
SELECT PKey,PKeyOfParent,0
FROM SelfRefTable
WHERE PKey = @nID
SET @RowsAdded = @@rowcount
WHILE @RowsAdded > 0
BEGIN
UPDATE @tDesc
SET processed = 1
WHERE processed = 0
INSERT @tDesc
SELECT e.PKey, e.PKeyOfParent,0
FROM SelfRefTable e, @tDesc r
WHERE e.PKeyOfParent =r.PKey and r.processed = 1
UPDATE @tDesc
SET processed = 2
WHERE processed = 1
SET @RowsAdded = @@rowcount
END
INSERT @tDescReturn
SELECT PKey
FROM @tDesc
return
end
Also, if you want all ancestors:
CREATE FUNCTION GetAncestors (@nID int)
RETURNS @tAscReturn table (PID int)
AS
begin
DECLARE @RowsAdded int
declare @tAsc TABLE (PKey int,PKeyOfParent int, processed tinyint default 0)
INSERT @tAsc
SELECT PKey,PKeyOfParent,0
FROM SelfRefTable
WHERE PKey = @nID
SET @RowsAdded = @@rowcount
WHILE @RowsAdded > 0
BEGIN
UPDATE @tAsc
SET processed = 1
WHERE processed = 0
INSERT @tAsc
SELECT e.PKey, e.PKeyOfParent,0
FROM SelfRefTable e, @tAsc r
WHERE e.PKey = r.PKeyOfParent and r.processed = 1
UPDATE @tAsc
SET processed = 2
WHERE processed = 1
SET @RowsAdded = @@rowcount
END
INSERT @tAscReturn
SELECT PKey
FROM @tAsc
return
end
Transact-SQL Reference:
CREATE FUNCTION[^]
see: Multi-statement table-valued function
--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
|
|
|
|
|
I have a project that involves a MS Access database. I need to take a listbox that is populated with a SELECT DISTINCT list of records from a database. I need to take the results from the listbox selections and assign it to the IN CLAUSE from a SQL statement as in:
SELECT FIELD1, FIELD2, FIELD3 FROM TABLE1 WHERE FIELD1 IN ('101001', '101002', '010003', '101004', '101005') ORDER BY FIELD2
If the list box control's name is lstExcludeViaControls then I suspect that the code to parse the selected items from the control should be:
' Declare variables and assign values
Dim count as Integer
Dom count_compare as Integer
count = 0
count_compare = 0
' Count the number of selected items from the list box
For Each listItem in lstExcludeViaControls.SelectedItems
count = count + 1
Next
' Concatenate the value to a variable and add a comma if the
number of items selected from the list box is less than the
total number of items selected, and if the number of items
is equal to the total number of items selected from the list box,
then do not add a comma.
For Each listItem in lstExcludeViaControls.SelectedItems
If count_compare < count Then
listItem = listItem + "'" + lstExcludeViaControls.Selected + "', "
else
listItem = listItem + "'" + lstExcludeViaControls.Selected + "'"
End If
count_compare = count_compare + 1
Next
Please provide some additional insights regarding how to approach this issue. How do I create a variable that is based upon the assembly a string of fields selected from the list box box? This variable would then need to be passed to the CRITERIA portion of a query. The string variable in the IN CLAUSE requires parameters to be provided to the SQL statement as:
WHERE FIELD1 NOT IN ('101001', '101002', '101003', '101004', '101005')
|
|
|
|
|
All,
I have the following table:
CREATE TABLE X
(
ID tinyint NOT NULL,
PARENT_ID tinyint NOT NULL,
CONSTRAINT X1 PRIMARY KEY (ID)
)
go
How can I write a select on it which order rows such that, a row with a particular ID appears BEFORE
a row which has the same value for PARENT_ID?
cheers,
Neil
|
|
|
|
|
Does this give you what you're after?
select *,case when parent_id is not null
then parent_id - .5
else id end as OrderByField
from x
order by OrderByField
--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
|
|
|
|
|
Hi,
Can anyone recommed a tutorial and websites where I can learn more about stored procedures?
thanks.
|
|
|
|
|
I have a CSV file which opens successfully in Excel. However, when I run the COPY TABLE statement on Ingres I get keep getting an unexpected end of file error message on line 281. I have checked this line in the file and there is nothing obviously wrong with it. When I delete that line I get the same error message but for a line with a lower number (eg. 104). Does anyone know the cause of this or how to fix it?
Thanks in advance 
|
|
|
|
|
Hi, I have a program that saves a variety of data to an access database. I was wondering, is this the best way of saving data or is it best to save as an XML file? This data will get large but will take a while for that to happen. If I give this program out to people with the database, will they be able to use the program straight away or do they need to have MS Access installed?
Cheers.
|
|
|
|
|
Friends, i've found a question with few choices on a newsgrop. I dont know the answer of this question. Please tell me the answer of this question and also tell me the reason.
You are writing an application that is initially designed to use an Oracle database. You know that there is a good chance that your company will be changing their corporate standard database platform to Microsoft SQL Server in the near future. You are concerned that your application needs to function with a minimum of changes if the database platform ever changes from Oracle to SQL Server.
Given the scenario above, how do you declare your DataReader objects to minimize the impact of a change to the database platform in the future?
1) OracleDataReader myReader = myCommand.ExecuteReader();
2) OleDbDataReader myReader = myCommand.ExecuteReader();
3) SQLDataReader myReader = myCommand.ExecuteReader();
4) OdbcDataReader myReader = myCommand.ExecuteReader();
5) IDataReader myReader = myCommand.ExecuteReader();
Imtiaz
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
