Design advise please

[John]

Hi

I need to design a winform db app preferably with bound controls. The app
will pick only one record at a time from backend db. The sql query that
brings data from backend needs to be flexible in terms of WHERE and ORDER BY
clauses. What data access method should I use?

Many Thanks

Regards

 

[Mr. Arnold]

Hi

I need to design a winform db app preferably with bound controls. The app
will pick only one record at a time from backend db. The sql query that
brings data from backend needs to be flexible in terms of WHERE and ORDER
BY clauses. What data access method should I use?

ADO.NET SQL Command Objects is one. Orr better yet, ADO.NET Entities
framework with Linq to Entities and ESQL, if you can use VS2008 and the 3.5
framework. ADO.NET Entities framework works with many database providers.

 

[John]

I am using vs2008. I am not experienced in ADO.NET Entities framework. Is it
worth learning for the benefits it offer? Is there a tutorial that gets me
started?

Thanks

Regards

 

[John]

Is ADO.NET Entities framework also useable with MS Access which is my
backend db?

Thanks

Regards

 

[Cor Ligthert[MVP]]

No

Is ADO.NET Entities framework also useable with MS Access which is my
backend db?

Thanks

Regards

 

[Cor Ligthert[MVP]]

John,

In this case I would create some textboxes on screen and then simple move by
"=" to the text property of the textboxes.
You don't update something so bound controls don't add anything.

Then an OleDbDataReader.

You can then every time change the Select string by Cmd.CommandText =
"Select ..............................."

Or even make the select string more dynamic by doing it as "Select
................... & OrderByClause

As somebody starts telling about vulnaribilty by SQL intection, be aware
that it is culprit in this case.
All was it alone that you cannot reach a Jet database direct over the
Internet.

In the sample in this link is used the integer as indexer, you can us as
well strings, I always use that because the integer gives direct problems as
you change the place of a column in your Sql statement or use an * and
change the columns in your database.
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.aspx

Cor

 

[sloan]

If you're only bringing back 1 records at a time, why do you need an ORDER
BY?


If you're bent on using access (actually "JET database" is a better term for
it, since you won't be using the program Microsoft Access)......then look
here:
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!176.entry

You CAN build an application that starts in Access, but doesn't paint you
into a corner.

.....................

What version of the Framework are you considering using??

 

[John]

Hi

Thanks. I have sql server all editions from MSDN. I need to design another
interface to the existing access app that is already deployed for several
years and unfortunately can't get rid of access except first re-writing the
original access app to sql server which is a daunting task.

Regards

 

[John]

Its the order in which I pick next or previous record in sequence.

Regards

 

[William Vaughn \(MVP\)]

John, what you're describing can be done against an Access/JET/ACE or a SQL
Server database but if not done correctly, only one user will be able to
access the data at any one time. When you execute a query

SELECT name, address, city, state FROM Customers WHERE State = 'TX'
ORDER BY CountyCode, Name

you'll return all of the customers in Texas--one row at a time if you use a
DataReader. However, until you fetch the last row, the database engine will
(probably) impose "share locks" on the data rows (and pages) that will
prevent changes to the underlying rows. For this reason (and many others) we
generally fetch the rows into a server-side cursor or into a client-side
structure like a DataTable or TableAdapter using a Fill or Load method. This
releases the locks and lets other users access the data.

The query shown above can be fed with one or more Parameters (via the
Command object) to focus the rowset--fewer rows mean fewer locks and less
overhead for everyone.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

 

[John]

Hi Bill

Generally I have four queries for first rec, last rec, prev rec, next rec.
They fetch like below which I hope only locks the page with the one record
that I have fetched?

Assuming ID order;

FillFirst: SELECT TOP 1 <fields list>
FROM Clients
ORDER BY ID

FillNext: SELECT TOP 1 <fields list>
FROM Clients
WHERE (ID > ?)
ORDER BY ID

FillPrev: SELECT TOP 1 <fields list>
FROM Clients
WHERE (ID < ?)
ORDER BY ID DESC

FillLast: SELECT TOP 1 <fields list>
FROM Clients
ORDER BY ID DESC

This has several advantages. One is of scalability and other is you do not
have to keep reading a number of records to ensure you have the most up to
date recordset in a multi-user environment where other users may be
updating/adding or deleting records.

The problem I now have is how to get this data to my bound controls with the
proviso that I can change the sql as needed.

Thanks

Regards

 

[Michel Posseth [MCP]]

As somebody starts telling about vulnaribilty by SQL intection, be aware
that it is culprit in this case.
All was it alone that you cannot reach a Jet database direct over the
Internet.

Well you may not be able to reach the database directly , this is in my
situation also the case with SQL server , in my company ( and for a fact
all the companies i worked for and with ) the SQL servers are also not
directly connected to the internet ( WAN ) but located on a LAN through a
seperate NIC on the Webserver .

But doest this make them safe for SQL injection ? ! , sure not ! as SQL
injection is mostly done through the URI or through user input fields wich
are availlable in the presentation layer . and it doesn`t mather wich
database it is ACCESS , MSSQL , MYSQL , ORACLE , DB2 etc etc etc the only 2
things you need is a Developer who doesn`t understand the concept and a
"wannabe" hacker who does understand it.

This is the reasson why i followed a course at
http://www.madison-gurkha.com/en/index.php where i found out with my
Development laptop that Microsoft already implemented a lot of counter
measures in the framework , and you almost have to write explicit code to
allow SQL injection , 2 of the biggest mistakes is
allowing string concatation to the db backend directly from the GUI ,
making detailed DB exceptions visible to the presentation layer

And believe me with the knowledge you get at such a course you can bring a
lot of websites or programs down , i believe that 80% of developers are not
aware of the risk they take with certain implementations , at one company i
was at a audit ( where we checked code that was written by other working
companies and / or by outsourced projects ) able to delete production
tables or getting valuable information from the GUI as customer info ,
passwords or wich database and or how the table structure was by deliberily
crashing the app with malicious data entry .

so hint :

Use a safe data access mode cause there is nothing as worse as that your
employer hires those guys of madison or simular to them to investigate what
has gone wrong and that there conclusion is that the developer of the
product made some common mistakes regarding SQL injection , i wonder what
will happen if this is in a company where the core business and so value is
within the data . I know for sure that i would be sent home as i work at
such a company , where the audit sure should take place if we were missing
data or it has gone corupted .


Personaly i write always at least a three tiered app , my presentation layer
will never expose anything about my database the user only has to know that
his data could not get stored or that there is a unexpected exception at the
database level but he doesn`t have to know that "a unexpected data error
occured at SQL.Client Insert into DBO.Users .......................... "
this seemingly harmless error tells me already that i now only have to focus
on MS SQL vulnerabilities and that there exists a table named Users

well i hope i made my point clear


Michel Posseth

 

[Cor Ligthert[MVP]]

Michel,

Every SQLServer has an IP address, (as every Server in fact has). Jet has no
IP address, simple because it is not a DataBase Server but a DataBase File.

This fact makes it in my idea unreachable for injections, it will never
process the injected code whatever you try.

(Or you should make a program that proccesses injections and load that on
the infected client. I think that as that is possible there are more simple
ways to reach your goals with an injection).

As someone has the oposite opinion, then please no elitair directions to a
webpage where somebody only says that injections with Jet is possible.

Cor

 

[Jon]

But couldn't you use SQL injection to delete a table, or something like this, even with a JET
database file?


Michel,

Every SQLServer has an IP address, (as every Server in fact has). Jet has no
IP address, simple because it is not a DataBase Server but a DataBase File.

This fact makes it in my idea unreachable for injections, it will never
process the injected code whatever you try.

(Or you should make a program that proccesses injections and load that on
the infected client. I think that as that is possible there are more simple
ways to reach your goals with an injection).

As someone has the oposite opinion, then please no elitair directions to a
webpage where somebody only says that injections with Jet is possible.

Cor

 

[Michel Posseth [MCP]]

Jon you are right with your statement

Every SQLServer has an IP address, (as every Server in fact has). Jet has no
IP address, simple because it is not a DataBase Server but a DataBase
File.

Yes and ?

This fact makes it in my idea unreachable for injections, it will never
process the injected code whatever you try.

Wrong , as SQL injection is mostly done through the input fields of the GUI
so ACCESS is just as vulnerable as MS SQL or MYSQL etc etc

As someone has the oposite opinion, then please no elitair directions to a
webpage where somebody only says that injections with Jet is possible.

Every SQL processing platform is potentially vulnerable for SQL injection

Here is some reading material of the concept SQL injection

http://www.ngssoftware.com/papers/advanced_sql_injection.pdf

http://en.wikipedia.org/wiki/SQL_injection

http://www.google.nl/search?hl=nl&q=SQL+injection&meta=



HTH

Michel Posseth [MCP]