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-------------------------

Creating and Executing a Command in ASP.NET

Creating and Executing a Command

When creating a Command object, you have the choice of several constructors. The most useful accepts a CommandText value and a Connection. Here's an example with the SqlCommand class:

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(commandText, con);

For standard providers, there are three ways to execute a command: ExecuteNonQuery( ) , ExecuteReader( ), and ExecuteScalar( ). You choose one of these methods, depending on the type of command you are executing. For example, ExecuteReader( ) returns a DataReader and provides read-only access to query results.

Some providers include additional members. For example, the ADO.NET SQL Server provider includes an ExecuteXmlReader( ) method that retrieves data as an XML document.

Executing a Command That Doesn't Return Rows

The SQL language includes several nonquery commands. The best known include UPDATE, DELETE, and INSERT. You can also use other commands to create, alter, or drop tables, constraints, relations, and so on. To execute any of these commands, just set the CommandText property with the full SQL statement, open a connection, and invoke the ExecuteNonQuery( ) method. The next sections consider examples that update, delete, and insert records.

Updating a record

The UPDATE statement, at its simplest, uses the following syntax:

UPDATE table SET update_expression WHERE search_condition

The UPDATE expression can thus modify a single record, or it can apply a change to an entire batch of records in a single table. Example below puts the UPDATE statement to work with a simple command that modifies a single field in a single category record in the Northwind database.

Example . Updating a record

// UpdateRecord.cs - Updates a single Category record

using System;

using System.Data.SqlClient;

public class UpdateRecord

{

    public static void Main() 

    {

        string connectionString = "Data Source=localhost;" +

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

        string SQL = "UPDATE Categories SET CategoryName='Beverages'" +

                     "WHERE CategoryID=1";

        // Create ADO.NET objects.

        SqlConnection con = new SqlConnection(connectionString);

        SqlCommand cmd = new SqlCommand(SQL, con);

        // Execute the command.

        con.Open();

        int rowsAffected = cmd.ExecuteNonQuery();

        con.Close();

        // Display the result of the operation.

        Console.WriteLine(rowsAffected.ToString() + " row(s) affected");

    }

}

Note that the ExecuteNonQuery( ) method returns the number of rows affected, not the row itself. In order to see the results of the change, you need to either query the row or use a tool such as SQL Server's Enterprise Manager to browse the database.

If the UPDATE statement fails to update any records because the WHERE clause is too restrictive, an error isn't generated. You must examine number of affected rows to determine if this is this case. If you are adding this logic to a custom data access component, you might want to raise an exception if this happens, because it indicates that no update took place.

Deleting a record

The SQL DELETE statement simply specifies a search condition that selects one or more records to be removed:

DELETE FROM table WHERE search_condition

You can modify the previous example to delete a record simply by changing the SQL variable:

string SQL = "DELETE FROM Categories WHERE CategoryID=1";

Inserting a record

Finally, you can insert a record using a list of column names, followed by a list of column values in the same order:

INSERT INTO table (column_list) VALUES (value_list)

Once again, the console example can be adapted to insert a category record just by modifying the SQL text:

string SQL = "INSERT INTO Categories (CategoryName, Description) " +

    "VALUES ('Beverages', 'Soft drinks, coffees, teas, beers, and ales')";

Note that the category table includes a CategoryID column that is configured as a unique identity value. That means the CategoryID number is created by the data source, which ensures that duplicate IDs don't occur. For that reason, the INSERT statement doesn't include a CategoryID value. As a side effect, this code will always succeed and create a new row with identical information, but with a new CategoryID. (If you want to replace a row you deleted in the previous example, you can manually specify a CategoryID with the value of 1).

Executing a Command That Returns a Single Value

ExecuteScalar( ) method returns a single value. If you perform a query, this will be the first value in the first column of the first row. More likely, you'll use ExecuteNonQuery( ) to return an aggregate value, which is the result of a calculation using a subset of rows.

An aggregate function must be part of a SQL SELECT statement, which indicates the table and (optionally) a search filter and sort order:

SELECT aggregate_expression FROM tables [WHERE search_condition]

       [ORDER BY order_expression ASC | DESC]

Example below shows how an aggregate command can retrieve the total number of orders for the year 1996.

Example . Executing an aggregate function

// TotalOrders.cs - Gets the number of order records from 1996

using System;

using System.Data.SqlClient;

public class TotalOrders

{

    public static void Main() 

    {

        string connectionString = "Data Source=localhost;" +

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

        string SQL = "SELECT COUNT(*) FROM Orders WHERE " +

                 "OrderDate >= '1996-01-01' AND OrderDate < '1997-01-01'";

        // Create ADO.NET objects.

        SqlConnection con = new SqlConnection(connectionString);

        SqlCommand cmd = new SqlCommand(SQL, con);

        // Execute the command.

        con.Open();

        int result = (int)cmd.ExecuteScalar();

        con.Close();

        // Display the result of the operation.

        Console.WriteLine(result.ToString() + " rows in 1996");

    }

}

Here's the sample output for this code:

152 rows in 1996

Opening and Closing Connections

Opening and Closing Connections

You've now seen all the ingredients you need to create and use a connection. You simply create the Connection object required for your data source, apply the appropriate connection string settings, and open the connection. In an example below, a connection is created to a SQL Server database on the local computer using integrated authentication. The code opens the connection, tests its state, and closes it.

Example. Opening and testing a connection //opining n closing database

// ConnectionTest.cs - Opens and verifies a connection

using System;

using System.Data.SqlClient;

public class ConnectionTest

{

    public static void Main() 

    {

        SqlConnection con = new SqlConnection("Data Source=localhost;" +

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

        con.Open();

        Console.WriteLine("Connection is " + con.State.ToString());

        con.Close();

        Console.WriteLine("Connection is " + con.State.ToString());

    }

}

The output clearly indicates whether the connection test is successful:

Connection is Open

Connection is Closed

Data Providers in ASP.net

Data Providers

The most commonly used .NET data providers are described in the following sections.

A. Microsoft SQL Server

The SQL Server .NET data provider ships with the .NET Framework. It uses the Tabular Data Stream (TDS) protocol to send requests to and receive responses from the SQL Server. This provider delivers very high performance because TDS is a fast protocol that can access Microsoft SQL Server directly without an OLE DB or ODBC layer and without COM interop. The SQL Server .NET data provider can be used with Microsoft SQL Server 7.0 or later. To access earlier versions of Microsoft SQL Server, the OLE DB .NET data provider with the SQL Server OLE DB provider (SQLOLEDB) should be used. The SQL Server .NET data provider classes are located in the System.Data.SqlClient namespace.

B. OLE DB

The OLE DB .NET data provider ships with the .NET Framework. It communicates with a data source using a data source-specific OLE DB provider through COM interop. The OLE DB provider, in turn, communicates directly with the data source using native OLE DB calls.

The OLE DB .NET data provider supports OLE DB interfaces later than Version 2.5. As a result, some OLE DB providers, including those for Microsoft Exchange Server and Internet Publishing, aren't supported. Also, the OLE DB .NET data provider can't be used with the OLE DB provider for ODBC (MSDASQL). To access ODBC data, use the ODBC .NET data provider discussed later in this chapter.

The OLE DB.NET data provider classes are located in the System.Data.OleDb namespace.

C. ODBC

The ODBC .NET data provider is installed as an add-in component to the .NET Framework Version 1.0 and ships with the .NET Framework Version 1.1. The provider communicates with the data source using native ODBC drivers through COM interop.

The following ODBC drivers are guaranteed compatible with the ODBC .NET data provider:

·         Microsoft SQL Server ODBC Driver

·         Microsoft ODBC Driver for Oracle

·         Microsoft Access (Jet) ODBC Driver

The ODBC .NET data provider classes are located in the Microsoft.Data.Odbc namespace in Version 1.0 of the .NET Framework. In Version 1.1, the namespace changes to System.Data.Odbc.

D. Oracle

The Oracle .NET data provider is installed as an add-in component to the .NET Framework Version 1.0 and ships with the .NET Framework Version 1.1. This provider accesses an Oracle database using the Oracle Call Interface (OCI). The Oracle .NET data provider can be used with Oracle 8i Release 3 (8.1.7) or later. Use the OLE DB .NET data provider with the Oracle OLE DB provider (MSDAORA) for earlier versions of Oracle. Oracle 9i is required to access UTF16 databases because UTF16 is a new feature in Oracle 9i.

The Microsoft Oracle .NET data provider classes are located in the System.Data.OracleClient namespace in both Versions 1.0 and 1.1 of the .NET Framework.

ADO.net notes and data adapter

ADO.NET

ADO.NET is the latest extension of the Universal Data Access technology. Its architecture is similar to classic ADO in some respects, but a great departure in others.ADO.NET is much simpler, less dependent on the data source, more flexible, and the format of data is textual instead of binary. Textual formatted data is more verbose than binary formatted data, and this makes it comparably larger. The tradeoff is ease of transportation through disconnected networks, flexibility, and speed.

www.syngress.com

Because data in ADO.NET is based on XML, Managed Providers are required to serve data in a proper XML format. Once a developer has written data access code, they only need to change a few parameters to connect to a different data source.

ADO.NET is based on a connection-less principle that is designed to ease the connection limitations that developers have traditionally had to deal with when creating distributed solutions. You no longer need to maintain a connection, or even worry about many of the connection options that developers had to deal with in the past.

A complete .NET data provider includes the following classes:

Connection

Connects to the data source.

Command

Executes commands against the data source.

DataReader

A forward-only, read-only connected result set.

ParameterCollection

Stores all parameters related to a Command and the mappings of both table and column names to the DataSet columns.

Parameter

Defines parameters for parameterized SQL statements and stored procedures.

Transaction

Groups statements modifying data into work units that are either committed in their entirety or cancelled.

DataAdapter

Bridges the connected components to the disconnected components, allowing a DataSet and DataTable to be filled from the data source and later reconciled with the data source.

1. High Level Commission for Information Technology, Singh Durbar, Kathmandu, Nepal

1. High Level Commission for Information Technology, Singh Durbar, Kathmandu, Nepal
What is the e-Government?
A Government which accepts information and communication technologies as a tools to transform its internal and external relationships and processes in governance to be more effective, transparent, professional and costing less to its citizens. In another terms, e-Government can be viewed as the process of creating public value with the use of Modern ICT. Indian Ministry of Communications and information Technology defines electronic governance ‘the application of information Technology to the processes of government functioning to bring about simple, moral, accountable, responsive and transparent government. Therefore, e-Government is the same government that applies ICT for its transformation to deliver better public services.
The value added by the government is the difference between the benefits that the public eventually enjoys and the resources and powers that citizens decide to give their government. The legitimacy of the government as a whole generally depends on how it creates public value”. The measurement of public value might be abstracts an UN report World Public Sector 2003 states” Difficult these things are to gauge, public value created by outcomes can be measured by the identification of causative factors (e.g. Was the government instrumental?) services can be measured by satisfaction and perception of fairness, trust, legitimacy and confidence can be measured by perceptions of overall performance of the government.
Experience of Malaysia rather wisdom from them is that e-Government is no panacea for those societies with congenitally corrupt and defective political, social and economic systems and structures. It is patently absurd to think that e-government could indeed transform a (failed state) into an efficient, credible, development oriented super state…. e-government realistically is a function of capacity, capability and political will to break away from an existing condition.
The Nepalese Government is well aware of the different roles it has to play and the role ICTs can play in performing these roles. In their e-Policy the Nepalese Government focuses on using E-Government in especially the second and the third role; the delivery of programmes and services and the usage of information infrastructures for improved internal administrative procedures. Policies and the regulatory framework will serve to further enhance the deployment of E-Government in these roles.
The benefits of appropriate use of E-Government tools are often measured in effectiveness and efficiency gains. However more importantly E-Government, if implemented well can positively influence the general publics’ perception of the trustworthiness and effectiveness of their Government. Adversely, if not implemented well E-Government projects will quite easily fail and thus lead to a decrease in citizen-trust.