ado.net cs795. what is ado.net? database language spoken by managed applications ado.net database...
TRANSCRIPT
ADO.Net
CS795
What is ADO.Net?
• Database language spoken by managed applications
• ADO.net database accesses go through modules: data providers– SQL Server .Net provider---to interface with MS SQL
databases without any help from unmanaged providers
– OLE DB .Net provider---to interface with other databases through unmanaged OLE DB providers
• OLE DB providers provide a uniform API over a variety of databases
System.Data.SqlClientusing System.Data.SqlClient…SqlConnection conn = new SqlConnection(“server=(local); Initial Catalog = database=pubs; uid=mukka; pwd=“);Or(“server=(local); Initial Catalog = database=pubs; Trusted_Connection=Yes;“);try { conn.Open(); SqlCommand cmd = new SqlCommand(“select * from titles”, conn); SqlDataReader reader = cmd.ExecuteReader (); while (reader.Read ()) Console.Writeln (reader[“title”]);}Catch (SqlException ex) {Console.WriteLine (ex.message);}finally {conn.Close();}Note: For OLEDB, simply replace Sql in the above code by OleDb
Connections, Commands, DataReaders
The canonical usage pattern for executing database commands in ADO.Net:
1. Create a connection object encapsulating a connection string
2. Open the connection by calling Open on the connection object
3. Create a command object encapsulating both an SQL command and the connection that the command will use
4. Call a method on the command object to execute the command
5. Close the connection by calling Close on the connection object
SqlConnection ClassSqlConnection = conn SqlConnection();Conn.ConnectionString = “server=localhost; database=pubs; uid=mukka; pwd=“;OrSqlConnection conn = “server=localhost; database=pubs; uid=mukka; pwd=“;
Other parameters for ConnectionString:http://authors.aspalliance.com/aspxtreme/sys/data/sqlclient/SqlConnectionClassConnectionString.aspx
It can also be defined on web.config:• The constructor of the SqlConnection object expects one parameter: the connection
string. The connection string identifies the SQL server name, the SQL database name, and satisfies the authorization requirement by providing a user name and a password, or by specifying Trusted_Connection=true. You can specify the connection string in line within the constructor line, or you can specify it in Web.config:
<add key="ConnectionString value="server=HAW2L1800\NetSDK;Trusted_Connection=true;database=StoreDOC“/>
Open and Closing Connections
• You must first open a connection and close it at the end.
SqlConnection conn = new SqlConnection(“server=localhost; database=pubs; uid=mukka; pwd=“);try{ conn.Open(); ….}catch (SqlConnection ex){ …..}finally { conn.Close();}
Command ClassesExecuteReader (for read)
ExecuteNonQuery (for updates)
SqlConnection conn = new SqlConnection (“server=localhost; datbase=pubs; uid=mukka; pwd=“);
try {
conn.Open();
SqlCommand cmd = new SqlCommand ();
cmd.CommandText= “delete from title where title_id = “xxxx”;
cmd.Connection = conn;
cmd.ExecuteNonQuery ();
}
catch (SqlException ex) {
….}
finally { conn.Close();}
ExecuteNonQuery
• To execute operations where database is changed• Example: insert, update, delete, create database, create
table, etc.• Insert, update, delete: Returns number of rows affected
by the operation• Returns -1 for othersSqlCommand cmd = new SqlCommand (“insert into titles (title_id, title, type, pubdate)”+ “values (‘CS150’,’C++ Programming’,” + “ ‘computer science’, ‘May 2006’), conn);cmd.ExecuteNonQuery();
ExecuteScalar
• Returns the 1st row of the 1st column in the result• Used for commands such as: count, avg, min,
max, sumtry{ conn.Open(); SqlCommand cmd = new SqlCommand (“select max (advance) from title”, conn); decimal amount = (decimal) cmd.ExecuteScalar (); Console.WriteLine (“ExecuteScalar returned (0:c)”, amount);}Catch (SqlException ex} {Console.Writeln (ex.Message);}finally {conn.Close();}
ExecuteScalar (cont.)• To retrieve BLOBs (Binary large objects) from databases• http://builder.com.com/5100-6371-5766889.html• http://support.microsoft.com/default.aspx?scid=kb;en-us;309158• http://www.codeproject.com/cs/database/images2db.asp• http://www.codeproject.com/useritems/Blobfield.aspFileStream stream new FileStream (“Logo.jpg”, FileMode.Open);byte[] blob new byte [stream.Length];stream.Read (blob, 0, (int) stream.Length);stream.Close();SqlConnection con = new …try{ conn.Open(); SqlCommand cmd = new SqlCommand (“insert into pub_info (pub_id, logo) values (‘9937’, @logo)”, conn);cmd.Parameters.Add (“@logo”, blob);cmd.ExecuteNonQuery ();} catch …finally …
ExecuteScalar (Cont.)
• To validate a user name and password (page 506-507, Jeff Prosie book)
Try { conn.Open();
StringBuilder builder = new StringBuilder ();
builder.Append (“select count (*) from users where username =
….
int count = (int) command.ExecuteScalar ();
return (count > 0);
}
ExecuteReader Method
• To perform database queries• Returns a DataReader object:
SqlDataReader or OleDataReadertry{ conn.Open(); SqlCommand cmd = new SqlCommand(“select * from titles”, conn);SqlDataReader reader = cmd.ExecuteReader();While (reader.Read()) Console.WriteLine (reader[“title”]); }******************try {conn.Open(); SqlCommand cmd = new SqlCommand(“select * from titles”, conn);SqlDataReader reader = cmd.ExecuteReader();for (int i=0; i <reader.FieldCount; i++) Console.WriteLine (reader.GetName[i])); }reader.Close();
Transactions
• Ex: transfer funds from one account (say 1234) to another account (say 9876).
SqlTransaction trans = null;
SqlConnection conn = new SqlConnection (“server=localhost; database=mybank; uid=mukka; pwd=“);
try{conn.Open();
trans = conn.BeginTransaction (IsolationLevel.Serializable);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction=trans;
cmd.CommandText = “update accounts set balance = balance-1500 where account_id = ‘1234’ “;
cmd.ExecuteNonQuery();
cmd.CommandText = “update accounts set balance = balance+1500 where account_id = ‘9876’ “;
cmd.ExecuteNonQuery();
Trans.Commit();
}
Alternate to Commit is Rollback.
Parameterized Commands
• When commands are the same but the parameters are different Try{ conn.Open();
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);
cmd.Parameters[“@amount”].Value = -1500;
cmd.Parameters[“@id”].Value = “1234”;
cmd.ExecuteNonQuery ();
cmd.Parameters[“@amount”].Value = 1500;
cmd.Parameters[“@id”].Value = “9867”;
cmd.ExecuteNonQuery ();
}
Stored Procedures• User defined command added to a database• Execute faster because they are already in compiled form.
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
How does an application call the stored procedure?
SqlConnection conn = new SqlConnection (“server=localhost; database=mybank; uid=mukka; pwd=“);
try{conn.Open();
SqlCommand cmd = new SqlCommand (“proc_TransferFunds”, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add (“@amount”, 1500);
cmd.Parameters.Add (“@from”, ‘1234’);
cmd.Parameters.Add (“@to”, ‘9876’);
cmd.ExecuteNonQuery ();
}
Catch (SqlException ex) { ….}
finally {conn.Close(); }
Example where it returns a value
CREATE PROCEDURE proc_GetBalance@ID char(10), @Balance money OUTPUTAS SELECT @Balance =Balance From Accounts WHERE Account_ID = @ID IF @@ROWCOUNT = 1 RETURN 0ELSE BEGIN SET @Balance = 0 RETURN -1 ENDGO*****************SqlConnection conn = new SqlConnection (“server=localhost; database=mybank; uid=mukka; pwd=“);try{conn.Open(); SqlCommand cmd = new SqlCommand (“proc_GetBalance”, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add (“@id”, ‘1234’); SqlParameter bal = cmd.Parameters.Add (“@balance”, SqlDbType.Money); bal.Direction = ParameterDirection.Output; SqlParameter ret = cmd.Parameters.Add (“@return”, SqlDbType.Int); ret.Direction = ParameterDirection.ReturnValue; cmd.ExecuteNonQuery (); int retval = (int) ret.value; decimal balance = (decimal) bal.Value;}catch (SqlException ex) { ….}finally {conn.Close(); }
DataSet ClassDataset Database
DataTable Table
DataRow Records in a DataTable
DataColumn Fields in a DataTable
DataSet.Tables
DataTabe.Rows
DataTable.Columns
UniuqeConstraint Add a contsraint to a column
DataRelation Relationship between two tables
Dataset (cont.)
• Datasets are ideal for retrieving results from database queries and storing them in memory.
• In addition, this data may be modified and propagated back to the database.
• It can also support, random access to the data (unlike DataReader)
• Great for caching, especially in web applications.
DataSets vs. DataReaders
• If the application simply queries a database and reads through the records one at a time until it finds the record it is looking for, DataReader is the right tool
• If the application requires all results from a query, say to display in a table, and have ability to iterate back and forth through the result set, DataSet is a good alternate.
DataAdapter
• DataSets don’t interact with databases directly;
• Instead, they interact through DataAdapters
• Purpose: To perform database queries and create DataTables containing the query results; also, to write the modified DataTables into databases
• Fill and Update
DataAdapter.FillSqlDataAdapter adapter = new SqldataAdapter (“select * from titles”,
“server=localhost; database=pubs; uid=mukka; pwd=“);
DataSet ds = new dataSet ();
adapter.Fill (ds, “Titles”);
What does Fill do?
(1) Open a connection to the pubs database using adapter.
(2) Performs a query on the pubs database using the query string passed to adapter.
(3) Creates a DataTable named “Titles” in ds.
(4) Initializes DataTable with a schema that matches that of the “Titles” table in the database.
(5) Retrieves records produced by the query and writes them to the DataTable
(6) Closes the connection to the database
DataTableforeach (DataTable table in ds.Tables) Console.WriteLine (table.TableName);
DataTable table = ds.Tables[0];
foreach (DataRow row in table.Rows) Console.WriteLine(row[0]);
DataTable table = ds.Tables[0];
foreach (DataRow row in table.Rows) Console.WriteLine(row[“account_id”]);
DataTable table = ds.Tables[0];
foreach (DataColumn col in table.Columns)
Console.WriteLine(“Name={0}, Type ={1}”, col.ColumnName, col.DataType);
Insert a record into DataTableSqlDataAdapter adapter = new SqldataAdapter (“select * from titles”,
“server=localhost; database=pubs; uid=mukka; pwd=“);
DataSet ds = new dataSet ();
adapter.Fill (ds. “Titles”);
DataTable table ds.Tables[“Titles”];
DataRow row = table.NewRow ();
row[“title_id”] = “CS795”;
row[“title”] = “.Net Security”;
row[“price”]=“70.99”;
Table.Rows.Add (row);
Propagating Changes back to Database
SqlDataAdapter adapter = new SqldataAdapter (“select * from titles”,
“server=localhost; database=pubs; uid=mukka; pwd=“);
SqlCommandBuilder builder = new SqlCommandBuilder (adapter);
DataSet ds = new dataSet ();
adapter.Fill (ds. “Titles”);
DataTable table ds.Tables[“Titles”];
DataRow row = table.NewRow ();
row[“title_id”] = “CS795”;
row[“title”] = “.Net Security”;
row[“price”]=“70.99”;
table.Rows.Add (row);
adapter.Update (table);
(only writes the ones that were changed)
Links
• The C# Station ADO.NET Tutorial
• Using ADO.NET for beginners
• In Depth ASP.NET using ADO.NET
Links
• The C# Station ADO.NET Tutorial
• Using ADO.NET for beginners
• In Depth ASP.NET using ADO.NET