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)
page revision: 14, last edited: 28 Apr 2010 11:27