MAX SQL Server Example

SQL Server > Built-in Functions > MAX

MAX returns the maximum value.

Warning! Null values are ignored!

Example

CREATE TABLE #LocalTempTable(

       ID            int,
       Name   varchar(50),
       Salary int,
       DeptId int)

insert into #LocalTempTable(Id, Name, Salary, DeptId) values (1,'p1',100,1),  (2,'p2',200,1) ,  (2,'p3', null,2) ,   (2,'p4', 50,2)

select * from #LocalTempTable

select MAX(Salary) Max_Salary_In_Company from #LocalTempTable

select MAX(Salary) Max_Salary_By_Dept, DeptId from #LocalTempTable group by DeptId

drop table #LocalTempTable

Retrieve List of Tables in MS Access File C# Example

C# > Data > OLE DB > Access - all tables

Use GetSchema to get all tables in MS Access

Example

OleDbConnection con = new OleDbConnection();

con.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=file.mdb;User Id=admin;Password=;";

con.Open();

DataTable userTables = null;
string[] restrictions = new string[4];
restrictions[3] = "Table";

userTables = con.GetSchema("Tables", restrictions);

for (int i = 0; i < userTables.Rows.Count; i++)
   lstTables.Items.Add(userTables.Rows[i][2].ToString());

con.Close();

RTRIM SQL Server Example

SQL Server > Built-in Functions >  RTRIM

RTRIM returns a sting after truncate trailing blanks.

Example:

DECLARE @str varchar(60);

SET @str =  'six spaces after      ';
SELECT @str + '/';
SELECT RTRIM(@str) + '/';

results:

six spaces after      /
six spaces after/

LTRIM SQL Server Example

SQL Server > Built-in Functions >  LTRIM

LTRIM returns a sting after removes leading blanks.






Example:

DECLARE @str varchar(60);

SET @str = '     six spaces before';
SELECT @str;
SELECT LTRIM(@str);

results:

     six spaces before
six spaces before

Average without Zero values SQL Server

SQL Server > Built-in Functions > AVG > Without zerous
 
Example to calculate average without zero

create table #sale(regionid int, amount int)

insert into #sale(regionid , amount ) values (1,100), (2,50) , (3,0)
select
       AVG (CASE WHEN amount <> 0 THEN amount ELSE NULL END)
from
   #sale
drop table #sale

result:
75
  

How to delete rows from DataGridView without delete rows from DataTable

C# > System.Data   > DataGridView > Delete rows

How to delete rows from DataGridView without delete rows from DataTable?

Solution

Copy original DataTable

Example:

  DataTable dt1 = dt.Copy();

  dataGridView1.DataSource = dt1;

  

Delete row from DataTable using LINQ VB.NET example

VB.NET > DataTable > Delete row with LINQ

Here is an example of deleting row from DataTable (dt) supposing you want to delete the row with id 1.

Dim row As DataRow = dt.AsEnumerable().SingleOrDefault(Function(r) r(0) = 1)

If Not row Is Nothing Then

    row.Delete()
End If
  

Add AutoIncrement Row Values to DataTable Having AutoIncrement and PrimaryKey DataColumn

VB.NET > DataGridView > Add AutoIncrement Row Values

Example how to add AutoIncrement and PrimaryKey DataColumn values to DataTable

VB.NET

  Dim dt As DataTable = New DataTable

  dt.Columns.Add("ID", GetType(Integer))

  dt.PrimaryKey = New DataColumn() {dt.Columns("ID")}
  dt.Columns("ID").AutoIncrement = True
  dt.Columns("ID").AutoIncrementSeed = 1
  dt.Columns("ID").ReadOnly = True

  dt.Columns.Add("Name", GetType(String))

  Dim dr = dt.NewRow()
  dr("Name") = "John"
  dt.Rows.Add(dr)

  dr = dt.NewRow()

  dr("Name") = "Dan"
   dt.Rows.Add(dr)

  DataGridView1.DataSource = dt

C#

DataTable dt = new DataTable();

dt.Columns.Add("ID", typeof(int));
dt.PrimaryKey = new DataColumn[] { dt.Columns["ID"] };
dt.Columns["ID"].AutoIncrement = true;
dt.Columns["ID"].AutoIncrementSeed = 1;
dt.Columns["ID"].ReadOnly = true;

dt.Columns.Add("Name", typeof(string));

dynamic dr = dt.NewRow();
dr("Name") = "John";
dt.Rows.Add(dr);

dr = dt.NewRow();
dr("Name") = "Dan";
dt.Rows.Add(dr);

DataGridView1.DataSource = dt;

c# font Italic label example

C# > Drawing > Font > Italic

Italic indicates when Font is italic.

Example:

label1.Font = new System.Drawing.Font(label1.Font, FontStyle.Italic);

C# Create new bitmap example

C# > Drawing > Bitmap

Bitmap contains  pixel data for a graphics image. You can save bitmap to GDI+ file formats: BMP, GIF, EXIF, JPG, PNG and TIFF.

Example
Create new bitmap in runtime

Bitmap bmp = new System.Drawing.Bitmap(200, 200);

for (int x = 0; x < bmp.Height; ++x)
   for (int y = 0; y < bmp.Width; ++y)
     bmp.SetPixel(x, y, Color.Coral );
for (int x = 0; x < bmp.Height / 2; ++x)
   for (int y = 0; y < bmp.Width / 2; ++y)
     bmp.SetPixel(x, y, Color.Blue );
pictureBox1.Image = bmp;

C# Drawing Font

C# > Drawing > Font

With Font you can format text and set properties like face, size, style.

FontStyle Enumeration

• Bold
• Italic

C# Drawing

C# > Drawing

Provides methods for drawing to the display device using GDI+ basic graphics functionality.

• Font
• Bitmap

Examples

• Capture screen to picturebox

Abs C# example

C#  > System > Math class > Abs

Abs returns the absolute value of a number.

Example:

int i = -32;
int j = Math.Abs(i); // 32

Math class C#

C#  > System > Math class

Math is a static class which contains constants and methods mathematical functions.

• Round
• Abs
• Pow

System namespace in C#

C# > System Namespace

System namespace in C# contains fundamental classes and base classes.

• GUID
• Math class
• Interfaces
• Predicate delegate
• ComponentModel
• String
• DateTime
• Environment
• Dynamic
• Func
• Reflection
• TimeSpan
• Convert
• BitConverter

Guid, NewGuid C# example

C#  > System > Guid

Guid is globally unique identifier and stored on 128 bit integer.
The probability to be duplicated over networks and computers is very low.

Example
1. Use NewGuid method to get a new guid.
  Guid g = Guid.NewGuid();

Call Stack Window Microsoft Visual Studio

Visual Studio > Call Stack Window

Using the Call Stack Window  you can view the function calls that are currently on the stack.
To open Call Stack Window click on
DEBUG > Windows > Call Stack

Foreach in C# example

C# > Statements > foreach, in

Foreach iterates through the collection . It can be exited by the goto, return, or throw.
Note: Cannot be used to add or remove items from the collection to avoid unpredictable side effects

Example

int[] intarray = new int[] { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };

foreach (int el in intarray)

{

MessageBox.Show(el.ToString()); 

}

C# Statements

C# > Statements

Statements are executed in sequence and are program instructions

Selection

• switch

Iteration

• while
• foreach in

Jump

• break

Namespace

• using

AddHandler Visual Basic Example

VB.NET > Statements > AddHandler

AddHandler link an event with an event handler at run time.

Example:

1. Add event to an object

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim Obj As New Class1
        ' Associate an event handler with an event
        AddHandler Obj.myEvent, AddressOf EvHandler
        Obj.Class1Event()
    End Sub

    Sub EvHandler()

        ' Handle the event
        MsgBox("EvHandler")
    End Sub

    Public Class Class1
        Public Event myEvent()
        Public Sub Class1Event()
            RaiseEvent myEvent()
        End Sub
    End Class

End Class

2. Handle events for dynamic runtime control

Enum type c# example

C# > Types > enum

Enum is s type that contains of a set of named constants called the enumerator list.
By default the first enumerator has the value 0, and the value of each successive enumerator is increased by 1.

• EnumMemberAttribute

Example:

A.

enum SomeMonths { January, February, March };
enum OtherMonths { April = 1, May }; //Enumerators can use initializers to override the default values

int v = (int)SomeMonths.January; // 0
v = (int)OtherMonths.May; // 2

B. String Enum

Long type C# Example

C# > Types > long

The long keyword is an integral type.
Range: –9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
Size: Signed 64-bit integer

Example:

long l = 21321321321;

long l1 = 21321321321L; // using suffix L

int i = 100L; // Error: Error 1 Cannot implicitly convert type 'long' to 'int'. An explicit conversion exists (are you missing a cast?)

int x = (int)100L; // OK

Serialization C#

C#  > Serialization  

Serialization  role is to save the state of an object. It converts an object into a stream of bytes. The reverse process is called deserialization.

• DataContractAttribute