SQL DB or XML DB?
I have a (new patient) form which has lot of combobox objects, eg. nationality, gender, country marital status.. etc and all these objects are currenlly reading data from tables in SQL Server but I am not sure if that's a good idea specally when it comes to application performamnce so I though why I can't store data in XML file(s) located in on the network instead of SQL tables..
But before moving to the XML file option I just wanted to ask you as an expert about this option? what do suggesst?
Well if you have many computers using the same application then its better to use SQL Server to store everything, since you can access it from anywhere and have everything "synced" so instead of Xml being stored locally, you would have duplicate records and inconsistancy of records etc... when storing things locally.
You must make sure you make your database design and procedures efficient. Take advantage of using SProcs (Stored Procedures) in SQL Server as these are faster to process data as well as being more securer. Make sure that you also do not constantly call the database in your application since calling databases and executing commands on the connection is expensive.
Xml is good and no doubt fast, since its local but overall for the long term, consider using SQL Server as a central storage server for data of your patients. It's also easier to maintain in a sense and do queries to your liking
I doubt a litle, but only until i read that you plan to put xml files on network share. If you have posibility to use SQL server features, you should alway use it. Only when you want to store something that is for Single user only, you can consider xml files. If you don't have SQL server, again you can consider xml files. But to store some data in xml files on network share versus puting in SQL Server, definitly NO.
best way would be to use either SqlDataReader to read the data, the SqlDataReader is a class that reads data waiting to be read in the buffer after getting the results returned back from SQL and is a fast forward only reader. You can access the values/columns by index and execute it in a while loop until no more data to be Read()
or you could fill a dataset using a SqlDataAdapter and then set the datasource to the dataset and set the displaymember to the column you want to show and the valuemember too. Also make sure that you use Stored Procedures for executing queries, inserting, updating or deleting records as this will make the execution faster and securer.
