The following VB.NET function NumericValidation is used to prevent the users from entering characters other than digits in text box
* Drag the TextBox Control and drop on Form and name it as TextBox1
* Call the function NumericValidation Whenever an key is pressed (KeyPress Event of Textbox)
/* This function accepts a single character entered in the text box as Input */
/* This function Permits Digits [0-9] */
/* This function permits a Single Dot for decimal value */
/* This function permits 4 digits after decimal point */
/* This function Permits a single - sign for Negative Symbol */
/* This function restricts Negative sign to be at the First Character */
/* This function does not permit 0 as first character */
VB.NET CODE
[CODE]
Private Function NumericValidation(ByVal chr As Char)
Dim index As Integer
Dim str As String = TextBox1.Text.Trim
'First Digit should not be 0
If chr = "0" And str = "" Then
Return False
End If
'Check for duplicate decimal digit
If (chr = ".") Then
index = str.IndexOf(".")
If (index > 0) Then
'Already dot is there
Return False
End If
End If
'Restrict 4 digit numbers after decimal point
index = str.IndexOf(".")
If (index > 0) Then
If (str.Length - index) > 4 Then
'Not More than 4 decimal digits after dot
If Not Char.IsControl(chr) Then 'To allow back space
Return False
End If
End If
End If
If Char.IsDigit(chr) Then 'If it is a digit allow
Return True
End If
If (chr = "-") Then '- (Negative) sign should come first
If (str.Length > 0) Then
Return False
End If
End If
If (Not Char.IsDigit(chr)) Then
' Non Digits are not allowed
If (Not (chr = ".") And Not (chr = "\b") And Not (chr = "-") And Not Char.IsControl(chr)) Then 'Other than back space or dot or negative sign
Return False
End If
End If
Return True
End Function
//Whenever an Key is Pressed We just need to call NumericValidation function with the Key pressed
Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress
'If the key entered does not meet the criteria
If Not NumericValidation(e.KeyChar) Then
e.Handled = True //Invalid value
Beep() //Beep sound
End If
End Sub
[/CODE]
Valid and Invalid Data
Valid Examples
-1.7897
23.7977
InValid Examples - Will not be permitted
A1235
0788
23.7.2
7-89
C# CODE
Add the Handler textBox1_KeyPress in the Entry Point
Whenever an Key is Pressed we have to Call NumericValidation with the pressed Character.
[CODE]
public Form1()
{
InitializeComponent();
//Add the handler for KeyPress Event for textbox
textBox1.KeyPress += new KeyPressEventHandler(textBox1_KeyPress);
}
//Whenever an Key is Pressed We just need to call NumericValidation function with the Key pressed
private void textBox1_KeyPress(Object sender, System.Windows.Forms.KeyPressEventArgs e)
{
if (!(NumericValidation(e.KeyChar))) 'If not valid do not allow
{
e.Handled = true;
}
}
//Numeric Validation Function
private bool NumericValidation(char chr)
{
string str = textBox1.Text.Trim();
int index = 0;
//First Digit should not be 0
if (chr == '0' & string.IsNullOrEmpty(str))
{
return false;
}
//Check for duplicate decimal digit
if ((chr == '.'))
{
index = str.IndexOf(".");
if ((index > 0))
{
//Already dot is there
return false;
}
}
//Restrict 4 digit numbers after decimal point
index = str.IndexOf(".");
if ((index > 0))
{
//Already dot is there
if ((str.Length - index) > 4)
{
//More than 4 decimal digits after dot
if (!char.IsControl(chr))
{
//To allow back space
return false;
}
}
}
if (char.IsDigit(chr))
{
return true;
}
if ((chr == '-'))
{
//- sign should come first
if ((str.Length > 0))
{
return false;
}
}
if ((!char.IsDigit(chr)))
{
// Non Digits are not allowed
if ((!(chr == '.') & !(chr == '\b') & !(chr == '-') & !char.IsControl(chr)))
{
//Other than back space or dot or negative sign
return false;
}
}
return true;
}
[/CODE]
Tuesday, June 30, 2009
Check List for Improving the Performance of .NET Applications
General Considerations
1. Resource Cleanup
Failing to clean up resources is a common cause of performance and scalability
bottlenecks. Review your code to make sure all resources are closed and released as
soon as possible. This is particularly important for shared and limited resources such as
connections. Make sure your code calls Dispose (or Close) on disposable resources.
Make sure your code uses finally blocks or using statements to ensure resources are
closed even in the event of an exception.
2. Exceptions
While structured exception handling is encouraged because it leads to more robust
code and code that is less complex to maintain than code that uses method return
codes to handle error conditions, exceptions can be expensive. Make sure you do not
use exception handling to control regular application flow. Use it only for exceptional
conditions. Avoid exception handling inside loops — surround the loop with a
try/catch block instead if that is required. Also identify code that swallows exceptions
or inefficient code that catches, wraps, and rethrows exceptions for no valid reason.
3. String Management
Excessive string concatenation results in many unnecessary allocations, creating extra
work for the garbage collector. Use StringBuilder for complex string manipulations and
when you need to concatenate strings multiple times. If you know the number of
appends and concatenate strings in a single statement or operation, prefer the +
operator. In ASP.NET applications, consider emitting HTML output by using multiple
Response.Write calls instead of using a StringBuilder.
4. Threading:
Server-side code should generally use the common language runtime (CLR) thread
pool and should not create threads on a per-request basis. Review your code to ensure
that appropriate use is made of the thread pool and that the appropriate
synchronization primitives are used. Make sure your code does not lock whole classes
or whole methods when locking a few lines of code might be appropriate. Also make
sure your code does not terminate or pause threads by using Thread.Abort or
Thread.Suspend.
5. Boxing
Boxing causes a heap allocation and a memory copy operation. Review your code to
identify areas where implicit boxing occurs. Pay particular attention to code inside
loops where the boxing overhead quickly adds up. Avoid passing value types in
method parameters that expect a reference type. Sometimes this is unavoidable. In
Check List for Improving the Performance of .NET Applications
this case, to reduce the boxing overhead, box your variable once and keep an object
reference to the boxed copy as long as needed, and then unbox it when you need a
value type again. Excessive boxing often occurs where you use collections that store
System.Object types. Consider using an array or a custom-typed collection class
instead. To identify locations that might have boxing overhead, you can search your
assembly’s Microsoft intermediate language (MSIL) code for the box and unbox
instructions, using the following command line.
Ildasm.exe yourcomponent.dll /text findstr box
Ildasm.exe yourcomponent.dll /text findstr unbox
To measure the overhead, use a profiler.
Checklist for Managed Code Performance
1. Design Considerations
1.1. Design for efficient resource management.
1.2. Reduce boundary crossings.
1.3. Prefer single large assemblies rather than multiple smaller assemblies.
1.4. Factor code by logical layers.
1.5. Treat threads as a shared resource.
1.6. Design for efficient exception management.
2. Class Design Considerations
2.1. Do not make classes thread safe by default.
2.2. Consider using the sealed keyword.
2.3. Consider the tradeoffs of using virtual members.
2.4. Consider using overloaded methods.
2.5. Consider overriding the Equals method for value types.
2.6. Know the cost of accessing a property.
2.7. Consider private versus public member variables.
2.8. Limit the use of volatile fields.
3. Garbage Collection Guidelines
3.1. Identify and analyze your application’s allocation profile.
3.2. Avoid calling GC.Collect.
3.3. Consider weak references with cached data.
3.4. Prevent the promotion of short-lived objects.
3.5. Set unneeded member variables to Null before making long-running calls.
3.6. Minimize hidden allocations.
3.7. Avoid or minimize complex object graphs.
3.8. Avoid preallocating and chunking memory.
4. Finalize and Dispose
4.1. Call Close or Dispose on objects that support it.
4.2. Use the using statement in Microsoft® C# and Try/Finally blocks in Microsoft
Visual
4.3. Basic®.NET to ensure Dispose is called.
4.4. Do not implement Finalize unless required.
4.5. Implement Finalize only if you hold unmanaged resources across client calls.
4.6. Move the finalization burden to the leaves of object graphs.
4.7. If you implement Finalize, implement IDisposable.
4.8. If you implement Finalize and Dispose, use the Dispose pattern.
4.9. Suppress finalization in your Dispose method.
4.10. Allow Dispose to be called multiple times.
4.11. Call Dispose on base classes and on IDisposable members.
4.12. Keep finalizer code simple to prevent blocking.
4.13. Provide thread-safe cleanup code only if your type is thread-safe.
5. Pinning
5.1. If you need to pin buffers, allocate them at startup.
6. Threading
6.1. Minimize thread creation.
6.2. Use the thread pool when you need threads.
6.3. Use a Timer to schedule periodic tasks.
6.4. Consider parallel versus synchronous tasks.
6.5. Do not use Thread.Abort to terminate other threads.
6.6. Do not use Thread.Suspend and Thread.Resume to pause threads.
7. Asynchronous Calls
7.1. Consider client-side asynchronous calls for UI responsiveness.
7.2. Use asynchronous methods on the server for I/O bound operations.
7.3. Avoid asynchronous calls that do not add parallelism.
8. Locking and Synchronization
8.1. Determine if you need synchronization.
8.2. Determine the approach.
8.3. Determine the scope of your approach.
8.4. Acquire locks late and release them early.
8.5. Avoid locking and synchronization unless required.
8.6. Use granular locks to reduce contention.
8.7. Avoid excessive fine-grained locks.
8.8. Avoid making thread safety the default for your type.
8.9. Use the fine grained lock (C#) statement instead of Synchronized.
8.10. Avoid locking “this”.
8.11. Coordinate multiple readers and single writers by using ReaderWriterLock
instead of lock.
8.12. Do not lock the type of the objects to provide synchronized access.
9. Boxing and Unboxing
9.1. Avoid frequent boxing and unboxing overhead.
9.2. Measure boxing overhead.
9.3. Use DirectCast in your Visual Basic .NET code.
10. Exception Management
10.1. Do not use exceptions to control application flow.
10.2. Use validation code to avoid unnecessary exceptions.
10.3. Use the finally block to ensure resources are released.
10.4. Replace Visual Basic .NET On Error Goto code with exception handling.
10.5. Do not catch exceptions that you cannot handle.
10.6. Be aware that rethrowing is expensive.
10.7. Preserve as much diagnostic information as possible in your exception
handlers.
10.8. Use performance monitor to monitor common language runtime (CLR)
exceptions.
11. Iterating and Looping
11.1. Avoid repetitive field or property access.
11.2. Optimize or avoid expensive operations within loops.
11.3. Copy frequently called code into the loop.
11.4. Consider replacing recursion with looping.
11.5. Use for instead of foreach in performance-critical code paths.
12. String Operations
12.1. Avoid inefficient string concatenation.
12.2. Use + when the number of appends is known.
12.3. Use StringBuilder when the number of appends is unknown.
12.4. Treat StringBuilder as an accumulator.
12.5. Use the overloaded Compare method for case-insensitive string comparisons.
13. Arrays
13.1. Prefer arrays to collections unless you need functionality.
13.2. Use strongly typed arrays.
13.3. Use jagged arrays instead of multidimensional arrays.
14. Collections
14.1. Analyze your requirements before choosing the collection type.
14.2. Initialize collections to the right size when you can.
14.3. Consider enumerating overhead.
14.4. Prefer to implement IEnumerable with optimistic concurrency.
14.5. Consider boxing overhead.
14.6. Consider for instead of foreach.
14.7. Implement strongly typed collections to prevent casting overhead.
14.8. Be efficient with data in collections.
15. Reflection and Late Binding
15.1. Prefer early binding and explicit types rather than reflection.
15.2. Avoid late binding.
15.3. Avoid using System.Object in performance critical code paths.
15.4. Enable Option Explicit and Option Strict in Visual Basic.NET.
16. Code Access Security
16.1. Consider SuppressUnmanagedCodeSecurity for performance-critical, trusted
scenarios.
16.2. Prefer declarative demands rather than imperative demands.
16.3. Consider using link demands rather than full demands for performance -
critical, trusted scenarios.
17. Working Set Considerations
17.1. Load only the assemblies you need.
17.2. Consider assemblies that are being loaded as side effects.
17.3. Reduce the number of application domains, and/or make assemblies shared
assemblies.
17.4. Reduce the number of threads.
Checklist for ADO.NET Performance
18. Design Considerations
18.1. Design your data access layer based on how the data is used.
18.2. Cache data to avoid unnecessary work.
18.3. Connect by using service accounts.
18.4. Acquire late, release early.
18.5. Close disposable resources.
18.6. Reduce round trips.
18.7. Return only the data you need.
18.8. Use Windows authentication.
18.9. Choose the appropriate transaction type.
18.10. Use stored procedures.
18.11. Prioritize performance, maintainability, and productivity when you choose
how to pass data across layers.
18.12. Consider how to handle exceptions.
18.13. Use appropriate normalization.
19. Microsoft® .NET Framework Data Providers
19.1. Use System.Data.SqlClient for Microsoft SQL Server™ 7.0 and later.
19.2. Use System.Data.OleDb for SQL Server 6.5 or OLE DB providers.
19.3. Use System.Data.ODBC for ODBC data sources.
19.4. Use System.Data.OracleClient for Oracle.
19.5. Use SQLXML managed classes for XML data and SQL Server 2000.
20. Connections
20.1. Open and close the connection in the method.
20.2. Explicitly close connections.
20.3. When using DataReaders, specify CommandBehavior.CloseConnection.
20.4. Do not explicitly open a connection if you use Fill or Update for a single
operation.
20.5. Avoid checking the State property of OleDbConnection.
20.6. Pool connections.
21. Commands
21.1. Validate SQL input and use Parameter objects.
21.2. Retrieve only the columns and rows you need.
21.3. Support paging over large result sets.
21.4. Batch SQL statements to reduce round trips.
21.5. Use ExecuteNonQuery for commands that do not return data.
21.6. Use ExecuteScalar to return single values.
21.7. Use CommandBehavior.SequentialAccess for very wide rows or for rows with
binary large
21.8. objects (BLOBs).
21.9. Do not use CommandBuilder at run time.
22. Stored Procedures
22.1. Use stored procedures.
22.2. Use CommandType.Text with OleDbCommand.
22.3. Use CommandType.StoredProcedure with SqlCommand.
22.4. Consider using Command.Prepare.
22.5. Use output parameters where possible.
22.6. Consider SET NOCOUNT ON for SQL Server.
23. Parameters
23.1. Use the Parameters collection when you call a stored procedure.
23.2. Use the Parameters collection when you build SQL statements.
23.3. Explicitly create stored procedure parameters.
23.4. Specify parameter types.
23.5. Cache stored procedure SqlParameter objects.
24. DataReader
24.1. Close DataReader objects.
24.2. Consider using CommandBehavior.CloseConnection to close connections.
24.3. Cancel pending data.
24.4. Consider using CommandBehavior.SequentialAccess with ExecuteReader.
24.5. Use GetOrdinal when using an index-based lookup.
25. DataSet
25.1. Reduce serialization.
25.2. Use primary keys and Rows.Find for indexed searching.
25.3. Use a DataView for repetitive non-primary key searches.
25.4. Use the optimistic concurrency model for datasets.
25.5. XML and DataSet Objects
25.6. Do not infer schemas at run time.
25.7. Perform bulk updates and inserts by using OpenXML.
26. Types
26.1. Avoid unnecessary type conversions.
27. Exception Management
27.1. Use the ConnectionState property.
27.2. Use try/finally to clean up resources.
27.3. Use specific handlers to catch specific exceptions.
28. Transactions
28.1. Use SQL transactions for server controlled-transactions on a single data store.
28.2. Use ADO.NET transactions for client-controlled transactions on a single data
store.
28.3. Use Distributed Transaction Coordinators (DTC) for transactions that span
multiple data stores.
28.4. Keep transactions as short as possible.
28.5. Use the appropriate isolation level.
28.6. Avoid code that can lead to deadlock.
28.7. Set the connection string Enlist property to false.
29. Binary Large Objects
29.1. Use CommandBehavior.SequentialAccess and GetBytes to read data.
29.2. Use READTEXT to read from SQL Server 2000.
29.3. Use OracleLob.Read to read from Oracle databases.
29.4. Use UpdateText to write to SQL Server databases.
29.5. Use OracleLob.Write to write to Oracle databases.
29.6. Avoid moving binary large objects repeatedly.
Best Practices in .NET Coding
Best Practices in .NET Coding
• We all just write code for “working application”• Any developer can do that• There is a need to write “Efficient Code”• Writing 'Efficient Code' is an art and you must learn and practice it.
Working Code vs. Good Code
• A good code is: 1. Reliable2. Maintainable3. Efficient• If any of the above is missing then the code is not a good code and a lot of time could be wasted in trying to understand code and identify issues.
Naming Conventions and Standards
• Pascal Casing - First character of all words are Upper Case and other characters are in lower case.
E.g. BackColor
• Camel Casing - First character of all words, except the first word are Upper Case and other characters are in lower case.
E.g. backColor
Use Pascal casing for Class names
public class HelloWorld
{
... }
Use Pascal casing for Method names
public class HelloWorld
{
void SayHello(string name)
{
...
}
}
Use Camel casing for variables and method parameters
public class HelloWorld
{
int totalCount = 0;
void SayHello(string name)
{
string fullMessage = "Hello " + name;
...
}
}
Do not use Hungarian notation to name variables
• In earlier days most of the programmers liked it - having the data type as a prefix for the variable name and using m_ as prefix for member variables.
Example:
string m_sName;
int nAge;
• However, in .NET coding standards, this is not recommended. Usage of datatype and M_ to represent member variables should not be used. All variables should use camel casing.
Uppercase
• Uppercase characters are often used for enumerations and constant values.• Can also be used for only identifiers that consist of two or fewer letters. For example:
System.IO
System.Web.IO
Use Meaningful, descriptive words to name variables
• Do not use abbreviations. Use name, address, salary etc instead of nam, addr, sal• Do not use single character variable names like i, n, x etc. Use names like index, temp• One exception in this case would be variables used for iterations in loops
for ( int i = 0; i < count; i++ )
{
...
}
• Do not use underscores (_) in variable names. • Do not use variable names that resemble keywords.• Prefix boolean variables, properties and methods with “is” or similar prefixes. Example: private bool isFinished
Namespace Naming Convention
• The general rule for naming namespaces is to use the company name followed by the technology name and optionally the feature and design as follows.
CompanyName.TechnologyName[.Feature][.Design]
For Example:
1.Microsoft.Media
2.Microsoft.Media.Design
Interface Naming Guidelines
• Name interfaces with nouns or noun phrases, or adjectives that describe behavior. For E.g.
public interface IServiceProvider
public interface IFormatable
• Use Pascal Case.• Prefix interface names with the letter “I”, to indicate that the type is an interface.• Do not use the underscore character (_).
Static Field Naming Guidelines
• Use nouns, noun phrases, or abbreviations of nouns to name static fields. • Use Pascal Case. • Do not use a Hungarian notation prefix on static field names. • It is recommended that you use static properties instead of public static fields whenever possible.
Parameter Naming Guidelines
• Use descriptive parameter names.• Use Camel Case for parameter names.• Do not prefix parameter names with Hungarian type notation.• Examples:
Type GetType(string typeName)
string Format(string format, object[] args)
Prefixes for the UI elements
• Use appropriate prefix for the UI elements so that you can identify them from the rest of the variables.• This will help you group all of the UI elements together and easy to access all of them from the intellisense.• Some prefixes are as follows:
Case Sensitivity
• Do not use names that require case sensitivity. Components must be fully usable from both case-sensitive and case-insensitive languages.• Do not create two namespaces with names that differ only by case. For example, a case insensitive language cannot distinguish between the following two namespace declarations.
Namespace IridiumSoftware
Namespace iridiumsoftware
• Do not create a function with parameter names that differ only by case. The following example is incorrect.
void MyFunction(string a, string A)
Case Sensitivity
• Do not create a type with property names that differ only by case. For Example:
int Color {get, set}
int COLOR {get, set}
• Do not create a type with method names that differ only by case. For Example:
void calculate()
void Calculate()
File name should match with class name
• For example, for the class HelloWorld, the file name should be HelloWorld.cs
Indentation and Spacing
• Use TAB for indentation. Do not use SPACES. • Comments should be in the same level as the code.
Example
Good Practice
// Format a message and display
string fullMessage = "Hello " + name;
DateTime currentTime = DateTime.Now;
string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString();
Not so Good Practice
// Format a message and display
string fullMessage = "Hello " + name;
DateTime currentTime = DateTime.Now;
string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString();
#endregion
Indentation and Spacing
• Curly braces ( {} ) should be in the same level as the code outside the braces. Example.
Indentation and Spacing
Use one blank line to separate logical groups of code.
Not a Good Practice:
bool SayHello(string name)
{
string fullMessage = "Hello " + name;
DateTime currentTime = DateTime.Now;
string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString();
MessageBox.Show(message);
if (true)
{
// Do something
// ...
return false;
}
return true;
}
return true;
}
Good Practice:
bool SayHello(string name)
{
string fullMessage = "Hello " + name;
DateTime currentTime = DateTime.Now;
string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString();
MessageBox.Show(message);
if (true)
{
// Do something
// ...
return false;
}
Indentation and Spacing
• There should be one and only one single blank line between each method inside the class. • The curly braces should be on a separate line and not in the same line as if, for etc.
Good:
if ( ... )
{
// Do something
}
Not good:
if ( ... ) {
// Do something
}
Indentation and Spacing
• Use a single space before and after each operator and brackets
Good:
if ( showResult == true )
{
for ( int i = 0; i < 10; i++ )
{
//
}
}
Not Good:
if(showResult==true)
{
for(int i= 0;i<10;i++)
{
//
}
}
Which code looks better?
bool SayHello (string name){
string fullMessage = "Hello " + name;
DateTime currentTime = DateTime.Now;
string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString();
MessageBox.Show ( message );
if ( ... ) {
// Do something //
...
return false;
}
return true;
}
bool SayHello (string name)
{
string fullMessage = "Hello " + name;
DateTime currentTime = DateTime.Now;
string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString();
MessageBox.Show ( message );
if ( ... )
{
// Do something //
...
return false;
}
return true;
}
Follow the best practices for best programming
• Avoid having too large files. If a file has more than 300~400 lines of code, you must consider refactoring code into helper classes. • Avoid writing very long methods. A method should typically have 1~25 lines of code. If a method has more than 25 lines of code, you must consider re factoring into separate methods.
Naming Methods
• Use verbs or verb phrases to name methods. • Use Pascal Case. • Method name should tell what it does. Do not use misleading names. If the method name is obvious, there is no need of documentation explaining what the method does.
Good Practice:
void SavePhoneNumber (string phoneNumber)
{
// Save the phone number.
}
Not a Good Practice:
// This method will save the phone number.
void SaveData (string phoneNumber)
{
// Save the phone number.
}
One job per Method
A method should do only 'one job'. Do not combine more than one job in a single method, even if those jobs are very small
Good Practice:
void main()
{
// Save the address.
SaveAddress(address);
// Send an email to the supervisor to inform that the address is updated.
SendEmail(address, email);
}
void SaveAddress(string address)
{
// Save the address. //
}
void SendEmail(string address, string email)
{
// Send an email to inform the supervisor that the address is changed.
}
•
Not a good practice:
// Save address and send an email to the supervisor to inform that
// the address is updated.
void main()
{
SaveAddress(address, email);
}
void SaveAddress(string address, string email)
{
// Job 1.
// Save the address.
// ...
// Job 2.
// Send an email to inform the supervisor that the address is changed.
// ...
}
Use of #region
•Use #region to group related pieces of code together.
• Keep private member variables, properties and methods in the top of the file and public members in the bottom.
Types
• Use the C# or VB.NET specific types, rather than the alias types defined in System namespace.
Good: int age;
string name; object contactInfo;
Not Good:
Int16 age;
String name;
Object contactInfo;
Some Key Points
• Do not hardcode numbers. Use constants instead or the better way is to use config files that can be easily changes.• Do not hardcode strings. Use config files. • Convert strings to lowercase or upper case before comparing. This will ensure the string will match even if the string being compared has a different case. For example:
if ( name.ToLower() == “john” )
{
//…
}
Some Key Points
• Use String.Empty instead of “” . For Example:
Good:
If ( name == String.Empty )
{
// do something
}
Not Good:
If ( name == “” )
{
// do something
}
Some Key Points
• Do not make the member variables public or protected. Keep them private and expose public/protected Properties.• Never hardcode a path or drive name in code. Get the application path programmatically and use relative path. • Never assume that your code will run from drive "C:". You may never know, some users may run it from network or from a "Z:". • Do not have more than one class in a single file.
Some Key Points
• Avoid public methods and properties, unless they really need to be accessed from outside the class. Use “internal” if they are accessed only within the same assembly.• Avoid passing too many parameters to a method. If you have more than 4~5 parameters, it is a good candidate to define a class or structure.• If you are opening database connections, sockets, file stream etc, always close them in the finally block. This will ensure that even if an exception occurs after opening the connection, it will be safely closed in the finally block.• Declare variables as close as possible to where it is first used. Use one variable declaration per line.
StringBuilder Class
Use StringBuilder class instead of String when you have to manipulate string objects in a loop.
Using String
public string ComposeMessage (string[] lines)
{
string message = String.Empty;
for (int i = 0; i < lines.Length; i++)
{
message += lines [i];
}
return message;
}
Using StringBuilder
public string ComposeMessage (string[] lines)
{
StringBuilder message = new StringBuilder();
for (int i = 0; i < lines.Length; i++)
{
message.Append( lines[i] );
}
return message.ToString();
}
Watch for unexpected values
Always watch for unexpected values.
If you are using a parameter with 2 possible values, never assume that if one is not matching then the only possibility is the other value.
void example()
{
//Good:
if(memberType == eMemberTypes.Registered)
{
// Registered user…do something…
}
else if ( memberType == eMemberTypes.Guest )
{
// Guest user... do something…
}
else
{
// Un expected user type. Throw an exception
throw new Exception ("Un expected value "+ memberType.ToString() + "’.")
// If we introduce a new user type in future, we can easily find
// the problem here.
}
Not Good:
if(memberType == eMemberTypes.Registered)
{
// Registered user… do something…
}
else
{
// Guest user... do something…
// If we introduce another user type in future, this code will
// fail and will not be noticed.
}
}
Use of ENUM
Use enum wherever required. Do not use numbers or strings to indicate discrete values.
Good:
enum MailType
{
Html,
PlainText,
Attachment
}
void SendMail(string message, MailType mailType)
{
switch (mailType)
{
case MailType.Html:
// Do something
break;
case MailType.PlainText:
// Do something
break;
case MailType.Attachment:
// Do something
break;
default:
// Do something
break;
}
}
Not Good:
void SendMail(string message, string mailType)
{
switch(mailType)
{
case "Html":
// Do something
break;
case "PlainText":
// Do something
break;
case "Attachment":
// Do something
break;
default:
// Do something
break;
}
}
Error Messages
• When displaying error messages, in addition to telling what is wrong, the message should also tell what should the user do to solve the problem. Instead of message like "Failed to update database.", suggest what should the user do: "Failed to update database. Please make sure the login id and password are correct." • Show short and friendly message to the user. But log the actual error with all possible information. This will help a lot in diagnosing problems.
Comments
• Do not write comments for every line of code and every variable declared.
• Write comments wherever required. But good readable code will require very less comments. If all variables and method names are meaningful, that would make the code very readable and will not need much comments.
• Less lines of comments will make the code more elegant. But if the code is not clean/readable and there are less comments, that is worse.
• If you have to use some complex or weird logic for any reason, document it very well with sufficient comments.
Comments
• Use // or /// for comments. Avoid using /* … */• If you initialize a numeric variable to a special number other than 0, -1 etc, document the reason for choosing that value.• The bottom line is, write clean, readable code such a way that it doesn't need any comments to understand. • Do a spell check on comments and also make sure proper grammar and punctuation is used.
Exception Handling
• Never do a 'catch exception and do nothing'. If you hide an exception, you will never know if the exception happened or not. • In case of exceptions, give a friendly message to the user, but log the actual error with all possible details about the error, including the time it occurred, method and class name etc. • Always catch only the specific exception, not generic exception.
Good Exception Handling
void ReadFromFile ( string fileName )
{
try
{
// read from file.
}
catch (FileIOException ex)
{
// log error.
// re-throw exception depending on your case.
throw;
}
}
Bad Exception Handling
void ReadFromFile ( string fileName )
{
try
{
// read from file.
}
catch (Exception ex)
{
// Catching general exception is bad... we will never know whether
// it was a file error or some other error.
// Here you are hiding an exception.
// In this case no one will ever know that an exception happened.
return "";
}
}
Exception Handling
• No need to catch the general exception in all your methods. Leave it open and let the application crash. This will help you find most of the errors during development cycle.
• Do not write try-catch in all your methods. Use it only if there is a possibility that a a specific exception may occur. For example, if you are writing into a file, handle only FileIOException.
• Do not use exceptions for normal or expected errors, or for normal flow of control. (Throwing an exception is an extremely costly operation.)
Exception Handling
• Do not write very large try-catch blocks. If required, write separate try-catch for each task you perform and enclose only the specific piece of code inside the try-catch. This will help you find which piece of code generated the exception and you can give specific error message to the user.• You may write your own custom exception classes, if required in your application. Do not derive your custom exceptions from the base class SystemException. Instead, inherit from ApplicationException.
• All code paths that result in an exception should provide a method to check for success without throwing an exception. For example, to avoid a FileNotFoundException you can call File.Exists. This might not always be possible, but the goal is that under normal execution no exceptions should be thrown.
Exception Handling
• Cleanup code should be put in finally blocks. Never do cleanup code, e.g., closing streams, restoring state (as the mouse cursor), outside of a finally block.
Do not create methods that throw checked Exceptions like NullReferenceException or IndexOutOfRangeException.
Thank You!
• We all just write code for “working application”• Any developer can do that• There is a need to write “Efficient Code”• Writing 'Efficient Code' is an art and you must learn and practice it.
Working Code vs. Good Code
• A good code is: 1. Reliable2. Maintainable3. Efficient• If any of the above is missing then the code is not a good code and a lot of time could be wasted in trying to understand code and identify issues.
Naming Conventions and Standards
• Pascal Casing - First character of all words are Upper Case and other characters are in lower case.
E.g. BackColor
• Camel Casing - First character of all words, except the first word are Upper Case and other characters are in lower case.
E.g. backColor
Use Pascal casing for Class names
public class HelloWorld
{
... }
Use Pascal casing for Method names
public class HelloWorld
{
void SayHello(string name)
{
...
}
}
Use Camel casing for variables and method parameters
public class HelloWorld
{
int totalCount = 0;
void SayHello(string name)
{
string fullMessage = "Hello " + name;
...
}
}
Do not use Hungarian notation to name variables
• In earlier days most of the programmers liked it - having the data type as a prefix for the variable name and using m_ as prefix for member variables.
Example:
string m_sName;
int nAge;
• However, in .NET coding standards, this is not recommended. Usage of datatype and M_ to represent member variables should not be used. All variables should use camel casing.
Uppercase
• Uppercase characters are often used for enumerations and constant values.• Can also be used for only identifiers that consist of two or fewer letters. For example:
System.IO
System.Web.IO
Use Meaningful, descriptive words to name variables
• Do not use abbreviations. Use name, address, salary etc instead of nam, addr, sal• Do not use single character variable names like i, n, x etc. Use names like index, temp• One exception in this case would be variables used for iterations in loops
for ( int i = 0; i < count; i++ )
{
...
}
• Do not use underscores (_) in variable names. • Do not use variable names that resemble keywords.• Prefix boolean variables, properties and methods with “is” or similar prefixes. Example: private bool isFinished
Namespace Naming Convention
• The general rule for naming namespaces is to use the company name followed by the technology name and optionally the feature and design as follows.
CompanyName.TechnologyName[.Feature][.Design]
For Example:
1.Microsoft.Media
2.Microsoft.Media.Design
Interface Naming Guidelines
• Name interfaces with nouns or noun phrases, or adjectives that describe behavior. For E.g.
public interface IServiceProvider
public interface IFormatable
• Use Pascal Case.• Prefix interface names with the letter “I”, to indicate that the type is an interface.• Do not use the underscore character (_).
Static Field Naming Guidelines
• Use nouns, noun phrases, or abbreviations of nouns to name static fields. • Use Pascal Case. • Do not use a Hungarian notation prefix on static field names. • It is recommended that you use static properties instead of public static fields whenever possible.
Parameter Naming Guidelines
• Use descriptive parameter names.• Use Camel Case for parameter names.• Do not prefix parameter names with Hungarian type notation.• Examples:
Type GetType(string typeName)
string Format(string format, object[] args)
Prefixes for the UI elements
• Use appropriate prefix for the UI elements so that you can identify them from the rest of the variables.• This will help you group all of the UI elements together and easy to access all of them from the intellisense.• Some prefixes are as follows:
Case Sensitivity
• Do not use names that require case sensitivity. Components must be fully usable from both case-sensitive and case-insensitive languages.• Do not create two namespaces with names that differ only by case. For example, a case insensitive language cannot distinguish between the following two namespace declarations.
Namespace IridiumSoftware
Namespace iridiumsoftware
• Do not create a function with parameter names that differ only by case. The following example is incorrect.
void MyFunction(string a, string A)
Case Sensitivity
• Do not create a type with property names that differ only by case. For Example:
int Color {get, set}
int COLOR {get, set}
• Do not create a type with method names that differ only by case. For Example:
void calculate()
void Calculate()
File name should match with class name
• For example, for the class HelloWorld, the file name should be HelloWorld.cs
Indentation and Spacing
• Use TAB for indentation. Do not use SPACES. • Comments should be in the same level as the code.
Example
Good Practice
// Format a message and display
string fullMessage = "Hello " + name;
DateTime currentTime = DateTime.Now;
string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString();
Not so Good Practice
// Format a message and display
string fullMessage = "Hello " + name;
DateTime currentTime = DateTime.Now;
string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString();
#endregion
Indentation and Spacing
• Curly braces ( {} ) should be in the same level as the code outside the braces. Example.
Indentation and Spacing
Use one blank line to separate logical groups of code.
Not a Good Practice:
bool SayHello(string name)
{
string fullMessage = "Hello " + name;
DateTime currentTime = DateTime.Now;
string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString();
MessageBox.Show(message);
if (true)
{
// Do something
// ...
return false;
}
return true;
}
return true;
}
Good Practice:
bool SayHello(string name)
{
string fullMessage = "Hello " + name;
DateTime currentTime = DateTime.Now;
string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString();
MessageBox.Show(message);
if (true)
{
// Do something
// ...
return false;
}
Indentation and Spacing
• There should be one and only one single blank line between each method inside the class. • The curly braces should be on a separate line and not in the same line as if, for etc.
Good:
if ( ... )
{
// Do something
}
Not good:
if ( ... ) {
// Do something
}
Indentation and Spacing
• Use a single space before and after each operator and brackets
Good:
if ( showResult == true )
{
for ( int i = 0; i < 10; i++ )
{
//
}
}
Not Good:
if(showResult==true)
{
for(int i= 0;i<10;i++)
{
//
}
}
Which code looks better?
bool SayHello (string name){
string fullMessage = "Hello " + name;
DateTime currentTime = DateTime.Now;
string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString();
MessageBox.Show ( message );
if ( ... ) {
// Do something //
...
return false;
}
return true;
}
bool SayHello (string name)
{
string fullMessage = "Hello " + name;
DateTime currentTime = DateTime.Now;
string message = fullMessage + ", the time is : " + currentTime.ToShortTimeString();
MessageBox.Show ( message );
if ( ... )
{
// Do something //
...
return false;
}
return true;
}
Follow the best practices for best programming
• Avoid having too large files. If a file has more than 300~400 lines of code, you must consider refactoring code into helper classes. • Avoid writing very long methods. A method should typically have 1~25 lines of code. If a method has more than 25 lines of code, you must consider re factoring into separate methods.
Naming Methods
• Use verbs or verb phrases to name methods. • Use Pascal Case. • Method name should tell what it does. Do not use misleading names. If the method name is obvious, there is no need of documentation explaining what the method does.
Good Practice:
void SavePhoneNumber (string phoneNumber)
{
// Save the phone number.
}
Not a Good Practice:
// This method will save the phone number.
void SaveData (string phoneNumber)
{
// Save the phone number.
}
One job per Method
A method should do only 'one job'. Do not combine more than one job in a single method, even if those jobs are very small
Good Practice:
void main()
{
// Save the address.
SaveAddress(address);
// Send an email to the supervisor to inform that the address is updated.
SendEmail(address, email);
}
void SaveAddress(string address)
{
// Save the address. //
}
void SendEmail(string address, string email)
{
// Send an email to inform the supervisor that the address is changed.
}
•
Not a good practice:
// Save address and send an email to the supervisor to inform that
// the address is updated.
void main()
{
SaveAddress(address, email);
}
void SaveAddress(string address, string email)
{
// Job 1.
// Save the address.
// ...
// Job 2.
// Send an email to inform the supervisor that the address is changed.
// ...
}
Use of #region
•Use #region to group related pieces of code together.
• Keep private member variables, properties and methods in the top of the file and public members in the bottom.
Types
• Use the C# or VB.NET specific types, rather than the alias types defined in System namespace.
Good: int age;
string name; object contactInfo;
Not Good:
Int16 age;
String name;
Object contactInfo;
Some Key Points
• Do not hardcode numbers. Use constants instead or the better way is to use config files that can be easily changes.• Do not hardcode strings. Use config files. • Convert strings to lowercase or upper case before comparing. This will ensure the string will match even if the string being compared has a different case. For example:
if ( name.ToLower() == “john” )
{
//…
}
Some Key Points
• Use String.Empty instead of “” . For Example:
Good:
If ( name == String.Empty )
{
// do something
}
Not Good:
If ( name == “” )
{
// do something
}
Some Key Points
• Do not make the member variables public or protected. Keep them private and expose public/protected Properties.• Never hardcode a path or drive name in code. Get the application path programmatically and use relative path. • Never assume that your code will run from drive "C:". You may never know, some users may run it from network or from a "Z:". • Do not have more than one class in a single file.
Some Key Points
• Avoid public methods and properties, unless they really need to be accessed from outside the class. Use “internal” if they are accessed only within the same assembly.• Avoid passing too many parameters to a method. If you have more than 4~5 parameters, it is a good candidate to define a class or structure.• If you are opening database connections, sockets, file stream etc, always close them in the finally block. This will ensure that even if an exception occurs after opening the connection, it will be safely closed in the finally block.• Declare variables as close as possible to where it is first used. Use one variable declaration per line.
StringBuilder Class
Use StringBuilder class instead of String when you have to manipulate string objects in a loop.
Using String
public string ComposeMessage (string[] lines)
{
string message = String.Empty;
for (int i = 0; i < lines.Length; i++)
{
message += lines [i];
}
return message;
}
Using StringBuilder
public string ComposeMessage (string[] lines)
{
StringBuilder message = new StringBuilder();
for (int i = 0; i < lines.Length; i++)
{
message.Append( lines[i] );
}
return message.ToString();
}
Watch for unexpected values
Always watch for unexpected values.
If you are using a parameter with 2 possible values, never assume that if one is not matching then the only possibility is the other value.
void example()
{
//Good:
if(memberType == eMemberTypes.Registered)
{
// Registered user…do something…
}
else if ( memberType == eMemberTypes.Guest )
{
// Guest user... do something…
}
else
{
// Un expected user type. Throw an exception
throw new Exception ("Un expected value "+ memberType.ToString() + "’.")
// If we introduce a new user type in future, we can easily find
// the problem here.
}
Not Good:
if(memberType == eMemberTypes.Registered)
{
// Registered user… do something…
}
else
{
// Guest user... do something…
// If we introduce another user type in future, this code will
// fail and will not be noticed.
}
}
Use of ENUM
Use enum wherever required. Do not use numbers or strings to indicate discrete values.
Good:
enum MailType
{
Html,
PlainText,
Attachment
}
void SendMail(string message, MailType mailType)
{
switch (mailType)
{
case MailType.Html:
// Do something
break;
case MailType.PlainText:
// Do something
break;
case MailType.Attachment:
// Do something
break;
default:
// Do something
break;
}
}
Not Good:
void SendMail(string message, string mailType)
{
switch(mailType)
{
case "Html":
// Do something
break;
case "PlainText":
// Do something
break;
case "Attachment":
// Do something
break;
default:
// Do something
break;
}
}
Error Messages
• When displaying error messages, in addition to telling what is wrong, the message should also tell what should the user do to solve the problem. Instead of message like "Failed to update database.", suggest what should the user do: "Failed to update database. Please make sure the login id and password are correct." • Show short and friendly message to the user. But log the actual error with all possible information. This will help a lot in diagnosing problems.
Comments
• Do not write comments for every line of code and every variable declared.
• Write comments wherever required. But good readable code will require very less comments. If all variables and method names are meaningful, that would make the code very readable and will not need much comments.
• Less lines of comments will make the code more elegant. But if the code is not clean/readable and there are less comments, that is worse.
• If you have to use some complex or weird logic for any reason, document it very well with sufficient comments.
Comments
• Use // or /// for comments. Avoid using /* … */• If you initialize a numeric variable to a special number other than 0, -1 etc, document the reason for choosing that value.• The bottom line is, write clean, readable code such a way that it doesn't need any comments to understand. • Do a spell check on comments and also make sure proper grammar and punctuation is used.
Exception Handling
• Never do a 'catch exception and do nothing'. If you hide an exception, you will never know if the exception happened or not. • In case of exceptions, give a friendly message to the user, but log the actual error with all possible details about the error, including the time it occurred, method and class name etc. • Always catch only the specific exception, not generic exception.
Good Exception Handling
void ReadFromFile ( string fileName )
{
try
{
// read from file.
}
catch (FileIOException ex)
{
// log error.
// re-throw exception depending on your case.
throw;
}
}
Bad Exception Handling
void ReadFromFile ( string fileName )
{
try
{
// read from file.
}
catch (Exception ex)
{
// Catching general exception is bad... we will never know whether
// it was a file error or some other error.
// Here you are hiding an exception.
// In this case no one will ever know that an exception happened.
return "";
}
}
Exception Handling
• No need to catch the general exception in all your methods. Leave it open and let the application crash. This will help you find most of the errors during development cycle.
• Do not write try-catch in all your methods. Use it only if there is a possibility that a a specific exception may occur. For example, if you are writing into a file, handle only FileIOException.
• Do not use exceptions for normal or expected errors, or for normal flow of control. (Throwing an exception is an extremely costly operation.)
Exception Handling
• Do not write very large try-catch blocks. If required, write separate try-catch for each task you perform and enclose only the specific piece of code inside the try-catch. This will help you find which piece of code generated the exception and you can give specific error message to the user.• You may write your own custom exception classes, if required in your application. Do not derive your custom exceptions from the base class SystemException. Instead, inherit from ApplicationException.
• All code paths that result in an exception should provide a method to check for success without throwing an exception. For example, to avoid a FileNotFoundException you can call File.Exists. This might not always be possible, but the goal is that under normal execution no exceptions should be thrown.
Exception Handling
• Cleanup code should be put in finally blocks. Never do cleanup code, e.g., closing streams, restoring state (as the mouse cursor), outside of a finally block.
Do not create methods that throw checked Exceptions like NullReferenceException or IndexOutOfRangeException.
Thank You!
Best ASP.NET coding practices
1. Use Master Pages.
Why?
For keeping UI consistency in earlier version different User controls were created and included in different pages. A master page is a template for other pages, with shared layout and functionality. The master page defines placeholders for content pages. The result page is a combination (merge) of the master page and the content page. So for applying a consistent look and feel for a whole web site Master pages is used in .Net .
2. Use Themes and Skins.
Why?
Themes is another feature of ASP.NET . Themes, or skins, allow developers to create a customized look for web applications. Using Themes, it makes simple to customize the appearance of a site and all visual elements can be customized. Themes can be applied to controls, pages, and entire sites.
3. Use separate files for JavaScript functions and include in Master pages.
Why?
It saves repetition of same JavaScript function in different files.
4. Use SiteMaps..
Why?
It saves time of coding for navigation from one page to another page in a web application.
5. Use a common Exception Handling page..
Why?
If any exception occur, catch it and redirect to a common Exception Handling page with exception details and Page name and display this information in user friendly manner in this Exception Handling page.
6. Use Web Parts..
Why?
Web Parts provides a consistent look for a site, while still allowing user customization of style and content. It contains Zone controls (areas on a page where the content is consistent) and Web part controls (content areas for each zone) which are new in .Net .
7. Use Login Controls..
Why?
Security is very important for protecting confi dential and personal information.
8. Place all code (.cs) fi les and dlls used, in App_Code folder..
Why?
In .Net , during compilation fi rst it compile code of code fi les resides in App_Code folder, so there will be no need to make reference of them.
9. Use bulk copy classes for bulk copying of data (e.g. SqlBulkCopy class in SQL .Net)..
Why?
Bulk copy classes provides the fastest way to transfer set of data from once
source to the other.
Benefits.
1. It helps to improve consistency and readability of code.
2. Error free work and helps to maintain the applications.
3. To improve the performance and reduce the code.
Why?
For keeping UI consistency in earlier version different User controls were created and included in different pages. A master page is a template for other pages, with shared layout and functionality. The master page defines placeholders for content pages. The result page is a combination (merge) of the master page and the content page. So for applying a consistent look and feel for a whole web site Master pages is used in .Net .
2. Use Themes and Skins.
Why?
Themes is another feature of ASP.NET . Themes, or skins, allow developers to create a customized look for web applications. Using Themes, it makes simple to customize the appearance of a site and all visual elements can be customized. Themes can be applied to controls, pages, and entire sites.
3. Use separate files for JavaScript functions and include in Master pages.
Why?
It saves repetition of same JavaScript function in different files.
4. Use SiteMaps..
Why?
It saves time of coding for navigation from one page to another page in a web application.
5. Use a common Exception Handling page..
Why?
If any exception occur, catch it and redirect to a common Exception Handling page with exception details and Page name and display this information in user friendly manner in this Exception Handling page.
6. Use Web Parts..
Why?
Web Parts provides a consistent look for a site, while still allowing user customization of style and content. It contains Zone controls (areas on a page where the content is consistent) and Web part controls (content areas for each zone) which are new in .Net .
7. Use Login Controls..
Why?
Security is very important for protecting confi dential and personal information.
8. Place all code (.cs) fi les and dlls used, in App_Code folder..
Why?
In .Net , during compilation fi rst it compile code of code fi les resides in App_Code folder, so there will be no need to make reference of them.
9. Use bulk copy classes for bulk copying of data (e.g. SqlBulkCopy class in SQL .Net)..
Why?
Bulk copy classes provides the fastest way to transfer set of data from once
source to the other.
Benefits.
1. It helps to improve consistency and readability of code.
2. Error free work and helps to maintain the applications.
3. To improve the performance and reduce the code.
Best practices to Improve your .net codes performance
The below mentioned practices can Improve your .net codes performance
1. DateTime.Now() method is commonly used to get the current time.
The value returned by the Now() method is in the current machine
time-zone context. A common practice is to convert time that is going
to be stored or sent between machines into Universal (UCT) time.
When daylight savings time is a possibility, this can introduce bugs.
For example –
DateTime date = DateTime.Now().ToUniversalTime();
The value that results from the above code will be off by an hour if
called during the extra hour that occurs during the daylight savings
time switch in the fall. To fi x this, a best practice is to call DateTime.
UtcNow() instead of calling DateTime.Now(), and then converting it
to universal time.
DateTime date = DateTime.UtcNow();
This code will always have the 24-hour-day perspective, and may
then be safely converted to local time.
2. When using Add/Subtract functions with DateTime, take into account
the daylight savings time.
For example –
DateTime dt = DateTime.Parse(“Nov 4, 2007 12:00:00 AM”);
dt = dt.AddHours(3.0);
The expected result will be 03:00:00 AM. This result may seem correct
at fi rst; however, on November 04, 2007, at 02:00 AM PST, the daylight
savings time change took effect. So the correct answer should have
been Nov 4, 2007, 02:00:00 AM. To avoid this, the following syntax
can be used:
dt = dt.ToUniversalTime().AddHours(3.0).ToLocalTime();
This converts the local time to UTC, performs the calculation, and
then converts back to local time.
3. Do not compare strings by converting them to uppercase or lowercase, use
String.Compare instead, which can ignore the case and compare.
4 .Avoid the use of ArrayList - Objects added to the ArrayList are added
as System.Object and when retrieving values back from the ArrayList,
these objects have to be unboxed to return the actual value type. So it is
recommended to use the custom typed collections instead of ArrayList.
For example - string objects can be stored in StringCollection
(System.Collection.Specialized)
5. Always catch the specifi c exceptions instead of generic System
Exception.
6. While copying data from one DataTable to another, the simple way
is to clone an existing DataTable, loop through all rows of source
DataTable and copy data column by column and add row to the
destination DataTable. But this is not a feasible solution if there are
millions of records.
Hence, a better way is to use the DataTable.
ImportRow()method.
The ImportRow() method of the DataTable
copies a row into a DataTable with all of the properties and data of the
row.
It actually calls NewRow() method on destination DataTable with
current table schema and sets DataRowState to "Added".
Benefits
* Performance of the application can be improved.
* Some of these tips will give more accurate results.
* Quality of coding can be improved.
1. DateTime.Now() method is commonly used to get the current time.
The value returned by the Now() method is in the current machine
time-zone context. A common practice is to convert time that is going
to be stored or sent between machines into Universal (UCT) time.
When daylight savings time is a possibility, this can introduce bugs.
For example –
DateTime date = DateTime.Now().ToUniversalTime();
The value that results from the above code will be off by an hour if
called during the extra hour that occurs during the daylight savings
time switch in the fall. To fi x this, a best practice is to call DateTime.
UtcNow() instead of calling DateTime.Now(), and then converting it
to universal time.
DateTime date = DateTime.UtcNow();
This code will always have the 24-hour-day perspective, and may
then be safely converted to local time.
2. When using Add/Subtract functions with DateTime, take into account
the daylight savings time.
For example –
DateTime dt = DateTime.Parse(“Nov 4, 2007 12:00:00 AM”);
dt = dt.AddHours(3.0);
The expected result will be 03:00:00 AM. This result may seem correct
at fi rst; however, on November 04, 2007, at 02:00 AM PST, the daylight
savings time change took effect. So the correct answer should have
been Nov 4, 2007, 02:00:00 AM. To avoid this, the following syntax
can be used:
dt = dt.ToUniversalTime().AddHours(3.0).ToLocalTime();
This converts the local time to UTC, performs the calculation, and
then converts back to local time.
3. Do not compare strings by converting them to uppercase or lowercase, use
String.Compare instead, which can ignore the case and compare.
4 .Avoid the use of ArrayList - Objects added to the ArrayList are added
as System.Object and when retrieving values back from the ArrayList,
these objects have to be unboxed to return the actual value type. So it is
recommended to use the custom typed collections instead of ArrayList.
For example - string objects can be stored in StringCollection
(System.Collection.Specialized)
5. Always catch the specifi c exceptions instead of generic System
Exception.
6. While copying data from one DataTable to another, the simple way
is to clone an existing DataTable, loop through all rows of source
DataTable and copy data column by column and add row to the
destination DataTable. But this is not a feasible solution if there are
millions of records.
Hence, a better way is to use the DataTable.
ImportRow()method.
The ImportRow() method of the DataTable
copies a row into a DataTable with all of the properties and data of the
row.
It actually calls NewRow() method on destination DataTable with
current table schema and sets DataRowState to "Added".
Benefits
* Performance of the application can be improved.
* Some of these tips will give more accurate results.
* Quality of coding can be improved.
DataGridView Checkbox Column Handling in VB.NET
Problem Description
We encounter a situation where we need to edit the Datagridview rows based on the value of Datagridview Checkbox column Cell.
But the datagridview Checkbox cell will not commit immediately after checking the cell.It will be committed only after checking the next cell.
So the below solution makes the cell to commits it's value once it is checked.
Solution Description
Bind the data to the Datagridview.Place the below code in the CurrentCellDirtyStateChanged event of the datagridview.
When the user checks the cell,CurrentCellDirtyStateChanged event wiil be fired and then cell's dirty stae is checked.
If so, CommitEdit method is called.
Sample Coding
Private Sub DataGridView1_CurrentCellDirtyStateChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.CurrentCellDirtyStateChanged
Dim dgCheckBoxCell As DataGridViewCheckBoxCell
Try
'Checking whether the Datagridview Checkbox column is the first column
If DataGridView1.CurrentCellAddress.X = 0 Then
dgCheckBoxCell = DataGridView1.CurrentRow.Cells(0)
If (DataGridView1.IsCurrentCellDirty) Then 'Checking for dirty cell
DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit) 'If it is dirty, making them to commit
If (dgCheckBoxCell.Value) Then
'Enabling the selected row editable for sample.
DataGridView1.CurrentRow.Cells("EmpID").ReadOnly = False
DataGridView1.CurrentRow.Cells("Designtn").ReadOnly = False
DataGridView1.CurrentRow.Cells("Location").ReadOnly = False
DataGridView1.CurrentRow.Cells("Experience").ReadOnly = False
Else
'Disabling the selected row for editing.
DataGridView1.CurrentRow.Cells("EmpID").ReadOnly = True
DataGridView1.CurrentRow.Cells("Designtn").ReadOnly = True
DataGridView1.CurrentRow.Cells("Location").ReadOnly = True
DataGridView1.CurrentRow.Cells("Experience").ReadOnly = True
End If
End If
End If
Catch ex As Exception
End Try
We encounter a situation where we need to edit the Datagridview rows based on the value of Datagridview Checkbox column Cell.
But the datagridview Checkbox cell will not commit immediately after checking the cell.It will be committed only after checking the next cell.
So the below solution makes the cell to commits it's value once it is checked.
Solution Description
Bind the data to the Datagridview.Place the below code in the CurrentCellDirtyStateChanged event of the datagridview.
When the user checks the cell,CurrentCellDirtyStateChanged event wiil be fired and then cell's dirty stae is checked.
If so, CommitEdit method is called.
Sample Coding
Private Sub DataGridView1_CurrentCellDirtyStateChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.CurrentCellDirtyStateChanged
Dim dgCheckBoxCell As DataGridViewCheckBoxCell
Try
'Checking whether the Datagridview Checkbox column is the first column
If DataGridView1.CurrentCellAddress.X = 0 Then
dgCheckBoxCell = DataGridView1.CurrentRow.Cells(0)
If (DataGridView1.IsCurrentCellDirty) Then 'Checking for dirty cell
DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit) 'If it is dirty, making them to commit
If (dgCheckBoxCell.Value) Then
'Enabling the selected row editable for sample.
DataGridView1.CurrentRow.Cells("EmpID").ReadOnly = False
DataGridView1.CurrentRow.Cells("Designtn").ReadOnly = False
DataGridView1.CurrentRow.Cells("Location").ReadOnly = False
DataGridView1.CurrentRow.Cells("Experience").ReadOnly = False
Else
'Disabling the selected row for editing.
DataGridView1.CurrentRow.Cells("EmpID").ReadOnly = True
DataGridView1.CurrentRow.Cells("Designtn").ReadOnly = True
DataGridView1.CurrentRow.Cells("Location").ReadOnly = True
DataGridView1.CurrentRow.Cells("Experience").ReadOnly = True
End If
End If
End If
Catch ex As Exception
End Try
End Sub
SQL Interview Questions Part 1
1. What is Distributed Queries in SQL Server?
Distributed queries access data from multiple heterogeneous data sources. These data sources can be stored on either the same or different computers. Microsoft SQL Server supports distributed queries by using OLE DB.
SQL Server users can use distributed queries to access the following:
#. Distributed data stored in multiple instances of SQL Server.
#. Heterogeneous data stored in various relational and nonrelational data sources accessed by using an OLE DB provider.
2. What is the use of Keyword WITH ENCRYPTION keyword in SQL Server?
WITH ENCRYPTION keyword is used to encrypt the text of the Stored Procedure.
Once SPs are encrypted, the original text of the SP are not visible. User who has created the SP will need to save the text to be used to create SP somewhere safe to reuse it again or Users that have access to system tables or database files can retrieve the encrypted text.
3. What is user-defined functions in SQL Server?
User-Defined Functions allows to define custom T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.
In terms of functionality it is similar to C# or VB.NET functions except the fact that code is written in T-SQL.
4. What is user-defined data type in SQL Server?
User-defined data types can be used when several tables must store the same type of data in a column and you must ensure that these columns have exactly the same data type, length, and nullability.
5. What is a tuple?
A tuple is an instance of data within a relational database
6. What is the use of bit data type and what kind of data is stored into it?
Bit datatype is used to store boolean information like 1 or 0 where 1 is considered as true and 0 is considered as false.
While querying the database we need to use 1 or 0 in the select statement to filter out the data instead of true and false.
7. What is blocking in SQL Server?
Blocking in SQL Server is a scenario where one connection to SQL Server locks one or more records, and a second connection to SQL Server requires a conflicting lock type on the record or records locked by the first connection. This causes the second connection to wait until the first connection releases its locks. By default, a connection will wait an unlimited amount of time for the blocking lock to go away.
8. Difference between varchar and char:
varchar are variable length strings with a maximum length specified. If a string is less than the maximum length, then it is stored verbatim without any extra characters.
char are fixed length strings with a set length specified. If a string is less than the set length, then it is padded with extra characters so that it's length is the set length.
Use varchar when your strings do not have a fixed length (e.g. names, cities, etc.)
Use char when your strings are always going to be the same length (e.g. phone numbers, zip codes, etc)
9. Can we create a Foreign Key with out Primary Key?
Yes. If the table has Unique Key then it is possible to create a Foreign Constraint.
10. What is Check Constraint?
Check constraint specifies a condition that is enforced for each row of the table on which the constraint is defined. Once constraint is defined, insert or update to the data within the tables is checked against the defined constraint.
11. How to get GUID in sql server?
select newid().
This will return you GUID
12. What is DTC?
The Microsoft Distributed Transaction Coordinator (MS DTC) is a transaction manager that allows client applications to include several different sources of data in one transaction. MS DTC coordinates committing the distributed transaction across all the servers enlisted in the transaction
13. What are three SQL keywords used to change or set someone’s permissions?
Grant, Deny and Revoke
14. What is Log Shipping?
In Microsoft SQL Server, you can use log shipping to feed transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database. This allows you to have a backup server and also provides a way to offload query processing from the main computer (the source server) to read-only destination servers
15. What is DTS?
DTS is a set of tools you can use to import, export, and transform heterogeneous data between one or more data sources, such as Microsoft SQL Server, Microsoft Excel, or Microsoft Access. Connectivity is provided through OLE DB, an open-standard for data access. ODBC (Open Database Connectivity) data sources are supported through the OLE DB Provider for ODBC.
Distributed queries access data from multiple heterogeneous data sources. These data sources can be stored on either the same or different computers. Microsoft SQL Server supports distributed queries by using OLE DB.
SQL Server users can use distributed queries to access the following:
#. Distributed data stored in multiple instances of SQL Server.
#. Heterogeneous data stored in various relational and nonrelational data sources accessed by using an OLE DB provider.
2. What is the use of Keyword WITH ENCRYPTION keyword in SQL Server?
WITH ENCRYPTION keyword is used to encrypt the text of the Stored Procedure.
Once SPs are encrypted, the original text of the SP are not visible. User who has created the SP will need to save the text to be used to create SP somewhere safe to reuse it again or Users that have access to system tables or database files can retrieve the encrypted text.
3. What is user-defined functions in SQL Server?
User-Defined Functions allows to define custom T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.
In terms of functionality it is similar to C# or VB.NET functions except the fact that code is written in T-SQL.
4. What is user-defined data type in SQL Server?
User-defined data types can be used when several tables must store the same type of data in a column and you must ensure that these columns have exactly the same data type, length, and nullability.
5. What is a tuple?
A tuple is an instance of data within a relational database
6. What is the use of bit data type and what kind of data is stored into it?
Bit datatype is used to store boolean information like 1 or 0 where 1 is considered as true and 0 is considered as false.
While querying the database we need to use 1 or 0 in the select statement to filter out the data instead of true and false.
7. What is blocking in SQL Server?
Blocking in SQL Server is a scenario where one connection to SQL Server locks one or more records, and a second connection to SQL Server requires a conflicting lock type on the record or records locked by the first connection. This causes the second connection to wait until the first connection releases its locks. By default, a connection will wait an unlimited amount of time for the blocking lock to go away.
8. Difference between varchar and char:
varchar are variable length strings with a maximum length specified. If a string is less than the maximum length, then it is stored verbatim without any extra characters.
char are fixed length strings with a set length specified. If a string is less than the set length, then it is padded with extra characters so that it's length is the set length.
Use varchar when your strings do not have a fixed length (e.g. names, cities, etc.)
Use char when your strings are always going to be the same length (e.g. phone numbers, zip codes, etc)
9. Can we create a Foreign Key with out Primary Key?
Yes. If the table has Unique Key then it is possible to create a Foreign Constraint.
10. What is Check Constraint?
Check constraint specifies a condition that is enforced for each row of the table on which the constraint is defined. Once constraint is defined, insert or update to the data within the tables is checked against the defined constraint.
11. How to get GUID in sql server?
select newid().
This will return you GUID
12. What is DTC?
The Microsoft Distributed Transaction Coordinator (MS DTC) is a transaction manager that allows client applications to include several different sources of data in one transaction. MS DTC coordinates committing the distributed transaction across all the servers enlisted in the transaction
13. What are three SQL keywords used to change or set someone’s permissions?
Grant, Deny and Revoke
14. What is Log Shipping?
In Microsoft SQL Server, you can use log shipping to feed transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database. This allows you to have a backup server and also provides a way to offload query processing from the main computer (the source server) to read-only destination servers
15. What is DTS?
DTS is a set of tools you can use to import, export, and transform heterogeneous data between one or more data sources, such as Microsoft SQL Server, Microsoft Excel, or Microsoft Access. Connectivity is provided through OLE DB, an open-standard for data access. ODBC (Open Database Connectivity) data sources are supported through the OLE DB Provider for ODBC.
Splitting Name field into First, Middle and Last Name
The following query is used to split up the FullName field of MS Access into First, Middle and Last Name based on delimiter space.
SELECT Left(FullName,instr(FullName , ' ')) AS [First Name], Right(FullName, ( Len
(FullName) - Instrev(FullName, ' ')) ) As [Last Name], Mid ( FullName, instr
(FullName , ' ') + 1, Instrev(FullName, ' ') - instr(FullName , ' ') - 1) As
[Middle Name]
FROM [Employee];
Assume the FullName Field of Employee table contains
FullName
Viji R Raj
Bob A Antony
Cath B Mony
OUTPUT
First Name Last Name Middle Name
Viji Raj R
Bob Antony A
Cath Mony B
SELECT Left(FullName,instr(FullName , ' ')) AS [First Name], Right(FullName, ( Len
(FullName) - Instrev(FullName, ' ')) ) As [Last Name], Mid ( FullName, instr
(FullName , ' ') + 1, Instrev(FullName, ' ') - instr(FullName , ' ') - 1) As
[Middle Name]
FROM [Employee];
Assume the FullName Field of Employee table contains
FullName
Viji R Raj
Bob A Antony
Cath B Mony
OUTPUT
First Name Last Name Middle Name
Viji Raj R
Bob Antony A
Cath Mony B
Determine if a particular column exists in MS Access table using VB.NET
In this article we will see how to determine if a particualar column exists in MS Access Database Table.
There are two methods:
1. Using DAO
2. Using OLEDB .NET provider
Using DAO
Prerequisites:
1. Need to add reference to Microsoft DAO 3.6 Object libraray
2. Add Reference -> COM tab -> Microsoft DAO 3.6 Object libraray
3. Imports DAO in the top of the form
'Determine if ColumnName Column exists in the given table
Function ColumnExists(ByVal ColumnName As String, ByVal AccessTableName As String) As Boolean
'Declaraion of Variables
Dim AccessDB As Database
Dim AccessTblDef As TableDef
Dim col As Field
Dim dbe As New DBEngine
Dim bColumnFound As Boolean = False
Try
AccessDB = dbe.OpenDatabase("C:\MyTestDB.mdb")
AccessTblDef = db.TableDefs(AccessTableName)
For Each col In tbl.Fields
'If column exists
If col.Name = ColumnName Then
bColumnFound = True
Exit For
End If
Next
Catch ex As Exception
Return bColumnFound
End Try
Return bColumnFound
End Function
Explanation
The DAO namespace contains data types Database, Tabledef and DBEngine
The Database type is used to assign access db.
A DAO tabledef, is an object that defines the structure of a base table or an attached table.
A base table is a table in a Microsoft Jet (.MDB) database. You can manipulate the structure of a base table using DAO objects or data definition language (DDL) SQL statements, and you can use recordsets and action queries to modify data in a base table.
The "Name" Field of fields of table def represents the column names.
Thus we can compare the name of the field f table def with the column name to search for.
Using OLEDB .NET Provider
Prerequisites:
Need to import reference to System.Data.OleDb Name space
Function ColumnExistsOLEDB(ByVal ColumnName As String, ByVal AccessTableName As String) As Boolean
'Declaration of variables
Dim OLEDBConn As New System.Data.OleDb.OleDbConnection
Dim SchemaTable As DataTable
Dim sConnString As String
Dim bColumnFound As Boolean = False
'Connection String - OLEDB
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyTestDB.mdb"
OLEDBConn.ConnectionString = sConnString
Try
'Open the database
OLEDBConn.Open()
'Retrieve schema information about AccessTableName Columns.
SchemaTable =
OLEDBConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, AccessTableName})
Dim i As Integer
Dim ColName As String
For i = 0 To SchemaTable.Rows.Count - 1
ColName = SchemaTable.Rows(RowCount)!COLUMN_NAME.ToString
'If the column exists
If ColName = ColumnName Then
bColumnFound = True
Exit For
End If
Next
Catch ex As Exception
Return bColumnFound
End Try
Return bColumnFound
End Function
Explanation
The GetOleDbSchemaTable returns schema information from a data source as indicated by a GUID, and after applying the specified restrictions.
Using this function we can get the schema definition of the specified table(Emp2008 in this case)
The COLUMN_NAME property of the schema does the trick and we can compare it with the column to search for.
USAGE Example
'Function to call ColumnExists with Access table name and column name to search for
Private Sub CheckForColumn ()
Dim bColumnExists As Boolean = False
'Column Name - EmpRefNo
'Table Name - Emp2008
bColumnExists = ColumnExists("EmpRefNo", "Emp2008")
If bColumnExists Then
MsgBox("Field Name EmpRefNo Exists in Emp2008 Table")
Else
MsgBox("Field Name EmpRefNo Does Not Exist in Emp2008 Table")
End If
End Function
'Function to call ColumnExists with Access table name and column name to search for
Private Sub CheckForColumn ()
Dim bColumnExists As Boolean = False
'Column Name - EmpRefNo
'Table Name - Emp2008
bColumnExists = ColumnExistsOLEDB("EmpRefNo", "Emp2008")
If bColumnExists Then
MsgBox("Field Name EmpRefNo Exists in Emp2008 Table")
Else
MsgBox("Field Name EmpRefNo Does Not Exist in Emp2008 Table")
End If
End Function
Though the approach is differenent, effectively the result is same.
There are two methods:
1. Using DAO
2. Using OLEDB .NET provider
Using DAO
Prerequisites:
1. Need to add reference to Microsoft DAO 3.6 Object libraray
2. Add Reference -> COM tab -> Microsoft DAO 3.6 Object libraray
3. Imports DAO in the top of the form
'Determine if ColumnName Column exists in the given table
Function ColumnExists(ByVal ColumnName As String, ByVal AccessTableName As String) As Boolean
'Declaraion of Variables
Dim AccessDB As Database
Dim AccessTblDef As TableDef
Dim col As Field
Dim dbe As New DBEngine
Dim bColumnFound As Boolean = False
Try
AccessDB = dbe.OpenDatabase("C:\MyTestDB.mdb")
AccessTblDef = db.TableDefs(AccessTableName)
For Each col In tbl.Fields
'If column exists
If col.Name = ColumnName Then
bColumnFound = True
Exit For
End If
Next
Catch ex As Exception
Return bColumnFound
End Try
Return bColumnFound
End Function
Explanation
The DAO namespace contains data types Database, Tabledef and DBEngine
The Database type is used to assign access db.
A DAO tabledef, is an object that defines the structure of a base table or an attached table.
A base table is a table in a Microsoft Jet (.MDB) database. You can manipulate the structure of a base table using DAO objects or data definition language (DDL) SQL statements, and you can use recordsets and action queries to modify data in a base table.
The "Name" Field of fields of table def represents the column names.
Thus we can compare the name of the field f table def with the column name to search for.
Using OLEDB .NET Provider
Prerequisites:
Need to import reference to System.Data.OleDb Name space
Function ColumnExistsOLEDB(ByVal ColumnName As String, ByVal AccessTableName As String) As Boolean
'Declaration of variables
Dim OLEDBConn As New System.Data.OleDb.OleDbConnection
Dim SchemaTable As DataTable
Dim sConnString As String
Dim bColumnFound As Boolean = False
'Connection String - OLEDB
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyTestDB.mdb"
OLEDBConn.ConnectionString = sConnString
Try
'Open the database
OLEDBConn.Open()
'Retrieve schema information about AccessTableName Columns.
SchemaTable =
OLEDBConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, AccessTableName})
Dim i As Integer
Dim ColName As String
For i = 0 To SchemaTable.Rows.Count - 1
ColName = SchemaTable.Rows(RowCount)!COLUMN_NAME.ToString
'If the column exists
If ColName = ColumnName Then
bColumnFound = True
Exit For
End If
Next
Catch ex As Exception
Return bColumnFound
End Try
Return bColumnFound
End Function
Explanation
The GetOleDbSchemaTable returns schema information from a data source as indicated by a GUID, and after applying the specified restrictions.
Using this function we can get the schema definition of the specified table(Emp2008 in this case)
The COLUMN_NAME property of the schema does the trick and we can compare it with the column to search for.
USAGE Example
'Function to call ColumnExists with Access table name and column name to search for
Private Sub CheckForColumn ()
Dim bColumnExists As Boolean = False
'Column Name - EmpRefNo
'Table Name - Emp2008
bColumnExists = ColumnExists("EmpRefNo", "Emp2008")
If bColumnExists Then
MsgBox("Field Name EmpRefNo Exists in Emp2008 Table")
Else
MsgBox("Field Name EmpRefNo Does Not Exist in Emp2008 Table")
End If
End Function
'Function to call ColumnExists with Access table name and column name to search for
Private Sub CheckForColumn ()
Dim bColumnExists As Boolean = False
'Column Name - EmpRefNo
'Table Name - Emp2008
bColumnExists = ColumnExistsOLEDB("EmpRefNo", "Emp2008")
If bColumnExists Then
MsgBox("Field Name EmpRefNo Exists in Emp2008 Table")
Else
MsgBox("Field Name EmpRefNo Does Not Exist in Emp2008 Table")
End If
End Function
Though the approach is differenent, effectively the result is same.
API Function to get the computer name of the PC
The following VB Function is used to get the computer name of the current PC.
It uses GetComputerName library.
We need to declare the dll GetComputerName in the top the VB Module.
' API declared to find the current computer name.
Public Declare Function GetComputerName Lib "Kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public Function ComputerName_FX() As String
' Function calls the API function and returns a string of the computer name.
On Error Resume Next
Dim lSize As Long
Dim lpstrBuffer As String
lSize = 255
lpstrBuffer = Space$(lSize)
'Call GetComputerName API
If GetComputerName(lpstrBuffer, lSize) Then
ComputerName_FX = Left$(lpstrBuffer, lSize)
Else
ComputerName_FX = ""
End If
End Function
Example:
ComputerNameStr = ComputerName_FX
Debug.Print ComputerNameStr
Output:
RAJ-PC
It uses GetComputerName library.
We need to declare the dll GetComputerName in the top the VB Module.
' API declared to find the current computer name.
Public Declare Function GetComputerName Lib "Kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public Function ComputerName_FX() As String
' Function calls the API function and returns a string of the computer name.
On Error Resume Next
Dim lSize As Long
Dim lpstrBuffer As String
lSize = 255
lpstrBuffer = Space$(lSize)
'Call GetComputerName API
If GetComputerName(lpstrBuffer, lSize) Then
ComputerName_FX = Left$(lpstrBuffer, lSize)
Else
ComputerName_FX = ""
End If
End Function
Example:
ComputerNameStr = ComputerName_FX
Debug.Print ComputerNameStr
Output:
RAJ-PC
Monday, June 29, 2009
Convert Date Format CCYYMMDD to MMDDYYYY/MMDDYY
The following SQL user defined function is used to convert the given date in CCYYMMDD or CCYY/MM/DD format to MM/DD/CCYY.
/*This function returns mm/dd/ccyy given a date in the format ccyymmdd */
/* Input of CCYYMMDD or CCYY/MM/DD will output MM/DD/CCYY */
/* dbo. FuncCCYYMMDD_MM_DD_CCYY(FieldName) */
/* Example: 19780621 = 06/21/1978*/
[CODE]
CREATE FUNCTION dbo.FuncCCYYMMDD_MM_DD_CCYY (@Date varChar(50)) RETURNS varchar(50)
AS
Begin
Declare @Result as varchar(50)
SET @Result = substring(@Date,5,2) + '/' + Substring(@Date,7,2) + '/' + Substring(@Date,1,4)
IF @Result = '00/00/0000'
Set @Result = Null
Return (@Result)
End
[/CODE]
This SQL user defined function is used to convert the given date in the format CCYYMMDD or CCYY/MM/DD into the format MM/DD/YY.
/*This function returns mm/dd/yy given a date in the format ccyymmdd */
/* Input of CCYYMMDD or CCYY/MM/DD will output MM/DD/YY */
/* dbo. FuncCCYYMMDD_MM_DD_YY(FieldName) */
/* Example: 19780621 = 06/21/78*/
[CODE]
CREATE FUNCTION dbo.FuncCCYYMMDD_MM_DD_YY (@Date VARCHAR(50)) RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @Result AS VARCHAR(50)
SET @Result = substring(@Date,5,2) + '/' + Substring(@Date,7,2) + '/' + Substring(@Date,3,2)
IF @Result = '00/00/0000'
SET @Result = NULL
RETURN (@Result)
END
[/CODE]
/*This function returns mm/dd/ccyy given a date in the format ccyymmdd */
/* Input of CCYYMMDD or CCYY/MM/DD will output MM/DD/CCYY */
/* dbo. FuncCCYYMMDD_MM_DD_CCYY(FieldName) */
/* Example: 19780621 = 06/21/1978*/
[CODE]
CREATE FUNCTION dbo.FuncCCYYMMDD_MM_DD_CCYY (@Date varChar(50)) RETURNS varchar(50)
AS
Begin
Declare @Result as varchar(50)
SET @Result = substring(@Date,5,2) + '/' + Substring(@Date,7,2) + '/' + Substring(@Date,1,4)
IF @Result = '00/00/0000'
Set @Result = Null
Return (@Result)
End
[/CODE]
This SQL user defined function is used to convert the given date in the format CCYYMMDD or CCYY/MM/DD into the format MM/DD/YY.
/*This function returns mm/dd/yy given a date in the format ccyymmdd */
/* Input of CCYYMMDD or CCYY/MM/DD will output MM/DD/YY */
/* dbo. FuncCCYYMMDD_MM_DD_YY(FieldName) */
/* Example: 19780621 = 06/21/78*/
[CODE]
CREATE FUNCTION dbo.FuncCCYYMMDD_MM_DD_YY (@Date VARCHAR(50)) RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @Result AS VARCHAR(50)
SET @Result = substring(@Date,5,2) + '/' + Substring(@Date,7,2) + '/' + Substring(@Date,3,2)
IF @Result = '00/00/0000'
SET @Result = NULL
RETURN (@Result)
END
[/CODE]
Resize an image's dimensions and Save it as a file in different extension
The following VB.NET code is used to resize the dimensions of an image file dynamically and save it as a file in different extension.
For example, we may want to resize the dimensions of a set of image files(1024 x 1024 jpg images) from a folder into 600 x 600 and save it as a bmp files.
We need to resize the image without affecting the quality of the image.
Code to resize an image and save it as a file in different extension:
[CODE]
Private Sub btnResize_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnResize.Click
'Original Image
Dim img As New Bitmap("C:\Users\Raj\Desktop\Niagara_Pictures\DSC06721.JPG")
OrigPictureBox.Image = img
Dim resizedImg As Image = Nothing
'Resize the Image to 300 x 300 dimension
resizedImg = ResizeImage(img, 300, 300)
If Not resizedImg Is Nothing Then
ResizedPictureBox.Image = resizedImg
'Save the Image file in different extension (.bmp)
'You can use OpenFileDialog to get the File Name
resizedImg.Save("E:\Test.bmp", Imaging.ImageFormat.Bmp)
End If
End Sub
//Function to return the resized image
Private Function ResizeImage(ByVal img As Image, ByVal NewWidth As Integer, ByVal NewHeight As Integer) As Image
Dim resizedImg As Image = Nothing
Try
Dim bmp As Bitmap = New Bitmap(NewWidth, NewHeight)
Dim gr As Graphics = Graphics.FromImage(bmp)
//Resize the image without affecting the quality
gr.SmoothingMode = Drawing2D.SmoothingMode.HighQuality
gr.DrawImage(img, 0, 0, Width, Height)
resizedImg = bmp
Catch ex As Exception
resizedImg = Nothing
End Try
Return resizedImg
End Function
[/CODE]
The final image will be with the new dimensoins but with high quality.
For example, we may want to resize the dimensions of a set of image files(1024 x 1024 jpg images) from a folder into 600 x 600 and save it as a bmp files.
We need to resize the image without affecting the quality of the image.
Code to resize an image and save it as a file in different extension:
[CODE]
Private Sub btnResize_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnResize.Click
'Original Image
Dim img As New Bitmap("C:\Users\Raj\Desktop\Niagara_Pictures\DSC06721.JPG")
OrigPictureBox.Image = img
Dim resizedImg As Image = Nothing
'Resize the Image to 300 x 300 dimension
resizedImg = ResizeImage(img, 300, 300)
If Not resizedImg Is Nothing Then
ResizedPictureBox.Image = resizedImg
'Save the Image file in different extension (.bmp)
'You can use OpenFileDialog to get the File Name
resizedImg.Save("E:\Test.bmp", Imaging.ImageFormat.Bmp)
End If
End Sub
//Function to return the resized image
Private Function ResizeImage(ByVal img As Image, ByVal NewWidth As Integer, ByVal NewHeight As Integer) As Image
Dim resizedImg As Image = Nothing
Try
Dim bmp As Bitmap = New Bitmap(NewWidth, NewHeight)
Dim gr As Graphics = Graphics.FromImage(bmp)
//Resize the image without affecting the quality
gr.SmoothingMode = Drawing2D.SmoothingMode.HighQuality
gr.DrawImage(img, 0, 0, Width, Height)
resizedImg = bmp
Catch ex As Exception
resizedImg = Nothing
End Try
Return resizedImg
End Function
[/CODE]
The final image will be with the new dimensoins but with high quality.
SqlConnection.RetrieveStatistics Method
The SqlConnection object has a method RetrieveStatistics which returns a name value pair collection of statistics(IDictionary Type) at the point in time the method is called.
This statistics will be very useful in debugging purposes. Let us consider an example of populating the datatable with 1 lac records.
We may want to check how many times the server round trip has happened, number of bytes received, connection time etc.
We can retrieve those statistics using the method RetrieveStatistics which was introduced in .NET 2.0.
There are totally 18 statistics information.
In this c# example we can see some statistics details (Connection Time, ServerRoundTrip and Bytes Received)
[CODE]
using System.Data.SqlClient;
private void populateGrid()
{
String ConnStr = "User ID=sa;Password=sa;Initial Catalog=OrderDB; Data Source=local";
SqlConnection connection = new SqlConnection(ConnStr);
//set StatisticsEnabled = true in order for the SqlConnection object to begin collecting statistics.
connection.StatisticsEnabled = true;
Try {
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
var strSql = "SELECT * FROM Orders";
SqlCommand cmd = new SqlCommand(strSql, connection);
cmd.CommandTimeout = 999999;
dataAdapter = new SqlDataAdapter(cmd);
lblStatus.Text = "Loading........Please Wait";
dataAdapter.Fill(dt);
//Retrieve the statistics in IDictionary
IDictionary statistics = connection.RetrieveStatistics();
//Some of the statistics
long serverRoundtrips = (long) statistics["ServerRoundtrips"];
long connectionTime = (long) statistics["ConnectionTime"];
long bytesReceived = (long) statistics["BytesReceived"];
dataAdapter.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
[/CODE]
If you want to get all the statistics details we can iterate through statistics(IDictionary) in a loop and all details
This statistics will be very useful in debugging purposes. Let us consider an example of populating the datatable with 1 lac records.
We may want to check how many times the server round trip has happened, number of bytes received, connection time etc.
We can retrieve those statistics using the method RetrieveStatistics which was introduced in .NET 2.0.
There are totally 18 statistics information.
In this c# example we can see some statistics details (Connection Time, ServerRoundTrip and Bytes Received)
[CODE]
using System.Data.SqlClient;
private void populateGrid()
{
String ConnStr = "User ID=sa;Password=sa;Initial Catalog=OrderDB; Data Source=local";
SqlConnection connection = new SqlConnection(ConnStr);
//set StatisticsEnabled = true in order for the SqlConnection object to begin collecting statistics.
connection.StatisticsEnabled = true;
Try {
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
var strSql = "SELECT * FROM Orders";
SqlCommand cmd = new SqlCommand(strSql, connection);
cmd.CommandTimeout = 999999;
dataAdapter = new SqlDataAdapter(cmd);
lblStatus.Text = "Loading........Please Wait";
dataAdapter.Fill(dt);
//Retrieve the statistics in IDictionary
IDictionary statistics = connection.RetrieveStatistics();
//Some of the statistics
long serverRoundtrips = (long) statistics["ServerRoundtrips"];
long connectionTime = (long) statistics["ConnectionTime"];
long bytesReceived = (long) statistics["BytesReceived"];
dataAdapter.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
[/CODE]
If you want to get all the statistics details we can iterate through statistics(IDictionary) in a loop and all details
Validation of numeric field in VB.NET
This code will allow the user to enter digits, '-' symbol(For negative value) and '.'(for decimal) only in the textbox
Private Sub expenseTextboxKeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles expenseTextBox.KeyPress, revenueTextBox.KeyPress
'Handles for multiple textboxes
Dim tb As TextBox = CType(sender, TextBox)
Dim chr As Char = e.KeyChar
If IsNumeric(e.KeyChar) And Not e.KeyChar = "-" Then
'If adding the character to the end of the current TextBox value results in
' a numeric value, go on. Otherwise, set e.Handled to True, and don't let
' the character to be added.
e.Handled = Not IsNumeric(tb.Text & e.KeyChar)
ElseIf e.KeyChar = "." Then
'For the decimal character (.) we need a different rule:
'If adding a decimal to the end of the current value of the TextBox results
' in a numeric value, it can be added. If not, this means we already have a
' decimal in the TextBox value, so we only allow the new decimal to sit in
' when it is overwriting the previous decimal.
If Not (tb.SelectedText = "." Or IsNumeric(tb.Text & e.KeyChar)) Then
e.Handled = True
End If
ElseIf e.KeyChar = "-" Then
'A negative sign is prevented if the "-" key is pressed in any location
' other than the begining of the number, or if the number already has a
' negative sign
If tb.SelectionStart <> 0 Or Microsoft.VisualBasic.Left(tb.Text, 1) = "-" Then
e.Handled = True
End If
ElseIf Not Char.IsControl(e.KeyChar) Then
'IsControl is checked, because without that, keys like BackSpace couldn't
' work correctly.
e.Handled = True
End If
End Sub
Private Sub expenseTextboxKeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles expenseTextBox.KeyPress, revenueTextBox.KeyPress
'Handles for multiple textboxes
Dim tb As TextBox = CType(sender, TextBox)
Dim chr As Char = e.KeyChar
If IsNumeric(e.KeyChar) And Not e.KeyChar = "-" Then
'If adding the character to the end of the current TextBox value results in
' a numeric value, go on. Otherwise, set e.Handled to True, and don't let
' the character to be added.
e.Handled = Not IsNumeric(tb.Text & e.KeyChar)
ElseIf e.KeyChar = "." Then
'For the decimal character (.) we need a different rule:
'If adding a decimal to the end of the current value of the TextBox results
' in a numeric value, it can be added. If not, this means we already have a
' decimal in the TextBox value, so we only allow the new decimal to sit in
' when it is overwriting the previous decimal.
If Not (tb.SelectedText = "." Or IsNumeric(tb.Text & e.KeyChar)) Then
e.Handled = True
End If
ElseIf e.KeyChar = "-" Then
'A negative sign is prevented if the "-" key is pressed in any location
' other than the begining of the number, or if the number already has a
' negative sign
If tb.SelectionStart <> 0 Or Microsoft.VisualBasic.Left(tb.Text, 1) = "-" Then
e.Handled = True
End If
ElseIf Not Char.IsControl(e.KeyChar) Then
'IsControl is checked, because without that, keys like BackSpace couldn't
' work correctly.
e.Handled = True
End If
End Sub
List all users logged in to a MS access database
This function will display all users logged in to a MS Access database
[CODE]
Declare Function LDBUser_GetUsers Lib "MSLDBUSR.DLL" _
(lpszUserBuffer() As String, ByVal lpszFilename As String, _
ByVal nOptions As Long) As Integer
Public Function GetUsers(Optional StrDbPath As String)
ReDim lpszUserBuffer(1) As String
Dim intLooper As Integer
Dim Cusers As Long
Dim strMsgBox As String
On Error GoTo Err_GetUsers
' Check to see if a database path was passed
' to the function. If the argument was not used,
' assume that we're to investigate the .ldb
' of the current database.
If IsMissing(StrDbPath) Or StrDbPath = "" Then
StrDbPath = CurrentDb.Name
End If
' Set Cusers to the number of computers currently connected
' to the database. Insert computer information into the
' lpszUserBuffer array.
' Arguments of LdbUser_Get Users:
' 1 = All users who have logged in since the LDB file was
' created
' 2 = Only users who are currently logged in
' 4 = Only users who are causing the database file to be
' corrupted
' 8 = Just return the count of users
Cusers = LDBUser_GetUsers(lpszUserBuffer(), StrDbPath, 2)
' Print possible errors returned by the function.
Select Case Cusers
Case -1
strMsgBox = "Can't open the LDB file"
Case -2
strMsgBox = "No user connected"
Case -3
strMsgBox = "Can't Create an Array"
Case -4
strMsgBox = "Can't redimension array"
Case -5
strMsgBox = "Invalid argument passed"
Case -6
strMsgBox = "Memory allocation error"
Case -7
strMsgBox = "Bad index"
Case -8
strMsgBox = "Out of memory"
Case -9
strMsgBox = "Invalid Argument"
Case -10
strMsgBox = "LDB is suspected as corrupted"
Case -11
strMsgBox = "Invalid argument"
Case -12
strMsgBox = "Unable to read MDB file"
Case -13
strMsgBox = "Can't open the MDB file"
Case -14
strMsgBox = "Can't find the LDB file"
End Select
If Not IsEmpty(strMsgBox) And strMsgBox <> "" Then
MsgBox strMsgBox, vbCritical, "Error"
Exit Function
End If
' Print computer names to Debug window.
For intLooper = 0 To Cusers - 1
Debug.Print "User"; intLooper + 1; ":"; _
lpszUserBuffer(intLooper)
Next
Exit_GetUsers:
Exit Function
Err_GetUsers:
MsgBox Err.Description
Resume Exit_GetUsers
End Function
[/CODE]
[CODE]
Declare Function LDBUser_GetUsers Lib "MSLDBUSR.DLL" _
(lpszUserBuffer() As String, ByVal lpszFilename As String, _
ByVal nOptions As Long) As Integer
Public Function GetUsers(Optional StrDbPath As String)
ReDim lpszUserBuffer(1) As String
Dim intLooper As Integer
Dim Cusers As Long
Dim strMsgBox As String
On Error GoTo Err_GetUsers
' Check to see if a database path was passed
' to the function. If the argument was not used,
' assume that we're to investigate the .ldb
' of the current database.
If IsMissing(StrDbPath) Or StrDbPath = "" Then
StrDbPath = CurrentDb.Name
End If
' Set Cusers to the number of computers currently connected
' to the database. Insert computer information into the
' lpszUserBuffer array.
' Arguments of LdbUser_Get Users:
' 1 = All users who have logged in since the LDB file was
' created
' 2 = Only users who are currently logged in
' 4 = Only users who are causing the database file to be
' corrupted
' 8 = Just return the count of users
Cusers = LDBUser_GetUsers(lpszUserBuffer(), StrDbPath, 2)
' Print possible errors returned by the function.
Select Case Cusers
Case -1
strMsgBox = "Can't open the LDB file"
Case -2
strMsgBox = "No user connected"
Case -3
strMsgBox = "Can't Create an Array"
Case -4
strMsgBox = "Can't redimension array"
Case -5
strMsgBox = "Invalid argument passed"
Case -6
strMsgBox = "Memory allocation error"
Case -7
strMsgBox = "Bad index"
Case -8
strMsgBox = "Out of memory"
Case -9
strMsgBox = "Invalid Argument"
Case -10
strMsgBox = "LDB is suspected as corrupted"
Case -11
strMsgBox = "Invalid argument"
Case -12
strMsgBox = "Unable to read MDB file"
Case -13
strMsgBox = "Can't open the MDB file"
Case -14
strMsgBox = "Can't find the LDB file"
End Select
If Not IsEmpty(strMsgBox) And strMsgBox <> "" Then
MsgBox strMsgBox, vbCritical, "Error"
Exit Function
End If
' Print computer names to Debug window.
For intLooper = 0 To Cusers - 1
Debug.Print "User"; intLooper + 1; ":"; _
lpszUserBuffer(intLooper)
Next
Exit_GetUsers:
Exit Function
Err_GetUsers:
MsgBox Err.Description
Resume Exit_GetUsers
End Function
[/CODE]
could not load file or assembly 'CrystalDecisions.Windows.Forms, Version=10.2.3600.0, Culture=nutral
could not load file or assembly 'CrystalDecisions.Windows.Forms, Version=10.2.3600.0, Culture=nutral, PublicKeyTocke=....' or one of its dependencies. The system cannot find the file specified.
Possible Reason:
Prerequisite Merge module is not found. Need to install Crystal Reports for .Net Framework 2.0.
Solutions
1.
To create the crystal reports installer for the server:
1. create a deployment project.
2. right click and opened the properities window of the deployment setup.
3. click the Prequesities button.
4. select MDAC 2.8 and Crystal Reports for .Net 2.0
compiled the deployment project.
5. the redistributabe now resides in the [Project Name]/release/Crystal Reports directory with the name CRRedist2005_x86.msi
2.
Run CRRedist2005_x86.msi.
This file is located under C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\CrystalReports
3.
If you don't find CRRedist2005_x86.msi, download the file "CRRedist2005_x86.msi" and install it will work
Possible Reason:
Prerequisite Merge module is not found. Need to install Crystal Reports for .Net Framework 2.0.
Solutions
1.
To create the crystal reports installer for the server:
1. create a deployment project.
2. right click and opened the properities window of the deployment setup.
3. click the Prequesities button.
4. select MDAC 2.8 and Crystal Reports for .Net 2.0
compiled the deployment project.
5. the redistributabe now resides in the [Project Name]/release/Crystal Reports directory with the name CRRedist2005_x86.msi
2.
Run CRRedist2005_x86.msi.
This file is located under C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\CrystalReports
3.
If you don't find CRRedist2005_x86.msi, download the file "CRRedist2005_x86.msi" and install it will work
Populate DataGridView From Excel Sheet
Populate DataGridView From Excel Where First Name of Employee starts with 'C'
Need to include the reference Microsoft DAO 3.6
[CODE]
Imports System.Data
Imports System.Data.OleDb
Private Sub btnPopulateGrid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPopulateGrid.Click
Const stFile As String = "E:\EmployeeExcels\Employee.xls"
Const stCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES"";"
Dim dao_dbE As dao.DBEngine
Dim dao_DB As dao.Database
Dim strFirstSheetName As String
Try
'To get the Excel Sheet's Name
dao_dbE = New dao.DBEngine
dao_DB = dao_dbE.OpenDatabase("E:\EmployeeExcels\Employee.xls", False, True, "Excel 8.0;")
strFirstSheetName = dao_DB.TableDefs(1).Name
'The range is named Source and it also contains the fieldnames.
'The fieldnames are automatically added to the Datagrid columnnames.
Dim stSQL As String = "SELECT * FROM [" & strFirstSheetName & "] WHERE [First Name] LIKE 'C%'"
Dim cn As New OleDbConnection(stCon)
cn.Open()
Dim da As New OleDbDataAdapter(stSQL, cn)
'The Datasets name is viewed on top of the Datagrid.
Dim dt As New DataTable
'Populate the Datatable
da.Fill(dt)
'Populate the DataGridView
With Me.dbGridView
.DataSource = dt
.Refresh()
End With
da.Dispose()
' Adjust the column widths based on the displayed values.
Me.dbGridView.AutoResizeColumns( _
DataGridViewAutoSizeColumnsMode.DisplayedCells)
Catch ex As Exception
MsgBox(ex.ToString)
Finally
'Release objects from memory.
If Not dt Is Nothing Then
dt = Nothing
End If
If Not cn is Nothing Then
If cn.State = ConnectionState.Open Then
cn.Close()
End If
cn = Nothing
End If
End Try
End Sub
[/CODE]
Need to include the reference Microsoft DAO 3.6
[CODE]
Imports System.Data
Imports System.Data.OleDb
Private Sub btnPopulateGrid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPopulateGrid.Click
Const stFile As String = "E:\EmployeeExcels\Employee.xls"
Const stCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES"";"
Dim dao_dbE As dao.DBEngine
Dim dao_DB As dao.Database
Dim strFirstSheetName As String
Try
'To get the Excel Sheet's Name
dao_dbE = New dao.DBEngine
dao_DB = dao_dbE.OpenDatabase("E:\EmployeeExcels\Employee.xls", False, True, "Excel 8.0;")
strFirstSheetName = dao_DB.TableDefs(1).Name
'The range is named Source and it also contains the fieldnames.
'The fieldnames are automatically added to the Datagrid columnnames.
Dim stSQL As String = "SELECT * FROM [" & strFirstSheetName & "] WHERE [First Name] LIKE 'C%'"
Dim cn As New OleDbConnection(stCon)
cn.Open()
Dim da As New OleDbDataAdapter(stSQL, cn)
'The Datasets name is viewed on top of the Datagrid.
Dim dt As New DataTable
'Populate the Datatable
da.Fill(dt)
'Populate the DataGridView
With Me.dbGridView
.DataSource = dt
.Refresh()
End With
da.Dispose()
' Adjust the column widths based on the displayed values.
Me.dbGridView.AutoResizeColumns( _
DataGridViewAutoSizeColumnsMode.DisplayedCells)
Catch ex As Exception
MsgBox(ex.ToString)
Finally
'Release objects from memory.
If Not dt Is Nothing Then
dt = Nothing
End If
If Not cn is Nothing Then
If cn.State = ConnectionState.Open Then
cn.Close()
End If
cn = Nothing
End If
End Try
End Sub
[/CODE]
Calculate the Check Digit for an 11 digit UPC Code
A check digit is a form of redundancy check used for error detection, the decimal equivalent of a binary checksum. It consists of a single digit computed from the other digits in the message.
With a check digit, one can detect simple errors in the input of a series of digits, such as a single mistyped digit, or the permutation of two successive digits.
The final digit of a Universal Product Code( a specific type of barcode, that is widely used in the United States and Canada for tracking trade items in stores.) is a check digit computed as follows:
1. Add the digits (up to but not including the check digit) in the odd-numbered positions (first, third, fifth, etc.) together and multiply by three.
2. Add the digits (up to but not including the check digit) in the even-numbered positions (second, fourth, sixth, etc.) to the result.
3. If the last digit of the result is 0, then the check digit is 0.
4. The check digit will be the smallest number required to round the Sum to the nearest multiple of 10.
For eg: if the UPC barcode for a box of tissues is "036000241457". The last digit is the check digit "7", and if the other numbers are correct then the check digit calculation must produce 7.
1. We add the odd number digits: 0+6+0+2+1+5 = 14
2. Multiply the result by 3: 14 × 3 = 42
3. We add the even number digits: 3+0+0+4+4 = 11
4. We add the two results together: 42 + 11 = 53
5. 60 (the next highest multiple of 10) modulo 53 is 7. Therefore, 7 is the check digit
/*This will return the check digit based on the rules for a 11 digit UPC code */
[code]
CREATE FUNCTION dbo.FuncCheckDigit(@UPCNumber as VarChar(20))
RETURNS Int AS
BEGIN
Declare @Return as Int
Declare @EvenSum as Int
Declare @OddSum as Int
Declare @Total as Int
Declare @CheckDigit as Int
If len(@UPCNumber) = 11
Begin
Set @EvenSum = Cast(Substring(@UPCNumber,1,1) as Int) + Cast(Substring(@UPCNumber,3,1) as Int) + Cast(Substring(@UPCNumber,5,1) as Int) + Cast(Substring(@UPCNumber,7,1) as Int) + Cast(Substring(@UPCNumber,9,1) as Int) + Cast(Substring(@UPCNumber,11,1) as Int)
Set @OddSum = Cast(Substring(@UPCNumber,2,1) as Int) + Cast(Substring(@UPCNumber,4,1) as Int) + Cast(Substring(@UPCNumber,6,1) as Int) + Cast(Substring(@UPCNumber,8,1) as Int) + Cast(Substring(@UPCNumber,10,1) as Int)
Set @Total = @EvenSum * 3 + @OddSum
Set @CheckDigit = 0
While @CheckDigit <=9
Begin
If (Cast((Cast(@Total as Decimal(5,1)) + Cast(@CheckDigit as Decimal(5,1)))/10 as Decimal(5,1))) - (Floor(Cast((Cast(@Total as Decimal(5,1)) + Cast(@CheckDigit as Decimal(5,1)))/10 as Decimal(5,1)))) = 0
Begin
Set @Return = @CheckDigit
End
Set @CheckDigit = @CheckDigit + 1
End
End
Return @Return
END
[/code]
With a check digit, one can detect simple errors in the input of a series of digits, such as a single mistyped digit, or the permutation of two successive digits.
The final digit of a Universal Product Code( a specific type of barcode, that is widely used in the United States and Canada for tracking trade items in stores.) is a check digit computed as follows:
1. Add the digits (up to but not including the check digit) in the odd-numbered positions (first, third, fifth, etc.) together and multiply by three.
2. Add the digits (up to but not including the check digit) in the even-numbered positions (second, fourth, sixth, etc.) to the result.
3. If the last digit of the result is 0, then the check digit is 0.
4. The check digit will be the smallest number required to round the Sum to the nearest multiple of 10.
For eg: if the UPC barcode for a box of tissues is "036000241457". The last digit is the check digit "7", and if the other numbers are correct then the check digit calculation must produce 7.
1. We add the odd number digits: 0+6+0+2+1+5 = 14
2. Multiply the result by 3: 14 × 3 = 42
3. We add the even number digits: 3+0+0+4+4 = 11
4. We add the two results together: 42 + 11 = 53
5. 60 (the next highest multiple of 10) modulo 53 is 7. Therefore, 7 is the check digit
/*This will return the check digit based on the rules for a 11 digit UPC code */
[code]
CREATE FUNCTION dbo.FuncCheckDigit(@UPCNumber as VarChar(20))
RETURNS Int AS
BEGIN
Declare @Return as Int
Declare @EvenSum as Int
Declare @OddSum as Int
Declare @Total as Int
Declare @CheckDigit as Int
If len(@UPCNumber) = 11
Begin
Set @EvenSum = Cast(Substring(@UPCNumber,1,1) as Int) + Cast(Substring(@UPCNumber,3,1) as Int) + Cast(Substring(@UPCNumber,5,1) as Int) + Cast(Substring(@UPCNumber,7,1) as Int) + Cast(Substring(@UPCNumber,9,1) as Int) + Cast(Substring(@UPCNumber,11,1) as Int)
Set @OddSum = Cast(Substring(@UPCNumber,2,1) as Int) + Cast(Substring(@UPCNumber,4,1) as Int) + Cast(Substring(@UPCNumber,6,1) as Int) + Cast(Substring(@UPCNumber,8,1) as Int) + Cast(Substring(@UPCNumber,10,1) as Int)
Set @Total = @EvenSum * 3 + @OddSum
Set @CheckDigit = 0
While @CheckDigit <=9
Begin
If (Cast((Cast(@Total as Decimal(5,1)) + Cast(@CheckDigit as Decimal(5,1)))/10 as Decimal(5,1))) - (Floor(Cast((Cast(@Total as Decimal(5,1)) + Cast(@CheckDigit as Decimal(5,1)))/10 as Decimal(5,1)))) = 0
Begin
Set @Return = @CheckDigit
End
Set @CheckDigit = @CheckDigit + 1
End
End
Return @Return
END
[/code]
Hide menu bars of ms access database
Suppose we may want to hide all menu bars of access database (File, Edit Menu etc)
The following code is used to hide the menu bars.
[CODE]
Sub HideMenu()
'This will hide all menu bars and tool bars
For i = 1 To CommandBars.Count
CommandBars(i).Enabled = False
Next i
'Showing and enabling command bar controls
Application.CommandBars("Menu Bar").Controls("Edit").Enabled = False
Application.CommandBars("Menu Bar").Controls("View").Enabled = False
Application.CommandBars("Formatting").Controls("Font").Visible = False
End Sub
[/CODE]
The following code is used to hide the menu bars.
[CODE]
Sub HideMenu()
'This will hide all menu bars and tool bars
For i = 1 To CommandBars.Count
CommandBars(i).Enabled = False
Next i
'Showing and enabling command bar controls
Application.CommandBars("Menu Bar").Controls("Edit").Enabled = False
Application.CommandBars("Menu Bar").Controls("View").Enabled = False
Application.CommandBars("Formatting").Controls("Font").Visible = False
End Sub
[/CODE]
Change the properties of ms access database dynamically
We can set the properties of mdb dynamically.
We can set the Startup Form, Title, Application Icon, hide the DB Window, prevent the use of special keys etc
Private Sub Form_Open(Cancel As Integer)
'setting up start up properties
Call SetStartupProperties
End Sub
Sub SetStartupProperties()
Const DB_Text As Long = 10
Const DB_Boolean As Long = 1
Dim intX As Integer
Dim i As Integer
intX = ChangeProperty("AppTitle", DB_Text, "TEST")
intX = ChangeProperty("AppIcon", DB_Text, "E:\Viji\Samples\Icons\LOGO.bmp")
CurrentDb.Properties("UseAppIconForFrmRpt") = 1
Application.RefreshTitleBar
ChangeProperty "StartupForm", DB_Text, "Customers"
ChangeProperty "StartupShowDBWindow", DB_Boolean, False
ChangeProperty "StartupShowStatusBar", DB_Boolean, False
ChangeProperty "AllowBuiltinToolbars", DB_Boolean, False
ChangeProperty "AllowToolbarChanges", DB_Boolean, False
ChangeProperty "AllowShortcutMenus", DB_Boolean, False
ChangeProperty "AllowFullMenus", DB_Boolean, False
ChangeProperty "AllowBreakIntoCode", DB_Boolean, False
ChangeProperty "AllowSpecialKeys", DB_Boolean, False
ChangeProperty "AllowBypassKey", DB_Boolean, False
End Sub
[CODE]
Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
Dim dbs As Object, prp As Variant
Const conPropNotFoundError = 3270
Set dbs = CurrentDb
On Error GoTo Change_Err
dbs.Properties(strPropName) = varPropValue
ChangeProperty = True
Change_Bye:
Exit Function
Change_Err:
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, _
varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else ' Unknown error.
ChangeProperty = False
Resume Change_Bye
End If
End Function
[/CODE]
We can set the Startup Form, Title, Application Icon, hide the DB Window, prevent the use of special keys etc
Private Sub Form_Open(Cancel As Integer)
'setting up start up properties
Call SetStartupProperties
End Sub
Sub SetStartupProperties()
Const DB_Text As Long = 10
Const DB_Boolean As Long = 1
Dim intX As Integer
Dim i As Integer
intX = ChangeProperty("AppTitle", DB_Text, "TEST")
intX = ChangeProperty("AppIcon", DB_Text, "E:\Viji\Samples\Icons\LOGO.bmp")
CurrentDb.Properties("UseAppIconForFrmRpt") = 1
Application.RefreshTitleBar
ChangeProperty "StartupForm", DB_Text, "Customers"
ChangeProperty "StartupShowDBWindow", DB_Boolean, False
ChangeProperty "StartupShowStatusBar", DB_Boolean, False
ChangeProperty "AllowBuiltinToolbars", DB_Boolean, False
ChangeProperty "AllowToolbarChanges", DB_Boolean, False
ChangeProperty "AllowShortcutMenus", DB_Boolean, False
ChangeProperty "AllowFullMenus", DB_Boolean, False
ChangeProperty "AllowBreakIntoCode", DB_Boolean, False
ChangeProperty "AllowSpecialKeys", DB_Boolean, False
ChangeProperty "AllowBypassKey", DB_Boolean, False
End Sub
[CODE]
Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
Dim dbs As Object, prp As Variant
Const conPropNotFoundError = 3270
Set dbs = CurrentDb
On Error GoTo Change_Err
dbs.Properties(strPropName) = varPropValue
ChangeProperty = True
Change_Bye:
Exit Function
Change_Err:
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, _
varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else ' Unknown error.
ChangeProperty = False
Resume Change_Bye
End If
End Function
[/CODE]
Resize the form according to System's resolution
If the .NET windows forms developed in 1280 x 800 resolution, it may not display properly in a pc with 800 x 600 resolution.
To resolve this issue, we need to resize the Form's controls and Font Size as well.
Just call Resize_Form in Form's Load Event
[CODE]
Private Sub MainForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Change Form's Width and height to Workable area of the screen
Me.Width = Screen.PrimaryScreen.Bounds.Width
Me.Height = Screen.PrimaryScreen.Bounds.Height
Me.Bounds = Screen.PrimaryScreen.Bounds
'Resize the form
Resize_Form()
End Sub
[/CODE]
[CODE]
Private Sub Resize_Form()
Dim cControl As Control
If (Screen.PrimaryScreen.Bounds.Height = 800) And (Screen.PrimaryScreen.Bounds.Width = 1280) Then
Exit Sub
End If
With Me
For i_i = 0 To .Controls.Count - 1
If TypeOf .Controls(i_i) Is ComboBox Then ' cannot change Height
.Controls(i_i).Left = .Controls(i_i).Left / 1280 * Me.Width
.Controls(i_i).Top = .Controls(i_i).Top / 800 * Me.Height
.Controls(i_i).Width = .Controls(i_i).Width / 1280 * Me.Width
ElseIf TypeOf .Controls(i_i) Is Panel Then
.Controls(i_i).Left = .Controls(i_i).Left / 1280 * Me.Width
.Controls(i_i).Top = .Controls(i_i).Top / 800 * Me.Height
.Controls(i_i).Width = .Controls(i_i).Width / 1280 * Me.Width
.Controls(i_i).Height = .Controls(i_i).Height / 800 * Me.Height
'Do the same for Panel's Children
For Each cControl In .Controls(i_i).Controls
cControl.Left = cControl.Left / 1280 * Me.Width
cControl.Top = cControl.Top / 800 * Me.Height
cControl.Width = cControl.Width / 1280 * Me.Width
cControl.Height = cControl.Height / 800 * Me.Height
cControl.Font = New Font(cControl.Font.Name, cControl.Font.Size / 1280 * Me.Width)
If TypeOf cControl Is PictureBox Then 'Make it stretch
cControl.BackgroundImageLayout = ImageLayout.Stretch
End If
Next
ElseIf TypeOf .Controls(i_i) Is System.Windows.Forms.TabControl Then
.Controls(i_i).Left = .Controls(i_i).Left / 1280 * Me.Width
.Controls(i_i).Top = .Controls(i_i).Top / 800 * Me.Height
.Controls(i_i).Width = .Controls(i_i).Width / 1280 * Me.Width
.Controls(i_i).Height = .Controls(i_i).Height / 800 * Me.Height
ElseIf TypeOf .Controls(i_i) Is GroupBox Then
.Controls(i_i).Left = .Controls(i_i).Left / 1280 * Me.Width
.Controls(i_i).Top = .Controls(i_i).Top / 800 * Me.Height
.Controls(i_i).Width = .Controls(i_i).Width / 1280 * Me.Width
.Controls(i_i).Height = .Controls(i_i).Height / 800 * Me.Height
Else
.Controls(i_i).Left = .Controls(i_i).Left / 1280 * Me.Width
.Controls(i_i).Top = .Controls(i_i).Top / 800 * Me.Height
.Controls(i_i).Width = .Controls(i_i).Width / 1280 * Me.Width
.Controls(i_i).Height = .Controls(i_i).Height / 800 * Me.Height
End If
.Controls(i_i).Font = New Font(.Controls(i_i).Font.Name, .Controls(i_i).Font.Size / 1280 * Me.Width)
Next i_i
End With
End Sub
[/CODE]
To resolve this issue, we need to resize the Form's controls and Font Size as well.
Just call Resize_Form in Form's Load Event
[CODE]
Private Sub MainForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Change Form's Width and height to Workable area of the screen
Me.Width = Screen.PrimaryScreen.Bounds.Width
Me.Height = Screen.PrimaryScreen.Bounds.Height
Me.Bounds = Screen.PrimaryScreen.Bounds
'Resize the form
Resize_Form()
End Sub
[/CODE]
[CODE]
Private Sub Resize_Form()
Dim cControl As Control
If (Screen.PrimaryScreen.Bounds.Height = 800) And (Screen.PrimaryScreen.Bounds.Width = 1280) Then
Exit Sub
End If
With Me
For i_i = 0 To .Controls.Count - 1
If TypeOf .Controls(i_i) Is ComboBox Then ' cannot change Height
.Controls(i_i).Left = .Controls(i_i).Left / 1280 * Me.Width
.Controls(i_i).Top = .Controls(i_i).Top / 800 * Me.Height
.Controls(i_i).Width = .Controls(i_i).Width / 1280 * Me.Width
ElseIf TypeOf .Controls(i_i) Is Panel Then
.Controls(i_i).Left = .Controls(i_i).Left / 1280 * Me.Width
.Controls(i_i).Top = .Controls(i_i).Top / 800 * Me.Height
.Controls(i_i).Width = .Controls(i_i).Width / 1280 * Me.Width
.Controls(i_i).Height = .Controls(i_i).Height / 800 * Me.Height
'Do the same for Panel's Children
For Each cControl In .Controls(i_i).Controls
cControl.Left = cControl.Left / 1280 * Me.Width
cControl.Top = cControl.Top / 800 * Me.Height
cControl.Width = cControl.Width / 1280 * Me.Width
cControl.Height = cControl.Height / 800 * Me.Height
cControl.Font = New Font(cControl.Font.Name, cControl.Font.Size / 1280 * Me.Width)
If TypeOf cControl Is PictureBox Then 'Make it stretch
cControl.BackgroundImageLayout = ImageLayout.Stretch
End If
Next
ElseIf TypeOf .Controls(i_i) Is System.Windows.Forms.TabControl Then
.Controls(i_i).Left = .Controls(i_i).Left / 1280 * Me.Width
.Controls(i_i).Top = .Controls(i_i).Top / 800 * Me.Height
.Controls(i_i).Width = .Controls(i_i).Width / 1280 * Me.Width
.Controls(i_i).Height = .Controls(i_i).Height / 800 * Me.Height
ElseIf TypeOf .Controls(i_i) Is GroupBox Then
.Controls(i_i).Left = .Controls(i_i).Left / 1280 * Me.Width
.Controls(i_i).Top = .Controls(i_i).Top / 800 * Me.Height
.Controls(i_i).Width = .Controls(i_i).Width / 1280 * Me.Width
.Controls(i_i).Height = .Controls(i_i).Height / 800 * Me.Height
Else
.Controls(i_i).Left = .Controls(i_i).Left / 1280 * Me.Width
.Controls(i_i).Top = .Controls(i_i).Top / 800 * Me.Height
.Controls(i_i).Width = .Controls(i_i).Width / 1280 * Me.Width
.Controls(i_i).Height = .Controls(i_i).Height / 800 * Me.Height
End If
.Controls(i_i).Font = New Font(.Controls(i_i).Font.Name, .Controls(i_i).Font.Size / 1280 * Me.Width)
Next i_i
End With
End Sub
[/CODE]
Copy files from remote machine
To copy files from local machine we can simply do
System.IO.File.Copy()
as we are already logged in to the machine.
How to copy the files from a remote machine where we have not logged in?
Here is the solution.
We need to provide domainname, user name and password of the remote machine.
[CODE]
Imports System.Security.Principal
Imports System.Runtime.InteropServices
Public Class Form1
Inherits System.Windows.Forms.Form
'Functions needed to copy files from remote machine
_
Public Shared Function LogonUser(ByVal lpszUserName As String, ByVal lpszDomain As String, _
ByVal lpszPassword As String, ByVal dwLogonType As Integer, ByVal dwLogonProvider As Integer, _
ByRef phToken As IntPtr) As Integer
End Function
Public Sub copyRemoteFiles(ByVal sourceFile As String, ByVal destFile As String)
Dim admin_token As IntPtr
Dim wid_current As WindowsIdentity = WindowsIdentity.GetCurrent()
Dim wid_admin As WindowsIdentity = Nothing
Dim wic As WindowsImpersonationContext = Nothing
Try
If LogonUser(sUserName, sDomainName, sPassword, 9, 0, admin_token) <> 0 Then
wid_admin = New WindowsIdentity(admin_token)
wic = wid_admin.Impersonate()
If System.IO.File.Exists(sourceFile) Then
System.IO.File.Copy(sourceFile, destFile, True)
Else 'Copy Failed
Exit Sub
End If
Else
Exit Sub
End If
Catch se As System.Exception
Dim ret As Integer = Marshal.GetLastWin32Error()
MessageBox.Show(ret.ToString(), "Error code: " + ret.ToString())
MessageBox.Show(se.Message)
If wic IsNot Nothing Then
wic.Undo()
End If
Exit Sub
Finally
If wic IsNot Nothing Then
wic.Undo()
End If
End Try
End Sub
'Copy remote file
Private Sub btnCopyFiles_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCopyFiles.Click
Dim sourceFile As String
Dim destFile as String
sourceFile = txtSourceFile.Text.Trim 'Remote File Path
destFile = txtDestFile.Text.Trim 'Local File Path
'Copy remote file to local file
copyRemoteFiles(sourceFile, destFile)
End Sub
End Class
[/CODE]
System.IO.File.Copy()
as we are already logged in to the machine.
How to copy the files from a remote machine where we have not logged in?
Here is the solution.
We need to provide domainname, user name and password of the remote machine.
[CODE]
Imports System.Security.Principal
Imports System.Runtime.InteropServices
Public Class Form1
Inherits System.Windows.Forms.Form
'Functions needed to copy files from remote machine
Public Shared Function LogonUser(ByVal lpszUserName As String, ByVal lpszDomain As String, _
ByVal lpszPassword As String, ByVal dwLogonType As Integer, ByVal dwLogonProvider As Integer, _
ByRef phToken As IntPtr) As Integer
End Function
Public Sub copyRemoteFiles(ByVal sourceFile As String, ByVal destFile As String)
Dim admin_token As IntPtr
Dim wid_current As WindowsIdentity = WindowsIdentity.GetCurrent()
Dim wid_admin As WindowsIdentity = Nothing
Dim wic As WindowsImpersonationContext = Nothing
Try
If LogonUser(sUserName, sDomainName, sPassword, 9, 0, admin_token) <> 0 Then
wid_admin = New WindowsIdentity(admin_token)
wic = wid_admin.Impersonate()
If System.IO.File.Exists(sourceFile) Then
System.IO.File.Copy(sourceFile, destFile, True)
Else 'Copy Failed
Exit Sub
End If
Else
Exit Sub
End If
Catch se As System.Exception
Dim ret As Integer = Marshal.GetLastWin32Error()
MessageBox.Show(ret.ToString(), "Error code: " + ret.ToString())
MessageBox.Show(se.Message)
If wic IsNot Nothing Then
wic.Undo()
End If
Exit Sub
Finally
If wic IsNot Nothing Then
wic.Undo()
End If
End Try
End Sub
'Copy remote file
Private Sub btnCopyFiles_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCopyFiles.Click
Dim sourceFile As String
Dim destFile as String
sourceFile = txtSourceFile.Text.Trim 'Remote File Path
destFile = txtDestFile.Text.Trim 'Local File Path
'Copy remote file to local file
copyRemoteFiles(sourceFile, destFile)
End Sub
End Class
[/CODE]
selected row content of a datagridview
c# Code
To select the particular column's value whenever the row selection is changing in the datagridview
[CODE]
Add the handler to OnLoad function of the form
protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
//Add Selection changed handler
dbGridView.SelectionChanged += new EventHandler(dbGridView_SelectionChanged);
}
//Whenever the row selection is changed, the text box is filled with the Column 1 data
private void dbGridView_SelectionChanged (object sender, EventArgs e)
{
DataGridView dgv = (DataGridView)sender; //User selected WHOLE ROW (by clicking in the margin)
if (dgv.SelectedRows.Count > 0)
textBox1.Text = (dgv.SelectedRows[0].Cells[0].Value.ToString());
//User selected a cell (show the first cell in the row)
if (dgv.SelectedCells.Count > 0)
textBox1.Text = (dgv.Rows[dgv.SelectedCells[0].RowIndex].Cells[0].Value.ToString());
//User selected a cell, show that cell
if (dgv.SelectedCells.Count > 0)
textBox1.Text =(dgv.SelectedCells[0].Value.ToString());
}
[/CODE]
To select the particular column's value whenever the row selection is changing in the datagridview
[CODE]
Add the handler to OnLoad function of the form
protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
//Add Selection changed handler
dbGridView.SelectionChanged += new EventHandler(dbGridView_SelectionChanged);
}
//Whenever the row selection is changed, the text box is filled with the Column 1 data
private void dbGridView_SelectionChanged (object sender, EventArgs e)
{
DataGridView dgv = (DataGridView)sender; //User selected WHOLE ROW (by clicking in the margin)
if (dgv.SelectedRows.Count > 0)
textBox1.Text = (dgv.SelectedRows[0].Cells[0].Value.ToString());
//User selected a cell (show the first cell in the row)
if (dgv.SelectedCells.Count > 0)
textBox1.Text = (dgv.Rows[dgv.SelectedCells[0].RowIndex].Cells[0].Value.ToString());
//User selected a cell, show that cell
if (dgv.SelectedCells.Count > 0)
textBox1.Text =(dgv.SelectedCells[0].Value.ToString());
}
[/CODE]
Display Money value of countries in their own currency in DataGridView
In this article, we will discuss how to display Money value of countries in their own currency in DataGridView cells.
The System.Globalization.CultureInfo Class is used to find the currency symbol for various countries (locale).
To illustrate this, we can display money values for various countries in a datagridview.
Prerequisties:
1. Drag and Drop DataGridView on Windows Form and name it as dbGridView
2. Add 5 columns to dbGridView with different country Names
1. US
2. UK
3. Denmark
4. Finland
5. Canada
3. Populate dataGridView with some values
The handling of CellFormatting event of DataGridview does the actual trick.
The System.Globalization.CultureInfo class gives the information of all countries for number formatting.
In this example I have applied currency format for US, UK, Denmark, Finland and Canada.
But there are many countries formats are available in System.Globalization.CultureInfo Class.
The list of countries supported is specified in the bottom of the article.
[CODE]
Private Sub PopulateGridView ()
Dim dt As DataTable = New DataTable()
Dim US As DataColumn
Dim UK As DataColumn
Dim Denmark As DataColumn
Dim Finland As DataColumn
Dim Canada As DataColumn
'Data Columns for various countries
US = New DataColumn("US", System.Type.GetType("System.Decimal"))
UK = New DataColumn("UK", System.Type.GetType("System.Decimal"))
Denmark = New DataColumn("Denmark", System.Type.GetType("System.Decimal"))
Finland = New DataColumn("Finland", System.Type.GetType("System.Decimal"))
Canada = New DataColumn("Canada", System.Type.GetType("System.Decimal"))
dt.Columns.Add(US)
dt.Columns.Add(UK)
dt.Columns.Add(Denmark)
dt.Columns.Add(Finland)
dt.Columns.Add(Canada)
dt.AcceptChanges()
'Bind the data table to DataGridview
dbGridView.dataSource = dt
End Sub
'Format each cell with respective country's currency sign
Private Sub dbGridView_CellFormatting(ByVal sender As Object, _
ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) _
Handles DataGridView1.CellFormatting
Dim MyCellValue As Decimal
Dim MyUSCultureInfo As System.Globalization.CultureInfo
'United States
If e.ColumnIndex = 0 Then
MyUSCultureInfo = New System.Globalization.CultureInfo("en-US")
If Not e.Value Is Nothing Then
MyCellValue = Convert.ToDecimal(e.Value)
e.Value = MyCellValue.ToString("c", MyUSCultureInfo)
End If
'Great Britain
ElseIf e.ColumnIndex = 1 Then
MyUSCultureInfo = New System.Globalization.CultureInfo("en-GB")
If Not e.Value Is Nothing Then
MyCellValue = Convert.ToDecimal(e.Value)
e.Value = MyCellValue.ToString("c", MyUSCultureInfo)
End If
'Denmark
ElseIf e.ColumnIndex = 2 Then
MyUSCultureInfo = New System.Globalization.CultureInfo("da-DK")
If Not e.Value Is Nothing Then
MyCellValue = Convert.ToDecimal(e.Value)
e.Value = MyCellValue.ToString("c", MyUSCultureInfo)
End If
'Finland
ElseIf e.ColumnIndex = 3 Then
MyUSCultureInfo = New System.Globalization.CultureInfo("fi-FI")
If Not e.Value Is Nothing Then
MyCellValue = Convert.ToDecimal(e.Value)
e.Value = MyCellValue.ToString("c", MyUSCultureInfo)
End If
'Canada
ElseIf e.ColumnIndex = 4 Then
MyUSCultureInfo = New System.Globalization.CultureInfo("en-CA")
If Not e.Value Is Nothing Then
MyCellValue = Convert.ToDecimal(e.Value)
e.Value = MyCellValue.ToString("c", MyUSCultureInfo)
End If
End If
End Sub
[/CODE]
OUTPUT
[CODE]
US UK Denmark Australia Canada
$0.00 £0.00 kr. 0,00 0,00 € $0.00
[/CODE]
Some of other cultures are:
ar-DZ Arabic - Algeria
ar-IQ Arabic - Iraq
bg-BG Bulgarian - Bulgaria
en-IE English - Ireland
en-NZ English - New Zealand
en-PH English - Philippines
en-ZA English - South Africa
en-TT English - Trinidad and Tobago
en-GB English - United Kingdom
en-US English - United States
en-ZW English - Zimbabwe
et Estonian
et-EE Estonian - Estonia
fo Faroese
fo-FO Faroese - Faroe Islands
fa Farsi
fa-IR Farsi - Iran
fi Finnish
fi-FI Finnish - Finland
fr French
The System.Globalization.CultureInfo Class is used to find the currency symbol for various countries (locale).
To illustrate this, we can display money values for various countries in a datagridview.
Prerequisties:
1. Drag and Drop DataGridView on Windows Form and name it as dbGridView
2. Add 5 columns to dbGridView with different country Names
1. US
2. UK
3. Denmark
4. Finland
5. Canada
3. Populate dataGridView with some values
The handling of CellFormatting event of DataGridview does the actual trick.
The System.Globalization.CultureInfo class gives the information of all countries for number formatting.
In this example I have applied currency format for US, UK, Denmark, Finland and Canada.
But there are many countries formats are available in System.Globalization.CultureInfo Class.
The list of countries supported is specified in the bottom of the article.
[CODE]
Private Sub PopulateGridView ()
Dim dt As DataTable = New DataTable()
Dim US As DataColumn
Dim UK As DataColumn
Dim Denmark As DataColumn
Dim Finland As DataColumn
Dim Canada As DataColumn
'Data Columns for various countries
US = New DataColumn("US", System.Type.GetType("System.Decimal"))
UK = New DataColumn("UK", System.Type.GetType("System.Decimal"))
Denmark = New DataColumn("Denmark", System.Type.GetType("System.Decimal"))
Finland = New DataColumn("Finland", System.Type.GetType("System.Decimal"))
Canada = New DataColumn("Canada", System.Type.GetType("System.Decimal"))
dt.Columns.Add(US)
dt.Columns.Add(UK)
dt.Columns.Add(Denmark)
dt.Columns.Add(Finland)
dt.Columns.Add(Canada)
dt.AcceptChanges()
'Bind the data table to DataGridview
dbGridView.dataSource = dt
End Sub
'Format each cell with respective country's currency sign
Private Sub dbGridView_CellFormatting(ByVal sender As Object, _
ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) _
Handles DataGridView1.CellFormatting
Dim MyCellValue As Decimal
Dim MyUSCultureInfo As System.Globalization.CultureInfo
'United States
If e.ColumnIndex = 0 Then
MyUSCultureInfo = New System.Globalization.CultureInfo("en-US")
If Not e.Value Is Nothing Then
MyCellValue = Convert.ToDecimal(e.Value)
e.Value = MyCellValue.ToString("c", MyUSCultureInfo)
End If
'Great Britain
ElseIf e.ColumnIndex = 1 Then
MyUSCultureInfo = New System.Globalization.CultureInfo("en-GB")
If Not e.Value Is Nothing Then
MyCellValue = Convert.ToDecimal(e.Value)
e.Value = MyCellValue.ToString("c", MyUSCultureInfo)
End If
'Denmark
ElseIf e.ColumnIndex = 2 Then
MyUSCultureInfo = New System.Globalization.CultureInfo("da-DK")
If Not e.Value Is Nothing Then
MyCellValue = Convert.ToDecimal(e.Value)
e.Value = MyCellValue.ToString("c", MyUSCultureInfo)
End If
'Finland
ElseIf e.ColumnIndex = 3 Then
MyUSCultureInfo = New System.Globalization.CultureInfo("fi-FI")
If Not e.Value Is Nothing Then
MyCellValue = Convert.ToDecimal(e.Value)
e.Value = MyCellValue.ToString("c", MyUSCultureInfo)
End If
'Canada
ElseIf e.ColumnIndex = 4 Then
MyUSCultureInfo = New System.Globalization.CultureInfo("en-CA")
If Not e.Value Is Nothing Then
MyCellValue = Convert.ToDecimal(e.Value)
e.Value = MyCellValue.ToString("c", MyUSCultureInfo)
End If
End If
End Sub
[/CODE]
OUTPUT
[CODE]
US UK Denmark Australia Canada
$0.00 £0.00 kr. 0,00 0,00 € $0.00
[/CODE]
Some of other cultures are:
ar-DZ Arabic - Algeria
ar-IQ Arabic - Iraq
bg-BG Bulgarian - Bulgaria
en-IE English - Ireland
en-NZ English - New Zealand
en-PH English - Philippines
en-ZA English - South Africa
en-TT English - Trinidad and Tobago
en-GB English - United Kingdom
en-US English - United States
en-ZW English - Zimbabwe
et Estonian
et-EE Estonian - Estonia
fo Faroese
fo-FO Faroese - Faroe Islands
fa Farsi
fa-IR Farsi - Iran
fi Finnish
fi-FI Finnish - Finland
fr French
Retrieve the last word of a string
About FuncGetLastWord
/*This function accepts a string as parameter */
/*This function returns the last word of the string*/
/*This function calls another Function FuncGetWord*/
CREATE FUNCTION dbo.FuncGetLastWord(@varString as varChar(2000)) RETURNS varChar(2000) AS
BEGIN
Declare @varLastWord as varChar(2000)
Declare @varWord as varChar(2000)
Declare @w As Integer
Set @W = 1
set @varWord = dbo.FuncGetWord(@varString,@W)
set @varLastWord = ''
If len(@varWord) = 0 Begin
Return @varLastWord
End
While(Len(@varWord)>0) Begin
set @varLastWord = @varWord
set @W = @W + 1
set @varWord = dbo.FuncGetWord(@varString,@W)
End
Return @varLastWord
End
FuncGetWord Function
CREATE FUNCTION [dbo].[FuncGetWord] (@varString as varChar(2000), @intPosition as integer) RETURNS varChar(2000) AS
BEGIN
Declare @i As Integer
Declare @j As Integer
Declare @k As Integer
Declare @varResult as varChar(2000)
Set @j = 0
Set @k = 1
Set @i = 1
Set @varString = ltrim(rtrim((@varString)) + ' ^'
While(@i<=Len(@varString)) Begin
If (Substring(@varString, @i, 1) = ' '
or Substring(@varString, @i, 1) = '.'
or Substring(@varString, @i, 1) = '&'
or Substring(@varString, @i, 1) = ','
)
and Substring(@varString, @k, @i - @k +1 ) <> ' ' Begin
Set @j = @j + 1
If @j = @intPosition and Substring(@varString, @k, @i - @k +1 ) <> '^' Begin
set @varResult = ltrim(rtrim(Substring(@varString, @k, @i - @k +1 )))
Return @varResult
End
Else Begin
Set @k = @i + 1
End
End
Set @i = @i + 1
End
If @j < @intPosition Begin
Set @varResult = Null
End
Else Begin
Set @varResult = substring(@varString, @k,2000)
End
Return @varResult
End
USAGE
dbo.FuncGetLastWord('Your String'
/* Example: dbo.FuncGetlastWord('Hello World') = World */
/*This function accepts a string as parameter */
/*This function returns the last word of the string*/
/*This function calls another Function FuncGetWord*/
CREATE FUNCTION dbo.FuncGetLastWord(@varString as varChar(2000)) RETURNS varChar(2000) AS
BEGIN
Declare @varLastWord as varChar(2000)
Declare @varWord as varChar(2000)
Declare @w As Integer
Set @W = 1
set @varWord = dbo.FuncGetWord(@varString,@W)
set @varLastWord = ''
If len(@varWord) = 0 Begin
Return @varLastWord
End
While(Len(@varWord)>0) Begin
set @varLastWord = @varWord
set @W = @W + 1
set @varWord = dbo.FuncGetWord(@varString,@W)
End
Return @varLastWord
End
FuncGetWord Function
CREATE FUNCTION [dbo].[FuncGetWord] (@varString as varChar(2000), @intPosition as integer) RETURNS varChar(2000) AS
BEGIN
Declare @i As Integer
Declare @j As Integer
Declare @k As Integer
Declare @varResult as varChar(2000)
Set @j = 0
Set @k = 1
Set @i = 1
Set @varString = ltrim(rtrim((@varString)) + ' ^'
While(@i<=Len(@varString)) Begin
If (Substring(@varString, @i, 1) = ' '
or Substring(@varString, @i, 1) = '.'
or Substring(@varString, @i, 1) = '&'
or Substring(@varString, @i, 1) = ','
)
and Substring(@varString, @k, @i - @k +1 ) <> ' ' Begin
Set @j = @j + 1
If @j = @intPosition and Substring(@varString, @k, @i - @k +1 ) <> '^' Begin
set @varResult = ltrim(rtrim(Substring(@varString, @k, @i - @k +1 )))
Return @varResult
End
Else Begin
Set @k = @i + 1
End
End
Set @i = @i + 1
End
If @j < @intPosition Begin
Set @varResult = Null
End
Else Begin
Set @varResult = substring(@varString, @k,2000)
End
Return @varResult
End
USAGE
dbo.FuncGetLastWord('Your String'
/* Example: dbo.FuncGetlastWord('Hello World') = World */
Find the last day of the month
About FuncGetLastDayOfMonth SQL User defined function
/* This function accepts the date as a parameter */
/*This function returns the last day of the month of @InDate */
CREATE FUNCTION dbo.FuncGetLastDayOfMonth (@InDate varChar(50)) RETURNS varchar(50)
AS
Begin
Declare @Result as varchar(50)
Declare @InMonth as Int
Declare @InYear As Int
Declare @TempDate As varchar(10)
Declare @TempDate2 As datetime
SET @InMonth = MONTH(@InDate)
SET @InMonth = @InMonth + 1
Set @InYear = YEAR(@InDate)
If @InMonth = 13
BEGIN
Set @InMonth = 1
Set @InYear = @InYear + 1
END
Set @TempDate = CAST(@InMonth as Varchar(2)) + '/01/' + Cast(@InYear as varchar(4))
Set @TempDate2 =DateAdd(d, -1,@TempDate)
Set @Result = CONVERT(varchar(10),@TempDate2,101)
Return (@Result)
End
USAGE
dbo.FuncGetLastDayOfMonth('Your Date')
/* If you pass a pass this function a date, FuncGetLastDayOfMonth(1/12/04)*/
/* Then you will get the last day of January as a result */
/* Example:
dbo.FuncGetLastDayOfMonth('02/01/09') will return 02/28/2009
dbo.FuncGetLastDayOfMonth('05/01/09') will return 05/31/2009
*/
/* This function accepts the date as a parameter */
/*This function returns the last day of the month of @InDate */
CREATE FUNCTION dbo.FuncGetLastDayOfMonth (@InDate varChar(50)) RETURNS varchar(50)
AS
Begin
Declare @Result as varchar(50)
Declare @InMonth as Int
Declare @InYear As Int
Declare @TempDate As varchar(10)
Declare @TempDate2 As datetime
SET @InMonth = MONTH(@InDate)
SET @InMonth = @InMonth + 1
Set @InYear = YEAR(@InDate)
If @InMonth = 13
BEGIN
Set @InMonth = 1
Set @InYear = @InYear + 1
END
Set @TempDate = CAST(@InMonth as Varchar(2)) + '/01/' + Cast(@InYear as varchar(4))
Set @TempDate2 =DateAdd(d, -1,@TempDate)
Set @Result = CONVERT(varchar(10),@TempDate2,101)
Return (@Result)
End
USAGE
dbo.FuncGetLastDayOfMonth('Your Date')
/* If you pass a pass this function a date, FuncGetLastDayOfMonth(1/12/04)*/
/* Then you will get the last day of January as a result */
/* Example:
dbo.FuncGetLastDayOfMonth('02/01/09') will return 02/28/2009
dbo.FuncGetLastDayOfMonth('05/01/09') will return 05/31/2009
*/
Subscribe to:
Posts (Atom)