andradrr

Hello people.

Please, can anyone point me any tutorial / walkthrough / references on how to use vb 2005 Express in conjuction with SQL Server Express without the use of any wizard

What I mean is: I'm creating a windows app that access data on a remote SQL Server Express and would like to control all the data access by myself.

Why I do want do do this, you may be asking... The short answer is that the application I'm developing have a high end user interface that do not behaves like the functionality of a data grid or even the navigating buttons that binded controls have.

I'm not developing and email client, but you can imagine something like Outlook and Exchange Server pair. You simply can't develop and Outlook client visual appeal with the result produced using the VB 2005 Express wizards. The app can't have the forward/backward navigating buttons and will continuously check the server for changes in the database in order to modify the contents of the user interface.

I also don't need a deep and very well explained tutorial. I just need the concepts of creating a TableAdapter/DataAdapter, link these to a DataSet and be able to handle the data (insert, update and delete), if it can also give some info about transactions iy wold be great! With the concepts in hand I'm sure I can dig the rest for myself.

I've already found a lot of things in google and msdn, but I'm still having some problems in order to wrap up everything.

Any help and/or suggestions are very very welcomed.

Tanks a lot in advance for all your time and attention.



Re: Visual Basic Express Edition Database without wizards

Squire James

A summary of the process is as follows:

1.  Create your DataTables and TableAdapters in a Dataset file as normal.  Use the regular wizard and a local connection string.

2.  Rather than binding to a DataGridView, declare the TableAdapter directly.  Similarly, declare your own Dataset.

3.  Change the TableAdapter's Connection property to a new connection with the remote connection string.

4.  Use the TableAdapter's Fill property to populate your Dataset.

5.  Set a timer that Updates and Fills your Dataset periodically.  Make it global enough so it can be stopped and restarted under certain circumstances.

6.  Apply changes directly to the Dataset as dictated by your code.

7.  Use the TableAdapter's Update property to write your Dataset back to the database.

This is all for the actual code, but more will need to be done at the remote computer.  SQL Server Express will need to be on the firewall exception list on the remote machine (I don't remember if you have to know the port numbers or not).  Finally, SQL Server Express has to be configured to use TCP/IP as well as named pipes.

I should note that TableAdapters are sometimes tempermental about forming Update, Insert, and Delete options if created through the wizard.  You may have to resort to untyped Datasets, where you have more of a free hand in crafting your queries (and absolutely no wizards).





Re: Visual Basic Express Edition Database without wizards

ReneeC

In the last year I've done almost exclusively database programming. For about two hours when I began, I used a wizard. Then I had to change a table and the wizard generated code didn't want to accododate that. This occurred two or three times and I grew tired of deleting projects and restarting them. I've never used a wizard since and since then I've written a lot of database code, with many of the databases being thousands of miles away from my programmings site. I never use the wizard for anything. When I need an adapter, I declare it like any other class. I've never needed a table adapter, I just use adapters and rarely do I use datasets.

I would agree with you that you cannot attain the look that you are looking for with the wizard. One place that I would vary with SquireJames is that I would make it event driven and not use a timer. To achieve this, I make extensive use of stored procedure and often create and throw away tables on the fly. The judicious use of stored procedures, minimizes network traffic and offloads the compute burden to the server. It also minimizes the amount of memory consumption on the client machine. If you do a good design, you can achieve what parallels local level performance.

I haven't done with with an Outlook look but I have done it with a knowledge base which has a UI that looks and behaves a lot like Windows Explorer.






Re: Visual Basic Express Edition Database without wizards

ron nash

Hi,

I had the same issue and purchased the following book: SQL Server 2005 EXpress, ISBN:1-59059-523-8

The way I would do this, without using stored Procedures, is as follows;

1) Add  Imports to the Class

Imports System.Data.SqlClient

Public Class Form1

2)  Define the data objects , I have given these a public scope as I need to reference them from other classes

Public NewAccountDataset As DataSet '\\ Declare dataset object

Public NewAccountTable As DataTable '\\ Declare DataTable for new account

Public newAccountTableAdapter As SqlDataAdapter

3) Define the connection string, I have used the IP loop back address here which means local machine. This would be changed to reference the sql server.I am also using windows Integrated security. The Catalog = SSOP needs to be changed to your database name. Catalog=XXXXXXX

cnn1 = New SqlConnection("data source=127.0.0.1\sqlexpress;Integrated Security=true;Initial Catalog=SSOP")

4)  Create connection and, in this example, select all records from the Accounts Database Table

 

Dim accountsQuery As String = " SELECT * FROM Accounts" '\\Sets SQL Query

NewAccountDataset = New DataSet '\\ Create New Dataset

newAccountTableAdapter = New SqlDataAdapter(accountsQuery, cnn1) '\\Create new DataAdapter setting the Query and connection string

newAccountTableAdapter.Fill(NewAccountDataset, "accounts") '\\ Fill the Dataset Table (accounts) with data from the database

NewAccountTable = NewAccountDataset.Tables("accounts") '\\Create new Object for the Dataset Table "accounts"

The newAccountTable object will now hold all the records from the Accounts DatabaseTable. You use the table object like you would have used the one using the wizard

With regards to SQL express, by default, remote connections are disabled. You will need to enable this using the SQL Express Manager. Also if you have a Firewall running you will need to configure it to allow communication. For testing disable it.

It is also of benifit to download the SQL 2005 Express SQL Server Management Studio from MS. This will allow you to manage, configure, create sql queries, stored procs etc etc.

 

Hope this helps

Ron Nash