ADO.NET

Walk through recordset - using SQL

Imports System.Data.SqlClient
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim sdr As SqlDataReader
Dim strConnection as string = "Data Source=dafehvvosql1\SQL2005;Initial Catalog=Ilnaz;Integrated Security=SSPI"
conn = New SqlClient.SqlConnection(strConnection)
conn.Open()
cmd = New SqlCommand("select CatName from Categories", conn)
sdr = cmd.ExecuteReader()
Do While sdr.Read
Loop
sdr.Close()
sdr = Nothing
cmd = Nothing
conn.Close()
conn = Nothing
'Connect to Datagridview: dgProducts.DataSource = cmd.ExecuteReader();

Walk through recordset - using typed datasets

dataset.csGetCountryDealersDataTable mDealersDT = new dataset.csGetCountryDealersDataTable();
datasetTableAdapters.csGetCountryDealersTA mDealersTA = new datasetTableAdapters.csGetCountryDealersTA();
mDealersDa.Fill(mDealersDt, countryID);
foreach (dataset.csGetCountryDealersRow mRow in mDealersDT.Rows)
{
  mDealer.id = mRow.DealerID;
  mDealer.commission = mRow.IscommissionAmountNull() ? 0 : mRow.commissionAmount;
}

Execute queries

dim ra as integer
cmd = new New SqlCommand("("Insert into Jobs values 'IT Manager'", conn)
ra = cmd.ExecuteNonQuery()

Return a single value

cmd = New SqlCommand("select count(*) from Categories", conn)
numberCategories = cmd.ExecuteScalar()

Call stored procedure with parameters

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("csSaveNewQuoteVersion", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter("@id", id);
command.Parameters.Add(param);
conn.Open();
NrOfAffectedRecords = command.ExecuteNonQuery();

Returning values from stored procedures

With out parameter

Stored procedure has to contain the parameter and put the value into it:
@version int out
set @version = (select version from table) -- mind the () !

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("csSaveNewQuoteVersion", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter("@version", System.Data.SqlDbType.Int);
param.Direction = System.Data.ParameterDirection.Output;
command.Parameters.Add(param);
conn.Open();
NrOfAffectedRecords = command.ExecuteNonQuery();
version = (int)param.Value;

With Return

Stored procedure has to return the value:
return @version

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("csSaveNewQuoteVersion", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter("@version", System.Data.SqlDbType.Int);
param.Direction = System.Data.ParameterDirection.ReturnValue;
command.Parameters.Add(param);
conn.Open();
command.ExecuteNonQuery();
version = (int)param.Value;

Connect to Access database

Imports System.Data.OleDb
Dim conn As OleDbConnection
Dim cmd As OleDbCommand

Connection strings

SQL server

Windows authorization

Data Source=dafehvvosql1\SQL2005;Initial Catalog=Ilnaz;Integrated Security=SSPI
Integrated security =
SSPI (= True): the current Windows account credentials are used for authentication
False: you have to provide user and password:
Integrated Security=False;User=domain\username;password=xxx

SQL server authorization

Data Source=Pentium06\SQLEXPRESS;Database=Lupasoft;UID=sa;PWD=xxx

MS Access

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;
Remark: in this case you have to use a oledb connection:
Dim conn As New OleDb.OleDbConnection(Provider)