|
Hi all,
I'm very new to designing and working with DB based apps so please forgive me if I write down things that can make seasoned developer's cry in aguish.
We are right now developping a Remote application scenario with a client app and a server app. The server app will be connected to a DB, SQL Server for more info, which is not, theoretically, reachable from any other machine in the intranet or internet. Only access is through the server app.
Ok the thing is we want the developers of our client app and the client app itself,which is basically 99% of the project workload, to "know as little as possible" of the DB behind the whole project and make all query and update to the DB as simple as possible (through the server app of course).
The design we (I) came up with is the following: (Shuts his eyes and prays he hasnt blown all major rules of designing applications and if so please forgive me)
The server app will have a main worker class named DbEngine which basically does all the work with a few helper classes. The method used to execute selection queries and bring back info to the client app would be the following:
byte[] ExecuteCommand(string commandName, Dictionary<string,object> parameters).
byte[] is a serialized datatable, compressed and most likely, allthough not yet encrypted.
The only thing the client application needs to know is what commandname to call and what parameterName and value to send if any. Please not that the parameters Dictionary does not store any Parameter class, its simply a string key with the parameter name (@columnname) and its value. Its pretty lightweight and easy to code.
The whole work is done in the server side. The thing is, we are storing a table in the SQL Server Database, which we would call TBL_SYS_COMMANDS with all the SQL/Stored Procedure commands our app is going to use. The info stored in this table would be:
[CommmandName] (Primary Unique Key)
[CommandText] (this would be a stored procedure name or a SQL command)
[CommmandType] (Text, StoredProcedure, etc. Server app would parse the string into the corresponding .NET enum)
[Parameters] (would contain custom serialization of parameter(s) info, except Value of course).
The server app would recieve the ExecuteCommand, look up the commandname in the cached TBL_SYS_COMMANDS datatable and if found, build up command and the parameters through reflection, assign their values, execute the command and send back info compressed in a byte array.
Security measures taken: The DbEngine class in the server side which takes care of executing the ExecuteCommand has a custom attribute with a list of allowed commandNames. If somebody somehow injected a harmfull command in TBL_SYS_COMMANDS it would not be executed because its not included in the custom attribute's allowed command list and the DBEngine class would throw a SecurityException...any new command needs the server code recompiled once the new command has been added to the allowed commands list in the DbEngine's custom attribute.
Thats basically it. Am I forgetting some great security hole somewhere storing my SQL commands in a database table? Is this design way to complicated? Basically we want to minimise coding and specially recoding if we make changes and fine tune the database, queries, stored procedures etc, and this system basically allows you to change all this without having to dig into almost any code at all...only update the allowed commands attribute.
Please recommend other approaches etc.
Thanks in advance, and sorry for the long post.
-- modified at 13:16 Friday 9th March, 2007
|
|
|
|
|
I have been writing SQL for about 10 years now. Never have I ever used the words "inner" or "outer" in my stored procedures. I use "join", "left join" and "right join". My question is what are the "inner" and "outer" key words used for? Isn't a join always an "inner join" and a left or right join always an "outer" join or am I missing somethin?
|
|
|
|
|
ToddHileHoffer wrote: My question is what are the "inner" and "outer" key words used for?
Explicitness.
You can alias a table like this:
SELECT * FROM MyTable t or like this
SELECT * FROM MyTable AS t I always use the latter form as I feel that it makes the intention that bit clearer.
Having said that I don't use OUTER as I feel that the LEFT or RIGHT part add enough explicitness. I do use INNER though.
|
|
|
|
|
i'm a biggener , and asked to design SQL database project run on server and send specific fields to clients on the local network ..
i need some recommenditions and books to help me
send accounting informations to and from accounting application , and another feilds to the administrator , and so on
how to train my ideas to begin ?
|
|
|
|
|
how to send data from server to client on the local network and vice versa , using SQL server 2000 ?
|
|
|
|
|
Mr.Kode wrote: how to send data from server to client on the local network and vice versa , using SQL server 2000 ?
You mean executing queries remotely ?
|
|
|
|
|
Hello
My requirement is to do search facility for my website. So what is your idea for maintaining the index ? I read one article that when google was created they used to crawl 10billion pages and index size was just 120GB. how's it possible ? Will they store all words in the target website ? How can I implement a search for single website ?
|
|
|
|
|
I have my DB in SQL Server 2000 with the applications developed in ASP, VB. Now i am looking for mirroring my DB because i am going to change the db strucute with similar modifications. i m going to recreate older ASP, vb applications into C#.net & ASP.net
if any one have the idea.. kindly help in this issue..
thanks in advance.
Tech_spidy
|
|
|
|
|
|
hi every one
well cananyone plz tell how we can backup a database so tat we can restore it with ONLY its table structure WITHOUT the data u know.
Rocky
|
|
|
|
|
Hi,
I presume u work with an SQL Server database.
Make a schema export using Entreprise manager by right clicking on the database / All tasks / Export schema.
The exported file is an sql script.
HTH
|
|
|
|
|
yes that looks preety help ful thanks alot 
|
|
|
|
|
Iam having a table containing 10 rows.
Generally for retrieving the Data we will use SELECT Query as
Select * from tablename;
But if we want a particular ROw then we use as
Select * from tablename where colname="xyz"
If I want to display first row without using where clause and to get the results from select Query then how is it possible?
Thanx in Advance
Priya
|
|
|
|
|
Hi
You can limit results using 'TOP n' keyword where n is the number of rows to be retrieved.
For Eg.,
SELECT Top 2 * FROM Employee; --> display only 2 rows
SELECT Top 2 FName,LName FROM Employee;
--> display only 2 rows but two columns FName and LName only
Hope this is clear
Harini
|
|
|
|
|
If I want a Specific Row then Compulsorily I Have to use WHere Clause,
Can't we write a Query without using Where Clause
Please Reply ne urgently.......
Thanks in advance
Priya
|
|
|
|
|
SQL Server does not store rows like a flat file so when you say first row all you can really do is SELECT TOP 1 * FROM TABLE. You can include a WHERE clause though by saying SELECT TOP 1 * FROM TABLE WHERE Name="xyz".
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that." - Tommy Boy
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
how to create sp for username and password
-- modified at 23:54 Thursday 8th March, 2007
|
|
|
|
|
make your question more clear
daku1 wrote: how to create sp for username and password
Do you want to create a procedure that can validate user name and password ? Then it could be something like this
CREATE PROCEDURE ValidateProcedure
(
@Username varchar(20),
@Password varchar(20)
)
AS
Select * From UserTable Where UserNameColumn = @Username and PasswordColumn = @Password
GO
Get the result in some reader. If the reader has records then u r login is authenticated. If you want to make your password case sensitive then go through this snippet http://www.w3hearts.com/showsnippet.aspx?show=22
|
|
|
|
|
Hi
Can anybody please let me know that How many tables we can have in sqldatareader. please let me know anybody have the solution. Thanks in advance
Thanks and Regards
Venkat
|
|
|
|
|
I think only one table. Dataset can have more than one table
|
|
|
|
|
Hi,
Thanks for the reply. But i have one doubt please clarify. In datareader there is one method called NextResult(). It retrives the next resultset. can you please clarify how the above mentioned methods works if datareader contains only one table. I am in too much confusion, please clarify my doubt. Thanks in advance
Thanks and Regards
Venkat
|
|
|
|
|
Great!!!!!!! It is possible. Datareader can contain multiple resultsets. NextResult() will advance to the next result. See the MSDN Documentation for NextResult().
Check this article which explains about storing multiple resultsets on datareader.
But since datareader is connection oriented, I prefer using datasets to manipulate with multiple tables.
Any way thank you for bringing out this topic here
|
|
|
|
|
I have written a service (in C#) that reads and writes to an SQL database. At the begining of the service I execute sp_who2 to see if another computer connected to the same database is already running the service and if it is I stop the service. My problem is that the service is not showing up in the results of sp_who2.
Do you have to initialise anything either in the database or my service for it to be displayed by sp_who2?
|
|
|
|
|
You would need to have an open connection to the database for it to show up. Normally you would not want a service to hold open a connection to the database just so you can tell it is running.
Why not just create a sql table that the service can look at?
Hope that helps.
Ben
|
|
|
|
|
I thought of that but if one computer is running the service and crashes the service's state would still be set to running in the table and then no other computer would be able to run that service until the state was manually reset in the database.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
