PATINDEX SQL Server Example

SQL Server > Built-in Functions > PATINDEX

PATINDEX returns the start position of pattern occurrence in an expression.

Warning! If pattern or expression is NULL, PATINDEX returns NULL.

Example

SELECT 'This is PATINDEX example' AS 'Expression'

SELECT PATINDEX('%pat%', 'This is PATINDEX example') as 'Find any position pat';

SELECT PATINDEX('%a_p%', 'This is PATINDEX example') as 'Find any a followed by any caracter and then p';

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