NET Data Access and Manipulation What is ADO

. NET Data Access and Manipulation

§ What is ADO. NET? § Disconnected vs. connected data access models § ADO. NET Architecture § ADO. NET Core Objects § Steps of Data Access § Advanced Techniques and UI Tools 2

§ ADO. NET is an object-oriented set of libraries that allows you to interact with data sources. Commonly, the data source is a database, but it could also be a text file, an Excel spreadsheet, or an XML file § A data-access technology that enables applications to connect to data stores and manipulate data contained in them in various ways § ADO. NET is a data access technology from Microsoft. Net Framework , which provides communication between relational and non-relational systems through a common set of components 3

§ An object oriented framework that allows you to interact with database systems 4

§ Support disconnected data architecture, § Tight integration with XML, § Common data representation § Ability to combine data from multiple and varied data sources § Optimized facilities for interacting with a database 5

6

§ Core namespace: System. Data §. NET Framework data providers: Data Provider Namespace Data Source Description SQL Server System. Data. Sql. Cl ient For interacting with Microsoft SQL Server. OLE DB System. Data. Ole. Db Data Sources that expose an Ole. Db interface, i. e. Access or Excel. ODBC System. Data. Odbc Data Sources with an ODBC interface. Normally older data bases. Oracle System. Data. Oracl e. Client For Oracle Databases. 7

Object Description Connection Establishes a connection to a specific data source. (Base class: Db. Connection) Command Executes a command against a data source. Exposes Parameters and can execute within the scope of a Transaction from a Connection. (The base class: Db. Command) Data. Reader Reads a forward-only, read-only stream of data from a data source. (Base class: Db. Data. Reader) Data. Adapter Populates a Data. Set and resolves updates with the data source. (Base class: Db. Data. Adapter) Data. Table Has a collection of Data. Rows and Data. Columns representing table data, used in disconnected model Data. Set Represents a cache of data. Consists of a set of Data. Tables and relations among them 8

9

10

Connected Disconnected Database Resources - + Network Traffic - + Memory Usage + - Data Access - + 11

12

§ Create connection § Create command (select-insert-update-delete) § Open connection § If SELECT -> use a Data. Reader to fetch data § If UPDATE, DELETE, INSERT -> use command object’s methods § Close connection 13

§ Creating Sql Connection § Sql. Connection conn = new Sql. Connection( "Data Source=(local); Initial Catalog=Northwind; Integrated Security=SSPI"); Connection String Parameter Name Description Data Source Identifies the server. Could be local machine, machine domain name, or IP Address. Initial Catalog Database name. Integrated Security Set to SSPI (Security Support Provider Interface)to make connection with user’s Windows login User ID Name of user configured in SQL Server. Password matching SQL Server User ID. § Sql. Connection conn = new Sql. Connection("Data Source=Database. Server; Initial Catalog=Northwind; User ID=Your. User. ID; Password=Your. Password"); 14

§ A Sql. Command object allows you to specify what type of interaction you want to perform with a database. For example, you can do select, insert, modify, and delete commands on rows of data in a database table. The Sql. Command object can be used to support disconnected data management scenarios § Sql. Command cmd = new Sql. Command("select Category. Name from Categories", conn); § Querying Data § When using a SQL select command, you retrieve a data set for viewing. To accomplish this with a Sql. Command object, you would use the Execute. Reader method, which returns a Sql. Data. Reader object. § Sql. Data. Reader rdr = cmd. Execute. Reader(); 15

§ Inserting Data § To insert data into a database, use the Execute. Non. Query method of the Sql. Command object. string insert. String = @" insert into Categories (Category. Name, Description) values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')"; // 1. Instantiate a new command with a query and connection Sql. Command cmd = new Sql. Command(insert. String, conn); // 2. Call Execute. Non. Query to send command cmd. Execute. Non. Query(); 16

§ Updating Data § The Execute. Non. Query method is also used for updating data § // prepare command string update. String = @" update Categories set Category. Name = 'Other' where Category. Name = 'Miscellaneous'"; // 1. Instantiate a new command with command text only Sql. Command cmd = new Sql. Command(update. String); // 2. Set the Connection property cmd. Connection = conn; // 3. Call Execute. Non. Query to send command cmd. Execute. Non. Query(); 17

§ Getting Single values § Sometimes all you need from a database is a single value, which could be a count, sum, average, or other aggregated value from a data set. § The following example shows how to do this with the execute. Scalar method § // 1. Instantiate a new command § Sql. Command cmd = new Sql. Command("select count(*) from Categories", conn); § § // 2. Call Execute. Non. Query to send command § int count = (int)cmd. Execute. Scalar(); 18

§ FOR XML AUTO — Builds a tree based on the tables in the FROM clause § FOR XML RAW — Maps result set rows to elements with columns mapped to attributes § FOR XML EXPLICIT — Requires that you specify the shape of the XML tree to be returned static void Execute. Xml. Reader() { string select = "SELECT Contact. Name, Company. Name " +"FROM Customers FOR XML AUTO"; Sql. Connection conn = new Sql. Connection(Get. Database. Connection()); conn. Open(); Sql. Command cmd = new Sql. Command(select, conn); Xml. Reader xr = cmd. Execute. Xml. Reader(); xr. Read(); string data; do { data = xr. Read. Outer. Xml(); if (!string. Is. Null. Or. Empty(data)) Console. Write. Line(data); } while (!string. Is. Null. Or. Empty(data)); 19

§ A Sql. Data. Reader is a type that is good for reading data in the most efficient manner possible. You can *not* use it for writing data. § You can read from Sql. Data. Reader objects in a forward-only sequential manner. Once you’ve read some data, you must save it because you will not be able to go back and read it again. § Creating a Sql. Data. Reader Object § Sql. Data. Reader rdr = cmd. Execute. Reader(); § Reading Data while (rdr. Read()) { // get the results of each column string contact = (string)rdr["Contact. Name"]; string company = (string)rdr["Company. Name"]; string city = (string)rdr["City"]; // print out the results Console. Write("{0, -25}", contact); Console. Write("{0, -20}", city); Console. Write("{0, -25}", company); Console. Write. Line(); § } 20

Sql. Command command = new Sql. Command(query. String, connection); try { connection. Open(); Sql. Data. Reader reader = command. Execute. Reader(); while (reader. Read()) { Console. Write. Line("t{0}t{1}“, reader[0], reader[1]); } reader. Close(); connection. close(); } catch (Exception ex) { Console. Write. Line(ex. Message); } } EXAMPLE static void Main() { string connection. String = Properties. Settings. Default. conn. Str; string query. String = "SELECT Category. ID, Category. Name FROM dbo. Categories; "; Sql. Connection connection = new Sql. Connection(connection. String); 21

§ A stored procedures is a pre-defined, reusable routine that is stored in a database. § SQL Server compiles stored procedures, which makes them more efficient to use. § Therefore, rather than dynamically building queries in your code, you can take advantage of the reuse and performance benefits of stored procedures. § let the Sql. Command object know which stored procedure to execute and tell the Sql. Command object that it is executing a stored procedure. These two steps are shown below: // 1. create a command object identifying the stored procedure Sql. Command cmd = new Sql. Command("Ten Most Expensive Products", conn); // 2. set the command object so it knows to execute a stored procedure cmd. Command. Type = Command. Type. Stored. Procedure; 22

§ Type the following in SQL Query Analyzer: § Start->Programs->Microsoft SQL Server->Query Analyzer § CREATE PROCEDURE Emp. Proc(@ Employee. Id INTEGER, @First. Name NCHAR(25)) AS SET NOCOUNT ON UPDATE Employees SET First. Name=@First. Name WHERE Employee. Id=@Employee. Id C# Program: Sql. Connection con=new Sql. Connection(“---”); con. open(); Sql. Command scmd=Command. Type. Stored. Procedure; Scmd. Prameters. Add(new Sql. Parameter(“@Employee. Id”, Sql. Db. Type. Int, 0, ”Employee. Id”)); Scmd. Prameters. Add(new Sql. Parameter(“@First. Name”, Sql. Db. Type. NChar, 50, ”First. Name”)); Scmd. Updated. Row. Source=Updated. Row. Source. None; Scmd. Parameters[0]. Value=999; Scmd. Parameters[1]. Value=“Raja”; Scmd. Execute. Non. Query(); String s=scmd. Parameters[1]. Value. To. String(); Messagebox. Show(s); 23

24

§ A Data. Set is an in-memory data store that can hold numerous tables. § Data. Sets only hold data and do not interact with a data source. § This is a collection of Data. Tables §. We use the Data. Set type to store many Data. Tables in a single collection. § Conceptually, the Data. Set acts as a set of Data. Table instances. § The Sql. Data. Adapter that manages connections with the data source and gives us disconnected behavior 25

§ Creating a Data. Set Object § Data. Set ds. Customers = new Data. Set(); § The Data. Set constructor doesn’t require parameters. However there is one overload that accepts a string for the name of the Data. Set, which is used if you were to serialize the data to XML. § Creating A Sql. Data. Adapter § The Sql. Data. Adapter holds the SQL commands and connection object for reading and writing data § Sql. Data. Adapter da. Customers = new Sql. Data. Adapter("select Customer. ID, Company. Name from Customers", conn); § The SQL select statement specifies what data will be read into a Data. Set. § The connection object, conn, should have already been instantiated, but not opened. It is the Sql. Data. Adapter’s responsibility to open and close the connection during Fill and Update method calls. 26

§ Sql. Command. Builder: § There are two ways to add insert, update, and delete commands: via Sql. Data. Adapter properties or with a Sql. Command. Builder. § Here’s how to add Sql. Command. Builder commands to the Sql. Data. Adapter with the § Sql. Command. Builder cmd. Bldr = new Sql. Command. Builder(da. Customers); § This tells the Sql. Command. Builder what Sql. Data. Adapter to add commands to § Filling the Data. Set § Once you have a Data. Set and Sql. Data. Adapter instances, you need to fill the Data. Set. § da. Customers. Fill(ds. Customers, "Customers"); § The Fill method, in the code above, takes two parameters: a Data. Set and a table name. The Data. Set must be instantiated before trying to fill it with data. The second parameter is the name of the table that will be created in the Data. Set. You can name the table anything you want. Its purpose is so you can identify the table with a meaningful name later on. Typically, I’ll give it the same name as the database table. 27

Sql. Data. Adapter da = new Sql. Data. Adapter(); Data. Set ds = new Data. Set(); Sql. Command. Builder cmd. Builder = new Sql. Command. Builder(da); da. Fill(ds); INITIAL CODE Data. Row dr = ds. Tables[0]. Rows[0]; dr. Delete(); da. Update. Command = builder. Get. Update. Command(); da. Update(ds); DELETE Data. Row dr = ds. Tables[0]. Rows[0]; dr["Customer. Name"] = "John"; da. Update. Command = builder. Get. Update. Command(); da. Update(ds); UPDATE Data. Row dr = ds. Tables[0]. New. Row(); dr["Customer. Name"] = "John"; dr["Customer. Sur. Name"] = "Smith"; ds. Tables[0]. Rows. Add(dr); da. Update. Command = builder. Get. Update. Command(); da. Update(ds); INSERT 28

using System; using System. Data. Sql. Client; EXAMPLE namespace Sample. Class { class Program { static void Main(string[] args) { string conn. Str = Properties. Settings. Default. conn. Str; Sql. Connection conn = new Sql. Connection(conn. Str); string query. String = "SELECT * from titles; "; Sql. Data. Adapter da = new Sql. Data. Adapter(query. String, conn); Data. Set ds = new Data. Set(); da. fill(ds); // Work on the data in memory using // the Data. Set (ds) object } } } 29

§ The type of functionality application requires should be considered § Use a dataset to: § Cache data locally in your application so that you can manipulate it § Remote data between tiers or from an XML Web service § Interact with data dynamically such as binding to a Windows Forms control or combining and relating data from multiple sources § Perform extensive processing on data without requiring an open connection to the data source, which frees the connection to be used by other clients § If readonly data is needed use Data. Reader to boost performance 30

§ Don’t create a new connection string for every code connecting to DB § Use app. config file to keep your connection strings through the application scope 1. 2. 3. § Right click on project and select properties Select settings from the left tabbed menu add the connection string to the table and save project, Name field is the name of the string to access at runtime Accessing settings at runtime: string conn. Str = Properties. Settings. Default. conn. Str; n You can keep any other variable to reach at runtime using this technique 31

§ It provide the way to link the contents of a control with a data source. § Simple Binding: Controls that contain one value, such as label or text box § Complex Binding: Controls populated with rows of data, such as list box, data grid § Simple Data Binding: § Data. Binding. Add(Control Propertey, Data Source, Data Member); § Program: Textbox 1. Data. Bindings. Add(“Text”, ds, ”Employes. Employee. ID”); Textbox 2. Data. Bindings. Add(“Text”, ds, ”Employes. Name”); § Complex Data Binding: § Two Properties: Data. Source, Display Member Sda. Fill(ds, ”Employees”); Data. Table dt=ds. Tables[0]; List. Box. Data. Source=ds; Listbox. Display. Member=“Employees. First. Name”; 32

using System; using System. Drawing; using System. Collections; using System. Component. Model; using System. Windows. Forms; using System. Data. Sql. Client; public class Form 1 : System. Windows. Form { private System. Windows. Forms. Text. Box text. Box 1; private System. Windows. Forms. Text. Box text. Box 2; private System. Windows. Forms. Button button. Back; private System. Windows. Forms. Button button. Next; private System. Data. Set data. Set 1; private System. Component. Model. Container components = null; private Binding. Manager. Base b. Mgr; 33

public Form 1() { Initialize. Component(); } private void Initialize. Component() { this. text. Box 1 = new System. Windows. Forms. Text. Box(); this. text. Box 2 = new System. Windows. Forms. Text. Box(); this. button. Back = new System. Windows. Forms. Button(); this. button. Next = new System. Windows. Forms. Button(); this. data. Set 1 = new System. Data. Set(); ((System. Component. Model. ISupport. Initialize)(this. data. Set 1)). Begin. Init(); this. Suspend. Layout(); this. text. Box 1. Location = new System. Drawing. Point(8, 8); this. text. Box 1. Name = "text. Box 1"; this. text. Box 1. Size = new System. Drawing. Size(160, 20); this. text. Box 1. Tab. Index = 0; 34

this. text. Box 1. Text = "text. Box 1"; this. text. Box 2. Location = new System. Drawing. Point(8, 40); this. text. Box 2. Name = "text. Box 2"; this. text. Box 2. Size = new System. Drawing. Size(160, 20); this. text. Box 2. Tab. Index = 1; this. text. Box 2. Text = "text. Box 2"; this. button. Back. Location = new System. Drawing. Point(24, 80); this. button. Back. Name = "button. Back"; this. button. Back. Size = new System. Drawing. Size(56, 23); this. button. Back. Tab. Index = 2; this. button. Back. Text = "<< Back"; this. button. Back. Click += new System. Event. Handler(this. button. Back_Click); this. button. Next. Location = new System. Drawing. Point(96, 80); this. button. Next. Name = "button. Next"; this. button. Next. Size = new System. Drawing. Size(56, 23); 35

this. button. Next. Tab. Index = 3; this. button. Next. Text = "Next>>"; this. button. Next. Click += new System. Event. Handler(this. button. Next_Click); this. data. Set 1. Data. Set. Name = "New. Data. Set"; US"); this. data. Set 1. Locale = new System. Globalization. Culture. Info("enthis. Auto. Scale. Base. Size = new System. Drawing. Size(5, 13); this. Client. Size = new System. Drawing. Size(176, 108); this. Controls. Add(this. button. Next); this. Controls. Add(this. button. Back); this. Controls. Add(this. text. Box 2); this. Controls. Add(this. text. Box 1); this. Name = "Form 1"; this. Text = "Form 1"; this. Load += new System. Event. Handler(this. Form 1_Load); 36

((System. Component. Model. ISupport. Initialize)(this. data. Set 1)). End. Init(); this. Resume. Layout(false); } static void Main() { Application. Run(new Form 1()); } private void Form 1_Load(object sender, System. Event. Args e) { string conn. String = "server=(local)\SQLEXPRESS; database=My. Database; Integrated Security=SSPI"; string sql = @"select * from employee "; Sql. Connection conn = new Sql. Connection(conn. String); Sql. Data. Adapter da = new Sql. Data. Adapter(sql, conn); da. Fill(data. Set 1, "employee"); text. Box 1. Data. Bindings. Add("text", data. Set 1, "employee. firstname"); 37

text. Box 2. Data. Bindings. Add("text", data. Set 1, "employee. lastname"); b. Mgr = this. Binding. Context[data. Set 1, "employee"]; } private void button. Next_Click(object sender, System. Event. Args e) { b. Mgr. Position += 1; } private void button. Back_Click(object sender, System. Event. Args e) { b. Mgr. Position -= 1; } } 38