Take a look at SQLite[^] for minimal installation and configuration.
Or SQL Server Compact edition as already mentioned, if you want to be able to upgrade to a larger version later on.
There are several inbetween versions of SQL-Server, there's an ok overview here[^]
A lot depends on what you are going to do with the data.
If you are going to be reading, inserting, updating and deleting then a database is the way to go - SQLite is a good choice.
If all you need to do is read a large amount of data into an application and never need to update this data then you could consider Serialising your classes(or collections of classes). This is what I have done for a calorie counting application and a visual screening application that I wrote(even if you are applying updates serialisation is still worth considering).
The nice bit about serialisation into XML is that you can look at your data with something like Notepad++ and make changes manually where necessary.
“That which can be asserted without evidence, can be dismissed without evidence.”
I suggest System.Data.SQLite: Downloads Page[^]. It is up to date, easy to use from c#, there is no service needed and it has very fast inserts and retrievals into/from tables. Last August I tested it by inserting up to 100 million of simple unique records on a Win 7 Pro with 4 GB of RAM and I could retrieve data in milliseconds, even from a table of 100 million... which I found quite impressive. When inserting I used parametrized queries. For fast retrieval an index needs to be created on a table column using SQL. Of course it depends on how complex is your data.
I hope this helps...
An Information System is a system, automated or manual, that include people, machines and/or organized methods for collect, process, transmit data that represent information.
(It is my translation from Spanish to English -with a little help of google translator-).
I will like to know if I understand well the concept. I'm going to type a real example:
To take an appointment there, you have to go to the hairdresser and say it to the haircutter. Then he write in his diary (physical) the date.
NOTE: there are not any technologies, all is MANUAL.
QUESTIONS: Is it an Information System?
I think that could be, because include an elements set (people -customer and haircutter- and organized methods -diary-) for collect, process, transmit data that represent information (the haircutter COLLECT the customer's date in his diary, he check if the date is unoccupied -PROCESS- and finally he TRANSMIT THE DATA that represent information to the customer, for example "Ok, remember you have appointment to the hairdresser X date").
Thank you for your answer Richard MacCutchan. I have my own notes about Information System concept (I wrote it from there), I invented a case study related to the concept, to know if I understand well the concept or not.
I Have a SQL server 2008 DB.I also have some Oracle Database.Now I Want to get data from Oracle DB to SQL server DB.
I also read some post about link server but I still not success connecting to Oracle DB.
For example some information about oracle DB that I can connect by using SQL developer
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
SQL Server Network Interfaces: Connection string is not valid .
OLE DB provider "SQLNCLI10" for linked server "BIM" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "BIM" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 87)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=87&LinkId=20476
I have two tables 1st table is Employee (Id, Name, Level) another one is EmployeeRelationShip (Id, EmployeeId, ManagerId), in Employee table all level 1's are Managers and Level 2's are workers.
In EmployeeRelationShip table all Employees would have their ManagerId as Manager's Employee Id. Now for this structure I want to Order by Name for all managers, again under each manager I want to order all his Employees order by name again?
Can anybody please help me in writing a script for me for this scenario? Any help may be a code snippet or a link or even a suggestion would be much helpful. Thanks in advance.
"There is already enough hatred in the world lets spread love, compassion and affection."
Yours is a little more complicated because you have the extra table, which could have been replaced by a single column on the Employee table "ReportsTo". It is also a little more complicated because you want the Managers in Name order followed by the Employees (for each Manager) also in Name order.
This query seems to do what you want (I've used the sample data provided in the earlier response)
;WITH emps AS
SELECT e.Id, e.[Name], e.[Level], er.ManagerId as ReportsTo, em.Name as ManagerName
FROM Employee e
leftouterjoin employeeRelationship er on er.EmployeeId = e.id
leftouterjoin employee em on er.ManagerId = em.id
), Emp_CTE AS (
-- Anchor member - get only the managers
SELECT Id, [Name], [Level], ReportsTo,
MtoE = CAST(CAST(isnull(ManagerName,'') ASCHAR(30)) + '/' + CAST([Name] ASCHAR(30)) ASVARCHAR(MAX))
FROM emps e
WHERE ReportsTo ISNULLUNIONALL-- Recursive member - get the people who report to the managers
SELECT e.Id, e.[Name], e.[Level], e.Id as ReportsTo,
MtoE = MtoE + '/' + CAST(e.[Name] ASCHAR(30))
FROM emps e
INNERJOIN Emp_CTE ecte ON ecte.Id = e.ReportsTo
SELECT Id, [Name], [Level]
Giving the results you asked for:
Id Name Level3 dddd 19 ef 28 ff 22 nnnn 17 az 26 za 21 zzzz 15 bc 24 df 2
The first CTE emps is just to simplify the table structure as I can't use a left outer join in the recursive CTE that follows it. If you run that query by itself you get the following
Id Name Level ReportTo ManagerName1 zzzz 1 NULL NULL
2 nnnn 1 NULL NULL
3 dddd 1 NULL NULL
4 df 21 zzzz
5 bc 21 zzzz
6 za 22 nnnn
7 az 22 nnnn
8 ff 23 dddd
9 ef 23 dddd
The second CTE Emp_CTE is a recursive query that first gets only the Managers (marked as the "Anchor member" in the comment in the code). Those results are then fed into the "Recursive member" of the CTE until all of the employees have been processed.
The "clever" bit is the derived column MtoE that "tracks" the levels of recursion, capturing first the Manager's name and then the Workers' name. I've cast the individual names to CHAR(30) to ensure that the subsequent ORDER BY works properly - i.e. making sure all of the names are the same length, padded by spaces if necessary. You might need to change the 30 to a larger number if you have some long names. Also note in the Anchor member I've cast the combination of names to be a VARCHAR as the column grows in length the further down the recursion you have to go (e.g. if there was a level 3 employee).
The very last bit just captures the data from Emp_CTE and ORDERs it BY the derived column so the output appears in name order.
I need to know how the recursive common table expression work behind the scene specifically when the recursive member returned with many result entries not a single result entry (i.e., single row) for example, the anchor member gets only one manager, and the first recursive member gets 3 employees follow that manager and they are, the 3 employees, in their turn are also managers to other employees etc. I hope that my question is clear
WITH TradingReceivableAccounts (AccountID, AccountCode, AccountName, GeneralAccount, AccountBranch, NatureOfAccount, AccountType, DebtorValue, CreditorValue, [Entry], [Description]) AS
ISNULL(aib.AccountBranch_LocalDebit, 0) AS DebtorValue,
ISNULL(aib.AccountBranch_LocalCredit, 0) AS CreditorValue,
CAST(NULLASNVARCHAR(250)) AS [Entry],
FROM AccountTree AS at INNERJOIN Account_InBranch AS aib
ON at.Account_ID = aib.AccountBranch_AccountID
AND aib.AccountBranch_Natural = 1AND aib.AccountBranch_Type = 2AND aib.AccountBranch_BranchID = 1UNIONALLSELECT ac.Account_ID,
CAST(0ASINT) AS AccountBranch,
CAST(0ASBIT) AS NatureOfAccount,
CAST(0ASTINYINT) AS AccountType,
FROM TradingReceivableAccounts AS CTE INNERJOIN AccountTree AS ac
ON CTE.AccountID = ac.Account_ID
INNERJOIN Journal_Details AS jd
ON jd.JournalDet_AccountID = ac.Account_ID
INNERJOIN Journal_Head AS jh
ON jh.Journal_ID = jd.JournalDet_HeadID
AND jh.Journal_BranchID = CTE.AccountBranch
AND jh.Journal_Date < N'02/18/2016')
The above query have the same idea. The anchor member returns a specific types of financial accounts and the recursive member gets all the payments\receipts transactions associated with each account. For example, lets say the anchor query returns the accounts 1, 2, 3, and 4 and with each account returned the recursive member will return all the payments\receipts associated with the account. I need to know how it will work behind the scene.
And created this rCTE query to traverse the hierarchy
;WITH Emp_CTE AS (
-- Anchor member - initialise the Level of recursion to 1
-- and start the Manager-to-Employee column MtoE
SELECT EmployeeID, ReportsTo, LastName, FirstName, Title, 1as RLevel
,MtoE = CAST(isnull(ReportsTo,0) ASVARCHAR(MAX)) + '/' + CAST(EmployeeID ASVARCHAR(MAX))
FROM Employees WHERE ReportsTo ISNULLUNIONALL-- Recursive member - and add on to MtoE the paths we are walking
SELECT e.EmployeeID, e.ReportsTo, e.LastName, e.FirstName, e.Title, RLevel + 1
,MtoE=MtoE + '/' + CAST(e.EmployeeID ASVARCHAR(MAX))
FROM Employees e
INNERJOIN Emp_CTE ecte ON ecte.EmployeeID = e.ReportsTo
SELECT EC.EmployeeID, EC.ReportsTo, EC.LastName, EC.FirstName, EC.Title, RLevel, MtoE
FROM Emp_CTE EC
If you examine the results
Emp.Id Manager LastName FirstName Job Title Level RecursionPath
2 NULL Fuller Andrew Vice President, Sales 1 0/2
1 2 Davolio Nancy Sales Representative 2 0/2/1
3 2 Leverling Janet Sales Representative 2 0/2/3
4 2 Peacock Margaret Sales Representative 2 0/2/4
5 2 Buchanan Steven Sales Manager 2 0/2/5
8 2 Callahan Laura Inside Sales Coordinator 2 0/2/8
6 5 Suyama Michael Sales Representative 3 0/2/5/6
7 5 King Robert Sales Representative 3 0/2/5/7
9 5 Dodsworth Anne Sales Representative 3 0/2/5/9
Those last two columns sort of describe the goings-on in the background. You can add something similar to your own query to see what is going on (I haven't used your query because there is no sample data for me to set up the appropriate tables)
I am so grateful for your interesting in answering me thanks a lot. Recall I know how the recursive CTE works behind the scene in is simplest for when the anchor query will have a single result each time, for example, Employee A is the CEO, Employee B reports to Employee A, Employee C reports to Employee B, Employee D reports to Employee C, and Employee E reports to Employee D. So the chain of management will look like:
B reports to A
C reports to B
D reports to C
E reports to D
But what if the management was looking like this: (A, B) -> (C, D, E) -> (F, G, H, I, J) where
C, D report to A
E report to B
F reports to C
G, H report to D
I, J report to E
So the first anchor member will return A and B those will be passed to the recursive member to get the C, D, and E etc. what I need to know is that is the mechanism of the recursion will go through A until it encounter NULL then go through B all way until it encounter NULL. I hope you got what I mean.
Depends on the database, Sql-server is breadth first, so it will return A,B,C,D,E,F,G,H,I,J.
Oracle on the other hand can do both Breadth first or Depth first via a parameter, and in Depth first it would return A,C,F,D,G,H,B,E,I,J.
How other databases handle things I don't know, but Breadth first is a good guess.
<edit>You can quite easy get the result you want by adding a simple order by recursionpath or order by level</edit>
Thanks a lot and as you said indeed no need to use the recursive common table expression within the code I shared and I already changed it and I used the CROSS APPLY operator and it works 100% fine and in 0 time instead of 24 seconds it was my fault what I wrote firstly . However, I've been curious on knowing how the recursive common table expression works behind the scene as I said I know how it works in its simplest form but I wanted to know what if the anchor member will return more than one result that will be joined with the recursive member and so on... how things will be done. Thanks for help now I knew what I need . Best regards, Amr Mohammad Rashad
i have a program needs to build and read schema information of mysql. there are several problems i still can not figure out. I hope I can get help here. My question are:
1.what kind engines support data directory when create a table of mysql?
2.what kind engines support index directory when create a table of mysql?
3.So far i only can retrieve data directory, how to retrieve index directory?
I have build C++/MFC windows desktop applications which using MS-Access databases with ADO.
The MS-Access databases which containing data are easy to include in the setup executable.
My applications also have access to a MS-SQLServer database in a cloud location.
Now I like to replace the MS-Access databases with local MS-SQLServer database.
I only have to change the connection-strings for ADO in the applications.
The only problem I think I have:
1. How to setup a local MS-SQLServer from my setup application.
2. How to deploy a not empty MS-SQLServer database.
I don't know where to start, will you hint me in the right direction?