ADO.NET Fundamentals

1. ADO.NET Fundamentals

2. Browsing And Modifying Databases • Using server explorer tab (behind toolbox), right click the Data Connections node and choose Add Connection • If Choose Data Source window appears, select Microsoft SQl Server and then click continue. • If SQL Server Full version enter localhost, otherwise localhost\\SQLEXPRESS • Test the connection to verify that it is ok. • Select or Enter a Database name from the drop down list. • If you want to see more than one database in VS you’ll need to add more than one data connection. • You can now focus on table and via right clicking and using Show Table Data item you can edit, add some records. • You can add tables also without the need of SQl Management Studio Express.

3. Running Queries in Visual Studio • Right-click you connection, and choose New Query. • Choose the table (tables) you want to use in your queriesfrom the add table dialog box. • Then you will face with a query builder window • You can create your query by adding check marks • You can edit SQL by hand. • This method is better because you can type anything you are not restricted with the tavbles you have choosen. • Using Query Designer-> Execute SQL from the menu or clicking exclamation button.

4. ADO.NET Basics • ADO.NET relies on the functionality in a small set of core classes. • Those that are used to contain and manage data • DataSet, DataTable, DataRow, and DataRelation. • No matter the what data source you are using. These classes are generic. • Those that are used to connect to a specific data source • Connection, Command, and DataReader. • Each set of data interaction classes is called an ADO.NET data provider. For example for mysql database you must install a third party provider from mysql.com (Sun’s) website. • .NET includes the following four providers • SQL Server provider: Provides optimized access to a SQL Server database (version 7.0 or • later) • OLE DB provider: Provides access to any data source that has an OLE DB driver • Oracle provider: Provides optimized access to an Oracle database (version 8i or later) • ODBC provider: Provides access to any data source that has an ODBC (Open DatabaseConnectivity) driver • In addition, third-party developers and database vendors have released their ownADO.NET providers, which follow the same conventions and can be used in the same way asthose that are included with the .NET Framework.

5. ADO.NET Layers

6. ADO.NET NameSpaces

7. The Data Provider Classes Remember, though the underlying technical details differ, the classes are almost identical. The only real differences are as follows: • The names of the Connection, Command, DataReader, and DataAdapter classes are different in order to help you distinguish them. • The connection string (the information you use to connect to the database) differs depending on what data source you’re using, where it’s located, and what type of security you’re using. • Occasionally, a provider may choose to add features, such as methods for specific features or classes to represent specific data types. For example, the SQL Server Command class includes a method for executing XML queries that aren’t part of the SQL standard. In this chapter, you’ll focus on the standard functionality, which is shared by all providers and used for the majority of data access operations.

8. Direct Data Access • The easiest way to interact with a database is to use direct data access. This means you’re incharge of building a SQL command and executing it. • To query information with simple data access, follow these steps: • 1. Create Connection, Command, and DataReader objects. • 2. Use the DataReader to retrieve information from the database, and display it in acontrol on a web form. • 3. Close your connection. • 4. Send the page to the user. At this point, the information your user sees and the informationin the database no longer have any connection, and all the ADO.NET objects have been destroyed. • To add or update information, follow these steps: • 1. Create new Connection and Command objects. • 2. Execute the Command (with the appropriate SQL statement).

9. Direct Data Access with ADO.NET

10. Steps of Running with Direct Data Access • Include ADO.NET namespaces • Using System.Data • Using System.Data.SqlCLient • 1st step is to connect • For SQL Express- The Sql server Connection • SqlConnection cnn= new SqlConnection(); With Windows Authentication: • cnn.ConnectionString=@“Data Source=localhost\SQLEXPRESS;Initial Catalog=DBName;Integrated Security=SSPI”; With Sql Server Authentication • cnn.ConnectionString=@“ Data Source=localhost\SQLEXPRESS;Initial Catalog=bloodbank;User ID=bloodbank;Password=Hello”; • For MDF file: • SqlConnection cnn= new SqlConnection(); • cnn.ConnectionString=@"Data Source=localhost\SQLEXPRESS;AttachDbFilename=|DataDirectory|\AddressBook.mdf;Integrated Security=True;"; // |DataDirectory| token already refers to the App_Datafolder

11. Storing the Connection String • You can also create a Connection object and supply the connection string in one step by using a dedicated constructor: • SqlConnection myConnection = new SqlConnection(connectionString); • // myConnection.ConnectionString is now set to connectionString. • You don’t need to hard-code a connection string. The <connectionStrings> section of theweb.configfile is a handy place to store your connection strings. Here’s an example: • <configuration> • <connectionStrings> • <add name="Pubs" connectionString= • "Data Source=localhost;Initial Catalog=Pubs;Integrated Security=SSPI"/> • </connectionStrings> • ... • </configuration> • You can then retrieve your connection string by name. First, import theSystem.Web.Configurationnamespace. Then, you can use code like this: string connectionString = WebConfigurationManager.ConnectionStrings["Pubs"].ConnectionString;

12. Storing the Connection String • This approach helps to ensure all your web pages are using the same connection string. Italso makes it easy for you to change the connection string for an application, without needingto edit the code in multiple pages. The examples in this chapter all store their connectionstrings in the web.config file in this way.

13. Making The Connection • Before you can perform any database operations, you need to explicitly open your connection: • myConnection.Open();

14. Testing Your Connection • // Define the ADO.NET Connection object. • string connectionString = @"Data Source=localhost\SQLEXPRESS;AttachDbFilename=|DataDirectory|\AddressBook.mdf;Integrated Security=True;"; • SqlConnection myConnection = new SqlConnection(connectionString); • try • { • // Try to open the connection. • myConnection.Open(); • lblInfo.Text = "<b>Server Version:</b> " + myConnection.ServerVersion; • lblInfo.Text+= "<br /><b>Connection Is:</b> " + myConnection.State.ToString(); • // connection state could be examined to check the state of connection also. • } • catch (Exception err) • { • // Handle an error by displaying the information. • lblInfo.Text = "Error reading the database. "; • lblInfo.Text += err.Message; • } • finally • { • // Either way, make sure the connection is properly closed. • // (Even if the connection wasn't opened successfully, • // calling Close() won't cause an error.) • myConnection.Close(); • lblInfo.Text += "<br /><b>Now Connection Is:</b> "; • lblInfo.Text += myConnection.State.ToString(); • }

15. Running Commands • To run queries we can use Command Objects • IF we are running select queries, to access the retrieved records we need datareader or dataset object. • Beside being several overload methods here is a sample Command preparation. • SqlCommand cmd= new SqlCommand(); • cmd.Connection=cnn; // the connection object • Cmd.CommandText=“select * from tbl”; • To run select queries: • cmd.ExecuteReader(); Or • cmd.ExecuteScalar(); • To run insert,delete,update,create queries.... • Cmd.ExecuteNonQuery();

16. Select Queries – Retrieve Data • To retrieve results quickly, DataReader is extremely simple. It gives chance to reach the results with fast-forward-only read-only access. • SqlDataReader dr=cmd.ExecuteReader(); • Ps. DataReader provides better performance than the Dataset. • Once you create the DataReader, you retrieve a single row at a time using the Read() method. • dr.Read(); • You can then access the records’ fields by using their field names like: • dr[“fname”] ; // returns as string • To move to the next row, use the Read() method again. If this method returns True, a rowof information has been successfully retrieved. If it returns False, you’ve attempted to readpast the end of your result set. There is no way to move backward to a previous row. • As soon as you’ve finished reading all the results you need, close the DataReader and Connection: • dr.Close(); • cnn.Close();

17. Select Queries – Retrieve Data • Beside examining with the Read() method. • HasRows property return true if there exists any records to retrieve, otherwise false. • if (dr.HasRows) • Label1.Text = "There exists some records"; • To retrieve all records such routine could be used • while (dr.Read()) • { • Label1.text+=dr[“name”]+”<br/>”; • }

18. Add a Record – Insert Query • SqlConnection cnn = new SqlConnection(); • cnn.ConnectionString = @"Data Source=localhost\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;"; • cnn.Open(); • SqlCommand cmd = new SqlCommand(); • cmd.Connection = cnn; • // an imaginary query is prepared • cmd.CommandText = “insert into tbl(name,surname) values(‘“+txtName.Text+”’,’”+txtSurname.Text+”)”; • int r=cmd.ExecuteNonQuery(); // number of records affected will return • if (r>0) • Label1.Text = “Record added"; • Here the problem is that if txtName or txtSurname contains special characters like ‘than query will return an error or the application will be vulnerable to sql injection attacks to avoid such problems parameters must be used. Officially known as parameterized commands.

19. Parameterized Queries • From previous sample: • cmd.CommandText = “insert into tbl(name,surname) values(‘“+txtName.Text+”’,’”+txtSurname.Text+”)”; • cmd.CommandText = “insert into tbl(name,surname) values(@name,@surnm)”; • cmd.Parameters.AddWithValue(“@name”,txtName.Text) • cmd.Parameters.AddWithValue(“@surnm”,txtSurname.Text) • int r=cmd.ExecuteNonQuery(); • cmd.Parameters.Clear(); method clears all parameters created. • cmd.Parameters.Add("@name", SqlDbType.DateTime).Value = DateTime.Now; //could be used to define and initialize any parameter. • There exists several additional methods and overloads of methods which have to be choosen (All does the same thing in grant).

20. Disconnected Data • With disconnected data access, in short what you are doing is that: • A copy of the data retained in memory while your code is running. • Every client works with his local data. • You fill the DataSet in much the same way that you connect a DataReader. However,although the DataReader holds a live connection, information in the DataSet is always disconnected. • selectSQL = "SELECT au_lname, au_fname, au_id FROM Authors"; • SqlConnection con = new SqlConnection(connectionString); • SqlCommandcmd = new SqlCommand(selectSQL, con); • SqlDataAdapter adapter = new SqlDataAdapter(cmd); • DataSet dsPubs = new DataSet(); • con.Open(); • // All the information in transferred with one command. • // This command creates a new DataTable (named Authors) • // inside the DataSet. • adapter.Fill(dsPubs, "Authors"); • con.Close();

21. Notes on Disconnected Data • If you want to extract records from a database and place them in a DataSet, you needto use a DataAdapter. Every DataAdapter can hold four commands: SelectCommand,InsertCommand, UpdateCommand, and DeleteCommand. This allows you to use a singleDataAdapter object for multiple tasks. The Command object supplied in the constructor isautomatically assigned to the DataAdapter.SelectCommand property. • The DataAdapter.Fill() method takes a DataSet and inserts one table of information. Inthis case, the table is named Authors, but any name could be used. That name is used later toaccess the appropriate table in the DataSet. • To access the individual DataRows, you can loop through the Rows collection of theappropriate table. Each piece of information is accessed using the field name, as it was withthe DataReader. • Datasets have their own capabilities which have to be examined in a special section. Like Dataset.Tables[“tbl”].find to locate a record and so ...