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"
ADO UpdateBatch Method
ADO UpdateBatch Method
The UpdateBatch method is used to save all changes in a Recordset to the database. This method is used when you are working on a Recordset in batch update mode.
If the save operation fails, a run-time error occurs and the errors are stored in the Errors collection.
E.g.
With rs
.ActiveConnection = CurrentProject.Connection
.Source = "Data Base Name"
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
.Open
Dim counter As Long
For counter = 0 To .RecordCount - 1
.Addnew
.MoveNext
Next
.UpdateBatch
rowsDel = counter
End With
The UpdateBatch method is used to save all changes in a Recordset to the database. This method is used when you are working on a Recordset in batch update mode.
If the save operation fails, a run-time error occurs and the errors are stored in the Errors collection.
E.g.
With rs
.ActiveConnection = CurrentProject.Connection
.Source = "Data Base Name"
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
.Open
Dim counter As Long
For counter = 0 To .RecordCount - 1
.Addnew
.MoveNext
Next
.UpdateBatch
rowsDel = counter
End With
Wednesday, September 22, 2010
RecordSet Lock Types ib VB 6.0
Lock Types
While cursor locations and cursor types specify how our data is going to be handled, the lock type property specifies how we are going to lock the underlying data to protect any changes we make and ensure they are processed. There are four different lock types, and the locktype is set in the recordset object as part of the open method (it can also be set using the LockType property of the recordset object). The four locktypes are: adLockReadOnly (default), adLockOptimistic, adLockPessimistic, and adLockBatchOptimistic. All four locktypes are available to a server-side cursor, the adLockPessimistic locktype is unavailable to a client-side
While cursor locations and cursor types specify how our data is going to be handled, the lock type property specifies how we are going to lock the underlying data to protect any changes we make and ensure they are processed. There are four different lock types, and the locktype is set in the recordset object as part of the open method (it can also be set using the LockType property of the recordset object). The four locktypes are: adLockReadOnly (default), adLockOptimistic, adLockPessimistic, and adLockBatchOptimistic. All four locktypes are available to a server-side cursor, the adLockPessimistic locktype is unavailable to a client-side
Cursor Types
I found a very nice document on web (did not remember the exact source) describing Cursor Types in details. So sharing the same with you all.
Cursor Types
In addition to the two cursor locations, there are four cursor types, three of which are supported under Connector/ODBC:
• adOpenStatic (Client-Side)
• adOpenForwardOnly (Server-Side)
• adOpenDynamic (Server-Side)
The different cursor types support different functionality and features, and I will now discuss each one in detail. The fourth cursor type, adOpenKeySet, is not currently supported by MySQL / MyODBC.
adOpenStatic
The static cursor is the only cursor type that is currently available when using adUseClient as your cursor location. With a static cursor, the server will send the result set to the client, after which there will be no further communication from the server to the client. The client may communicate with the server to send changes back to the server. This makes the static cursor more resource-intensive for the client and less resource-intensive for the server, as the result set is stored in the client's memory instead of the server's.
If a different client makes changes to the underlying data after the query results are sent, the original client will receive no notification of the change. A static cursor is bi-directional, meaning that your application can move forwards and backwards through the recordset. The following methods are available to a recordset using a static cursor and the adLockOptimistic lock type (more on lock types later):
• AddNew
• Delete
• Find
• MoveFirst
• MovePrevious • MoveNext
• MoveLast
• Resync
• Update
• UpdateBatch
The static cursor will also show an accurate value for the RecordCount property of your recordset, and supports the getchunk and appendchunk methods for dealing with BLOB data. If you are having trouble with either of these problems, explicitly setting your connection's cursorlocation to adUseClient should solve them.
One handy feature of the static cursor is the ability to fetch data asynchronously. When data is fetched asynchronously., a separate thread is started to handle row retrieval, and your VB application can begin processing returned rows immediately. An in depth article on asynchronous data fetching is pending, but to activate this feature, simple use the adFetchAsync option during your recordset.open method call.
If you specify any cursor type other than adOpenStatic when opening a recordset with an adUseClient cursor location, it will be automatically converted to a static cursor.
adOpenForwardOnly
The adForwardOnly cursor type is the fastest performing cursortype, and also the most limited. The forward-only cursor does not support the RecordCount property, and does not support the MovePrevious methods of the recordset object.
The most efficient way to access data for display to the screen out output to a file is to use a adOpenForwardOnly cursor with a adLockReadOnly lock type when opening a recordset. This combination is often referred to as a Firehose Cursor. A firehose cursor bypasses a lot of handling code between the client and server and allows for very fast data access when moving sequentially through the resulting rows.
The following recordset methods are supported when using a forward-only cursor with an optimistic lock:
• AddNew
• Delete
• Find
• Update
• UpdateBatch
In addition, the forward-only cursor type supports non-caching queries. While an asynchronous query allows data to be worked on immediately, it offers no memory benefits when accessing large resultsets, as all rows eventually wind up in memory, taxing system resources when accessing a large number of rows, or a medium number of rows when BLOB data is involved.
With MySQL and Connector/ODBC, we can specify option 1048576 in our connection string or check off the option "Don't Cache Results" in the ODBC manager in order to specify to the ODBC driver that it should only retrieve one row at a time from the server. With this option set, memory usage on the client is limited as only one row at a time is stored in memory. With every call to the recordset's MoveNext method, the previous row is discarded and the next row is queried from the server.
adOpenDynamic
While the forward-only cursor is the most efficient of the cursor types, the dynamic cursor, specified but adOpenDynamic, is the least efficient. Because of it's inefficiency, dynamic cursor support must be manually activated by using option 32 in your connection string, or by checking "Enable Dynamic Cursor" in the ODBC manager. Without this option enabled, any cursortype other than forward-only with be automatically converted to a static cursor, with it enabled, all cursor types other than forward-only will be converted to dynamic.
Why is a dynamic cursor so slow? As there is no native support for dynamic, server-side cursors in MySQL, every call to a row-moving method(MoveNext, MovePrevious, etc.) results in the Connector/ODBC driver converting your method call to a SQL query, posting the query, and returning the resulting row. This also means that for a dynamic cursor to work properly, your underlying table needs a primary key column to determine the next/previous row with. As such, dynamic cursors are not recommended unless absolutely necessary.
The dynamic cursor supports the following recordset methods when opened with a optimistic lock:
• AddNew
• Delete
• Find
• MoveFirst
• MovePrevious
• Update
• UpdateBatch
While Dynamic cursors can be beneficial for multi-user applications, it is best to avoid them when possible, and work around multi-user issues when possible by calling the resync and requery methods when possible, and executing UPDATE queries that increment and decrement count values instead of using the recordset to do updates (i.e. rather than getting an inventory count in a recordset, incrementing it in VB, and doing a recordset.update, use the connection object to execute a query similar to UPDATE inventory SET count = count - 1 WHERE itemcode = 5)
Cursor Types
In addition to the two cursor locations, there are four cursor types, three of which are supported under Connector/ODBC:
• adOpenStatic (Client-Side)
• adOpenForwardOnly (Server-Side)
• adOpenDynamic (Server-Side)
The different cursor types support different functionality and features, and I will now discuss each one in detail. The fourth cursor type, adOpenKeySet, is not currently supported by MySQL / MyODBC.
adOpenStatic
The static cursor is the only cursor type that is currently available when using adUseClient as your cursor location. With a static cursor, the server will send the result set to the client, after which there will be no further communication from the server to the client. The client may communicate with the server to send changes back to the server. This makes the static cursor more resource-intensive for the client and less resource-intensive for the server, as the result set is stored in the client's memory instead of the server's.
If a different client makes changes to the underlying data after the query results are sent, the original client will receive no notification of the change. A static cursor is bi-directional, meaning that your application can move forwards and backwards through the recordset. The following methods are available to a recordset using a static cursor and the adLockOptimistic lock type (more on lock types later):
• AddNew
• Delete
• Find
• MoveFirst
• MovePrevious • MoveNext
• MoveLast
• Resync
• Update
• UpdateBatch
The static cursor will also show an accurate value for the RecordCount property of your recordset, and supports the getchunk and appendchunk methods for dealing with BLOB data. If you are having trouble with either of these problems, explicitly setting your connection's cursorlocation to adUseClient should solve them.
One handy feature of the static cursor is the ability to fetch data asynchronously. When data is fetched asynchronously., a separate thread is started to handle row retrieval, and your VB application can begin processing returned rows immediately. An in depth article on asynchronous data fetching is pending, but to activate this feature, simple use the adFetchAsync option during your recordset.open method call.
If you specify any cursor type other than adOpenStatic when opening a recordset with an adUseClient cursor location, it will be automatically converted to a static cursor.
adOpenForwardOnly
The adForwardOnly cursor type is the fastest performing cursortype, and also the most limited. The forward-only cursor does not support the RecordCount property, and does not support the MovePrevious methods of the recordset object.
The most efficient way to access data for display to the screen out output to a file is to use a adOpenForwardOnly cursor with a adLockReadOnly lock type when opening a recordset. This combination is often referred to as a Firehose Cursor. A firehose cursor bypasses a lot of handling code between the client and server and allows for very fast data access when moving sequentially through the resulting rows.
The following recordset methods are supported when using a forward-only cursor with an optimistic lock:
• AddNew
• Delete
• Find
• Update
• UpdateBatch
In addition, the forward-only cursor type supports non-caching queries. While an asynchronous query allows data to be worked on immediately, it offers no memory benefits when accessing large resultsets, as all rows eventually wind up in memory, taxing system resources when accessing a large number of rows, or a medium number of rows when BLOB data is involved.
With MySQL and Connector/ODBC, we can specify option 1048576 in our connection string or check off the option "Don't Cache Results" in the ODBC manager in order to specify to the ODBC driver that it should only retrieve one row at a time from the server. With this option set, memory usage on the client is limited as only one row at a time is stored in memory. With every call to the recordset's MoveNext method, the previous row is discarded and the next row is queried from the server.
adOpenDynamic
While the forward-only cursor is the most efficient of the cursor types, the dynamic cursor, specified but adOpenDynamic, is the least efficient. Because of it's inefficiency, dynamic cursor support must be manually activated by using option 32 in your connection string, or by checking "Enable Dynamic Cursor" in the ODBC manager. Without this option enabled, any cursortype other than forward-only with be automatically converted to a static cursor, with it enabled, all cursor types other than forward-only will be converted to dynamic.
Why is a dynamic cursor so slow? As there is no native support for dynamic, server-side cursors in MySQL, every call to a row-moving method(MoveNext, MovePrevious, etc.) results in the Connector/ODBC driver converting your method call to a SQL query, posting the query, and returning the resulting row. This also means that for a dynamic cursor to work properly, your underlying table needs a primary key column to determine the next/previous row with. As such, dynamic cursors are not recommended unless absolutely necessary.
The dynamic cursor supports the following recordset methods when opened with a optimistic lock:
• AddNew
• Delete
• Find
• MoveFirst
• MovePrevious
• Update
• UpdateBatch
While Dynamic cursors can be beneficial for multi-user applications, it is best to avoid them when possible, and work around multi-user issues when possible by calling the resync and requery methods when possible, and executing UPDATE queries that increment and decrement count values instead of using the recordset to do updates (i.e. rather than getting an inventory count in a recordset, incrementing it in VB, and doing a recordset.update, use the connection object to execute a query similar to UPDATE inventory SET count = count - 1 WHERE itemcode = 5)
Cursor Location
Most of the new VB programmer face problems with Cursor Location when it comes to creating connection with data base. Even I did face the problems while working with the database in my early days. I found a very nice document on web (did not remember the exact source) describing Cursor Location in details. So sharing the same with you all.
What Are Cursors?
In ADO, when we talk about cursors, we are essentially talking about a set of rows. When you execute a query that returns rows of data, such as SELECT * FROM mytable, the resulting data is handled using a cursor. A cursor can be located either on the client with the adUseClient argument, or on the server with the adUseServer argument. In addition, the are 4 types of cursor: adOpenForwardOnly, adOpenStatic, adOpenDynamic, and adOpenKeyset.
The different types and locations will be discussed in further detail below. Your choice of cursor type and cursor location will affect what you are able to do with the data you retrieve, and how changes made to the data by other users are reflected in your copy of the data.
Cursor Location
The ADODB.Connection object (the ADO object used to broker all data exchanges between the VB application and the MySQL server) has a property known as CursorLocation which is used to set/retrieve the cursor location that will be used by any recordset objects that access their data through the connection object.
The CursorLocation property can only be set while the connection is closed, and the property will be inherited by any recordset objects that access their data through the given connection object. Recordset objects can also explicitly set a cursorlocation different than the connection objects cursorlocation as long as it is set before the recordset is open. The two options available for this property are adUseClient and adUseServer, with adUseServer being the default property.
adUseServer
When using the adUseServer server-side cursorlocation, responsibility for handling the data generated by a query lies with the database server. MySQL itself does not support server-side cursors, so the data handling is actually done by the Connector / ODBC driver. The benefit of server-side cursors is that we gain access to the dynamic cursor type. This allows us to see any changes to the data that are made by other users in the data our application is accessing.
For example: let's say we are selling tickets to a concert with our application, we need to know that a given seat is available for sale in real-time to ensure we do not double-book the seat. With a server-side cursor, we can be sure that the data we are manipulating is the most current possible. In addition, we have the ability to lock the data we are working on as we edit it, to make sure our changes are going to be posted to the database successfully.
With a server-side cursor (adUseServer), we have access to the adOpenDynamic and adOpenForwardOnly cursor types, and all four of the recordset lock types, which will be discussed below.
It should be noted that using a server-side cursor, and the adOpenDynamic cursor in particular, will result in a significant performance loss, and should be avoided if at all possible. In addition, certain functionality, such as the RecordCount property of a Recordset and the GetChunk and Appendchunk function for handling BLOB data, will fail or return abnormal results when used with a server-side cursor.
adUseClient
Client-side cursors, specified with the adUseClient keyword, are handled internally by ADO. These cursors offer more functionality than their server-side counterparts, and also result in less load being placed on the server. Most advanced ADO functionality is designed for use with client-side cursors, and I personally use client-side cursors for all my applications (with one exception).
When using a client-side adUseClient cursor, only the adOpenStatic cursor is available, and we cannot use the adLockPessimistic lock type (see below).
Client-side cursors also help decrease load on our MySQL server, since with a static cursor data is sent to the client and then the server has no further communications with the client. This allows your server to scale a lot better than with server-side cursors.
What Are Cursors?
In ADO, when we talk about cursors, we are essentially talking about a set of rows. When you execute a query that returns rows of data, such as SELECT * FROM mytable, the resulting data is handled using a cursor. A cursor can be located either on the client with the adUseClient argument, or on the server with the adUseServer argument. In addition, the are 4 types of cursor: adOpenForwardOnly, adOpenStatic, adOpenDynamic, and adOpenKeyset.
The different types and locations will be discussed in further detail below. Your choice of cursor type and cursor location will affect what you are able to do with the data you retrieve, and how changes made to the data by other users are reflected in your copy of the data.
Cursor Location
The ADODB.Connection object (the ADO object used to broker all data exchanges between the VB application and the MySQL server) has a property known as CursorLocation which is used to set/retrieve the cursor location that will be used by any recordset objects that access their data through the connection object.
The CursorLocation property can only be set while the connection is closed, and the property will be inherited by any recordset objects that access their data through the given connection object. Recordset objects can also explicitly set a cursorlocation different than the connection objects cursorlocation as long as it is set before the recordset is open. The two options available for this property are adUseClient and adUseServer, with adUseServer being the default property.
adUseServer
When using the adUseServer server-side cursorlocation, responsibility for handling the data generated by a query lies with the database server. MySQL itself does not support server-side cursors, so the data handling is actually done by the Connector / ODBC driver. The benefit of server-side cursors is that we gain access to the dynamic cursor type. This allows us to see any changes to the data that are made by other users in the data our application is accessing.
For example: let's say we are selling tickets to a concert with our application, we need to know that a given seat is available for sale in real-time to ensure we do not double-book the seat. With a server-side cursor, we can be sure that the data we are manipulating is the most current possible. In addition, we have the ability to lock the data we are working on as we edit it, to make sure our changes are going to be posted to the database successfully.
With a server-side cursor (adUseServer), we have access to the adOpenDynamic and adOpenForwardOnly cursor types, and all four of the recordset lock types, which will be discussed below.
It should be noted that using a server-side cursor, and the adOpenDynamic cursor in particular, will result in a significant performance loss, and should be avoided if at all possible. In addition, certain functionality, such as the RecordCount property of a Recordset and the GetChunk and Appendchunk function for handling BLOB data, will fail or return abnormal results when used with a server-side cursor.
adUseClient
Client-side cursors, specified with the adUseClient keyword, are handled internally by ADO. These cursors offer more functionality than their server-side counterparts, and also result in less load being placed on the server. Most advanced ADO functionality is designed for use with client-side cursors, and I personally use client-side cursors for all my applications (with one exception).
When using a client-side adUseClient cursor, only the adOpenStatic cursor is available, and we cannot use the adLockPessimistic lock type (see below).
Client-side cursors also help decrease load on our MySQL server, since with a static cursor data is sent to the client and then the server has no further communications with the client. This allows your server to scale a lot better than with server-side cursors.
SQL Joins
SQL Joins
Inner join
An inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column
values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate.
When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. The result of the join can be defined as the outcome
of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B)—then return all records which
satisfy the join predicate. Actual SQL implementations normally use other approaches like a Hash join or a Sort-merge join where possible, since computing the Cartesian product
is very inefficient.
Syntax : Select * from [Table 1] Inner Join [Table 2] On [Table 1].[Match Field] = [Table 2].[Match Field]
E.g. Select * from A Inner Join B On A.ID = B.ID
Equi-join
An equi-join, also known as an equijoin, is a specific type of comparator-based join, or theta join, that uses only equality comparisons in the join-predicate. Using other
comparison operators (such as <) disqualifies a join as an equi-join. The query shown above has already provided an example of an equi-join:
Syntax : Select * from [Table 1] EQUI JOIN [Table 2] On [Table 1].[Match Field] = [Table 2].[Match Field]
E.g. Select * from A EQUI JOIN B On A.ID = B.ID
Natural join
A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the
joined tables. The resulting joined table contains only one column for each pair of equally-named columns.
Syntax : Select * from [Table 1] NATURAL JOIN [Table 2]
E.g. Select * from A NATURAL JOIN B
Cross join
A cross join, cartesian join or product provides the foundation upon which all types of inner joins operate. A cross join returns the cartesian product of the sets of records
from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement. In
other words, a cross join combines every row in B with every row in A. The number of rows in the result set will be the number of rows in A times the number of rows in B.
Thus, if A and B are two sets, then the cross join is written as A × B.
The SQL code for a cross join lists the tables for joining (FROM), but does not include any filtering join-predicate.
Syntax : SELECT * FROM [Table 1] CROSS JOIN [Table 2] Or
SELECT * FROM [Table 1], [Table 2]
Outer joins
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists.
Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).
1. Left outer join
The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any
matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each
column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join
predicate). If the left table returns one row and the right table returns more than one matching row for it, the values in the left table will be repeated for each distinct row
on the right table.
Syntax : Select * from [Table 1] LEFT OUTER JOIN [Table 2] On [Table 1].[Match Field] = [Table 2].[Match Field]
E.g. Select * from A LEFT OUTER JOIN B On A.ID = B.ID
2. Right outer joins
A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the
joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B.
A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).
Syntax : Select * from [Table 1] RIGHT OUTER JOIN [Table 2] On [Table 1].[Match Field] = [Table 2].[Match Field]
E.g. Select * from A RIGHT OUTER JOIN B On A.ID = B.ID
3. Full outer join
A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on
either side.
For example, this allows us to see each employee who is in a department and each department that has an employee, but also see each employee who is not part of a department and
each department which doesn't have an employee.
Syntax : Select * from [Table 1] FULL OUTER JOIN [Table 2] On [Table 1].[Match Field] = [Table 2].[Match Field]
E.g. Select * from A FULL OUTER JOIN B On A.ID = B.ID
Inner join
An inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column
values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate.
When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. The result of the join can be defined as the outcome
of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B)—then return all records which
satisfy the join predicate. Actual SQL implementations normally use other approaches like a Hash join or a Sort-merge join where possible, since computing the Cartesian product
is very inefficient.
Syntax : Select * from [Table 1] Inner Join [Table 2] On [Table 1].[Match Field] = [Table 2].[Match Field]
E.g. Select * from A Inner Join B On A.ID = B.ID
Equi-join
An equi-join, also known as an equijoin, is a specific type of comparator-based join, or theta join, that uses only equality comparisons in the join-predicate. Using other
comparison operators (such as <) disqualifies a join as an equi-join. The query shown above has already provided an example of an equi-join:
Syntax : Select * from [Table 1] EQUI JOIN [Table 2] On [Table 1].[Match Field] = [Table 2].[Match Field]
E.g. Select * from A EQUI JOIN B On A.ID = B.ID
Natural join
A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the
joined tables. The resulting joined table contains only one column for each pair of equally-named columns.
Syntax : Select * from [Table 1] NATURAL JOIN [Table 2]
E.g. Select * from A NATURAL JOIN B
Cross join
A cross join, cartesian join or product provides the foundation upon which all types of inner joins operate. A cross join returns the cartesian product of the sets of records
from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement. In
other words, a cross join combines every row in B with every row in A. The number of rows in the result set will be the number of rows in A times the number of rows in B.
Thus, if A and B are two sets, then the cross join is written as A × B.
The SQL code for a cross join lists the tables for joining (FROM), but does not include any filtering join-predicate.
Syntax : SELECT * FROM [Table 1] CROSS JOIN [Table 2] Or
SELECT * FROM [Table 1], [Table 2]
Outer joins
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists.
Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).
1. Left outer join
The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any
matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each
column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join
predicate). If the left table returns one row and the right table returns more than one matching row for it, the values in the left table will be repeated for each distinct row
on the right table.
Syntax : Select * from [Table 1] LEFT OUTER JOIN [Table 2] On [Table 1].[Match Field] = [Table 2].[Match Field]
E.g. Select * from A LEFT OUTER JOIN B On A.ID = B.ID
2. Right outer joins
A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the
joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B.
A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).
Syntax : Select * from [Table 1] RIGHT OUTER JOIN [Table 2] On [Table 1].[Match Field] = [Table 2].[Match Field]
E.g. Select * from A RIGHT OUTER JOIN B On A.ID = B.ID
3. Full outer join
A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on
either side.
For example, this allows us to see each employee who is in a department and each department that has an employee, but also see each employee who is not part of a department and
each department which doesn't have an employee.
Syntax : Select * from [Table 1] FULL OUTER JOIN [Table 2] On [Table 1].[Match Field] = [Table 2].[Match Field]
E.g. Select * from A FULL OUTER JOIN B On A.ID = B.ID
SQL Self Join
SQL Self Join:
A Self Join is a type of sql join which is used to join a table to itself, particularly when the table has a FOREIGN KEY that references its own PRIMARY KEY. It is necessary to ensure that the join statement defines an alias for both copies of the table to avoid column ambiguity.
The below query is an example of a self join,
SELECT a.sales_person_id, a.name, a.manager_id, b.sales_person_id, b.name
FROM sales_person a, sales_person b
WHERE a.manager_id = b.sales_person_id;
A Self Join is a type of sql join which is used to join a table to itself, particularly when the table has a FOREIGN KEY that references its own PRIMARY KEY. It is necessary to ensure that the join statement defines an alias for both copies of the table to avoid column ambiguity.
The below query is an example of a self join,
SELECT a.sales_person_id, a.name, a.manager_id, b.sales_person_id, b.name
FROM sales_person a, sales_person b
WHERE a.manager_id = b.sales_person_id;
SQL HAVING
SQL HAVING
The SQL HAVING clause is used to restrict conditionally the output of a SQL statement, by a SQL aggregate function used in your SELECT list of columns.
You can't specify criteria in a SQL WHERE clause against a column in the SELECT list for which SQL aggregate function is used. For example the following SQL statement will generate an error:
SELECT Employee, SUM (Hours)
FROM EmployeeHours
WHERE SUM (Hours) > 24
GROUP BY Employee
The SQL HAVING clause is used to do exactly this, to specify a condition for an aggregate function which is used in your query:
SELECT Employee, SUM (Hours)
FROM EmployeeHours
GROUP BY Employee
HAVING SUM (Hours) > 24
The above SQL statement will select all employees and the sum of their respective hours, as long as this sum is greater than 24. The result of the SQL
Socurce : http://www.sql-tutorial.net
The SQL HAVING clause is used to restrict conditionally the output of a SQL statement, by a SQL aggregate function used in your SELECT list of columns.
You can't specify criteria in a SQL WHERE clause against a column in the SELECT list for which SQL aggregate function is used. For example the following SQL statement will generate an error:
SELECT Employee, SUM (Hours)
FROM EmployeeHours
WHERE SUM (Hours) > 24
GROUP BY Employee
The SQL HAVING clause is used to do exactly this, to specify a condition for an aggregate function which is used in your query:
SELECT Employee, SUM (Hours)
FROM EmployeeHours
GROUP BY Employee
HAVING SUM (Hours) > 24
The above SQL statement will select all employees and the sum of their respective hours, as long as this sum is greater than 24. The result of the SQL
Socurce : http://www.sql-tutorial.net
Tuesday, September 21, 2010
Looping through all Grid view Data : ASP.Net
Scope of accessing data in a Grid view can be bifurcated into three major parts:
foreach (GridViewRow row in GridViewName.Rows)
{
//row is obect type of GridViewRow. It can be used to pass row index value too.
if (...Condition Check...)
{
Code... ;
}
}
foreach (GridViewRow row in GridViewName.Rows)
{
//row is obect type of GridViewRow. It can be used to pass row index value too.
if (...Condition Check...)
{
Code... ;
}
}
Accessing Grid view Data : ASP.Net
Scope of accessing data in a Gridview can be bifurcated into three major parts:
1. When Gridview is not in Edit mode
2. When Gridview is not in Edit mode and column is changed to Template
3. When Gridview is in Edit mode
When Gridview is not in Edit mode then the data is representation inside Gridview is as good as writing a text betting tags of a Table i.e. data is in cell.
Sample code:
GridViewName.Rows[row.RowIndex].Cells[integer value]).Text
Note: Here, ‘integer value’ is the Column Number which can be hard coded like 1,2,10, etc.
When Gridview is not in Edit mode and column is changed to Template then cell property cannot be used to access/extract data.
Note: If you make a particular column as Template then Cell property won’t work since Label controls are used to show data during non-Editable mode.
Sample code:
((Label)(GridViewName.Rows[i].FindControl("lblItem"))).Text
Note: Here, ‘i’ is the RowIndex which can be captured using method explained in 1st section.
When data is in Edit mode then all column data values are shown using different controls.
Sample Code:
((DropDownList)(GridViewName.Rows[i].FindControl("cmbName"))).SelectedValue
Note: You can use any properties of the control after using above method. You can also use column index after specifying row index instead of using FindControl to find that control in that row.
1. When Gridview is not in Edit mode
2. When Gridview is not in Edit mode and column is changed to Template
3. When Gridview is in Edit mode
When Gridview is not in Edit mode then the data is representation inside Gridview is as good as writing a text betting
Sample code:
GridViewName.Rows[row.RowIndex].Cells[integer value]).Text
Note: Here, ‘integer value’ is the Column Number which can be hard coded like 1,2,10, etc.
When Gridview is not in Edit mode and column is changed to Template then cell property cannot be used to access/extract data.
Note: If you make a particular column as Template then Cell property won’t work since Label controls are used to show data during non-Editable mode.
Sample code:
((Label)(GridViewName.Rows[i].FindControl("lblItem"))).Text
Note: Here, ‘i’ is the RowIndex which can be captured using method explained in 1st section.
When data is in Edit mode then all column data values are shown using different controls.
Sample Code:
((DropDownList)(GridViewName.Rows[i].FindControl("cmbName"))).SelectedValue
Note: You can use any properties of the control after using above method. You can also use column index after specifying row index instead of using FindControl to find that control in that row.
Capturing Row Index in ASP.Net Grid View
Sometimes you need to perform certain action on a particular row of Gridview. For that you need to find row index of that row first and in order to capture the row index you need to call RowIndex property of GridViewRow class inside any event of a control present in that particular row.
Suppose you have modified some columns of Gridview in Edit mode to some combo boxes/ dropdown lists and you want to perform certain action on that particular row selected row. Then you need to call RowIndex Property using DropDownList class. Similarly other classes for different controls are available like, LinkButton, TextBox, etc.
Sample code:
DropDownList ddl = (DropDownList)sender;
GridViewRow row = (GridViewRow)ddl.Parent.Parent;
int i = row.RowIndex;
or
LinkButton ddl = (LinkButton)sender;
GridViewRow row = (GridViewRow)ddl.Parent.Parent;
int i = row.RowIndex;
or
LinkButton ddl = (LinkButton)sender;
GridViewRow row = (GridViewRow)ddl.Parent.Parent;
int i = row.RowIndex;
Some other method to do the same:
int i = this.GridViewName.SelectedIndex;
or
int i = Convert.ToInt32(GridViewName.DataKeys[row.RowIndex].Value);
or
i = Convert.ToInt32(GridViewName.DataKeys[e.RowIndex].Value);
i = GridViewName.Rows[e.RowIndex].FindControl("ControlName ");
But, here ‘e’ should of type GridViewCommandEventArgs and not of EventArgs.
void GridViewName_AnyEvent (Object sender, GridViewCommandEventArgs e)
Suppose you have modified some columns of Gridview in Edit mode to some combo boxes/ dropdown lists and you want to perform certain action on that particular row selected row. Then you need to call RowIndex Property using DropDownList class. Similarly other classes for different controls are available like, LinkButton, TextBox, etc.
Sample code:
DropDownList ddl = (DropDownList)sender;
GridViewRow row = (GridViewRow)ddl.Parent.Parent;
int i = row.RowIndex;
or
LinkButton ddl = (LinkButton)sender;
GridViewRow row = (GridViewRow)ddl.Parent.Parent;
int i = row.RowIndex;
or
LinkButton ddl = (LinkButton)sender;
GridViewRow row = (GridViewRow)ddl.Parent.Parent;
int i = row.RowIndex;
Some other method to do the same:
int i = this.GridViewName.SelectedIndex;
or
int i = Convert.ToInt32(GridViewName.DataKeys[row.RowIndex].Value);
or
i = Convert.ToInt32(GridViewName.DataKeys[e.RowIndex].Value);
i = GridViewName.Rows[e.RowIndex].FindControl("ControlName ");
But, here ‘e’ should of type GridViewCommandEventArgs and not of EventArgs.
void GridViewName_AnyEvent (Object sender, GridViewCommandEventArgs e)
Visual Basic Array
Found a very nice document about the visual basic Array, did not remember the exact source. Just wanted to share.
Visual Basic Array
• Types of Arrays
• Fixed-Size Arrays
• Dynamic Arrays
• Retrieving the Contents of an Array
• Adding New Elements on the Fly
• Erasing an Array
• The Split Function
• The Join Function
• Multidimensional Arrays
Types of Arrays
An array is a lot like a CD rack. You know: one of those rectangular boxes with slots to slide CDs in, each above another. There are two types of Visual Basic arrays: fixed-size and dynamic.
Fixed-Size Arrays
A fixed-size array most closely matches our CD rack anology. There are a limited number of slots you can slide CDs into. Pretend you have three CDs - one by the Deftones, another by Tool, and a third by Disturbed. To fit all of these in your rack, the rack must contain at least three slots. So you declare your CD rack as having three slots:
Dim strCDRack(0 to 2) As String
You've just made a variable 'strCDRack' that contains three slots (#0, #1, and #2) and is of a String data type. Now you can insert your CDs into it:
Dim strCDRack(0 to 2) As String
strCDRack(0) = "Deftones"
strCDRack(1) = "Tool"
strCDRack(2) = "Disturbed"
Notice that each of the three new lines starts off with the variable name and then gives an element number before having a value assigned. This is like numbering the slots on your CD rack starting at 0 up to 2 and then inserting a CD into each slot.
The format for declaring an array is:
Dim Public/Private ArrayName(Subscript) As DataType
- Dim, Public, and Private declare the array and its scope. Using Dim in a procedure will make the array only available from within that procedure. Using it in the General Declarations section will make it available to all procedures in that module. Private has the same effect and should be used only at the modular level. Using Public will make the array available throughout the project.
- ArrayName is the name of the array.
- Subscript is the dimensions of the array.
- DataType is any valid data type.
-
Dynamic Arrays
The new Charlotte Church CD came out but your rack only has three slots. You don't want to throw away any of your CDs to make room for the new one so you decide to use your ultimate building skills to attach another slot. You start building:
Dim strCDRack() As String
ReDim strCDRack(0 to 2) As String
strCDRack(0) = "Deftones"
strCDRack(1) = "Tool"
strCDRack(2) = "Disturbed"
What have you done? Nothing wrong, you've just dimensioned your array another way that allows for expansion. Notice that the subscript of the Dim statement is missing. This is OK; it tells VB that your array is a dynamic array, meaning that you can change its size with ReDim.
Now that you've rebuilt the structure of your CD rack, allowing for expansion, it is time to expand:
Dim strCDRack() As String
ReDim strCDRack(0 to 2) As String
strCDRack(0) = "Deftones"
strCDRack(1) = "Tool"
strCDRack(2) = "Disturbed"
ReDim Preserve strCDRack(0 to 3) As String
strCDRack(3) = "Charlotte Church"
This snippet has two more lines, the first redimensioning the array one element larger and the second setting this element's value. Notice the Preserve keyword: it forces Visual Basic to retain all existing elements' values. Without this keyword all your old CDs would be lost and you'd be stuck with just Charlotte Church.
The ReDim keyword's syntax is:
ReDim [Preserve] ArrayName(Subscript) As DataType
- ReDim is the keyword that denotes we are redimensioning an array.
- Preserve is an optional keyword that forces Visual Basic to retain all existing elements' values. Without it all elements will return to their default values. (Numeric data types to 0, variable-length strings to "" (a zero-length string), fixed-length strings filled with zeros, and variants to empty.)
- ArrayName is the name of the array.
- Subscript is the dimensions of the array.
- DataType is any valid data type. The data type cannot be changed from its initial declaration when using the ReDim keyword. (Unless it was initially declared as a Variant.)
Retrieving the Contents of an Array
Now that you know how to build an array, you might ask how to retrieve its contents. Say you've built an array of your friends' names:
Dim strFriends(0 to 6) As String
strFriends(0) = "aaa"
strFriends(1) = "bbb"
strFriends(2) = "ccc"
strFriends(3) = "ddd"
strFriends(4) = "eee"
strFriends(5) = "fff"
strFriends(6) = "ggg"
That's all good and dandy but you want to display their names in successive message boxes, so you construct a loop:
Dim strFriends(0 to 6) As String, lngPosition as Long
strFriends(0) = "aaa"
strFriends(1) = "bbb"
strFriends(2) = "ccc"
strFriends(3) = "ddd"
strFriends(4) = "eee"
strFriends(5) = "fff"
strFriends(6) = "ggg"
For lngPosition = LBound(strFriends) To UBound(strFriends)
MsgBox strFriends(lngPosition)
Next lngPositionlngPositionlngPosition
There are two new functions in that snippet of code. LBound and UBound are used to determine the lower and upper bounds of an array. Because strFriends has a lower bound of 0 and an upper bound of 6. These functions allow you to to iterate through an array with a dynamic size and they keep you from having to keep track of the array's size yourself. With each iteration of that loop, lngPosition will count up from 0 to 6. By accessing the array as strFriends(lngPosition) you are greatly reducing the amount of code you have to write.
Adding New Elements on the Fly
Sometimes you have an array that needs to keep growing, and you don't know what the upper bound will end up being. Maybe you are making a crappy MP3 player and need to ask the user to input song names. You might do something like this:
Dim strSongNames() As String 'Array of song names
Dim blDimensioned As Boolean 'Is the array dimensioned?
Dim strText As String 'To temporarily hold names
Dim lngPosition as Long 'Counting
'The array has not yet been dimensioned:
blDimensioned = False
Do
'Ask for a song name
strText = InputBox("Enter a song name:")
If strText <> "" Then
'Has the array been dimensioned?
If blDimensioned = True Then
'Yes, so extend the array one element large than its current upper bound.
'Without the "Preserve" keyword below, the previous elements in our array would be erased with the resizing
ReDim Preserve strSongNames(0 To UBound(strSongNames) + 1) As String
Else
'No, so dimension it and flag it as dimensioned.
ReDim strSongNames(0 To 0) As String
blDimensioned = True
End If
'Add the song name to the last element in the array.
strSongNames(UBound(strSongNames)) = strText
End If
Loop Until strText = ""
'Display entered song names:
For lngPosition = LBound(strSongNames) To UBound(strSongNames)
MsgBox strSongNames(lngPosition)
Next lngPosition
'Erase array
Erase strSongName
Look to the comments for an explanation of what is going on.
Erasing an Array
You should always erase your array when you are done using it, especially if you are using dynamic arrays. It's rather easy:
Dim strFriends(0 to 2) As String
strFriends(0) = "aaa"
strFriends(1) = "bbb"
strFriends(2) = "ccc"
Erase strFriends
The Split Function
Sometimes we run into situations where we want to take the information from within a given string, separate it into multiple strings, and then place those strings in an array. For example, say we had this code:
Dim cdList As String
cdList = "Nevermind, OK Computer, I Care Because You Do, Icky Thump"
It'd be nice if we could easily take that list and put it in an array, wouldn't it? This could be done by using Visual Basic's built in string functions, however, writing and updating that code could prove to be time consuming and tedious. Luckily for us, Visual Basic 6.0 provides a built in function called split that allows us to easily parse out information from a string and place it into an array. It has the following syntax:
ArrayName = split(Sting Input[, Delimiter[, Length Limit[, Compare Mode]]])
- String Input is the string that you want to parse.
- Delimiter is an optional parameter that indicates what type of string separates the elements in the input string. By default this parameter is set to " ". That would mean an input string of "This is a test" would yield an array of 4 elements ("This", "is", "a", "test").
- Length Limit is the maximum size your output array can be. The text remaining to be parsed will be set as the final element in the array.
- Compare Mode. By default, Visual Basic compares strings character by character using their ASCII values. However, you can use different modes that will cause Visual Basic to compare strings differently. For example, vbTextCompare causes string comparisons to be case insensitive. This parameter effects how the Delimiter parses Input String.
The following is an example showing how to parse the list we showed earlier:
Dim strCDRack() As String
Dim cdList As String
Dim i As Integer
cdList = "Nevermind, OK Computer, I Care Because You Do, Icky Thump"
strCDRack = Split(cdList, ", ")
For i = LBound(strCDRack) To UBound(strCDRack)
MsgBox strCDRack(i)
Next
The Join Function
The split function allowed us to break strings down into arrays, is there a function that allows us to take arrays and make them one big long string? Yes, yes there is, and it is called join. join is a very simple function. It has the following syntax:
StringName = join(Array Input[, Delimiter])
- Array Input is the array that you want to place into a string.
- Delimiter is an optional parameter that indicates what you want to place between elements are added to the string. By default this parameter is set to "".
Using one of our previous examples, here is some sample code on how one might use join:
Dim strFriends(0 to 6) As String, lngPosition as Long
strFriends(0) = "aaa"
strFriends(1) = "bbb"
strFriends(2) = "ccc"
strFriends(3) = "ddd"
strFriends(4) = "eee"
strFriends(5) = "fff"
strFriends(6) = "ggg"
Dim myFriends As String
'This will produce the following string: "Bianca, Jeana, Sam, Jenna, Erin, Carolyn, Kate"
myFriends = Join(strFriends, ", ")
MsgBox myFriends
Multidimensional Arrays
So far all of the examples we've looked at have used one dimensional arrays, but arrays can be multidimensional too. Multidimensional arrays can be thought of as arrays-of-arrays. For example, to visualize a two dimensional array we could picture a row of CD racks. To make things easier, we can imagine that each CD rack could be for a different artist. Like the CDs, the racks would be identifiable by number. Below we'll define a two dimensional array representing a row of CD racks. The strings inside of the array will represent album titles.
' Here we will define an array where the first dimension contains 2 elements and
' the second dimension contains 4 elements
ReDim cdRack(0 to 1, 0 to 3) As String
' A CD rack for the Beatles
cdRack(0, 0) = "Rubber Soul"
cdRack(0, 1) = "Revolver"
cdRack(0, 2) = "The White Album"
cdRack(0, 3) = "Let It Be"
' A CD rack for the Rolling Stones
cdRack(1, 0) = "Sticky Fingers"
cdRack(1, 1) = "Beggars Banquet"
cdRack(1, 2) = "Let It Bleed"
cdRack(1, 3) = "Tattoo You"
The first item of the first dimension is an array for Beatles CDs while the second item of the first dimension is an array for Rolling Stones CDs. You could also add a third dimension if you wanted. Keeping with our CD rack analogy, you could picture this third dimension as a hallway with several rooms. Inside of each room would be a row of CDs racks. If you wanted your hallways to have 10 rooms, each with CD racks like the ones in the above example, you could declare your array as follows:
Dim cdRackHallway(0 to 9, 0 to 1, 0 to 3) As String
In Visual Basic 6.0, you can create arrays with up to 60 dimensions. In Visual Basic .NET, the maximum number of dimensions an array can have is 32. Most arrays you will need to deal with will only be one or two dimensions. Multidimensional arrays can require a decent amount of memory, so use them with care, especially large multidimensional arrays.
Lastly, for multidimensional arrays it should be noted that only the last dimension can be resized. That means that given our example above, once we created the array with two CD racks, we would not be able to add more racks, we would only be able to change the number of CDs each rack held. Example:
' Here we will define an array where the first dimension contains 2 elements and
' the second dimension contains 4 elements
ReDim cdRack(0 to 1, 0 to 3) As String
' A CD rack for the Beatles
cdRack(0, 0) = "Rubber Soul"
cdRack(0, 1) = "Revolver"
cdRack(0, 2) = "The White Album"
cdRack(0, 3) = "Let It Be"
' A CD rack for the Rolling Stones
cdRack(1, 0) = "Sticky Fingers"
cdRack(1, 1) = "Beggars Banquet"
cdRack(1, 2) = "Let It Bleed"
cdRack(1, 3) = "Tattoo You"
ReDim Preserve cdRack(0 to 1, 0 to 4) As String
' Lets add another Beatles CD
cdRack(0, 4) = "Abby Road"
' Lets add another Rolling Stones CD
cdRack(1, 4) = "Exile on Main St."
Visual Basic Array
• Types of Arrays
• Fixed-Size Arrays
• Dynamic Arrays
• Retrieving the Contents of an Array
• Adding New Elements on the Fly
• Erasing an Array
• The Split Function
• The Join Function
• Multidimensional Arrays
Types of Arrays
An array is a lot like a CD rack. You know: one of those rectangular boxes with slots to slide CDs in, each above another. There are two types of Visual Basic arrays: fixed-size and dynamic.
Fixed-Size Arrays
A fixed-size array most closely matches our CD rack anology. There are a limited number of slots you can slide CDs into. Pretend you have three CDs - one by the Deftones, another by Tool, and a third by Disturbed. To fit all of these in your rack, the rack must contain at least three slots. So you declare your CD rack as having three slots:
Dim strCDRack(0 to 2) As String
You've just made a variable 'strCDRack' that contains three slots (#0, #1, and #2) and is of a String data type. Now you can insert your CDs into it:
Dim strCDRack(0 to 2) As String
strCDRack(0) = "Deftones"
strCDRack(1) = "Tool"
strCDRack(2) = "Disturbed"
Notice that each of the three new lines starts off with the variable name and then gives an element number before having a value assigned. This is like numbering the slots on your CD rack starting at 0 up to 2 and then inserting a CD into each slot.
The format for declaring an array is:
Dim Public/Private ArrayName(Subscript) As DataType
- Dim, Public, and Private declare the array and its scope. Using Dim in a procedure will make the array only available from within that procedure. Using it in the General Declarations section will make it available to all procedures in that module. Private has the same effect and should be used only at the modular level. Using Public will make the array available throughout the project.
- ArrayName is the name of the array.
- Subscript is the dimensions of the array.
- DataType is any valid data type.
-
Dynamic Arrays
The new Charlotte Church CD came out but your rack only has three slots. You don't want to throw away any of your CDs to make room for the new one so you decide to use your ultimate building skills to attach another slot. You start building:
Dim strCDRack() As String
ReDim strCDRack(0 to 2) As String
strCDRack(0) = "Deftones"
strCDRack(1) = "Tool"
strCDRack(2) = "Disturbed"
What have you done? Nothing wrong, you've just dimensioned your array another way that allows for expansion. Notice that the subscript of the Dim statement is missing. This is OK; it tells VB that your array is a dynamic array, meaning that you can change its size with ReDim.
Now that you've rebuilt the structure of your CD rack, allowing for expansion, it is time to expand:
Dim strCDRack() As String
ReDim strCDRack(0 to 2) As String
strCDRack(0) = "Deftones"
strCDRack(1) = "Tool"
strCDRack(2) = "Disturbed"
ReDim Preserve strCDRack(0 to 3) As String
strCDRack(3) = "Charlotte Church"
This snippet has two more lines, the first redimensioning the array one element larger and the second setting this element's value. Notice the Preserve keyword: it forces Visual Basic to retain all existing elements' values. Without this keyword all your old CDs would be lost and you'd be stuck with just Charlotte Church.
The ReDim keyword's syntax is:
ReDim [Preserve] ArrayName(Subscript) As DataType
- ReDim is the keyword that denotes we are redimensioning an array.
- Preserve is an optional keyword that forces Visual Basic to retain all existing elements' values. Without it all elements will return to their default values. (Numeric data types to 0, variable-length strings to "" (a zero-length string), fixed-length strings filled with zeros, and variants to empty.)
- ArrayName is the name of the array.
- Subscript is the dimensions of the array.
- DataType is any valid data type. The data type cannot be changed from its initial declaration when using the ReDim keyword. (Unless it was initially declared as a Variant.)
Retrieving the Contents of an Array
Now that you know how to build an array, you might ask how to retrieve its contents. Say you've built an array of your friends' names:
Dim strFriends(0 to 6) As String
strFriends(0) = "aaa"
strFriends(1) = "bbb"
strFriends(2) = "ccc"
strFriends(3) = "ddd"
strFriends(4) = "eee"
strFriends(5) = "fff"
strFriends(6) = "ggg"
That's all good and dandy but you want to display their names in successive message boxes, so you construct a loop:
Dim strFriends(0 to 6) As String, lngPosition as Long
strFriends(0) = "aaa"
strFriends(1) = "bbb"
strFriends(2) = "ccc"
strFriends(3) = "ddd"
strFriends(4) = "eee"
strFriends(5) = "fff"
strFriends(6) = "ggg"
For lngPosition = LBound(strFriends) To UBound(strFriends)
MsgBox strFriends(lngPosition)
Next lngPositionlngPositionlngPosition
There are two new functions in that snippet of code. LBound and UBound are used to determine the lower and upper bounds of an array. Because strFriends has a lower bound of 0 and an upper bound of 6. These functions allow you to to iterate through an array with a dynamic size and they keep you from having to keep track of the array's size yourself. With each iteration of that loop, lngPosition will count up from 0 to 6. By accessing the array as strFriends(lngPosition) you are greatly reducing the amount of code you have to write.
Adding New Elements on the Fly
Sometimes you have an array that needs to keep growing, and you don't know what the upper bound will end up being. Maybe you are making a crappy MP3 player and need to ask the user to input song names. You might do something like this:
Dim strSongNames() As String 'Array of song names
Dim blDimensioned As Boolean 'Is the array dimensioned?
Dim strText As String 'To temporarily hold names
Dim lngPosition as Long 'Counting
'The array has not yet been dimensioned:
blDimensioned = False
Do
'Ask for a song name
strText = InputBox("Enter a song name:")
If strText <> "" Then
'Has the array been dimensioned?
If blDimensioned = True Then
'Yes, so extend the array one element large than its current upper bound.
'Without the "Preserve" keyword below, the previous elements in our array would be erased with the resizing
ReDim Preserve strSongNames(0 To UBound(strSongNames) + 1) As String
Else
'No, so dimension it and flag it as dimensioned.
ReDim strSongNames(0 To 0) As String
blDimensioned = True
End If
'Add the song name to the last element in the array.
strSongNames(UBound(strSongNames)) = strText
End If
Loop Until strText = ""
'Display entered song names:
For lngPosition = LBound(strSongNames) To UBound(strSongNames)
MsgBox strSongNames(lngPosition)
Next lngPosition
'Erase array
Erase strSongName
Look to the comments for an explanation of what is going on.
Erasing an Array
You should always erase your array when you are done using it, especially if you are using dynamic arrays. It's rather easy:
Dim strFriends(0 to 2) As String
strFriends(0) = "aaa"
strFriends(1) = "bbb"
strFriends(2) = "ccc"
Erase strFriends
The Split Function
Sometimes we run into situations where we want to take the information from within a given string, separate it into multiple strings, and then place those strings in an array. For example, say we had this code:
Dim cdList As String
cdList = "Nevermind, OK Computer, I Care Because You Do, Icky Thump"
It'd be nice if we could easily take that list and put it in an array, wouldn't it? This could be done by using Visual Basic's built in string functions, however, writing and updating that code could prove to be time consuming and tedious. Luckily for us, Visual Basic 6.0 provides a built in function called split that allows us to easily parse out information from a string and place it into an array. It has the following syntax:
ArrayName = split(Sting Input[, Delimiter[, Length Limit[, Compare Mode]]])
- String Input is the string that you want to parse.
- Delimiter is an optional parameter that indicates what type of string separates the elements in the input string. By default this parameter is set to " ". That would mean an input string of "This is a test" would yield an array of 4 elements ("This", "is", "a", "test").
- Length Limit is the maximum size your output array can be. The text remaining to be parsed will be set as the final element in the array.
- Compare Mode. By default, Visual Basic compares strings character by character using their ASCII values. However, you can use different modes that will cause Visual Basic to compare strings differently. For example, vbTextCompare causes string comparisons to be case insensitive. This parameter effects how the Delimiter parses Input String.
The following is an example showing how to parse the list we showed earlier:
Dim strCDRack() As String
Dim cdList As String
Dim i As Integer
cdList = "Nevermind, OK Computer, I Care Because You Do, Icky Thump"
strCDRack = Split(cdList, ", ")
For i = LBound(strCDRack) To UBound(strCDRack)
MsgBox strCDRack(i)
Next
The Join Function
The split function allowed us to break strings down into arrays, is there a function that allows us to take arrays and make them one big long string? Yes, yes there is, and it is called join. join is a very simple function. It has the following syntax:
StringName = join(Array Input[, Delimiter])
- Array Input is the array that you want to place into a string.
- Delimiter is an optional parameter that indicates what you want to place between elements are added to the string. By default this parameter is set to "".
Using one of our previous examples, here is some sample code on how one might use join:
Dim strFriends(0 to 6) As String, lngPosition as Long
strFriends(0) = "aaa"
strFriends(1) = "bbb"
strFriends(2) = "ccc"
strFriends(3) = "ddd"
strFriends(4) = "eee"
strFriends(5) = "fff"
strFriends(6) = "ggg"
Dim myFriends As String
'This will produce the following string: "Bianca, Jeana, Sam, Jenna, Erin, Carolyn, Kate"
myFriends = Join(strFriends, ", ")
MsgBox myFriends
Multidimensional Arrays
So far all of the examples we've looked at have used one dimensional arrays, but arrays can be multidimensional too. Multidimensional arrays can be thought of as arrays-of-arrays. For example, to visualize a two dimensional array we could picture a row of CD racks. To make things easier, we can imagine that each CD rack could be for a different artist. Like the CDs, the racks would be identifiable by number. Below we'll define a two dimensional array representing a row of CD racks. The strings inside of the array will represent album titles.
' Here we will define an array where the first dimension contains 2 elements and
' the second dimension contains 4 elements
ReDim cdRack(0 to 1, 0 to 3) As String
' A CD rack for the Beatles
cdRack(0, 0) = "Rubber Soul"
cdRack(0, 1) = "Revolver"
cdRack(0, 2) = "The White Album"
cdRack(0, 3) = "Let It Be"
' A CD rack for the Rolling Stones
cdRack(1, 0) = "Sticky Fingers"
cdRack(1, 1) = "Beggars Banquet"
cdRack(1, 2) = "Let It Bleed"
cdRack(1, 3) = "Tattoo You"
The first item of the first dimension is an array for Beatles CDs while the second item of the first dimension is an array for Rolling Stones CDs. You could also add a third dimension if you wanted. Keeping with our CD rack analogy, you could picture this third dimension as a hallway with several rooms. Inside of each room would be a row of CDs racks. If you wanted your hallways to have 10 rooms, each with CD racks like the ones in the above example, you could declare your array as follows:
Dim cdRackHallway(0 to 9, 0 to 1, 0 to 3) As String
In Visual Basic 6.0, you can create arrays with up to 60 dimensions. In Visual Basic .NET, the maximum number of dimensions an array can have is 32. Most arrays you will need to deal with will only be one or two dimensions. Multidimensional arrays can require a decent amount of memory, so use them with care, especially large multidimensional arrays.
Lastly, for multidimensional arrays it should be noted that only the last dimension can be resized. That means that given our example above, once we created the array with two CD racks, we would not be able to add more racks, we would only be able to change the number of CDs each rack held. Example:
' Here we will define an array where the first dimension contains 2 elements and
' the second dimension contains 4 elements
ReDim cdRack(0 to 1, 0 to 3) As String
' A CD rack for the Beatles
cdRack(0, 0) = "Rubber Soul"
cdRack(0, 1) = "Revolver"
cdRack(0, 2) = "The White Album"
cdRack(0, 3) = "Let It Be"
' A CD rack for the Rolling Stones
cdRack(1, 0) = "Sticky Fingers"
cdRack(1, 1) = "Beggars Banquet"
cdRack(1, 2) = "Let It Bleed"
cdRack(1, 3) = "Tattoo You"
ReDim Preserve cdRack(0 to 1, 0 to 4) As String
' Lets add another Beatles CD
cdRack(0, 4) = "Abby Road"
' Lets add another Rolling Stones CD
cdRack(1, 4) = "Exile on Main St."
Create Connection String
1) Create a file anywhere on your machine with extension as ‘UDL’ (Uniform Data Link).
2) Double click on that file.
3) Select the OLE DB Provider from Provider Tab.
4) Click on Next.
5) On Connection Tab:
a. Select/enter server name or select server name through dropdown.
b. Select Windows NT login or enter authenticated user name and password to connect.
c. Select the required database.
6) Click Ok.
7) Open the same UDL file with notepad and copy the Connection String.
Note: Copy from ‘Provider’ text except when using for connection MS Sql Server.
2) Double click on that file.
3) Select the OLE DB Provider from Provider Tab.
4) Click on Next.
5) On Connection Tab:
a. Select/enter server name or select server name through dropdown.
b. Select Windows NT login or enter authenticated user name and password to connect.
c. Select the required database.
6) Click Ok.
7) Open the same UDL file with notepad and copy the Connection String.
Note: Copy from ‘Provider’ text except when using for connection MS Sql Server.
This Function is used to calculate the number of week days in given date range
///
/// This Function is used to calculate the number of week days in given date range
///
///Number of Week Days
public int CalculateNumberOfWeekdays(String stdate, String etdate)
{
DateTime dtBegin = DateTime.Parse(stdate);
DateTime dtEnd = DateTime.Parse(etdate);
int dayCount = 0;
while (dtEnd.CompareTo(dtBegin) > 0)
{
//check if the day is not a weekend day
if ((dtBegin.DayOfWeek != DayOfWeek.Saturday) && (dtBegin.DayOfWeek != DayOfWeek.Sunday))
{
dayCount++;
}
//go to next day
dtBegin = dtBegin.AddDays(1);
}
return dayCount;
}
/// This Function is used to calculate the number of week days in given date range
///
///
public int CalculateNumberOfWeekdays(String stdate, String etdate)
{
DateTime dtBegin = DateTime.Parse(stdate);
DateTime dtEnd = DateTime.Parse(etdate);
int dayCount = 0;
while (dtEnd.CompareTo(dtBegin) > 0)
{
//check if the day is not a weekend day
if ((dtBegin.DayOfWeek != DayOfWeek.Saturday) && (dtBegin.DayOfWeek != DayOfWeek.Sunday))
{
dayCount++;
}
//go to next day
dtBegin = dtBegin.AddDays(1);
}
return dayCount;
}
Function to retrive a field value from specified table using given where clause in VB 6.0\MSAccess
Public Function GetValue(FiledName As String, TableName As String, WhereClause As String) As String
Dim strSQL As String
Dim ConnectFR As ADODB.Connection
Dim rsValue As ADODB.Recordset
Set ConnectFR = ConFR.OpenConnection
If FiledName <> "" And TableName <> "" And WhereClause <> "" Then
strSQL = "Select " & FiledName & " From " & TableName & " Where " & WhereClause & ";"
Set rsValue = New ADODB.Recordset
rsValue.Open strSQL, ConnectFR, adOpenStatic, adLockOptimistic
If rsValue.BOF = False And rsValue.EOF = False Then
GetValue = rsValue(0).Value & ""
Else
GetValue = ""
End If
Else
GetValue = ""
End If
ConFR.CloseConnection
Set ConnectFR = Nothing
End Function
Dim strSQL As String
Dim ConnectFR As ADODB.Connection
Dim rsValue As ADODB.Recordset
Set ConnectFR = ConFR.OpenConnection
If FiledName <> "" And TableName <> "" And WhereClause <> "" Then
strSQL = "Select " & FiledName & " From " & TableName & " Where " & WhereClause & ";"
Set rsValue = New ADODB.Recordset
rsValue.Open strSQL, ConnectFR, adOpenStatic, adLockOptimistic
If rsValue.BOF = False And rsValue.EOF = False Then
GetValue = rsValue(0).Value & ""
Else
GetValue = ""
End If
Else
GetValue = ""
End If
ConFR.CloseConnection
Set ConnectFR = Nothing
End Function
Create Unique ID in VB 6.0/MSAccess
Public Function CreateId() As String
Dim strNewGuid As String
Dim objGUID As Object
'Create Object from wich GUID can be retrived
Set objGUID = CreateObject("Scriptlet.TypeLib")
strNewGuid = Left(objGUID.Guid, 38)
strArray = Split(CStr(strNewGuid), "-")
strNewGuid = strArray(4)
strNewGuid = Trim(Replace(strNewGuid, "}", ""))
'Return GUID
CreateId = strNewGuid
End Function
Dim strNewGuid As String
Dim objGUID As Object
'Create Object from wich GUID can be retrived
Set objGUID = CreateObject("Scriptlet.TypeLib")
strNewGuid = Left(objGUID.Guid, 38)
strArray = Split(CStr(strNewGuid), "-")
strNewGuid = strArray(4)
strNewGuid = Trim(Replace(strNewGuid, "}", ""))
'Return GUID
CreateId = strNewGuid
End Function
Retrive NTLogin Name in VB6.0
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public ConFR As New Connnection ' Create the object of connection class
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public ConFR As New Connnection ' Create the object of connection class
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function
Sending Mail Using C#.net
///
/// This Function is used to send a Mail
///
/// Mail From
/// Mail To
/// Mail CC
/// Mail Subject
/// Mail Boday
public void sendemail(String MailFrom,String MailTo,String MailCC,String MailSubject,String MailBody)
{
System.Net.Mail.MailMessage tmail = new System.Net.Mail.MailMessage(MailFrom, strTO);
tmail.CC.Add(MailCC);
tmail.IsBodyHtml = true;
tmail.CC.Add(MailCC);
tmail.Subject = MailSubject;
tmail.Body = MailBody;
System.Net.Mail.SmtpClient sclient = new System.Net.Mail.SmtpClient();
System.Net.NetworkCredential basicAuthenticationInfo = new System.Net.NetworkCredential("UserName", "Password");
sclient.Host = "Mailserver";
sclient.UseDefaultCredentials = false;
sclient.Credentials = basicAuthenticationInfo;
sclient.Send(tmail);
}
/// This Function is used to send a Mail
///
/// Mail From
/// Mail To
/// Mail CC
/// Mail Subject
/// Mail Boday
public void sendemail(String MailFrom,String MailTo,String MailCC,String MailSubject,String MailBody)
{
System.Net.Mail.MailMessage tmail = new System.Net.Mail.MailMessage(MailFrom, strTO);
tmail.CC.Add(MailCC);
tmail.IsBodyHtml = true;
tmail.CC.Add(MailCC);
tmail.Subject = MailSubject;
tmail.Body = MailBody;
System.Net.Mail.SmtpClient sclient = new System.Net.Mail.SmtpClient();
System.Net.NetworkCredential basicAuthenticationInfo = new System.Net.NetworkCredential("UserName", "Password");
sclient.Host = "Mailserver";
sclient.UseDefaultCredentials = false;
sclient.Credentials = basicAuthenticationInfo;
sclient.Send(tmail);
}
Monday, September 20, 2010
Function to retrive the value of a given field form the given table depending on specified criteria
///
/// This function is used to retrive the value of a given field form the given table depending on specified criteria
///
/// Value to retied
/// Table from which values is to retived
/// filter criteria
///Given field vale
public string getValue(String FieldName, String TableName, String WhereClause)
{
String SQL;
if (FieldName != string.Empty && FieldName != null && FieldName != "")
{
if (TableName != string.Empty && TableName != null && TableName != "")
{
if (WhereClause != string.Empty && WhereClause != null && WhereClause != "")
{
try
{
SQL = "Select " + FieldName + " From " + TableName + " Where " + WhereClause;
Con = Myconn.CreateConn();
Da = new MySqlDataAdapter(SQL, Con);
DS = new DataSet();
Da.Fill(DS);
return DS.Tables[0].Rows[0][0].ToString();
}
catch (Exception)
{
return "";
}
}
else
{
return "";
}
}
else
{
return "";
}
}
else
{
return "";
}
}
/// This function is used to retrive the value of a given field form the given table depending on specified criteria
///
/// Value to retied
/// Table from which values is to retived
/// filter criteria
///
public string getValue(String FieldName, String TableName, String WhereClause)
{
String SQL;
if (FieldName != string.Empty && FieldName != null && FieldName != "")
{
if (TableName != string.Empty && TableName != null && TableName != "")
{
if (WhereClause != string.Empty && WhereClause != null && WhereClause != "")
{
try
{
SQL = "Select " + FieldName + " From " + TableName + " Where " + WhereClause;
Con = Myconn.CreateConn();
Da = new MySqlDataAdapter(SQL, Con);
DS = new DataSet();
Da.Fill(DS);
return DS.Tables[0].Rows[0][0].ToString();
}
catch (Exception)
{
return "";
}
}
else
{
return "";
}
}
else
{
return "";
}
}
else
{
return "";
}
}
Creating connection with MYSQL Database using C#.Net
using System;
using System.Data;
using System.Configuration;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
///
/// Summary description for Connection
///
public class Connection
{
public Connection()
{
//
// TODO: Add constructor logic here
//
}
public MySqlConnection MyConnection;
public MySqlDataReader DataReader;
public MySqlCommand Command;
private string mySqlConnectionString = "Connection String";
///
///
///
public void CloseConn()
{
if (MyConnection != null)
{
if (MyConnection.State == ConnectionState.Open)
{
MyConnection.Close();
}
MyConnection.Dispose();
}
}
///
///
///
///
public MySqlConnection CreateConn()
{
if (MyConnection == null) { MyConnection = new MySqlConnection(); };
if (MyConnection.ConnectionString == string.Empty || MyConnection.ConnectionString == null)
{
try
{
MyConnection.ConnectionString = mySqlConnectionString;
MyConnection.Open();
}
catch (Exception)
{
if (MyConnection.State != ConnectionState.Closed)
{
MyConnection.Close();
}
MyConnection.ConnectionString = mySqlConnectionString;
MyConnection.Open();
}
return MyConnection;
}
if (MyConnection.State != ConnectionState.Open)
{
try
{
MyConnection.ConnectionString = mySqlConnectionString;
MyConnection.Open();
}
catch (Exception)
{
if (MyConnection.State != ConnectionState.Closed)
{
MyConnection.Close();
}
MyConnection.ConnectionString = mySqlConnectionString;
MyConnection.Open();
}
}
return MyConnection;
}
}
using System.Data;
using System.Configuration;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
///
/// Summary description for Connection
///
public class Connection
{
public Connection()
{
//
// TODO: Add constructor logic here
//
}
public MySqlConnection MyConnection;
public MySqlDataReader DataReader;
public MySqlCommand Command;
private string mySqlConnectionString = "Connection String";
///
///
///
public void CloseConn()
{
if (MyConnection != null)
{
if (MyConnection.State == ConnectionState.Open)
{
MyConnection.Close();
}
MyConnection.Dispose();
}
}
///
///
///
///
public MySqlConnection CreateConn()
{
if (MyConnection == null) { MyConnection = new MySqlConnection(); };
if (MyConnection.ConnectionString == string.Empty || MyConnection.ConnectionString == null)
{
try
{
MyConnection.ConnectionString = mySqlConnectionString;
MyConnection.Open();
}
catch (Exception)
{
if (MyConnection.State != ConnectionState.Closed)
{
MyConnection.Close();
}
MyConnection.ConnectionString = mySqlConnectionString;
MyConnection.Open();
}
return MyConnection;
}
if (MyConnection.State != ConnectionState.Open)
{
try
{
MyConnection.ConnectionString = mySqlConnectionString;
MyConnection.Open();
}
catch (Exception)
{
if (MyConnection.State != ConnectionState.Closed)
{
MyConnection.Close();
}
MyConnection.ConnectionString = mySqlConnectionString;
MyConnection.Open();
}
}
return MyConnection;
}
}
Subscribe to:
Posts (Atom)