Application development pointers

[John]

Hi

I have been developing vb/access but am new to sql server.Is there a sample
vb.net/sql server app somewhere that I can use to pick some pointer on how
to develop sql server apps in vb.net? Any other pointers will also be
appreciated.

Many Thanks

Regards

 

[Cor Ligthert[MVP]]

John,

I think that you should be more precise.

What do you want, develop in a way you did with ms-access
Have you used Jet (often called Access) with VB
What kind of VB did you use.

If the VB was a Net version and the database was Jet, then the only
difference is that you for SQL server better can use the SQLClient provider
instead of OledDB, but that is beside the connection string almost
changeable with Find and Replace (and not use the ? with parameters, as in
SQL those are named)

Cor

 

[William Vaughn \(MVP\)]

John, there is a LOT more to learning SQL Server over JET/Access development
than changing Connection Strings. I've written several books on the subject
and the ones still available would go a long way to bringing you up to
speed. See my website for more details.

--
__________________________________________________________________________
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
____________________________________________________________________________________________

 

[Mary Chipman [MSFT]]

The following paper discusses Access/SQL Server in the context of
using Access as a front-end, but it will nevertheless be helpful in
understanding the differences between the two engines, which will be
critical to succeeding on a server-based platform --
http://msdn.microsoft.com/en-us/library/bb188204.aspx. There isn't a
lot of documentation about how Jet works as contrasted with SQL
Server--this is pretty much it. OTOH, there's lots of VB/.NET
resources out there -- good luck with the project ;-)

--Mary

 

[Cor Ligthert[MVP]]

Hi Mary,

Is it really so difficult to go from Access to SQL server.

But Bill and you live in Redmond. There you learn probably first to fly a
spaceshutle before you step on a bicycle and later a car.

Here we have to do it with a scooter.

Cor

 

[Mary Chipman [MSFT]]

Actually, I live in Florida and unlike Bill, I have no corporate jet
much less a space shuttle. Having been MVP from 1995 to 2004 when I
joined msft, I've seen a world of trouble from people who have come to
grief in various migration scenarios, so I can categorically state
with great authority that YES, it really can be quite difficult to go
from Access to SQL Server. They are totally different database
engines, and ignorance is not your friend should you ever have to make
that journey from the desktop to a server RDBMS.

--Mary

 

[Mike]

Actually, I live in Florida and unlike Bill, I have no corporate jet
much less a space shuttle. Having been MVP from 1995 to 2004 when I
joined msft, I've seen a world of trouble from people who have come to
grief in various migration scenarios, so I can categorically state
with great authority that YES, it really can be quite difficult to go
from Access to SQL Server. They are totally different database
engines, and ignorance is not your friend should you ever have to make
that journey from the desktop to a server RDBMS.

+1

Back around 2002/3, we switched from our ACCESS sales database to
Web-Based ODBC/SQL. It was not a piece of cake.

Probably what may it easier was that MySQL had a ACCESS converter
which created schema and *.SQL files. Probably the main reason why
the migration project was pushed.

I'm sure MSSQL has a converter? no?

After that, it was a matter of creation the HTML/GUI access to it -
that more tedious part of the project. I recall (but not the details)
even writing something that recreated the SWITCH menu panels into a
HTML menuing layout. That gave us the project task outline.

If MSQL does not have a converter, you might want to use another tool
to create the schema and definition SQL files, then use that to
adjust/import into MSSQL. But I suspect this is the easy part and a
non-issue.

--

 

[Paul]

But in many respects ADO.NET abstracts the database engines from us.

All that is needed to understand are standard SQL and learn about procedures
possibly. Maybe years of using DB's has clouded my judgement but having
written Oracle and SQL and My SQL and in my dreary past Access I fail to see
how it is that big a step if you have a good grounding in SQL / SQL DML.

Yes all the stuff under the hood takes time but if you are migrating an
Access DB do you really need to know this? If he was migrating from Access
to oracle I would say he would struggle but Access to SQL Server, yes there
will be a learning curve but the difference in ADO.NET terms should be
minimal.

 

[Mary Chipman [MSFT]]

The relational engine may be abstracted away from the programmability
layer, but it's still there, and if you don't have a good grounding in
relational engine fundamentals (which is not a requirement for using
Access successfully), then you are guaranteed to encounter
difficulties if your application is at all complex or problematical. A
straight port of a failed Access app is almost guaranteed to be worse
in SQL Server when server and network overhead is added to the mix. So
I'd say that migrating may be no big deal for a person such as
yourself who does know their way around the block, but you are by no
means in the majority of people who post to the newsgroups on the
subject ;-)

--Mary

 

[sqlguru]

Do you even know what ADO.NET is? I hope you're not referring to
"hacks" like Linq, MVC framework, and any guaranteed to fail
RAD techniques.
Get a book on application architecture and design patterns.

 

[Paul]

If that was aimed at me. Then Yes, and I do not use Linq or MVC.

ADO.NET is still an abstraction.

I would run rings around you in application architecture and design
patterns.

Good day.

Do you even know what ADO.NET is? I hope you're not referring to
"hacks" like Linq, MVC framework, and any guaranteed to fail
RAD techniques.
Get a book on application architecture and design patterns.

 

[Paul]

Hi Mary,

You are right I probably just don't think about it but just do it. I suppose
with access allowing you to create a UI as well as just persistence the
problem can cover BLL, UI not just persistence, and with the ability to
create a non standard structure comes some re-design.

Paul

 

[sqlguru]

Haha, don't make me laugh. How exactly do you expect ADO.NET to
"abstract" the database engine from you without using hacks like MVC
and Linq?? Do you know what abstraction is? Get yourself a beginners
level book on software engineering.

 

[Paul]

So using ADO.NET directly without MVC or Linq am I not abstracted from

1. Communication Protocol?
2. Database engine?

Whether you like it or not ADO.NET itself is an abstraction to a DB's
interfaces and over communication protocols for talking to those interfaces.

Linq is an ORM abstraction, and probably over ADO.NET although not using
Linq I don't know that.

BTW there is no need to be an A$$.



Haha, don't make me laugh. How exactly do you expect ADO.NET to
"abstract" the database engine from you without using hacks like MVC
and Linq?? Do you know what abstraction is? Get yourself a beginners
level book on software engineering.

 

[sqlguru]

So you're telling me that you actually write SQL queries WITHOUT
knowing what type of database engine you're querying against? Or even
knowing the proprietary implementations of that database vendor that
may actually be more efficient and faster? Also, aren't you using
custom drivers/providers for different DB vendors?


Or do you just use a quick RAD tool to generate applications for you?
Now, that's abstraction!

 

[Paul]

LOL

Yes I do write SQL queries that can be used in multiple DB types and I have
a DB Provider of my own over ADO.NET that allows me to distinguish vendor
providers at runtime. I do not always need such functionality but as it is
written why not re-use it.

I have used the SQLClient, The ODP.NET and MySQL.NET specific providers, the
funny thing is although there are some differences as you point out
specifically around DBTypes enums especially they all seem to use the
ADO.NET architecture.

I never use a RAD tool and if you read some of my posts you would know I do
not rate Linq and the like, hence why I took offence. Yes using these is
more obvious abstraction.

But when you talk to SQL Server do you code the communication protocol and
code sending the SQL to a stream that the server understands. NO because all
that is abstracted from you by the vendors ADO.NET implementation/driver.
Funny enough thats also abstraction.

Bye

So you're telling me that you actually write SQL queries WITHOUT
knowing what type of database engine you're querying against? Or even
knowing the proprietary implementations of that database vendor that
may actually be more efficient and faster? Also, aren't you using
custom drivers/providers for different DB vendors?


Or do you just use a quick RAD tool to generate applications for you?
Now, that's abstraction!

 

[sqlguru]

You use your own DB provider? I think you don't know the difference
between utility implementation and provider.

I think you meant that you use your own DB utility and not provider.
Provider is a driver for the DB. For an example, for Oracle, the
provider is Oracle.DataAccess driver and the implementation DB
"utility" is OracleCommand, OracleConnection and other tools.

It's also a shame an expert like you does not know about the ADO.NET
"Common" framework which works for any database (DbCommand ring any
bells?) but I guess reinventing the wheel with a custom "utility not
provider" is better.

Also, how are you writing complex SQL queries that work for any
database vendor????? It must be very simple queries. As of yet, no DB
vendor fully supports ANSI standard but you should already know that.

I guess you don't need to know about the columns you're querying
against or the DB structure or any of that. It's all abstracted right?

Get a beginner level book on .NET Framework architecture.

 

[Paul]

It's also a shame an expert like you does not know about the ADO.NET

"Common" framework which works for any database (DbCommand ring any
bells?) but I guess reinventing the wheel with a custom "utility not
provider" is better.

This is flawed (well certain implementation are) and I touched on an example
above. Do a search on DBType mappings Oracle. Provider is a term used to
describe a number of things. It is a specific layer I create to
Abstract/Bridge these small differences in DB vendor implementations. But
you would know that if you had actually used more than SQL Server. What I
call it in my framework is particular to me.

Also, how are you writing complex SQL queries that work for any
database vendor????? It must be very simple queries. As of yet, no DB
vendor fully supports ANSI standard but you should already know that.

LOL. Good design negates the use for complexity within the database. surely
as a guru you know that, and any complexities should be an exception to the
rule rather than the rule and these can be hadled by my Provider or Bridge
or Facade whatever suits your definition.

I guess you don't need to know about the columns you're querying
against or the DB structure or any of that. It's all abstracted right?

Strangely enough I leave that to the ORM which is distinctly vendor provider
specific free as it uses those wonderful ADO.NET intefaces or "Common"
framework as you so eloquently put it.

Its strange how you can formulate an argument against me that would have
actually answered your own question if you actually knew what you were
talking about. I get the impression you are someone who talks rather than
does, specifically getting hung up on semantics. Your not worth the time.

 

[sqlguru]

This is flawed (well certain implementation are) and I touched on an example

above. Do a search on DBType mappings Oracle. Provider is a term used to
describe a number of things. It is a specific layer I create to
Abstract/Bridge these small differences in DB vendor implementations. But
you would know that if you had actually used more than SQL Server. What I
call it in my framework is particular to me.

I guess you create applications without requirements,
design,functional reqs, or documentation. If you had bothered to read
the inference of DBType, you would know the exact mappings (http://
download.oracle.com/docs/html/B14164_01/featOraCommand.htm#i1007297).
Blaming .NET for your ignorance is not the solution.

Also, if you failed to read the documentation of the Oracle Provider
(rookie error), then you would have created a conversion method for
OracleDbType to DbType instead of writing a whole new DB "provider".

LOL. Good design negates the use for complexity within the database. surely
as a guru you know that, and any complexities should be an exception to the
rule rather than the rule and these can be hadled by my Provider or Bridge
or Facade whatever suits your definition.

Okay, so write me a ANSI standard paging query that works in any
database without the use of anything proprietary. You have done
pagination before right??

Strangely enough I leave that to the ORM which is distinctly vendor provider
specific free as it uses those wonderful ADO.NET intefaces or "Common"
framework as you so eloquently put it.

First you're against ORM, and now you say you use ORM. Which is it?
You're applications are nothing more than 1-2-3-poof magic.

Its strange how you can formulate an argument against me that would have
actually answered your own question if you actually knew what you were
talking about. I get the impression you are someone who talks rather than
does, specifically getting hung up on semantics. Your not worth the time.

Do yourself a favor and get some beginner/dummy level books on
database architecture, application architecture, .NET framework
architecture so you can stop writing 1-2-3-poof magic applications.

 

[Paul]

I guess you create applications without requirements,
design,functional reqs, or documentation. If you had bothered to read
the inference of DBType, you would know the exact mappings (http://
download.oracle.com/docs/html/B14164_01/featOraCommand.htm#i1007297).
Blaming .NET for your ignorance is not the solution.

Also, if you failed to read the documentation of the Oracle Provider
(rookie error), then you would have created a conversion method for
OracleDbType to DbType instead of writing a whole new DB "provider".

Did I or didn't I read the documentation?make your mind up. Iam crazy to
know where I should have put this translation? Some entity that we dare not
call a provider, or ORM or DAL. Come on hotshot explain to us mortals rather
than telling us to read books because that makes me think you are just a
troll.

LOL. Good design negates the use for complexity within the database.
surely
as a guru you know that, and any complexities should be an exception to
the
rule rather than the rule and these can be hadled by my Provider or Bridge
or Facade whatever suits your definition.

Okay, so write me a ANSI standard paging query that works in any
database without the use of anything proprietary. You have done
pagination before right??

Did you actually read what I put?

Let me quote it again.

'any complexities should be an exception to the rule rather than the rule'

I live in the real world unlike you so understand about exceptions.

First you're against ORM, and now you say you use ORM. Which is it?
You're applications are nothing more than 1-2-3-poof magic.

I create my own DAL/ORM that converts IDataReaders to model objects yes. I
said I dont use the entity framework/linq/datasets.

Do yourself a favor and get some beginner/dummy level books on
database architecture, application architecture, .NET framework
architecture so you can stop writing 1-2-3-poof magic applications.

You have no arguments you just come out with simpleton comments like this
and not only to me. Explain to us and elighten us how you would do it then
and maybe then I will have more respect for you but for now your just one of
those wanna bees who is all hot air.