Windows Systems Programming: Spring 2004

[ Home | Syllabus | Course Notes | Assignments | Search]


ADO.Net (Chapter 12)   

Comments on XML and database here http://sm.vanx.org/pipermail/vanx-list/2003-September/000050.html 

 

Free tool for accessing SQL data bases http://www.msde.biz/msdequery/download.htm 

Class Data base is available (within the ODU Firewall) at "nebula.cs.odu.edu".

All students have accounts (using same username and password as sensor).

The class data base (that we all share) is called "SW04"

Here is the result of querying for all entries used in the Comic Book data bound examples

Two interfaces

  1. System.Data.SqlClient for SQL data bases

  2. System.Data.OleDb for others which uses the ODBC drivers

 

///*************
// Sample ADO.Net program to accessSQL database
//
// Programmer: Chris Wild
// Date: April 5, 2004
//*************

using System;
using System.Data.SqlClient;
  
  
class seePubs
{
	static void Main () {
		SqlConnection conn = new SqlConnection 
			("server=nebula.cs.odu.edu;database=pubs;User id=cwild;Password=");
		try {
		    conn.Open ();
		    SqlCommand cmd = new SqlCommand ("select * from titles", conn); 
		    SqlDataReader reader = cmd.ExecuteReader (); 
		    while (reader.Read ())
		        Console.WriteLine (reader["title"]);
		}
		catch (SqlException ex) {
		    Console.WriteLine (ex.Message);
		}
		finally {
		    conn.Close ();
		}
	}
}


Connection objects have several parameters that control the nature of the connection - e.g.

SqlConnection conn = new SqlConnection
    ("server=hawkeye\wintellect;database=pubs;uid=sa;pwd=;" +
    "min pool size=10;max pool size=50;connect timeout=10");

Creating a DataBase

///*************
// Sample ADO.Net program to create SQL database
//
// Programmer: Chris Wild
// Date: April 5, 2004
//*************

using System;
using System.Data.SqlClient;
  
  
class seePubs
{
	static void Main () {
		SqlConnection conn = new SqlConnection
			("server=nebula.cs.odu.edu;User ID=cwild;password=");
		try {
		    conn.Open ();
		    SqlCommand cmd = new SqlCommand ("create database MyDatabase", conn);
		   cmd.ExecuteNonQuery ();
		}
		catch (SqlException ex) {
		    Console.WriteLine (ex.Message);
		}
		finally {
		    conn.Close ();
		}
	}
}

However we don't have permissions to create data bases - but we can create tables

	sqlCommand cmd = new SqlCommand("create table boats(manufacturer text, make text, length int)", conn);
        cmd.ExecuteNonQuery ();

And insert into them

	SqlCommand cmd = new SqlCommand();
	cmd.Connection = conn;
	cmd.CommandText = "insert into boats values ('Lagoon', '380', 38)";
	cmd.ExecuteNonQuery ();

You can also delete records, enumerate field names, and in general execute SQL commands.


Releasing resources

reader.Close (); // Does NOT close the connection!

DataReader.Close closes the DataReader, which frees the connection associated with the DataReader so that it can be used again. For example, suppose you use a command object to create a DataReader and then try to use that command object (or the connection that it encapsulates) for something else, as shown here:

SqlCommand cmd = new SqlCommand ("select * from titles", conn);
SqlDataReader reader = cmd.ExecuteReader ();
while (reader.Read ())
    Console.WriteLine (reader["title"]);

cmd.CommandText = "select * from authors";
reader = cmd.ExecuteReader ();
while (reader.Read ())
    Console.WriteLine (reader["au_lname"]);

The second call to ExecuteReader throws an InvalidOperationException. Why? Because the underlying connection is still associated with the first DataReader, which hasn’t been closed. To correct this error, close the first DataReader before reusing the connection:

SqlCommand cmd = new SqlCommand ("select * from titles", conn);
SqlDataReader reader = cmd.ExecuteReader ();
while (reader.Read ())
    Console.WriteLine (reader["title"]);

reader.Close ();cmd.CommandText = "select * from authors";
reader = cmd.ExecuteReader ();
while (reader.Read ())
    Console.WriteLine (reader["au_lname"]);

As an aside, you can configure a DataReader so that its Close method does close the underlying connection. The secret is to pass ExecuteReader a “command behavior”:

reader = cmd.ExecuteReader (CommandBehavior.CloseConnection);

If you elect to close a connection this way, be sure to position the statement that closes the DataReader in a finally block to prevent exceptions from leaking connections.


Transactions

SqlTransaction trans = null;
SqlConnection conn = new SqlConnection
    ("server=localhost;database=mybank;uid=sa;pwd=");

try {
    conn.Open ();

    // Start a local transaction
    trans = conn.BeginTransaction (IsolationLevel.Serializable);

    // Create and initialize a SqlCommand object
    SqlCommand cmd = new SqlCommand ();
    cmd.Connection = conn;
    cmd.Transaction = trans;

    // Debit $1,000 from account 1111
    cmd.CommandText = "update accounts set balance = " +
        "balance - 1000 where account_id = '1111'";
    cmd.ExecuteNonQuery ();

    // Credit $1,000 to account 2222
    cmd.CommandText = "update accounts set balance = " +
        "balance + 1000 where account_id = '2222'";
    cmd.ExecuteNonQuery ();

    // Commit the transaction (commit changes)
    trans.Commit ();
}
catch (SqlException) {
    // Abort the transaction (roll back changes)
    if (trans != null)
        trans.Rollback ();
}
finally {
    conn.Close ();
}

Parameterized Commands

    SqlCommand cmd = new SqlCommand
        ("update accounts set balance = balance + @amount " +
        "where account_id = @id", conn);
    cmd.Parameters.Add ("@amount", SqlDbType.Money);
    cmd.Parameters.Add ("@id", SqlDbType.Char);

    // Debit $1,000 from account 1111
    cmd.Parameters["@amount"].Value = -1000;
    cmd.Parameters["@id"].Value = "1111";
    cmd.ExecuteNonQuery ();

    // Credit $1,000 to account 2222
    cmd.Parameters["@amount"].Value = 1000;
    cmd.Parameters["@id"].Value = "2222";
    cmd.ExecuteNonQuery ();

Stored Procedures

Assuming you have the stored procedure

CREATE PROCEDURE proc_TransferFunds
    @Amount money,
    @From char (10),
    @To char (10)
AS
    BEGIN TRANSACTION
    UPDATE Accounts SET Balance = Balance - @Amount
        WHERE Account_ID = @From
    IF @@ROWCOUNT = 0
    BEGIN
        ROLLBACK TRANSACTION
        RETURN
    END
    UPDATE Accounts SET Balance = Balance + @Amount
        WHERE Account_ID = @To
    IF @@ROWCOUNT = 0
    BEGIN
        ROLLBACK TRANSACTION
        RETURN
    END
    COMMIT TRANSACTION
GO

You can access it using the following .net program

try {
    conn.Open ();
    SqlCommand cmd = new SqlCommand ("proc_TransferFunds", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add ("@amount", 1000);
    cmd.Parameters.Add ("@from", 1111);
    cmd.Parameters.Add ("@to", 2222);
    cmd.ExecuteNonQuery ();
}

Set vs Stream Access

 

DataSet is like an in memory data base - allows random access and updates

DataStream supports sequential access

 

DataSet ds = new DataSet ();
// TODO: Initialize the DataSet
MyDataGrid.DataSource = ds;
MyDataGrid.DataBind ();

Oftentimes, the same code can be implemented more efficiently with a Data­Reader:

SqlDataReader reader = cmd.ExecuteReader ();
MyDataGrid.DataSource = reader;
MyDataGrid.DataBind ();
Using the data set
SqlDataAdapter adapter = new SqlDataAdapter ("select * from titles",
    "server=localhost;database=pubs;uid=sa;pwd=");
DataSet ds = new DataSet ();
adapter.Fill (ds, "Titles");

asp.net example

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace db1
{
	/// <summary>
	/// Summary description for WebForm1.
	/// </summary>
	public class WebForm1 : System.Web.UI.Page
	{
		protected System.Web.UI.WebControls.DataGrid DataGrid1;
		protected System.Web.UI.WebControls.TextBox TextBox1;
		protected System.Web.UI.WebControls.TextBox TextBox2;
		protected System.Web.UI.WebControls.TextBox TextBox3;
		protected System.Web.UI.WebControls.Label Label2;
		protected System.Web.UI.WebControls.Label Label3;
		protected System.Web.UI.WebControls.Label Label4;
		protected System.Web.UI.WebControls.Button Button1;
		protected System.Web.UI.WebControls.Label Label1;
		private SqlDataAdapter adapter;
		private DataSet ds;
	
		private void Page_Load(object sender, System.EventArgs e)
		{
			// Put user code to initialize the page here
			adapter = new SqlDataAdapter
				("select * from boats order by length",
				"server=nebula.cs.odu.edu;database=SW04;User ID=cwild;password=");
			SqlCommandBuilder builder = new SqlCommandBuilder (adapter);
			ds = new DataSet ();
			adapter.Fill (ds,"boats");
			DataGrid1.DataSource = ds;
			DataGrid1.DataBind ();
		}

		

		private void Button1_Click(object sender, System.EventArgs e)
		{
			// Create a new DataRow
			DataTable table = ds.Tables["boats"];
			DataRow row = table.NewRow ();

			// Initialize the DataRow
			row["manufacturer"] = TextBox1.Text;
			row["make"] = TextBox2.Text;
			row["length"] = TextBox3.Text;
			
			// Add the DataRow to the DataTable
			table.Rows.Add (row);
			adapter.Update (table);
			ds = new DataSet ();
			adapter.Fill (ds);
			DataGrid1.DataSource = ds;
			DataGrid1.DataBind ();
		}
	}
}
 

 

 

 
 

 


Copyright chris wild 1999-2004.
For problems or questions regarding this web contact [Dr. Wild].
Last updated: April 05, 2004.