using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text.RegularExpressions;
using MySql.Data;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using System.IO;
public partial class Treeview : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
MySqlDataAdapter daParentNode; //MYSQL Data Adapter For Parent Node
DataSet dsParent; //Data Set For Parent Node
MySqlDataAdapter daChild_1_Node; //MYSQL Data Adapter For Child_1 Node
DataSet dsChild_1; //Data Set For Child_1 Node
MySqlDataAdapter daChild_2_Node; //MYSQL Data Adapter For Child_1 Node
DataSet dsChild_2; //Data Set For Child_1 Node
//Get the data in DA
daParentNode = new MySqlDataAdapter("Select * from tbl_ParentNode", MyConn);
dsParent = new DataSet();
//fill the DS with DA
daParentNode.Fill(dsParent);
if (TreeView1.Nodes.Count == 0)
{
for (int i = 0; i <= dsParent.Tables[0].Rows.Count - 1; i++) //Loop through the data till we find the child nodes
{
//Create a new child node node
TreeNode pnode = new TreeNode();
pnode.Value = dsParent.Tables[0].Rows[i]["ParaentNode_ID"].ToString();
pnode.Text = dsParent.Tables[0].Rows[i]["ParaentNode"].ToString();
// set the blank ULR for Parent node
pnode.NavigateUrl = "Paraent Node URL";
this.NodeTree.Nodes.Add(pnode); // Add child node to parent node
daChild_1_Node = new MySqlDataAdapter("select * from Child1", MyConn);
dsChild_1 = new DataSet();
daChild_1_Node.Fill(dsChild_1);
for (int j = 0; j <= dsChild_1.Tables[0].Rows.Count - 1; j++)
{
TreeNode child1 = new TreeNode();
child1.Value = dsChild_1.Tables[0].Rows[j]["Child1_ID"].ToString();
child1.Text = dsChild_1.Tables[0].Rows[j]["Child1"].ToString();
// set the blank ULR for Child node
child.NavigateUrl = "Chlid1 URL";
pnode.ChildNodes.Add(child1);
daChild_2_Node = new MySqlDataAdapter("select * from Child2", MyConn);
dsChild_2 = new DataSet();
daChild_2_Node.Fill(dsChild_2);
for (int x = 0; x <= dsChild_2.Tables[0].Rows.Count - 1; x++)
{
TreeNode child_2 = new TreeNode();
child_2.Value = dsChild_2.Tables[0].Rows[x]["Child1_ID"].ToString();
child_2.Text = dsChild_2.Tables[0].Rows[x]["Child2"].ToString();
// set the blank ULR for child_1 node
child_2.NavigateUrl = "Child2 URL";
child1.ChildNodes.Add(child_2);
}
}
}
TreeView1.CheckedNodes.Add(pnode);
}
}
}
Friday, October 22, 2010
Update All fields in a table on a given condition (VBA)
Given below function is used to update All fields in a table on a given condition.
Sub ListTableFields(TableName As String)
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field
Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr(TableName)
For Each fldCurr In tdfCurr.Fields
DoCmd.SetWarnings False
If fldCurr.Type = 10 Then
DoCmd.RunSQL ("Update " & TableName & " Set " & fldCurr.Name & " =NEw Values Where " & fldCurr.Name & "=Value To be updated ")
End If
Next fldCurr
Set fldCurr = Nothing
Set tdfCurr = Nothing
Set dbCurr = Nothing
End Sub
Sub ListTableFields(TableName As String)
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field
Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr(TableName)
For Each fldCurr In tdfCurr.Fields
DoCmd.SetWarnings False
If fldCurr.Type = 10 Then
DoCmd.RunSQL ("Update " & TableName & " Set " & fldCurr.Name & " =NEw Values Where " & fldCurr.Name & "=Value To be updated ")
End If
Next fldCurr
Set fldCurr = Nothing
Set tdfCurr = Nothing
Set dbCurr = Nothing
End Sub
Friday, September 24, 2010
Inheritance and Polymorphism - OOPS
Inheritance and Polymorphism
In this article, we will discuss two important concepts of object-oriented programming — inheritance and polymorphism. Inheritance permits you to use the existing features of a class as it is and add a few of your own. Instead of using all the features of an existing class on an as-is-where-is basis, we can also override a few of them and provide our own implementation of them.
For example, a class that can create a window may already exist, which we can extend to create a button, a list box or a combo box. While extending a class, the existing class remains unchanged. The new class is called ‘derived class’, whereas, the existing class is called ‘base class’. The main advantage of inheritance is code reusability. The code reusability is of great help in the case of distributing class libraries. A programmer can use a class created by another person or company, and, without modifying it, derive other classes from it. C# goes one step further and allows cross language inheritance too.
Polymorphism and inheritance go hand in hand. Polymorphism implies the existence of an entity in different forms. Polymorphism allows classes to provide different implementations of a method that can be called in the same way. Polymorphism can be of three types—inheritance polymorphism, interface polymorphism and polymorphism through abstract classes.
Let us first write a program that shows inheritance at work. Suppose a bank has a very generic class called account that allows debit and credit transactions on an account. A bank has to maintain various accounts like savings bank account, fixed deposit account, current account, etc. Every account has a few unique features. For example, a savings account can be operated only ten times in a month. Similarly, it needs to have a minimum balance against it. On the other hand, a current account allows unlimited transactions to be performed. So we can create more specific classes to maintain different kinds of accounts from the same account class. These classes can use the basic functionality of the account class and add their own account-specific functionality. Following code shows the base class account and derived classes savingaccount and currentaccount.
class account
{
protected string name ;
protected float balance ;
public account ( string n, float b )
{
name = n ;
balance = b ;
}
public void deposit ( float amt )
{
balance += amt ;
}
public void withdraw ( float amt )
{
balance -= amt ;
}
public void display( )
{
Console.WriteLine ( “Name: {0} Balance: {1}”, name, balance ) ;
}
}
class savingaccount : account
{
static int accno = 1000 ;
int trans ;
public savingaccount ( string s, float b ) : base ( s, b )
{
trans = 0 ;
accno++ ;
}
public void withdraw ( float amt )
{
if ( trans >= 10 )
{
Console.WriteLine (“Number of transactions exceed 10” ) ;
return ;
}
if ( balance - amt < 500 )
Console.WriteLine ( “Below minimum balance” ) ;
else
{
base.withdraw ( amt ) ;
trans++ ;
}
}
public void deposit ( float amt )
{
if ( trans >= 10 )
{
Console.WriteLine ( “Number of transactions exceed 10” ) ;
return;
}
base.deposit ( amt ) ;
trans++ ;
}
public void display( )
{
Console.WriteLine ( “Name: {0} Account no.: {1} Balance: {2} ”,
name, accno, balance ) ;
}
}
class currentaccount : account
{
static int accno = 1000 ;
public currentaccount ( string s, float b ) : base ( s, b )
{
accno++ ;
}
public void withdraw ( float amt )
{
if ( balance - amt < 0 )
Console.WriteLine ( “No balance in account” ) ;
else
balance -= amt ;
}
public void display( )
{
Console.WriteLine ( “Name: {0} Account no.: {1} Balance: {2} ”,
name, accno, balance ) ;
}
}
The savingaccount class has two data members—accno that stores account number, and trans that keeps track of the number of transactions. We can create an object of savingaccount class as shown below.
savingaccount s = new savingaccount ( “Amar”, 5600.00f ) ;
From the constructor of savingaccount class we have called the two-argument constructor of the account class using the base keyword and passed the name and balance to this constructor using which the data member’s name and balance are initialised.
We can write our own definition of a method that already exists in a base class. This is called method overriding. We have overridden the deposit( ) and withdraw( ) methods in the savingaccount class so that we can make sure that each account maintains a minimum balance of Rs. 500 and the total number of transactions do not exceed 10. From these methods we have called the base class’s methods to update the balance using the base keyword. We have also overridden the display( ) method to display additional information, i.e. account number.
Working of currentaccount class is more or less similar to that of savingaccount class.
Using the derived class's object, if we call a method that is not overridden in the derived class, the base class method gets executed. Using derived class's object we can call base class's methods, but the reverse is not allowed.
Unlike C++, C# does not support multiple inheritance. So, in C# every class has exactly one base class.
Now, suppose we declare reference to the base class and store in it the address of instance of derived class as shown below.
account a1 = new savingaccount ( “Amar”, 5600.00f ) ;
account a2 = new currentaccount ( “MyCompany Pvt. Ltd.”, 126000.00f) ;
Such a situation arises when we have to decide at run-time a method of which class in a class hierarchy should get called. Using a1 and a2, suppose we call the method display( ), ideally the method of derived class should get called. But it is the method of base class that gets called. This is because the compiler considers the type of reference (account in this case) and resolves the method call. So, to call the proper method we must make a small change in our program. We must use the virtual keyword while defining the methods in base class as shown below.
public virtual void display( )
{
}
We must declare the methods as virtual if they are going to be overridden in derived class. To override a virtual method in derived classes we must use the override keyword as given below.
public override void display( )
{
}
Now it is ensured that when we call the methods using upcasted reference, it is the derived class's method that would get called. Actually, when we declare a virtual method, while calling it, the compiler considers the contents of the reference rather than its type.
If we don't want to override base class's virtual method, we can declare it with new modifier in derived class. The new modifier indicates that the method is new to this class and is not an override of a base class method.
Abstract classes and methods
If you analyse the above program carefully, you will notice that the account class is so general that we would
seldom require calling its methods directly. Rather, we would always override them in derived class and call the
derived class's methods. So, instead of defining the functionality in account class we can design the account class in such a way that it would only specify what functionality the derived classes should have. So, an abstract class always serves as the base class for other classes. We can achieve this by declaring the class as abstract.
abstract class account
{
abstract public void deposit( ) ;
abstract public void withdraw( ) ;
abstract public void display( ) ;
}
An abstract method does not have a definition in base class. As such, it is similar to a pure virtual function of C++. We cannot instantiate objects of abstract classes. A class that inherits an abstract class has to define all the abstract methods declared in the class.
Interfaces
Polymorphism is also achieved through interfaces. Like abstract classes, interfaces also describe the methods that a class needs to implement. The difference between abstract classes and interfaces is that abstract classes always act as a base class of the related classes in the class hierarchy. For example, consider a hierarchy-car and truck classes derived from four-wheeler class; the classes two-wheeler and four-wheeler derived from an abstract class vehicle. So, the class 'vehicle' is the base class in the class hierarchy. On the other hand dissimilar classes can implement one interface. For example, there is an interface that compares two objects. This interface can be implemented by the classes like box, person and string, which are unrelated to each other.
C# allows multiple interface inheritance. It means that a class can implement more than one interface.
The methods declared in an interface are implicitly abstract. If a class implements an interface, it becomes mandatory for the class to override all the methods declared in the interface, otherwise the derived class would become abstract.
One last thing. Be careful while designing an interface. Because, once an interface is published, we cannot change it. We cannot even add a method because all the classes that implement this interface will have to implement this method.
Source : http://www.expresscomputeronline.com/20021118/techspace2.shtml
In this article, we will discuss two important concepts of object-oriented programming — inheritance and polymorphism. Inheritance permits you to use the existing features of a class as it is and add a few of your own. Instead of using all the features of an existing class on an as-is-where-is basis, we can also override a few of them and provide our own implementation of them.
For example, a class that can create a window may already exist, which we can extend to create a button, a list box or a combo box. While extending a class, the existing class remains unchanged. The new class is called ‘derived class’, whereas, the existing class is called ‘base class’. The main advantage of inheritance is code reusability. The code reusability is of great help in the case of distributing class libraries. A programmer can use a class created by another person or company, and, without modifying it, derive other classes from it. C# goes one step further and allows cross language inheritance too.
Polymorphism and inheritance go hand in hand. Polymorphism implies the existence of an entity in different forms. Polymorphism allows classes to provide different implementations of a method that can be called in the same way. Polymorphism can be of three types—inheritance polymorphism, interface polymorphism and polymorphism through abstract classes.
Let us first write a program that shows inheritance at work. Suppose a bank has a very generic class called account that allows debit and credit transactions on an account. A bank has to maintain various accounts like savings bank account, fixed deposit account, current account, etc. Every account has a few unique features. For example, a savings account can be operated only ten times in a month. Similarly, it needs to have a minimum balance against it. On the other hand, a current account allows unlimited transactions to be performed. So we can create more specific classes to maintain different kinds of accounts from the same account class. These classes can use the basic functionality of the account class and add their own account-specific functionality. Following code shows the base class account and derived classes savingaccount and currentaccount.
class account
{
protected string name ;
protected float balance ;
public account ( string n, float b )
{
name = n ;
balance = b ;
}
public void deposit ( float amt )
{
balance += amt ;
}
public void withdraw ( float amt )
{
balance -= amt ;
}
public void display( )
{
Console.WriteLine ( “Name: {0} Balance: {1}”, name, balance ) ;
}
}
class savingaccount : account
{
static int accno = 1000 ;
int trans ;
public savingaccount ( string s, float b ) : base ( s, b )
{
trans = 0 ;
accno++ ;
}
public void withdraw ( float amt )
{
if ( trans >= 10 )
{
Console.WriteLine (“Number of transactions exceed 10” ) ;
return ;
}
if ( balance - amt < 500 )
Console.WriteLine ( “Below minimum balance” ) ;
else
{
base.withdraw ( amt ) ;
trans++ ;
}
}
public void deposit ( float amt )
{
if ( trans >= 10 )
{
Console.WriteLine ( “Number of transactions exceed 10” ) ;
return;
}
base.deposit ( amt ) ;
trans++ ;
}
public void display( )
{
Console.WriteLine ( “Name: {0} Account no.: {1} Balance: {2} ”,
name, accno, balance ) ;
}
}
class currentaccount : account
{
static int accno = 1000 ;
public currentaccount ( string s, float b ) : base ( s, b )
{
accno++ ;
}
public void withdraw ( float amt )
{
if ( balance - amt < 0 )
Console.WriteLine ( “No balance in account” ) ;
else
balance -= amt ;
}
public void display( )
{
Console.WriteLine ( “Name: {0} Account no.: {1} Balance: {2} ”,
name, accno, balance ) ;
}
}
The savingaccount class has two data members—accno that stores account number, and trans that keeps track of the number of transactions. We can create an object of savingaccount class as shown below.
savingaccount s = new savingaccount ( “Amar”, 5600.00f ) ;
From the constructor of savingaccount class we have called the two-argument constructor of the account class using the base keyword and passed the name and balance to this constructor using which the data member’s name and balance are initialised.
We can write our own definition of a method that already exists in a base class. This is called method overriding. We have overridden the deposit( ) and withdraw( ) methods in the savingaccount class so that we can make sure that each account maintains a minimum balance of Rs. 500 and the total number of transactions do not exceed 10. From these methods we have called the base class’s methods to update the balance using the base keyword. We have also overridden the display( ) method to display additional information, i.e. account number.
Working of currentaccount class is more or less similar to that of savingaccount class.
Using the derived class's object, if we call a method that is not overridden in the derived class, the base class method gets executed. Using derived class's object we can call base class's methods, but the reverse is not allowed.
Unlike C++, C# does not support multiple inheritance. So, in C# every class has exactly one base class.
Now, suppose we declare reference to the base class and store in it the address of instance of derived class as shown below.
account a1 = new savingaccount ( “Amar”, 5600.00f ) ;
account a2 = new currentaccount ( “MyCompany Pvt. Ltd.”, 126000.00f) ;
Such a situation arises when we have to decide at run-time a method of which class in a class hierarchy should get called. Using a1 and a2, suppose we call the method display( ), ideally the method of derived class should get called. But it is the method of base class that gets called. This is because the compiler considers the type of reference (account in this case) and resolves the method call. So, to call the proper method we must make a small change in our program. We must use the virtual keyword while defining the methods in base class as shown below.
public virtual void display( )
{
}
We must declare the methods as virtual if they are going to be overridden in derived class. To override a virtual method in derived classes we must use the override keyword as given below.
public override void display( )
{
}
Now it is ensured that when we call the methods using upcasted reference, it is the derived class's method that would get called. Actually, when we declare a virtual method, while calling it, the compiler considers the contents of the reference rather than its type.
If we don't want to override base class's virtual method, we can declare it with new modifier in derived class. The new modifier indicates that the method is new to this class and is not an override of a base class method.
Abstract classes and methods
If you analyse the above program carefully, you will notice that the account class is so general that we would
seldom require calling its methods directly. Rather, we would always override them in derived class and call the
derived class's methods. So, instead of defining the functionality in account class we can design the account class in such a way that it would only specify what functionality the derived classes should have. So, an abstract class always serves as the base class for other classes. We can achieve this by declaring the class as abstract.
abstract class account
{
abstract public void deposit( ) ;
abstract public void withdraw( ) ;
abstract public void display( ) ;
}
An abstract method does not have a definition in base class. As such, it is similar to a pure virtual function of C++. We cannot instantiate objects of abstract classes. A class that inherits an abstract class has to define all the abstract methods declared in the class.
Interfaces
Polymorphism is also achieved through interfaces. Like abstract classes, interfaces also describe the methods that a class needs to implement. The difference between abstract classes and interfaces is that abstract classes always act as a base class of the related classes in the class hierarchy. For example, consider a hierarchy-car and truck classes derived from four-wheeler class; the classes two-wheeler and four-wheeler derived from an abstract class vehicle. So, the class 'vehicle' is the base class in the class hierarchy. On the other hand dissimilar classes can implement one interface. For example, there is an interface that compares two objects. This interface can be implemented by the classes like box, person and string, which are unrelated to each other.
C# allows multiple interface inheritance. It means that a class can implement more than one interface.
The methods declared in an interface are implicitly abstract. If a class implements an interface, it becomes mandatory for the class to override all the methods declared in the interface, otherwise the derived class would become abstract.
One last thing. Be careful while designing an interface. Because, once an interface is published, we cannot change it. We cannot even add a method because all the classes that implement this interface will have to implement this method.
Source : http://www.expresscomputeronline.com/20021118/techspace2.shtml
The ADO.NET Data Architecture
The ADO.NET Data Architecture
Data Access in ADO.NET relies on two components: DataSet and Data Provider.
DataSet
The dataset is a disconnected, in-memory representation of data. It can be considered as a local copy of the relevant portions of the database. The DataSet is persisted in memory and the data in it can be manipulated and updated independent of the database. When the use of this DataSet is finished, changes can be made back to the central database for updating. The data in DataSet can be loaded from any valid data source like Microsoft SQL server database, an Oracle database or from a Microsoft Access database.
Data Provider
The Data Provider is responsible for providing and maintaining the connection to the database. A DataProvider is a set of related components that work together to provide data in an efficient and performance driven manner. The .NET Framework currently comes with two DataProviders: the SQL Data Provider which is designed only to work with Microsoft's SQL Server 7.0 or later and the OleDb DataProvider which allows us to connect to other types of databases like Access and Oracle. Each DataProvider consists of the following component classes:
The Connection object which provides a connection to the database
The Command object which is used to execute a command
The DataReader object which provides a forward-only, read only, connected recordset
The DataAdapter object which populates a disconnected DataSet with data and performs update
Data access with ADO.NET can be summarized as follows:
A connection object establishes the connection for the application with the database. The command object provides direct execution of the command to the database. If the command returns more than a single value, the command object returns a DataReader to provide the data. Alternatively, the DataAdapter can be used to fill the Dataset object. The database can be updated using the command object or the DataAdapter.
![]()
Component classes that make up the Data Providers
The Connection Object
The Connection object creates the connection to the database. Microsoft Visual Studio .NET provides two types of Connection classes: the SqlConnection object, which is designed specifically to connect to Microsoft SQL Server 7.0 or later, and the OleDbConnection object, which can provide connections to a wide range of database types like Microsoft Access and Oracle. The Connection object contains all of the information required to open a connection to the database.
The Command Object
The Command object is represented by two corresponding classes: SqlCommand and OleDbCommand. Command objects are used to execute commands to a database across a data connection. The Command objects can be used to execute stored procedures on the database, SQL commands, or return complete tables directly. Command objects provide three methods that are used to execute commands on the database:
ExecuteNonQuery: Executes commands that have no return values such as INSERT, UPDATE or DELETE
ExecuteScalar: Returns a single value from a database query
ExecuteReader: Returns a result set by way of a DataReader object
The DataReader Object
The DataReader object provides a forward-only, read-only, connected stream recordset from a database. Unlike other components of the Data Provider, DataReader objects cannot be directly instantiated. Rather, the DataReader is returned as the result of the Command object's ExecuteReader method. The SqlCommand.ExecuteReader method returns a SqlDataReader object, and the OleDbCommand.ExecuteReader method returns an OleDbDataReader object. The DataReader can provide rows of data directly to application logic when you do not need to keep the data cached in memory. Because only one row is in memory at a time, the DataReader provides the lowest overhead in terms of system performance but requires the exclusive use of an open Connection object for the lifetime of the DataReader.
The DataAdapter Object
The DataAdapter is the class at the core of ADO .NET's disconnected data access. It is essentially the middleman facilitating all communication between the database and a DataSet. The DataAdapter is used either to fill a DataTable or DataSet with data from the database with it's Fill method. After the memory-resident data has been manipulated, the DataAdapter can commit the changes to the database by calling the Update method. The DataAdapter provides four properties that represent database commands:
SelectCommand
InsertCommand
DeleteCommand
UpdateCommand
When the Update method is called, changes in the DataSet are copied back to the database and the appropriate InsertCommand, DeleteCommand, or UpdateCommand is executed.
Source : http://www.startvbdotnet.com/ado/default.aspx
Data Access in ADO.NET relies on two components: DataSet and Data Provider.
DataSet
The dataset is a disconnected, in-memory representation of data. It can be considered as a local copy of the relevant portions of the database. The DataSet is persisted in memory and the data in it can be manipulated and updated independent of the database. When the use of this DataSet is finished, changes can be made back to the central database for updating. The data in DataSet can be loaded from any valid data source like Microsoft SQL server database, an Oracle database or from a Microsoft Access database.
Data Provider
The Data Provider is responsible for providing and maintaining the connection to the database. A DataProvider is a set of related components that work together to provide data in an efficient and performance driven manner. The .NET Framework currently comes with two DataProviders: the SQL Data Provider which is designed only to work with Microsoft's SQL Server 7.0 or later and the OleDb DataProvider which allows us to connect to other types of databases like Access and Oracle. Each DataProvider consists of the following component classes:
The Connection object which provides a connection to the database
The Command object which is used to execute a command
The DataReader object which provides a forward-only, read only, connected recordset
The DataAdapter object which populates a disconnected DataSet with data and performs update
Data access with ADO.NET can be summarized as follows:
A connection object establishes the connection for the application with the database. The command object provides direct execution of the command to the database. If the command returns more than a single value, the command object returns a DataReader to provide the data. Alternatively, the DataAdapter can be used to fill the Dataset object. The database can be updated using the command object or the DataAdapter.
Component classes that make up the Data Providers
The Connection Object
The Connection object creates the connection to the database. Microsoft Visual Studio .NET provides two types of Connection classes: the SqlConnection object, which is designed specifically to connect to Microsoft SQL Server 7.0 or later, and the OleDbConnection object, which can provide connections to a wide range of database types like Microsoft Access and Oracle. The Connection object contains all of the information required to open a connection to the database.
The Command Object
The Command object is represented by two corresponding classes: SqlCommand and OleDbCommand. Command objects are used to execute commands to a database across a data connection. The Command objects can be used to execute stored procedures on the database, SQL commands, or return complete tables directly. Command objects provide three methods that are used to execute commands on the database:
ExecuteNonQuery: Executes commands that have no return values such as INSERT, UPDATE or DELETE
ExecuteScalar: Returns a single value from a database query
ExecuteReader: Returns a result set by way of a DataReader object
The DataReader Object
The DataReader object provides a forward-only, read-only, connected stream recordset from a database. Unlike other components of the Data Provider, DataReader objects cannot be directly instantiated. Rather, the DataReader is returned as the result of the Command object's ExecuteReader method. The SqlCommand.ExecuteReader method returns a SqlDataReader object, and the OleDbCommand.ExecuteReader method returns an OleDbDataReader object. The DataReader can provide rows of data directly to application logic when you do not need to keep the data cached in memory. Because only one row is in memory at a time, the DataReader provides the lowest overhead in terms of system performance but requires the exclusive use of an open Connection object for the lifetime of the DataReader.
The DataAdapter Object
The DataAdapter is the class at the core of ADO .NET's disconnected data access. It is essentially the middleman facilitating all communication between the database and a DataSet. The DataAdapter is used either to fill a DataTable or DataSet with data from the database with it's Fill method. After the memory-resident data has been manipulated, the DataAdapter can commit the changes to the database by calling the Update method. The DataAdapter provides four properties that represent database commands:
SelectCommand
InsertCommand
DeleteCommand
UpdateCommand
When the Update method is called, changes in the DataSet are copied back to the database and the appropriate InsertCommand, DeleteCommand, or UpdateCommand is executed.
Source : http://www.startvbdotnet.com/ado/default.aspx
Thursday, September 23, 2010
ADO Recordset Find method
The Find method searches for a record in a Recordset that satisfies a specified criteria. If the search is successful, the record pointer will point to the first found record.
Note: A current row position (like MoveFirst) must be set before calling this method, otherwise an error will occur.
Syntax : recordset.Find Criteria, SkipRecords, SearchDirection, Start
The Find method is used to search a Recordset for a Record that matches the search criteria (a search string). This method will work if the Recordset supports bookmarks. If the search is successful, the current record pointer will be moved to point to the first Record that matches. If the search fails, the Recordset will point to either EOF or BOF.
There is one mandatory and three optional parameters.
The mandatory Criteria parameter is a string that defines the search criteria. This string must contain one field (column) name, one comparison operator, and a search value.
You can only search on one field (column).
The comparison operators in Criteria can only be one of the following:
= > >= < <= <> LIKE
You cannot use OR or AND.
The value in Criteria can be a date, number, or string. If the value is a string, it must be enclosed (delimited) within a pair of single quotes ("State = ' Tennessee' ") or a pair of pound signs ("State = #Tennessee# "). If the value is a date, it must be enclosed (delimited) within a pair of pound signs ("Birthdate = #6/26/1943# "). Numbers are not delimited ("Age = 104").
If you are using the LIKE operator, you can also use the asterisk * wildcard either after the value in Criteria or before and after the value in Criteria ( "LastName LIKE ' * stein * ' " or "State = ' T * ' ). Some providers also support using the % and _ wildcards.
The optional SkipRecords parameter is a long value that specifies how many records beyond the current record to skip to before starting the search. The default is zero which means that the search starts at the current record.
The optional SearchDirection parameter is one of the SearchDirectionEnum constants that specify which direction the search should proceed, either forward or backward. If no matching record is found for a forward search, the record pointer is set at EOF. If no matching record is found for a backward search, the record pointer is set at BOF.
Source : http://www.devguru.com/technologies/ado/quickref/recordset_find.html
Note: A current row position (like MoveFirst) must be set before calling this method, otherwise an error will occur.
Syntax : recordset.Find Criteria, SkipRecords, SearchDirection, Start
The Find method is used to search a Recordset for a Record that matches the search criteria (a search string). This method will work if the Recordset supports bookmarks. If the search is successful, the current record pointer will be moved to point to the first Record that matches. If the search fails, the Recordset will point to either EOF or BOF.
There is one mandatory and three optional parameters.
The mandatory Criteria parameter is a string that defines the search criteria. This string must contain one field (column) name, one comparison operator, and a search value.
You can only search on one field (column).
The comparison operators in Criteria can only be one of the following:
= > >= < <= <> LIKE
You cannot use OR or AND.
The value in Criteria can be a date, number, or string. If the value is a string, it must be enclosed (delimited) within a pair of single quotes ("State = ' Tennessee' ") or a pair of pound signs ("State = #Tennessee# "). If the value is a date, it must be enclosed (delimited) within a pair of pound signs ("Birthdate = #6/26/1943# "). Numbers are not delimited ("Age = 104").
If you are using the LIKE operator, you can also use the asterisk * wildcard either after the value in Criteria or before and after the value in Criteria ( "LastName LIKE ' * stein * ' " or "State = ' T * ' ). Some providers also support using the % and _ wildcards.
The optional SkipRecords parameter is a long value that specifies how many records beyond the current record to skip to before starting the search. The default is zero which means that the search starts at the current record.
The optional SearchDirection parameter is one of the SearchDirectionEnum constants that specify which direction the search should proceed, either forward or backward. If no matching record is found for a forward search, the record pointer is set at EOF. If no matching record is found for a backward search, the record pointer is set at BOF.
Source : http://www.devguru.com/technologies/ado/quickref/recordset_find.html
ADO Sort Property
ADO Sort Property
Most of the time required the data in record set to be sorted in specific order. We can achieve this by using ‘Sort’ Property of record set.
The Sort property sets or returns a string value that specifies the field names in the Recordset to sort on. Each field name must be separated by a comma. To choose the sort order, you can specify ASC (ascending order), or DESC (descending order) after the field name. Default is ASC.
E.g.
1. rs.Sort="City_Name,Company_Name"
2. rs.Sort="City_Name Desc,Company_Name"
Most of the time required the data in record set to be sorted in specific order. We can achieve this by using ‘Sort’ Property of record set.
The Sort property sets or returns a string value that specifies the field names in the Recordset to sort on. Each field name must be separated by a comma. To choose the sort order, you can specify ASC (ascending order), or DESC (descending order) after the field name. Default is ASC.
E.g.
1. rs.Sort="City_Name,Company_Name"
2. rs.Sort="City_Name Desc,Company_Name"
ADO Recordset Filter property
The Filter property sets or returns a variant that contains a filter for the data in a Recordset object. The filter allows you to select records that fit a specific criteria.
Examples of a criteria string:
rs.Filter="Lastname='abs'"
rs.Filter="Lastname='abc' AND Birthdate >= #02/18/1986#"
rs.Filter="Lastname='jems' OR Lastname='Bond'"
rs.Filter= "Lastname LIKE bond*"
rs.Filter="[Company Name]='gemsworld' OR Cost >= 250"
Examples of a criteria string:
rs.Filter="Lastname='abs'"
rs.Filter="Lastname='abc' AND Birthdate >= #02/18/1986#"
rs.Filter="Lastname='jems' OR Lastname='Bond'"
rs.Filter= "Lastname LIKE bond*"
rs.Filter="[Company Name]='gemsworld' OR Cost >= 250"
Subscribe to:
Posts (Atom)