Transcript Day2.1 - Computer Science

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.SqlClient
using 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 Class
SqlConnection = conn SqlConnection();
Conn.ConnectionString = “server=localhost; database=pubs; uid=mukka; pwd=“;
Or
SqlConnection conn = “server=localhost; database=pubs; uid=mukka; pwd=“;
Other parameters for ConnectionString:
http://authors.aspalliance.com/aspxtreme/sys/data/sqlclient/SqlConnectionClassConnectionString.asp
x
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 Classes
ExecuteReader (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 others
SqlCommand 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, sum
try{
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.asp
FileStream 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 OleDataReader
try{ 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 OUTPUT
AS
SELECT @Balance =Balance From Accounts WHERE Account_ID = @ID
IF @@ROWCOUNT = 1 RETURN 0
ELSE BEGIN SET @Balance = 0 RETURN -1 END
GO
*****************
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 Class
Dataset
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.Fill
SqlDataAdapter 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
DataTable
foreach (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 DataTable
SqlDataAdapter 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
•
•
•
•
•
•
•
•
•
•
•
Video 1: https://www.youtube.com/watch?v=aoFDyt8oG0k
Video 2: https://www.youtube.com/watch?v=Mo0ECWKVVDU
Video 3:
https://www.youtube.com/watch?annotation_id=annotation_294978&feature=iv&index=3&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7&
src_vid=Mo0ECWKVVDU&v=pBCPc44CE74
Video 4:
https://www.youtube.com/watch?annotation_id=annotation_953179&feature=iv&index=4&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7&
src_vid=pBCPc44CE74&v=vDvV3KrRuMA
Video 5:
https://www.youtube.com/watch?annotation_id=annotation_907100&feature=iv&index=5&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7&
src_vid=vDvV3KrRuMA&v=Zv9i1S_dkTI
Video 6:
https://www.youtube.com/watch?annotation_id=annotation_30659&feature=iv&index=6&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7&s
rc_vid=Zv9i1S_dkTI&v=QKhHkEmv3Kw
Video 7:
https://www.youtube.com/watch?annotation_id=annotation_197748&feature=iv&index=7&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7&
src_vid=QKhHkEmv3Kw&v=stIWeAcO45Y
Video 8:
https://www.youtube.com/watch?annotation_id=annotation_38739&feature=iv&index=8&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7&s
rc_vid=stIWeAcO45Y&v=EzwBuqILfds
Video 9:
https://www.youtube.com/watch?annotation_id=annotation_789082&feature=iv&index=9&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7&
src_vid=EzwBuqILfds&v=rovj0xmM-0M
Video 10:
https://www.youtube.com/watch?annotation_id=annotation_276753&feature=iv&index=10&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7
&src_vid=rovj0xmM-0M&v=vN-CZYP2ZuY
Video 11:
https://www.youtube.com/watch?annotation_id=annotation_730981&feature=iv&index=11&list=PL6n9fhu94yhX5dzHunAI2t4kE0kOuv4D7
&src_vid=vN-CZYP2ZuY&v=TsKx5CLisSM
Links
• The C# Station ADO.NET Tutorial
• Using ADO.NET for beginners
• In Depth ASP.NET using ADO.NET