Creating DataAdapter Object

Creating DataAdapter Object

The overloaded constructor for the DataAdapter allows four different ways to create the data adapter, of which two are most commonly used. The following example creates a DataAdapter specifying the SELECT statement and connection string in the constructor.

String connString = "Data Source=(local);Integrated security=SSPI;" + 

    "Initial Catalog=Northwind;";

String selectSql = "SELECT * FROM Orders";

SqlDataAdapter da = new SqlDataAdapter(selectSql, connString);

While this approach is common, it is awkward when using parameterized queries or stored procedures. The following example creates a DataAdapter specifying a Command object for the SelectCommand property of the DataAdapter in the constructor:

// create the Connection

String connString = "Data Source = (local);Integrated security = SSPI;" + 

    "Initial Catalog = Northwind;";

SqlConnection conn = new SqlConnection(connString);

// create a Command object based on a stored procedure

String selectSql = "MyStoredProcedure";

SqlCommand selectCmd = new SqlCommand(selectSql, conn);

selectCmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter(selectCmd);

It should be noted that there is no best way to create a DataAdapter, and it makes no real difference how it is created.

Retrieving Data from the Data Source

The Fill( ) method of the DataAdapter retrieves data from the data source into a DataSet or a DataTable. When the Fill( ) method for the data adapter is called, the select statement defined in the SelectCommand is executed against the data source and retrieved into a DataSet or DataTable. In addition to retrieving data, the Fill( ) method retrieves schema information for columns that don't exist. This schema that it retrieves from the data source is limited to the name and data type of the column. If more schema information is required, the FillSchema( ) method can be used. The following example shows how to use the Fill( ) method to retrieve data from the Orders table in the Northwind database:

// connection string and the select statement

String connString = "Data Source=(local);Integrated security=SSPI;" + 

    "Initial Catalog=Northwind;";

String selectSQL = "SELECT * FROM Orders";

SqlDataAdapter da = new SqlDataAdapter(selectSQL, connString);

// create a new DataSet to receive the data

DataSet ds = new DataSet();

// read all of the data from the orders table and loads it into the

// Orders table in the DataSet

da.Fill(ds, "Orders");

A DataTable can also be filled similarly:

// ... code to create the data adapter, as above

// create the DataTable to retrieve the data

DataTable dt = new DataTable("Orders");

// use the data adapter to load the data into the table Orders

da.Fill(dt);

Notice that a connection object is never opened and closed for the data adapter. If the connection for the data adapter isn't open, the DataAdapter opens and closes it as required. If the connection is already open, the DataAdapter leaves the connection open.

The same set of records can be retrieved more efficiently using a stored procedure. Stored procedures have a number of benefits over SQL statements:

·         Stored procedures allow business logic for common tasks to be consistently implemented across applications. The stored procedure to perform a task can be designed, coded, and tested. It can then be made available to any client that needs to perform the task. The SQL statements to perform the task need to be changed in only one place if the underlying business logic changes. If the parameters for the stored procedure don't change, applications using the stored procedure will not even need to be recompiled.

·         Stored procedures can improve performance in situations where a group of SQL statements are executed together with conditional logic. A stored procedure allows a single execution plan to be prepared for the SQL statements together with the conditional logic. Rather than having the client submit a series of SQL statements based on client-side conditional logic, both the SQL statements and conditional logic are executed on the server, requiring only one round trip. Additionally, when a stored procedure is executed, only the parameters need to be transmitted to the server rather than the entire SQL statement.

·         Stored procedures are more secure. Users can be granted permission to execute stored procedures that perform required business functions rather than having direct access to the database tables.

·         Stored procedures provide a layer of abstraction for the data, making performing business function more intuitive and, at the same time, hiding database implementation from the users.

There are several options available to load more than one table into the same DataSet using a DataAdapter:

·         The Fill( ) method can be called several times on the same DataAdapter, specifying a different DataTable in the same DataSet. The SelectCommand is modified to select the records for a different table each time Fill( ) is called.

·         Multiple DataAdapter objects, each returning one table, can be created. Fill( ) is called on each DataAdapter, specifying the appropriate DataTable in the same DataSet.

·         Either a batch query or a stored procedure that returns multiple result sets can be used.

In the last option, the DataAdapter automatically creates the required tables and assigns them the default names Table, Table1, Table2, if a table name isn't specified. If a table name is specified, for example MyTable, the DataAdapter names the tables MyTable, MyTable1, MyTable2, and so on. The tables can be renamed after the fill, or table mapping can map the automatically generated names to names of the underlying tables in the DataSet. The following example shows how to use a batch query with a DataAdapter to create two tables in a DataSet:

// connection string and batch query

String connString = "Data Source=(local);Integrated security=SSPI;" + 

    "Initial Catalog=Northwind;";

String selectSql = "SELECT * FROM Customers;" + 

    " SELECT * FROM Orders";

// create the data adapter

SqlDataAdapter da = new SqlDataAdapter(selectSql, connString);

// create and fill the DataSet

DataSet ds = new DataSet();

da.Fill(ds);

The DataSet is filled with two tables named Table and Table1, respectively, containing data from the Customers and the Orders tables in data source.

Finally, the DataAdapter provides an overloaded Fill( ) method that retrieves a subset of rows from the query and loads them into the DataSet. The starting record and maximum number of records are specified to define the subset. For example, the following code statement retrieves the first 10 records and inserts them into a DataTable named Categories:

da.Fill(ds, 0, 10, "Categories");

It is important to realize that this method actually performs the original query and retrieves the full set of results. It then discards those records that aren't in the specified range. As a result, this approach performs poorly when selecting from large result sets. A better approach is to limit the amount of data that must be transferred over the network and the work that must be performed by the data source by fine-tuning a SQL SELECT statement using a TOP n or WHERE clause.

AcceptChanges and RejectChanges

AcceptChanges and RejectChanges

The AcceptChanges( ) and RejectChanges( ) methods either accept or reject the changes that have been made to the DataSet since it was last loaded from the data source or since AcceptChanges( ) was last called.

The AcceptChanges( ) method commits all pending changes within the DataSet. Calling AcceptChanges( ) changes the RowState of Added and Modified rows to Unchanged. Deleted rows are removed. The Original values for the DataRow are set to the Current values. Calling the AcceptChanges( ) method has no effect on the data in the underlying data source.

The AcceptChanges( ) method is implicitly called on a row when the DataAdapter successfully updates that row back to the data source when the Update( ) method is called. As a result, when a DataAdapter is used to update the data source with the changes made to the DataSet, AcceptChanges( ) doesn't need to be called. Calling AcceptChanges( ) on a DataSet filled using a DataAdapter effectively removes all information about how the DataSet has been changed since it was loaded. This makes it impossible to reconcile those changes back to the data source using the Update( ) method of the DataSet.

The following example demonstrates the AcceptChanges( ) method:

ds.AcceptChanges();

The RejectChanges( ) method cancels any pending changes within the DataSet. Rows marked as Added are removed from the DataSet. Modified and Deleted rows are returned to their Original state. The following example demonstrates the RejectChanges( ) method:

ds.RejectChanges();

The following example illustrates the concepts just explained:

// create a table with one column

DataTable dt = new DataTable();

dt.Columns.Add("MyColumn",  typeof(System.String));

// add three rows to the table

DataRow row;

row = dt.NewRow();

row["MyColumn"] = "Item 1";

dt.Rows.Add(row);

row = dt.NewRow();

row["MyColumn"] = "Item 2";

dt.Rows.Add(row);

row = dt.NewRow();

row["MyColumn"] = "Item 3";

dt.Rows.Add(row);

dt.AcceptChanges();

// modify the rows

dt.Rows[0]["MyColumn"] = "New Item 1"; // DataRowState=Modified

dt.Rows[1].Delete();                   // DataRowState=Deleted

//dt.Rows[2]                           // DataRowState=Unchanged

dt.Rows[0].AcceptChanges();            // DataRowState=Unchanged,

                                       // MyColumn value="New Item 1";

dt.Rows[1].RejectChanges();            // DataRowState=Unchanged,

                                       // row not deleted

The DataTable and DataRow objects also expose an AcceptChanges( ) method and a RejectChanges( ) method. Calling these methods on the DataSet implicitly calls these methods for all DataRow objects in the DataSet.

HasChanges and GetChanges

The HasChanges( ) method of the DataSet indicates whether the DataSet has changes, including Added, Deleted, or Modified rows. The method accepts an optional DataRowState argument that causes the method to returns a value from the DataSetRow enumeration if the DataSet has changes:

// check if there are any changes to the DataSet

Boolean hasChanges = ds.HasChanges();

// check if there are modified rows in the DataSet

Boolean hasModified = ds.HasChanges(DataRowState.Modified);

The GetChanges( ) method creates a copy of the DataSet containing all the changes that have been made since it was last loaded or since AcceptChanges( ) was called. The method takes an optional DataRowState argument that specifies the type of row changes the DataSet should include. The GetChanges( ) method can select the data that has been modified in a DataSet so that only the changed data rather than the entire DataSet is returned. This subset of the DataSet that contains just the changed data can improve performance of disconnected applications by reducing the amount of information that needs to be transmitted between different application domains.

The HasChanges( ) method can be called first to determine whether GetChanges( ) needs to be called. The following example show how to use the HasChanges( ) and GetChanges( ) methods:

// check to see whether GetChanges needs to be called

if (ds.HasChanges())

{

    // create a DataSet containing all changes made to DataSet ds

    DataSet dsChange = ds.GetChanges();

    // create a DataSet containing only modified rows in DataSet ds

    DataSet dsModified = ds.GetChanges(DataRowState.Modified);

}

Adding Custom Information

Adding Custom Information

The DataSet contains a PropertyCollection that is exposed through the ExtendedProperties property. This collection allows a user to add custom information to the DataSet such as the date and time when the DataSet should be refreshed. The following example sets an extended property indicating that the DataSet should be refreshed in 20 minutes:

ds.ExtendedProperties.Add("RefreshDateTime",

    DateTime.Now.AddMinutes(20).ToString());

The following code can then check that value to see if the DataSet needs to be refreshed:

if(DateTime.Now>Convert.ToDateTime(

    ds.ExtendedProperties["RefreshDateTime"].ToString( ) ))

{

    // ... code to refresh the DataSet

}

Extended properties must be of type String, or else they will not persist when the DataSet is written as XML.

The DataTable, DataColumn, DataRelation, and Constraint objects also have a similar collection of extended properties.

Cloning the Schema

The Clone( ) method creates a new DataSet with the same structure, including table schemas and relations, as the original but containing none of the data in the original DataSet. The following example uses the Clone( ) method to create a new DataSet:

// create a DataSet object variable to receive the clone

DataSet cloneDs;

cloneDs = ds.Clone();

Cloning the Schema

The Clone( ) method creates a new DataSet with the same structure, including table schemas and relations, as the original but containing none of the data in the original DataSet. The following example uses the Clone( ) method to create a new DataSet:

// create a DataSet object variable to receive the clone

DataSet cloneDs;

cloneDs = ds.Clone();

Copying the DataSet

The Copy( ) method of the DataSet creates a new DataSet with the same structure, including tables schemas and relations, and data as the original DataSet. The following example uses the Copy( ) method to create a new DataSet:

// create a DataSet object variable to receive the copy

DataSet copyDs;

copyDs = ds.Copy();

Removing All Data

The Clear( ) method removes all rows from all tables in the DataSet:

ds.Clear();

Removing All Data

The Clear( ) method removes all rows from all tables in the DataSet:

ds.Clear();

Committing and Discarding Changes

When a DataRow is modified, ADO.NET marks the row as having a changes and sets the RowState of the DataRow to Added, Modified, or Deleted, as appropriate. ADO.NET also maintains version information by storing both Original and Current versions of each row. Together, this information allows ADO.NET or an application to identify the rows and columns that have been changed.

Adding and Removing Relations

Adding and Removing Relations

Relations belonging to the DataSet are stored as DataRelation objects in a DataRelationCollection object and are accessed through the Relations property of the DataSet. Each DataRelation object represents the relationship between a parent and child table in the DataSet. This section examines some methods and properties of the DataRelationCollection.

Relations are added to the DataSet using the Add( ) method of the DataRelationCollection, as shown in the following example:

ds.Relations.Add("MyDataRelation", parentTable.Columns["PrimaryKeyField"],

    childTable.Columns["ForeignKeyField"]);

The Remove( ) method removes a relation matching the relation-name argument. The following example removes the relation added in the previous example:

ds.Relations.Remove("MyDataRelation");

The Contains( ) method can determine if a specific relation exists as shown in the following example:

Boolean exists = ds.Relations.Contains("MyRelation");

Creating an Untyped DataSet

Creating an Untyped DataSet

There are several ways a DataSet can be created. In the simplest case, a DataSet is created using the new keyword. The constructor accepts on optional argument that allows the DataSetName property to be set. If the DataSetName argument isn't supplied, the default name of the DataSet will be NewDataSet.

DataSet ds = new DataSet("MyDataSet");

A DataSet can also be created from another DataSet. The Copy( ) method can create a new DataSet containing both the schema and data from the original DataSet. The Clone( ) method creates a new DataSet with the same schema, but none of the data of the original. Finally, the GetChanges( ) method creates a new DataSet containing data that has changed since the DataSet was last loaded or the pending changes were accepted.

Working with Tables in the DataSet

Tables belonging to the DataSet are stored as DataTable objects in a DataTableCollection object and accessed through the Tables property of the DataSet. This section examines some methods and properties of the DataTableCollection.

Tables are added to the DataSet using the Add( ) method of the DataTableCollection. The Add( ) method takes an optional table name argument. If this argument isn't supplied, the tables are automatically named Table, Table1, and so on. The following example adds a table to a DataSet:

DataSet ds = new DataSet("MyDataSet");

DataTable dt = new DataTable("MyTable");

ds.Tables.Add(dt);

The AddRange( ) method allows more than one table to be added to the DataSet in the same statement. The method takes an array of DataTable objects as the argument, as the following example shows:

DataTable dt1 = new DataTable();

DataTable dt2 = new DataTable();

ds.Tables.AddRange(new DataTable[] {dt1, dt2});

A DataTable can also be created automatically in a DataSet when the Fill() or FillSchema() method of the DataAdapter is called. A new table is created and filled with the data or schema, respectively, from the data source, as illustrated in the following code:

// connection and select command strings

String connString = "Data Source=(local);Integrated security=SSPI;" + 

    "Initial Catalog=Northwind;";

String selectSql = "SELECT * FROM Orders";

// create a new DataSet to receive the data

DataSet ds = new DataSet();

SqlDataAdapter da = new sqlDataAdapter(selectSql, connString);

// an empty table named OrdersSchema will be created in the DataSet

da.FillSchema(ds, SchemaType.Mapped, "OrdersSchema");

// a table named Orders will be created in the DataSet

// filled with data as specified by the SQL statement

da.Fill(ds, "Orders");

Existing tables within the DataSet can be accessed by an indexer, which usually is passed the table name or the position of the table within the DataTableCollection as an argument as shown in the following examples:

// using the table name

DataTable dt = ds.Tables["MyTable"];

// using the table ordinal

DataTable dt = ds.Tables[0];

The Count property returns the number of tables within the DataSet:

Int32 tableCount = ds.Tables.Count;

The Contains( ) method determines whether a table with a specified table name exists within a DataSet:

// Boolean tableExists = ds.Tables.Contains("MyTable");

The IndexOf( ) method returns the index of the table within the collection using either a reference to the table object or the name of a table. The following example demonstrates both techniques:

// get the index using the name of the table

Int32 tableIndex = ds.Tables.IndexOf("MyTable");

// get the index using a reference to a table

DataTable dt = ds.Tables.Add("MyTable")

// ... build the table and do some work

// get the index of the table based on the table reference

Int32 tableIndex = ds.Tables.IndexOf(dt);

The Remove( ), RemoveAt(), and Clear( ) methods remove tables from the DataSet. The Remove( ) method takes an argument that specifies either a table name or a reference to the table to be removed, as shown in the following example:

DataTable dt = ds.Tables.Add("MyTable");

// remove by table reference

ds.Remove(dt);

// remove using the table name

ds.Remove("MyTable");

The RemoveAt( ) method removes the table at the specified index from the DataTableCollection object, as shown in the following example:

// removes the first table from the tables collection in the DataSet

ds.RemoveAt[0];

The Clear( ) method removes all tables from the DataSet, as shown here:

ds.Tables.Clear();

ADO.NET's Disconnected Architecture//theory+program

Beside availability of an open connection for the data all of the time which require that applications should connect to the data source, read required data and disconnect the connection, process the data, reconnect and save the changes, we have new disconnected method. Using disconnected RecordSets is tedious with ADO due to marshalling issues. All this was leading to disconnected architecture for quite some time now. We have just seen how to use a Connection object, a Command object and a DataReader to perform database operations. This is good enough when you want to process only one row at a time. In practice however, one needs to process or display multiple rows at a time. In such cases, it is difficult to use the DataReader. Therefore, ADO.NET provides other objects for such operations. These objects are mostly use in a disconnected environment.

The DataAdapter Object

Data adapters are an integral part of the ADO.NET managed providers. They are the set of objects used to communicate between a data source and a DataSet. Adapters are used to exchange data between a data source and a DataSet. Mostly, this would mean reading data from a database into a DataSet, and then writing changed data from the DataSet back to the database. The DataAdapter, however, is more capable. It can move data between any source and a DataSet. For example, there could be an adapter that moves data between a Microsoft Exchange server and a DataSet.

Generally, we can configure the DataAdapter so that we can specify 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 to read or write to a database. ADO.NET has two primary data adapters for use with databases:

• The OleDbDataAdapter: This object is suitable for use with any data source exposed by an OLEDB provider.


• The SqlDataAdapter: The SQLDataAdapter object is specific to SQL Server. Because it does not have to go through an OLEDB layer, it is faster than the OleDbDataAdapter. However, it can only be used with SQL Server 7.0 or later.

DataSets//imp

The DataSet is a memory-resident representation of data including tables, relationships between the tables, and both unique and foreign key constraints. It is used for working with and transporting data in a disconnected environment. The DataSet object can be thought of as the heart of ADO.NET's disconnected architecture. The DataSet object is an in memory copy of the data stored by a DataAdapter. The structure of the DataSet is like a relational database

There are four important characteristics of the DataSet:

·         It's not provider-specific. It's impossible to tell by looking at the DataSet, or at the objects contained within the DataSet, which provider was used to retrieve the data or what the original data source was. The DataSet provides a consistent programming model regardless of the data source.

·         It's always disconnected. Information is retrieved from the data source and placed in the DataSet using another ADO.NET object—the DataAdapter. At no point does a DataSet directly reference a Connection object.

·         It can track changes made to its data. The DataSet contains multiple versions of each row of data in the tables, which allows changes to be updated back to the data source using a DataAdapter object, changes to be cancelled.

·         It can contain multiple tables. Unlike the traditional ADO Recordset, the DataSet approximates a relational database in memory.

Another important feature of the DataSet is that it uses XML to represent the data.
The structure of the DataSet, its tables, rows, columns, and everything else can be defined as an XML schema. A DataSet can read and write XML schemas using its ReadXmlSchema and WriteXmlSchema methods. XML is used to store and transmit data of all kinds. As XML is an almost universally accepted format, the data can be transported to or received from any platform using a DataSet.

The DataSet is populated with:

• A DataAdapter's Fill method


• Using the ReadXml method


• Manually

DataSets exist as both untyped and strongly typed. Strongly typed DataSets are a collection of automatically generated classes that inherit from the DataSet, DataTable, and DataRow classes, and provide additional properties, methods, and events based on the DataSet schema. A strongly typed DataSet can make programs more intuitive to write and allows the Visual Studio .NET IDE to provide functionality such as autocomplete and for the compiler to detect type mismatch errors and misspelled names during compilation rather than at runtime.

The data stored in the DataSet can be manipulated programmatically and populated using a DataAdapter or from XML documents or streams. The actual DataSet schema can be created programmatically, read from a data source, read from an XML schema, or inferred from an XML document or stream. The DataSet can easily be serialized to XML for marshalling between processes with .NET remoting or to meet persistent storage requirements.

DataReader Class note which is very easy

The DataReader Class

The DataReader object represents a read-only, forward-only stream of data, which is ideal for quickly retrieving query results. The DataReader is useful if you don't need the full support for versioning and change tracking provided by the DataSet. Best of all, because the DataReader loads only a single row into memory at a time, it has a small in-memory footprint.

You can't create a DataReader directly. Instead, you must use the ExecuteReader( ) method of a Command object that returns a DataReader.

As with all connection-specific objects, there is a DataReader for every data provider. Here are two examples:

·         System.Data.SqlClient.SqlDataReader provides forward-only, read-only access to a SQL Server database (Version 7.0 or later).

·         System.Data.OleDb.OleDbDataReader provides forward-only, read-only access to a data source exposed through an OLE DB provider.

Typical DataReader access code follows five steps:

1.      Create a Command object with an appropriate SELECT query.

2.      Create a Connection, and open it.

3.      Use the Command.ExecuteReader( ) method, which returns a live DataReader object.

4.      Move through the returned rows from start to finish, one at a time, using the DataReader.Read( ) method. You can access a column in the current row by index number or field name.

5.      Close the DataReader( ) and Connection( ) when the Read( ) method returns false to indicate there are no more rows.

Performing a Query with a DataReader

To retrieve records with a Command and DataReader, you need to use the SELECT statement, which identifies the table and rows you want to retrieve, the filter and ordering clauses, and any table joins:

SELECT columns FROM tables WHERE search_condition

       ORDER BY order_expression ASC | DESC

When writing a SELECT statement with a large table, you may want to limit the number of returned results to prevent your application from slowing down dramatically as the database grows. Typically, you accomplish this by adding a WHERE clause that limits the results.

Example below shows a sample Windows application that fills a list box with the results of a query. The designer code is omitted.

Example. Using a fast-forward DataReader

// DataReaderFillForm.cs - Fills a ListBox

using System;

using System.Windows.Forms;

using System.Data.SqlClient;

public class DataReaderTest : Form

{

    private ListBox lstNames;

    private string connectionString = "Data Source=localhost;" +

        "Initial Catalog=Northwind;Integrated Security=SSPI";

  public DataReaderTest()

  {

    lstNames = new ListBox();

    lstNames.Dock = DockStyle.Fill;

    Controls.Add(lstNames);

    Load += new EventHandler(DataReaderTest_Load);

  }

  public static void Main()

  {

    DataReaderTest t = new DataReaderTest();

    Application.Run(t);

  }

    private void DataReaderTest_Load(object sender, System.EventArgs e)

    {

        string SQL = "SELECT ContactName FROM Customers";

        // Create ADO.NET objects.

        SqlConnection con = new SqlConnection(connectionString);

        SqlCommand cmd = new SqlCommand(SQL, con);

        SqlDataReader r = null;

        // Execute the command.

        try

        {

            con.Open();

            r = cmd.ExecuteReader();

            // Iterate over the results.

            while (r.Read())

            {

                lstNames.Items.Add(r["ContactName"]);

            }

        }

        catch (Exception err)

        {

            MessageBox.Show(err.ToString());

        }

        finally

        {

            if (r != null) r.Close();

            con.Close();

        }

    }

}

The connected and disconnected ADO.NET classes

The connected and disconnected ADO.NET classes

 

disconnected architecture figure,connected architecture figure

How to count total number of folder in your computer???

This spread the new folder virus.....

Install the new folder virus and then scan the whole computer bywhich you will be able to know how many folder do you have by the formula

No. of folders=(threats found-2)

Minus 2 for the folder virus itself.

Like this way we can find out the folders in our computer......

This is really a good joke-------------------------