ADO.NET deals with accessing Databases. ItÕs a new technology that runs
within the .NET environment. It uses the concept of managed code. ADO.NET provides
access to data sources such as Microsoft SQL Server, OLE DB and XML. Applications
connect to these data sources to access, manipulate or update data. Our article
deals with Microsoft SQL server.
Writing into the database
1. Prepare the connection string, say myConnStr, which would contain the relevant
information about the data store. An example would be:
String myConnStr;
myConnStr= "User ID=sa; Initial Catalog=Northwind;" +"Data Source=mySqlServer;Password=;";
Be sure to substitute your user id, Sql Server name and password appropriately.
2. Instantiate a Connection object, say myConn as
SQLConnection myConn = new SQLConnection(myConnStr);
3. Prepare the SQL string that will be used to extract necessary data from
the data store, for example:
String mySql;
// build the sql query string
mySql = "Insert into Product values(12,"wheat",20)";
4. Instantiate a Command object by passing the connection object and the SQL
string as follows:
SQLCommand myCmd = new SQLCommand(mySql, myConn);
5. Open the Connection, as myConn.Open();
6. The final step is to execute the command with:
myCmd.ExecuteNonQuery()
DataReader - Reading from the database
The ADODataReader (or its twin SQLDataReader) fetches rows of data in streams.
It provides forward-only data streams. The DataReader object cannot be instantiated
directly. In order to create a DataReader object, we need a Command object.
To execute a Command, we require a Connection object.
Creating A DataReader Object:
The following steps are necessary to create a DataReader object.
1. Prepare the connection string, say myConnStr, which would contain the relevant
information about the data store. An example would be:
String myConnStr;
myConnStr= "User ID=sa; Initial Catalog=Northwind;" + "Data Source=mySqlServer;Password=;";
Be sure to substitute your user id, Sql Server name and password appropriately.
2. Instantiate a Connection object, say myConn as
SQLConnection myConn = new SQLConnection(myConnStr);
3. Prepare the SQL string that will be used to extract necessary data from
the data store, for example:
String mySql;
// build the sql query string
mySql = "SELECT ProductId, ProductName, UnitPrice " + "FROM Products WHERE UnitPrice > 55.00";
4. Instantiate a Command object by passing the connection object and the SQL
string as follows:
SQLCommand myCmd = new SQLCommand(mySql, myConn);
5. Open the Connection, as myConn.Open();
6. Define a reference to a DataReader object, such as
DataReader myDataReader
7. Execute the command object by passing the DataReader object’s reference,
for example,
myCmd.execute(out myDataReader);
Retrieving Data From a DataReader
After step 7 is executed, the DataReader object will be instantiated. An imaginary
cursor will be located at the top of the retrieved rows (above the first row),
and we will have to apply the DataReader.Read() method to get to the first row.
Once the cursor is located on a row, we may retrieve the values of columns
of the row by their names or ordinal positions. For example, to retrieve the
first column’s data from the current row, we may use myDataReader.GetInt32(0).
Alternatively, we may also use the name of the column, such as myDataReader["productId"].
Without further delay, we will get into an example.
Example 1.
using System.Data.SQLClient;
public class CreateDataReader
{
public static void Main()
{
String mySql;
String myConnStr;
SQLDataReader myDataReader;
// build the connection string
myConnStr = "User ID=yourUserId; Initial Catalog = northwind;" + "Data Source=YouSqlServerName;Password=yourPassword";
// build the sql query string
mySql = "SELECT ProductId, ProductName, unitprice " + "FROM Products WHERE UnitPrice > 55.00";
// instantiate the Connection and Command object
SQLConnection myConn = new SQLConnection(myConnStr);
SQLCommand myCmd = new SQLCommand(mySql, myConn);
try
{
myConn.Open();
Console.WriteLine("Opened the Connection");
myCmd.Execute(out myDataReader);
Console.WriteLine("Executed the Command, DataReader Instantiated");
Console.WriteLine();
while (myDataReader.Read())
{
Console.Write(myDataReader.GetInt32(0) + " : " +
// Same as Console.Write(myDataReader["productId"] + " : "
+
myDataReader.GetString(1) + " : " +
myDataReader.GetDecimal(2).ToString());
Console.WriteLine();
}
Console.WriteLine();
myDataReader.Close();
Console.WriteLine("DataReader Closed");
myConn.Close();
Console.WriteLine("Connection closed");
}
catch(Exception myException)
{
Console.WriteLine ("The following bad thing happened");
Console.WriteLine(myException.ToString());
}
}
}
Alternate Method - Uses Dataadap
//Connecting database
con = new SqlConnection("Data Source=mysource;Initial Catalog=mydbname;uid=sa");
//create sql adapter for the "emp" table
SqlDataAdapter sqlDa = new SqlDataAdapter("select * from emp", con);
//create dataset instance
DataSet dSet = new DataSet();
//fill the dataset
sqlDa.Fill(dSet, "emp");
//bind the data grid with the data set
dataGrid1.DataSource=dSet.Tables["emp"];
//build select command
SqlCommand selCmd = new SqlCommand("select * from emp",con);
sqlDa.SelectCommand=selCmd;
//build insert command
SqlCommand insCmd = new SqlCommand("insert into emp (Name, Age) values(@Name, @Age)",con);
insCmd.Parameters.Add("@Name", SqlDbType.NChar, 10, "Name");
insCmd.Parameters.Add("@Age", SqlDbType.Int, 4, "Age");
sqlDa.InsertCommand = insCmd;
//build update command
SqlCommand upCmd = new SqlCommand("update emp set Name=@Name, Age=@Age where No=@No",con);
upCmd.Parameters.Add("@Name", SqlDbType.NChar, 10, "Name");
upCmd.Parameters.Add("@Age", SqlDbType.Int, 4, "Age");
upCmd.Parameters.Add("@No", SqlDbType.Int, 4, "No");
sqlDa.UpdateCommand = upCmd;
//build delete command
SqlCommand delCmd = new SqlCommand("delete from emp where No=@No",con);
delCmd.Parameters.Add("@No", SqlDbType.Int, 4, "No");
sqlDa.DeleteCommand = delCmd;
//now update the data adapter with dataset.
sqlDa.Update(dSet,"emp");
0 comments:
Post a Comment