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();

}

Way to unblock the facebook

http://m.facebook.com/
the url will direct the facebook and open it so try it and use it to unblock the facebook and use it properly and be more friendly with your friend