[ Home | Syllabus | Course Notes | Assignments | Search]
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
System.Data.SqlClient for SQL data bases
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 (); } } }
F1: To access a data base need a connection to it - this defines the connection string but does not open it
F2: opens the connection - can throw an exception
F3: after the connection can issue a command - this defines the command string
F4:executes the command (can throw the exception)
F5: Access the query set
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");
///************* // 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.
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.
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 (); }
F6: highest isolation level available
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 ();
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 (); }
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 DataReader:
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");
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 (); } } }