Processing - an open source programming language

From http://processing.org/

"Processing is an open source programming language and environment for people who want to create images, animations, and interactions. Initially developed to serve as a software sketchbook and to teach fundamentals of computer programming within a visual context, Processing also has evolved into a tool for generating finished professional work. Today, there are tens of thousands of students, artists, designers, researchers, and hobbyists who use Processing for learning, prototyping, and production.."

More -->

DELETE

The delete operation is used to delete records from a table. A filter must be specified for the operation to take place (or you may end up deleting everything!) 

 

DELETE FROM Customers WHERE State = @State
Deletes all the customer records matching the parameter of State..

Note the following code sample:

Public Sub deleteFromCart(CartID As Integer)

        Dim myConn As SqlConnection = New SqlConnection(MyConnString)
        myConn.Open()
        Dim strSQL As String = "DELETE FROM XYZCart WHERE CartID = @CartID"
        Dim myComm As SqlCommand = New SqlCommand(strSQL, myConn)
        myComm.Parameters.AddWithValue("@CartID", CartID)
        myComm.ExecuteNonQuery()
        myConn.Close()

End Sub

Once again, parameters are used to pass the criteria used to delete the records and the ExecuteNonQuery method of the command object is engaged to complete the operation.

UPDATE

The update operation allows us to make changes to existing fields.

Using a filter - WHERE
By using a filter (WHERE), we can specify which fields of which records to update. Once again, there is no data retrieved from the database as part of the operation. For this reason, we do not need to use a data adapter or dataset.

UPDATE Customers SET FirstName=@FirstName,LastName=@LastName WHERE CustomerID=@CustomerID

Updates the specified fields of the a customer record matching the parameter of CustomerID.

BE CAREFUL
If you omit the filter, you will update all records, as below:

UPDATE Customers SET FirstName=@FirstName,LastName=@LastName

Updates all of the specified fields of all of the customer records.

Note the following code sample:

Public Sub updateCartQuantityPlusOne(CartID As Integer)

    Dim myConn As SqlConnection = New SqlConnection(MyConnString)
    myConn.Open()
    Dim strSQL As String = "UPDATE XYZCart SET Quantity = Quantity + 1 WHERE CartID = @CartID"
    Dim myComm As SqlCommand = New SqlCommand(strSQL, myConn)
    myComm.Parameters.AddWithValue("@CartID", CartID)
    myComm.ExecuteNonQuery()
    myConn.Close()


End Sub

Once again, parameters are used to pass the data to update the record and the ExecuteNonQuery method of the command object is engaged to complete the operation.

INSERT

The insert statement modifies the contents of the database in that we use it to create new records. By default, there is no data retrieved from the database as part of the operation. For this reason, we do not need to use a data adapter or dataset.

The INSERT SQL Statement

INSERT INTO TableName (FieldName1, FieldName2) VALUES (@FieldName1, @FieldName2)

Inserts data into the specified fields, creating a new record. Note that parameters are used to pass the values that need to be inserted into the fields.

Note the following code sample:

Public Sub insertIntoCart(ProductID As Integer, Quantity As Integer)
        
    Dim myConn As SqlConnection = New SqlConnection(MyConnString)
    myConn.Open()
    Dim strSQL As String = "INSERT INTO XYZCart (ProductID, Quantity, CustomerID) VALUES (@ProductID, @Quantity, @CustomerID)"
    Dim myComm As SqlCommand = New SqlCommand(strSQL, myConn)
    myComm.Parameters.AddWithValue("@ProductID", ProductID)
    myComm.Parameters.AddWithValue("@Quantity", ProductID)
    myComm.Parameters.AddWithValue("@CustomerID", 0)
    myComm.ExecuteNonQuery()
    myConn.Close()


End Sub

Parameters are used to pass the data for insertion. Note that they are passed to the AddWithValue method in the order they are specified in the SQL statement.

Also note, the ExecuteNonQuery method of the command object is engaged to complete the operation. This is common to the insert, update & delete operations.

Parameters (place-holder values)

SQL statements may contain ‘hard-coded’ values such as the following:

SELECT FieldName1, FieldName2 FROM TableName WHERE ColumnID = ‘1’ AND FieldName1 = ‘X’

..or they may contain a parameter(s) that is used as a place-holder value such as in the following:

SELECT FieldName1, FieldName2 FROM TableName WHERE ColumnID = @ColumnID AND FieldName1 = @FieldName

Using a parameter allows the SQL statement to work dynamically - that is, to take a current value and use it in the query. A parameter is preceded by the ‘@’ symbol.

When we define a parameter, we must provide further explanation as to where the real value will come from, at run time.

Note the following code sample:

Dim strConnectionString As String = "Provider=Microsoft.Ace.OLEDB.12.0;" & _

"Data Source=C:\FolderName\DatabaseName.accdb"

Dim MyConnection As New OleDb.OleDbConnection(strConnectionString)

MyConnection.Open()

Dim MyCommand As New OleDb.OleDbCommand("SELECT * FROM Customers WHERE CustomerID = @CustomerID AND LastName = @LastName", MyConnection)

MyCommand.Parameters.AddWithValue(“@CustomerID”, lblCustomerID.Text)

MyCommand.Parameters.AddWithValue(“@LastName”, lblLastName.Text)

Dim MyDataset As New Data.DataSet

Dim MyDataAdapter As New OleDb.OleDbDataAdapter(MyCommand)

MyDataAdapter.Fill(MyDataset, "MyQueryResults")

MyConnection.Close()

This select command includes two parameters - @CustomerID and @LastName. The next two statements inform the command object of where to find the actual values to use. The @CustomerID parameter’s value can be found in the text property of the label called lblCustomerID and the @LastName parameter value can be found in the text property of the label called lblLastName.

Notes on using parameters:
    •    Parameter names are preceded by the ‘@’ symbol
    •    You may name a parameter anything you like, however convention is to name the parameter strictly after the field name it represents.
    •    Each parameter must be passed to the command object’s Parameters.AddWithValue method
    •    Parameters must be passed to the command object’s Parameters.AddWithValue method in the same order as they are defined in the SQL statement.
    •    Spelling and case must match

SELECT

A select operation enables records to be retrieved from a database. When records are retrieved, they are brought back to the application and placed into memory. Because of this, a select operation contains objects that also allow these records to be stored.

It is important to note that the select operation does not modify the contents of the database, it simply retrieves data.

Variations on the SELECT SQL statement:

SELECT * FROM TableName

Returns all fields from all records

SELECT * FROM TableName WHERE ColumnID = ‘1’

Return all fields of the record that has a ColumnID of 1

SELECT FieldName1, FieldName2 FROM TableName

Returns the fields specified, from all records

SELECT FieldName1, FieldName2 FROM TableName WHERE ColumnID = ‘1’

Returns the fields specified, from the record that has a ColumnID of 1

SELECT FieldName1, FieldName2 FROM TableName WHERE ColumnID = ‘1’ AND FieldName1 = ‘X’

Returns the fields specified, from the record that has a ColumnID of 1 and FieldName1 has a value of X.

Another example which substitutes literal values for parameter values:

Public Function getCart() As Data.DataTable

    Dim myConn As SqlConnection = New SqlConnection(MyConnString)

    myConn.Open()

    Dim strSQL As String = "SELECT * FROM XYZCart"

    Dim myComm As SqlCommand = New SqlCommand(strSQL, myConn)

    Dim dsResults As New Data.DataSet

    Dim daDataAdapter As New     Data.SqlClient.SqlDataAdapter(myComm)

    daDataAdapter.Fill(dsResults, "CartResults")

    myConn.Close()

    Return dsResults.Tables("CartResults")

End Function

At the conclusion of the operation, any retrieved results are stored in the dataset.

Database Operations

There are four commands that may be executed against database tables:

* SELECT
* INSERT
* DELETE
* UPDATE


Commands are executed using Structured Query Language (SQL).