Microsoft Data Access Interfaces

' this
		example uses late bound objects ' (see note later in the chapter), which '
		makes it possible to use in ASP projects ' and makes it less DLL-versions-error
		prone ' there is a price to pay in terms of speed and ' typing errors (no
		IntelliSense help for the ' late-bound ActiveX objects
		
		Dim objConnection Dim objCommand Dim objADORecordset Dim strConnectString Dim
		strSQL ' ' assemble the connection string ' uses Microsoft ODBC bridge for
		OLEDB ' the user DSN ACME_DSN was created earlier in the chapter '
		strConnectString = "Provider=MSDASQL;DSN=ACME_DSN;" strConnectString =
		strConnectString & "User ID=ACME;Password=ACME;" ' ' create late-bound
		ADODB Connection object ' Set objConnection = CreateObject("ADODB.Connection")
		' ' open connection to the IBM DB2 UDB database ' objConnection.Open
		strConnectString ' ' create late-bound ADODB Command object ' Set objCommand =
		CreateObject("ADODB.Command") ' ' assign objConnection to its ActiveConnection
		property ' objCommand.ActiveConnection = objConnection ' ' assign SQL statement
		to the CommandText property ' objCommand.CommandText = strSQL ' ' set type of
		the command to adCmdText ' meaning that raw SQL statement would be executed '
		as opposed to stored procedure, for instance ' objCommand.CommandType = 1 ' '
		the execute command returns an ADO Recordset (if succeeded) ' Set
		objADORecordset = objCommand.Execute ' ' scroll the recordset ' With
		objADORecordset ' ' position the cursor on the very first row ' in the
		recordset ' .MoveFirst Do While Not objADORecordset.EOF ' ' display the
		value of the first field ' of the result set in a message box ' MsgBox
		.Fields(1).Value ' 'move to the next record ' .MoveNext Loop End With ' ' clean
		up: close all the connections ' and destroy objects; usually done '
		automatically by Visual Basic once the object ' goes out of scope
		objADORecordset.Close Set objADORecordset = Nothing Set objCommand = Nothing
		objConnection.Close Set objConnection = Nothing

 Sub Main()
		Dim objDataReader As SqlClient.SqlDataReader Dim strConn As String Dim strSQL
		As String Dim lCount As Integer = 0 ' ' assemble
		connection string using SQLOLEDB provider ' strConn = "Provider=SqlOleDb;Data
		Source=localhost;" strConn = "Initial Catalog=Acme;" strConn = "User
		ID=acme;Password=acme;" ' ' assemble SQL query ' strSQL = "SELECT * FROM
		customer" strSQL = strSQL & " WHERE CUST_NAME_S LIKE 'WI%'" ' ' write
		comments onto console ' Console.WriteLine("An example of using SQLOLEDB")
		Console.WriteLine("Provider with VB.NET")
		Console.WriteLine("-------------------") ' ' connect to the SQL Server and
		fetch the data ' Try ' ' create new Connection object ' using the connection
		string Dim objConnection As New SqlClient.SqlConnection(strConn) ' ' open
		connection to the SQL server ' objConnection.Open() ' ' create a command
		object, with the SQL statement ' and connection object as initialization params
		' Dim objCommand As New SqlClient.SqlCommand(strSQL, objConnection) ' '
		retrieve results into DataReader object ' objDataReader =
		objCommand.ExecuteReader() ' ' scroll the data reader, and reteieve ' value
		from the 4th field in the recordset ' CUST_NAME_S, and increment counter ' Do
		While objDataReader.Read Console.WriteLine
		(objDataReader.GetSqlString(4).ToString) lCount = lCount + 1 Loop ' ' output
		the final message ' Console.WriteLine("-------------------")
		Console.WriteLine("Total records:" & lCount) ' ' wait for the input '
		Console.ReadLine() ' ' close the connection within ' an appropriate scope '
		objConnection.Close() Catch e As SqlClient.SqlException
		Console.WriteLine(e.Message) Finally ' ' close DataReader '
		objDataReader.Close() End Try End Sub

using System;
		using System.Data; using System.Data.SqlClient; class SQL_Connect { static void
		Main(string[] args) { // //prepare connection string //use integrated security
		(Windows Authentication) //as opposed to UserID/Password pair // string s_conn
		= @"server=(local);" + "Integrated Security=true;" + "database=ACME"; //
		//Create SqlConnection object instance // SqlConnection connSQL = new
		SqlConnection(s_conn); try { // //open connection to the local instance //of
		your SQL Server // connSQL.Open(); string s_SQL = "SELECT * FROM customer WHERE
		CUST_NAME_S LIKE 'MA%'"; // //create command object and pass to it //connection
		object and SQL query string // SqlCommand commSQL = new
		SqlCommand(s_SQL,connSQL); // //retrieve all the records returned by the
		//query into SqlDatReader object // SqlDataReader
		sqlRead=commSQL.ExecuteReader(); Console.WriteLine("Example demonstrating .NET
		Data Provider"); Console.WriteLine("connection to Microsoft SQL Server 2000");
		// //scroll the result set and print the output //onto standard output console
		// while (sqlRead.Read()) { Console.WriteLine("{0}|{1}",
		sqlRead["CUST_NAME_S"].ToString().PadRight(30),
		sqlRead["CUST_ID_N"].ToString()); Console.WriteLine("--------------"); } }
		catch(Exception e) { // // in the case an error occurred // display a message,
		including error source // Console.WriteLine("Error occurred:" + e.Message);
		Console.WriteLine("Error Source:" + e.Source); } finally { // //Close
		connection to SQL Server// connSQL.Close(); Console.WriteLine("Connection
		closed."); // //wait for user input //to keep MS-DOS Window //
		Console.ReadLine(); } } }