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.

Complete Program To insert, update, show and delete in .Net using ADO

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace databaseinsertion
{
public partial class Form1 : Form
{
int myid;
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
string constr = "Data Source=NOBEL-PC\\SQLEXPRESS;Initial Catalog=db_ecommerce;user id=rupak;password=rupak";
SqlConnection sqlcon = new SqlConnection(constr);
SqlCommand scmd = new SqlCommand("productinsertion", sqlcon);
scmd.CommandType = CommandType.StoredProcedure;
scmd.Parameters.AddWithValue("@names", textBox1.Text);
scmd.Parameters.AddWithValue("@description", richTextBox1.Text);
try
{
sqlcon.Open();
scmd.ExecuteNonQuery();
MessageBox.Show("Data Inserted");
}
catch (SqlException se)
{
MessageBox.Show(se.Message);
}
sqlcon.Close();

}

private void button3_Click(object sender, EventArgs e)
{
listView1.Items.Clear();
string constr = "Data Source=NOBEL-PC\\SQLEXPRESS;Initial Catalog=db_ecommerce;user id=rupak;password=rupak";
SqlConnection sqlcon = new SqlConnection(constr);
string query = "select * from product";
SqlCommand cmd = new SqlCommand(query, sqlcon);
try
{
sqlcon.Open();
SqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
ListViewItem lvt = new ListViewItem();
lvt.Tag=r["id"];
lvt.Text = r["product_name"].ToString();
lvt.SubItems.Add(r["product_desc"].ToString());
listView1.Items.Add(lvt);
}
}
catch (SqlException se)
{
MessageBox.Show(se.Message);
}
sqlcon.Close();
}

private void listView1_SelectedIndexChanged(object sender, EventArgs e)
{

if (listView1.SelectedItems.Count > 0)
{
ListViewItem lv = listView1.SelectedItems[0];
myid=Convert.ToInt32(lv.Tag);
textBox1.Text = lv.Text;
richTextBox1.Text = lv.SubItems[1].Text;
}
}

private void button2_Click(object sender, EventArgs e)
{
string constr = "Data Source=NOBEL-PC\\SQLEXPRESS;Initial Catalog=db_ecommerce;user id=rupak;password=rupak";
SqlConnection sqlcon = new SqlConnection(constr);
string product_name = textBox1.Text;
string product_description = richTextBox1.Text;
string query = "updateproduct";
SqlCommand scmd = new SqlCommand(query, sqlcon);
scmd.CommandType = CommandType.StoredProcedure;
scmd.Parameters.AddWithValue("@id",myid);
// MessageBox.Show(myid.ToString());
scmd.Parameters.AddWithValue("@names", product_name);
scmd.Parameters.AddWithValue("@description", richTextBox1.Text);

try
{
sqlcon.Open();
scmd.ExecuteNonQuery();
MessageBox.Show("Data Updated");
}
catch (SqlException se)
{
MessageBox.Show(se.Message);
}
sqlcon.Close();
}

private void button5_Click(object sender, EventArgs e)
{
string constr = "Data Source=NOBEL-PC\\SQLEXPRESS;Initial Catalog=db_ecommerce;user id=rupak;password=rupak";
SqlConnection sqlcon = new SqlConnection(constr);
SqlCommand scmd = new SqlCommand("selectproduct",sqlcon);
scmd.CommandType = CommandType.StoredProcedure;

try
{
sqlcon.Open();
SqlDataReader r = scmd.ExecuteReader();
while (r.Read())
{
ListViewItem lvt = new ListViewItem();
lvt.Tag = r["id"];
lvt.Text = r["product_name"].ToString();
lvt.SubItems.Add(r["product_desc"].ToString());
listView1.Items.Add(lvt);
}
}
catch (SqlException se)
{
MessageBox.Show(se.Message);
}
sqlcon.Close();


}

private void button4_Click(object sender, EventArgs e)
{
string constr = "Data Source=NOBEL-PC\\SQLEXPRESS;Initial Catalog=db_ecommerce;user id=rupak;password=rupak";
SqlConnection sqlcon = new SqlConnection(constr);
string query = "deleteproduct";
SqlCommand scmd = new SqlCommand(query, sqlcon);
scmd.CommandType = CommandType.StoredProcedure;
scmd.Parameters.AddWithValue("@id", myid);
try
{
sqlcon.Open();
scmd.ExecuteNonQuery();
MessageBox.Show("Data Deleted");
}
catch (SqlException se)
{
MessageBox.Show(se.Message);
}
sqlcon.Close();
}
}
}

delete with the use of stored procedure code in .net and sql

private void button4_Click(object sender, EventArgs e)
{
string constr = "Data Source=NOBEL-PC\\SQLEXPRESS;Initial Catalog=db_ecommerce;user id=rupak;password=rupak";
SqlConnection sqlcon = new SqlConnection(constr);
string query = "deleteproduct";
SqlCommand scmd = new SqlCommand(query, sqlcon);
scmd.CommandType = CommandType.StoredProcedure;
scmd.Parameters.AddWithValue("@id", myid);
try
{
sqlcon.Open();
scmd.ExecuteNonQuery();
MessageBox.Show("Data Deleted");
}
catch (SqlException se)
{
MessageBox.Show(se.Message);
}
sqlcon.Close();
}

Update code in .net using stored procedure

private void button2_Click(object sender, EventArgs e)
{
string constr = "Data Source=NOBEL-PC\\SQLEXPRESS;Initial Catalog=db_ecommerce;user id=rupak;password=rupak";
SqlConnection sqlcon = new SqlConnection(constr);
string product_name = textBox1.Text;
string product_description = richTextBox1.Text;
string query = "updateproduct";
SqlCommand scmd = new SqlCommand(query, sqlcon);
scmd.CommandType = CommandType.StoredProcedure;
scmd.Parameters.AddWithValue("@id",myid);
// MessageBox.Show(myid.ToString());
scmd.Parameters.AddWithValue("@names", product_name);
scmd.Parameters.AddWithValue("@description", richTextBox1.Text);

try
{
sqlcon.Open();
scmd.ExecuteNonQuery();
MessageBox.Show("Data Updated");
}
catch (SqlException se)
{
MessageBox.Show(se.Message);
}
sqlcon.Close();
}

Click in the listView and type the code to see data from the richTextBox and textbox

private void listView1_SelectedIndexChanged(object sender, EventArgs e)
{

if (listView1.SelectedItems.Count > 0)
{
ListViewItem lv = listView1.SelectedItems[0];
myid=Convert.ToInt32(lv.Tag);
textBox1.Text = lv.Text;
richTextBox1.Text = lv.SubItems[1].Text;
}
}

button code to insert data into database with the use of store procedure

button code to insert data into database with the use of store procedure .net code

private void button1_Click(object sender, EventArgs e)
{
string constr = "Data Source=thismaydiffer\\SQLEXPRESS;Initial Catalog=db_name;user id=usernamerupak;password=userpasswordrupak";
SqlConnection sqlcon = new SqlConnection(constr);
SqlCommand scmd = new SqlCommand("productinsertion", sqlcon);
scmd.CommandType = CommandType.StoredProcedure;
scmd.Parameters.AddWithValue("@names", textBox1.Text);
scmd.Parameters.AddWithValue("@description", richTextBox1.Text);
try
{
sqlcon.Open();
scmd.ExecuteNonQuery();
MessageBox.Show("Data Inserted");
}
catch (SqlException se)
{
MessageBox.Show(se.Message);
}
sqlcon.Close();

}