using connection

[cops]

hello expert...
i try learn how to develop server client info system
i read example how to retrieving and saving in data base
i already read here and i still confuse in using a connection
is there someone can help explain to me about using connection?
also the color text

thanks


#1
Private connection As New SqlConnection("connection string here")
Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _ connection)

#2
Using connection As New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\ProjectMW\MachineWale\MachineWale\DatabaseMW.mdf;Integrated Security=True;User Instance=True")
Using command As New SqlClient.SqlCommand("INSERT INTO MachineProfile ([MachineID], [Category], [kmsrun]) VALUES (@MachineID, @Category, @kmsrun) where [MachineID] = '" & TextBox1.Text & "'", connection)

#3

Dim connString As String = "Server=.\SQLExpress;AttachDbFilename=|DataDirectory|tiresoft.mdf; Database=tiresoft.mdf;Trusted_Connection=Yes;"
Dim sql, state As String
Dim conn As SqlConnection
Dim CMD As SqlCommand
Dim DRead As SqlDataReader

[jmcilhinney]

Unlike ADO as used in VB6, ADO.NET is designed using good OO practices, including the single-responsibility principle. That means that, rather than there being one super-class that does everything, there are multiple classes, each with a single responsibility.

The SqlConnection class manages the connection between your app and the database. The connection string includes all the details of the server and database you want to connect to, the user name and password used to connect and any other parameters that control how the connection is made. When you open the connection you are able to pass data between the app and the database. Once it's closed, you can't.

The SqlCommand class represents a SQL statement or collection of SQL statements that you can execute over a connection, either to retrieve data, modify data or manipulate the database in some other way.

A SqlDataReader represents the result set of a query, i.e. SELECT statement, that you can access in a read-only, forward only manner. You open a SqlConnection, call ExecuteReader on a SqlCommand and it returns a SqlDataReader. You can then access the results of that query row by row via the SqlDataReader.

A SqlDataAdapter basically groups together up to four SqlCommands that represent four basic CRUD (Create, Retrieve, Update, Delete) operations. You call Fill on a SqlDataAdapter and its executes its SelectCommand to populate a DataTable with the result set. You can then modify the data in that DataTable in whatever way you see fit. You then call Update on the SqlDataAdapter and it will execute its DeleteCommand, InsertCommand and UpdateCommand as required to save all the changes in the DataTable back to the database.

That CodeBank thread of mine shows examples of using each of those classes and the types of scenarios in which each is appropriate. You'll always need a SqlConnection for any database operation involving SQL Server and you'll always need a SqlCommand to get or save any data. The main question is whether you retrieve data using a SqlDataAdapter or a SqlDataReader when retrieving data and my CodeBank thread covers that.

[cops]

thanks....jmcilhinney
thats the answer i looking for..
and i waiting for you to answer this question
coz i read it from ur thread
sorry for bothering u hopefully u will help more


next question
1.
what is different with
connection As New SqlConnection and connection As New SqlClient.SqlConnection
2. is this correct
Dim connString As String = "Server=.\SQLExpress;AttachDbFilename=|DataDirectory|tiresoft.mdf; Database=tiresoft.mdf;Trusted_Connection=Yes;"
should i write the attachdb and also the database?

[jmcilhinney]

1. The SqlConnection class is a member of the System.Data.SqlClient namespace. By default, most projects import the System.Data namespace, so you would refer to the class as SqlClient.SqlConnection. If you import the System.Data.SqlClient namespace then you can refer to it just as SqlConnection.

2. No you shouldn't use AttachDbFilename and Database. You use Database if you want to connect to a database that is already attached to an instance. If you're using SQL Server Express and you've added an MDF file to your project then you need to attach that each time you connect, so you use AttachDbFilename.

[cops]

so for my case i should write like this?
Dim connString As String = "Server=.\SQLExpress;AttachDbFilename=|DataDirectory|tiresoft.mdf;Trusted_Connection=Yes;"

[jmcilhinney]

I would suggest that you consult www.connectionstrings.com. They provide various connection string formats for various scenarios. Just find the one that matches your circumstances.

[cops]

ok thanks...