SQL Wednesday

Boot up

IIS

Starts Automatically

SQLEXPRESS

Server Starts Manually

Visual Basic 2008

Associations made to SQLEXPRESS in Server Explorer

Initial Development: Code Behind Experiment

Experimenting with the VB concept of code behind

Using C# in an aspx.cs file referenced in the head of the client document

Testing out connections between forms, elements and IDs in C#

SQL TEST #1

Work with Direct Data Access, requires three steps

1. Create Connection object
   
2. Create Command object
3. Create Data Reader object

----------------

CONNECTION OBJECT

Step One:

Different ways to create a connection object.  Need to know:

1. Server Name, aka Data Source (e.g. localhost\SQLEXPRESS)
2. Database Name aka Initial Catalog (e.g. Pubs)
3. Security Type aka Integrated Security (e.g. SSPI)

In C# be sure to import the proper namespaces for the Data Provider you're using. It could be SQL, OLE DB, Oracle or ODBC. I'm using SQL, so the namespaces to import are:

The Data Provider Classes for SQL Server Data Provider are

• Connections - SqlConnection
• Command - SqlCommand
• DataReader - SqlDataReader
• DataAdapter - SqlDataAdapter

And the namespaces to import are

• using System.Data;
• using System.Data.SqlClient; 
  

Once these namespaces are imported, you can create an object of type SqlConnection

SqlConnection myConnection = new SqlConnection();

myConnection.ConnectionString = "Data Source=localhost\SQLEXPRESS;" + "Initial Catalog=Pubs;Integrated Security=SSPI";

This code would normally go in the code behind aka the aspx.cs file of the operative file. Since many different operative files will likely use the same connection string, a better approach is to centralize the connection string for easy universal reference. This could be done as a class member variable. Even better, put it in a configuration file.  

The configuration file is an xml file called web.config.  It sits at the root of the project and can be accesed globally. Thus it is a good place for the connection string info.

I'm placing it in the section of the web.config file.

Retrieve the connection string by name using OO notation after adding namespace System.Web.Configuration:

string connectionString =  WebConfigurationManager.ConnectionStrings["Pubs"].ConnectionString;

-----------------

COMMAND OBJECT

To actually retrieve data requires

• A SQL statement that selects the information you want
• A Command object that executes the SQL statement
• A DataReader or DataSet object to access the retrieved records

Here's a method

SqlCommand myCommand = new SqlCommand();

myCommand.Connection = myConnection;

myCommand.CommandText = "SELECT * FROM Authors ORDER BY au_lname ";

--------------

DATA READER

to create a DataReader, you use the ExecuteReader() method of the command object:

SqlDataReader myReader;

myReader = myCommand.ExecuteReader();

move forward one line at a time using the myReader.Read(); method

with each row, you can add to an object this way:

1stNames.Items.Add(myReader["au_lname"] + ", " + myReader["au_fname"]);

to move to the next row, use the Read() method again. When finished, close the DataReader and Connection

myReader.Close();

myConnection.Close();

Done for now.