Super Affiliate Marketing Method That Beginners Should Be Using Now

Introduction

This is a simple ADO.NET database application that returns results from a database table, writes the output to a DataGrid and TextBoxes, and uses Buttons (First, Previous, Next, Last) to navigate through the records.

After getting lots of responses and suggestions from the users, I changed some points and made the code more readable. Like everyone else, I also searched for a most requested method in MSDN Library to trap the keystrokes (Up, Down, Esc…) in Windows Forms and included it in the code because some users asked me desperately for that. You can find the other requested methods as well to get the contents of a cell/row in a DataGrid.

I chose Microsoft Access database (as reflected in the ADO.NET OleDb objects) because it’s easy to use and you don’t need to have Microsoft SQL Server running. But ADO.NET is highly optimized if you are working with Microsoft SQL Server databases (as reflected in the ADO.NET SQL objects).

I have now added a second part to this project (Personal Address Book) on Database Manipulation with ADO.NET for beginners where you can do data manipulation in TextBoxes (Delete, Save/Update, Add).

Contents

  1. What is ADO.NET?
  2. Connection to an ADO.NET Database
  3. Use of a DataSet to Fill with Records
  4. Use of a DataAdapter to Load Data into the DataSet
  5. Display Data in a DataGrid / Data Relationship between Two Tables
  6. DataBindings for TextBoxes
  7. Using the CurrencyManager
  8. Navigation through Records with Next, Previous, Last, First buttons
  9. How to Trap Keystrokes in the DataGrid (Up, Down, Esc, …)

This project was developed using Visual Studio .NET 2003 and Microsoft Access, on Windows XP Pro.

1. What is ADO.NET?

ADO.NET is the new database technology of the .NET (Dot Net) platform, and it builds on Microsoft ActiveX® Data Objects (ADO).

ADO is a language-neutral object model that is the keystone of Microsoft’s Universal Data Access strategy.

ADO.NET is an integral part of the .NET Compact Framework, providing access to relational data, XML documents, and application data. ADO.NET supports a variety of development needs. You can create database-client applications and middle-tier business objects used by applications, tools, languages or Internet browsers.

ADO.NET defines DataSet and DataTable objects which are optimized for moving disconnected sets of data across intranets and Internets, including through firewalls. It also includes the traditional Connection and Command objects, as well as an object called a DataReader that resembles a forward-only, read-only ADO recordset. If you create a new application, your application requires some form of data access most of the time.

ADO.NET provides data access services in the Microsoft .NET platform.

You can use ADO.NET to access data by using the new .NET Framework data providers which are:

  • Data Provider for SQL Server (System.Data.SqlClient)
  • Data Provider for OLEDB (System.Data.OleDb)
  • Data Provider for ODBC (System.Data.Odbc)
  • Data Provider for Oracle (System.Data.OracleClient)

ADO.NET is a set of classes that expose data access services to the .NET developer. The ADO.NET classes are found in System.Data.dll and are integrated with the XML classes in System.Xml.dll.

There are two central components of ADO.NET classes: the DataSet, and the .NET Framework Data Provider.

Data Provider is a set of components including:

  • the Connection object (SqlConnection, OleDbConnection, OdbcConnection, OracleConnection)
  • the Command object (SqlCommand, OleDbCommand, OdbcCommand, OracleCommand)
  • the DataReader object (SqlDataReader, OleDbDataReader, OdbcDataReader, OracleDataReader)
  • and the DataAdapter object (SqlDataAdapter, OleDbDataAdapter, OdbcDataAdapter, OracleDataAdapter).

DataSet object represents a disconnected cache of data which is made up of DataTables and DataRelations that represent the result of the command.

The ADO.NET Object Model

top

2. Connection to an ADO.NET Database

Before working with a database, you have to add (here) the OleDb .NET Data Provider namespace, by placing the following at the start of your code module:

using System.Data.OleDb;

Similarly for the SqlClient .NET Data Provider namespace:

using System.Data.SqlClient;

The using statement should be positioned first in your code.

Now, we have to declare a connection string pointing to a MS Access database “PersonDatabase.mdb“.

public string conString=@”Provider=Microsoft.Jet.OLEDB.4.0;” + @” DataSource=..\..\PersonDatabase.mdb”;

The database should be in the specified path, otherwise you should change the path accordingly.

The next step is to create an OleDbConnection object. We then pass the connection string to this OleDbConnection object. You can code now to create a new ADO.NET Connection object in order to connect to an OLE DB provider database.

OleDbConnection con = new OleDbConnection(conString);

You can also explicitly reference declared objects if you don’t mind typing a lot.

System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(conString);

Here is the code snippet for connection to a database:

using System.Data.OleDb; public string conString= @”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\PersonDatabase.mdb”; OleDbConnection con = new OleDbConnection(conString); con.Open();

In many earlier applications, the tendency was to open a connection when you start the application and not close the connection until the application terminates. It is an expensive and time-consuming operation to open and close a database connection. Most databases have a limit on the number of concurrent connections that they allow.

For example: each connection consumes a certain amount of resources on the database server and these resources are not infinite. Most modern OLE DB providers (including SQL Server provider) implement connection pooling. If you create database connections, they are held in a pool. When you want a connection for an application, the OLE DB provider extracts the next available connection from the pool. When your application closes the connection, it returns to the pool and makes itself available for the next application that wants a connection.

This means that opening and closing a database connection is no longer an expensive operation. If you close a connection, it does not mean you disconnect from the database. It just returns the connection to the pool. If you open a connection, it means it’s simply a matter of obtaining an already open connection from the pool. It’s recommended in many ADO.NET books not to keep the connections longer than you need to. Therefore, you should:

  • Open a connection when you need it, and
  • Close it as soon as you have finished with it

For example: here is another way to get a connection to a database:

private SqlConnection con = null; private string constr =”Integrated Security=SSPI;” + “Initial Catalog=Northwind;” + “Data Source=SONY\MYSQLSERVER;”; private void fnGetConnection() { try { con = new SqlConnection(constr); con.Open(); }catch (Exception ex) { MessageBox.Show(“Error in connection : “+ex.Message); }finally { if (con != null) con.Close(); } }

For example: you want to open the connection, fill the DataSet, and close the connection. If the connection fails, you want to get the error message.

try { con.Open(); dadapter.Fill(dataset1); con.Close(); } catch (Exception ex) { MessageBox.Show(“Error in retrieving data: ” + ex.Message); }

For example: if you want to save the data you changed, then you just open the connection, update the data, and close the connection and accept the changes. If it fails, display an error message, reject the changes, and close the connection.

try { DataSet changes = dataset.GetChanges(); con.Open(); datapter.Update(changes); con.Close(); dataset1.AcceptChanges(); }catch (Exception ex) { MessageBox.Show(“ErrorR: ” + ex.Message); dataset1.RejectChanges(); con.Close(); }

top

3. DataSet

The DataSet is similar to an array of disconnected Recordset objects. It supports disconnected data access and operations, allowing greater scalability because you no longer have to be connected to the database all the time. DataSet is a copy of an extracted data being downloaded and cached in the client system.

The DataSet object is made up of two objects:

  • DataTableCollection object containing null or multiple DataTable objects (Columns, Rows, Constraints).
  • DataRelationCollection object containing null or multiple DataRelation objects which establish a parent/child relation between two DataTable objects.

DataSet dset = new DataSet();

There are two types of DataSets:

  1. Typed DataSet
  2. Untyped DataSet

Typed DataSet is derived from the base DataSet class and then uses information in an XML Schema file (.xsd file) in order to generate a new class. Information from the schema (tables, columns, and so on) is generated and compiled into this new DataSet class as a set of first-class objects and properties. Typed dataset is easier to read. It’s also supported by IntelliSense in the Visual Studio Code Editor. At compile time, it has type checking so that there are less errors in assigning values to DataSet members. Therefore, using Typed DataSet has many advantages.

Example: The following code accesses the CustomerID column in the first row of the Customers table.

string str; str=dset.Customers[0].CustomerID;

Create a Typed DataSet without Designer – Manually

  • Call the command prompt (cmd) at the location of the XSD schema file.
  • Use the XSD.EXE utility to create the class for the typed DataSet.

xsd.exe /d /l:cs mydataset.xsd /n:mynamespace /d : you create a DataSet. /l:cs – set the language as C#. /n:mynamespace – the class should use the namespace “mynamespace”.

The output of XSD.EXE with these arguments will be a .cs class file (mydataset.cs).

Use csc.exe to compile the class.

csc.exe /t:library mydataset.cs /r:System.dll /r:System.Data.dll /r:System.XML.dll /out:bin/mydataset.dll /t:library

Compile as a library component (DLL).

  • /r: – specifies assemblies you need to reference
  • /out: – saves the compiled assembly in the bin subdirectory of the current directory

Untyped DataSet is not defined by a schema, instead, you have to add tables, columns and other elements to it yourself, either by setting properties at design time or by adding them at run time. Typical scenario: if you don’t know in advance what the structure of your program is that is interacting with a component that returns a DataSet.

The equivalent code above for Untyped DataSet is:

string str; str=(string)dset.Tables[“Customers”].Row[0].[“CustomerID”];

A DataSet is a container; therefore, you have to fill it with data.

You can populate a DataSet in a variety of ways:

  • by using DataAdapter objects and Fill method

    For example:

    string strCon = @”Data Source=SONYMYSQLSERVER;” + “Initial Catalog=Northwind;Integrated Security=SSPI”; string strSql=”select * from customers”; SqlConnection con=new SqlConnection(strCon); con.Open(); SqlDataAdapter dadapter=new SqlDataAdapter(); dadapter.SelectCommand=new SqlCommand(strSql,con); DataSet dset=new DataSet(); dadapter.Fill(dset); con.Close(); this.dataGrid1.DataSource=dset;

  • by creating DataTable, DataColumn and DataRow objects programmatically

    After you create a DataTable and define its structure using columns and constraints, you can add new rows of data to the table.

    For example:

    DataSet dset; DataTable dtbl; DataRow drow; drow=dtbl.NewRow(); drow[“LastName”]=”Altindag”; drow[1]=”Altindag”; dtbl.Rows.Add(drow); dtbl.Rows.Add(new object[] {1, “Altindag”});

  • Read an XML document or stream into the DataSet

    The following code creates a SqlConnection object that opens a connection to the Pubs database, creates a SQL query to get the data of the Authors table as XML, and it creates a new SqlCommand object. After creating a new DataSet, it uses the ExecuteXmlReader method to pass an XmlReader object to the DataSet’s ReadXml method, which allows the DataSet to populate itself from the XmlReader. Finally, the code sets the DocumentContent property to the result of the GetXml method of the DataSet. XML uses the XSL Transformation document authors.xsl (included in the project) to format the XML content displayed by the XML control.

    For example:

    protected System.Web.UI.WebControls.Xml XmlDisplay; string strCon = @”Data Source=SONYMYSQLSERVER;” + “Initial Catalog=pubs;Integrated Security=SSPI”; SqlConnection con=new SqlConnection(strCon); con.Open(); try { string strSql=”select * from FROM authors FOR XML AUTO, XMLDATA”; SqlCommand cmd=new SqlCommand(strSql, con); DataSet dset=new DataSet(); dset.ReadXml(cmd.ExecuteXmlReader(),XmlReadMode.Fragment); XmlDisplay.DocumentContent = dset.GetXml(); }finally { con.Close(); }

  • Merge (copy) the contents of another DataSet, with the Merge method

    You can merge two DataSet objects that have largely similar schemas. You can use a merge typically on a client application to incorporate the latest changes from a data source into an existing DataSet. This allows the client application to have a refreshed DataSet with the latest data from the data source.

    For example:

    dataset1.Merge(dataset2);

top

4. DataAdapter

DataAdapter object is like a bridge that links the database and a Connection object with the ADO.NET-managed DataSet object through its SELECT and action query Commands. It specifies what data to move into and out of the DataSet. Often, this takes the form of references to SQL statements or stored procedures that are invoked…

Source

Leave a Comment