[RESOLVED] Moving from VB6 to VB.NET have some questions about Sql and ADO etc

[easymoney]

Finally making the push to .NET and need some clarification on some working code. This example use SqlCe and .NET CF, but it is a not directly related to Mobile Development.

        Dim objAdapter As SqlCeDataAdapter
        Dim objDataset As Data.DataSet
        Dim strSQL As String

        strSQL = "SELECT * FROM Accounts"

        objAdapter = New SqlCeDataAdapter(strSQL, "Data Source = \My Documents\test.sdf")

        objDataset = New Data.DataSet()
        objAdapter.Fill(objDataset, "Accounts")

        dgAccounts.DataSource = objDataset.Tables("Accounts")

1) objAdapter is a SqlCeDataAdapter that is used to connect to the SqlCe db.
2) objDataset is configured as Data.DataSet, basically the ADO data connection.
3) The objAdapter is connected to the SqlCe db test.sdf
4) Then objDataset = New Data.DataSet() connects the SqlCe db to the ADO and then fills objDataset with the data slected from the Accounts Table
5) Finally, the datagrid populated with data from objDataset

I guess this is how data is transferred from the DataAdapter to the ADO data connection?

Also, I this the proper use of ' Data.DataSet' or should they be an declared like 'Imports System.Data.SqlServerCe" or someother declaration?

How is the datagrid populated? .NET must have libraries to do this for us, in VB6 we had to load data cell by cell or is this a DataBound control..???

Thanks in advance...

[techgnome]

Since you aren't doing mobile development, what you should be using is the SQLClient namespace, and not the CE one. Look in my sig for the ADO.NET tutorials (don't get them confused with the VB6 ones).... it walks you through some of the basics, including using a datagrid.

-tg

[easymoney]

Since you aren't doing mobile development, what you should be using is the SQLClient namespace, and not the CE one. Look in my sig for the ADO.NET tutorials (don't get them confused with the VB6 ones).... it walks you through some of the basics, including using a datagrid.

-tg

Never meant to say that the code wan't related to mobile development, I just meant the questions were not directly related. It is from some working code from a handheld app.

I am just curious of how the data connection works with Sql and ADO... Just trying to get a grasp on the what the terms are referencing, etc.

Whether or not I am using Sql or Sqlce, You would still use DataAdapters and Data.DataSet refernce with Sql or SqlCe just different namespaces, correct ?

[jmcilhinney]

All ADO.NET works the same way no matter the platform or data source. You create a Connection with a connection string to handle connecting to the database. You create a Command to execute an SQL statement over the connection. You create a DataAdapter to encapsulate all the Commands that will operate on a single table. A DataTable is used to store data locally and a DataSet is used to store multiple tables and the relationships between them.

[easymoney]

All ADO.NET works the same way no matter the platform or data source. You create a Connection with a connection string to handle connecting to the database. You create a Command to execute an SQL statement over the connection. You create a DataAdapter to encapsulate all the Commands that will operate on a single table. A DataTable is used to store data locally and a DataSet is used to store multiple tables and the relationships between them.

Thanks. That was a very helpful clarification, thanks for filling in the gap. It is starting to make sense now.

[easymoney]

Of course my primary goal for this app is for it to deploy a back-end Sql Server 2005 server running on a dedicated machine. The VB front end will run from client workstations on a local network.

I assume for now I should start with Sql Server 2005 Express for the development phase. Is it lightweight enough to run with VS 2005 ide on the local machine vs full blown Sql server connecting across a network?

Sadly, I am attempting to migrate a large MDB access file to Sql. Currently the application authorization process is merely usernames and passwords stores in an un-encrypted file shared on a network drive. It works ok for 4 or 5 users, but of course any one on the network could simply edit the db or even delete the entire file.

I need to nail down a process and model for preventing the types of actions. So on top of Domain based user security, I need db connection via IP and server based security. Utimately I need the application to have the ability to log each users login info, accounts accessed, data changed, etc and also lockout users during critical process or db updates. I would like to allow the app security to have user and group access to limit data access based on username and password vs total server administration.

Finally on the wishlist is the ability for parts of the db be exported to a webserver so that basic functions can be accessed from the web. Now lets backup into reality at this very moment.....

For now, it is just basic data access and creating a working model, but what should the roadmap look like to at least start in the right direction? Any insight anyone... TIA

[jmcilhinney]

Once your app is connection to SQL Server it doesn't care. You can change the connection to connect to a different database or a different server. You can develop your app by attaching an MDF file while debugging and then simply change the connection string when you deploy to connect to an existing database on a local or remote server. It's all the same to your app.

SQL Server will handle a lot of those tasks for you. You should use the in-built capabilities of the server as much as you can. For anything else you can build the functionality into your app when the time comes.

[easymoney]

Been doing some reading, I think the model I am utilmately migrating OLTP type of database.

OLTP Database

Online Transaction Processing (OLTP) relational databases are optimal for managing changing data. They typically have several users who are performing transactions at the same time that change real-time data. Although individual requests by users for data generally reference few rows, many of these requests are being made at the same time.

OLTP databases are designed to let transactional applications write only the data needed to handle a single transaction as quickly as possible. OLTP databases generally do the following:

Support large numbers of concurrent users who are regularly adding and modifying data.

Represent the constantly changing state of an organization, but do not save its history.

Contain lots of data, including extensive data used to verify transactions.

Have complex structures.

Are tuned to be responsive to transaction activity.

Provide the technology infrastructure to support the day-to-day operations of an organization.

Individual transactions are completed quickly and access relatively small amounts of data. OLTP systems are designed and tuned to process hundreds or thousands of transactions being entered at the same time.

The data in OLTP systems is organized primarily to support transactions, such as the following:

Recording an order from a point-of-sale terminal or entered through a Web site.

Placing an order for more supplies when inventory quantities drop to a specified level.

Tracking components as they are assembled into a final product in a manufacturing facility.

Recording employee data.

[easymoney]

I have the Sql Management Studio Express which I can successfully enter Sql statements to Create tables, insert data, etc.

I know I can make an Sql statement to view data from Sql db's like a table displayed in Datagrid, but is there anyway to view the db field names, data types etc. once the DB has already been created?

Here is my example, don't know if it is correct, but it works Sql MSE:

USE master
GO
select * from sysdatabases where name='Northwind'

GO
use "Northwind"

select * from Employees

Also, is there anyway to do this in a visual interface. I guess Sql is a big boy's toy, use Sql statement to do everything... maybe that's why it is Sql. Not having much luck. Thanks.

[easymoney]

Nevermind, I see it now. It is in Sql Management tool to do this....

[jmcilhinney]

Questions regarding SQL Server Management Studio Express are beyond the scope of the VB.NET forum anyway.

[easymoney]

Yes, I posted another topic in another forum. I forgot to mark this thread resolved. My primary goals have been worked out. Thanks.