Saturday, May 23, 2020

Datetime Picker control in VB.Net DataGridView Row

Hi, How can we set the DateTime picker control in a DataGridView row.
Just create a class name GridDateControl, Now paste this code and use it in any VB.Net DataGridView.

Imports System

Imports System.Windows.Forms

Public Class GridDateControl

    Inherits DataGridViewColumn

     Public Sub New()

        MyBase.New(New CalendarCell())

    End Sub

     Public Overrides Property CellTemplate() As DataGridViewCell

        Get

            Return MyBase.CellTemplate

        End Get

        Set(ByVal value As DataGridViewCell)

            ' Ensure that the cell used for the template is a CalendarCell.

            If Not (value Is Nothing) AndAlso _

                Not value.GetType().IsAssignableFrom(GetType(CalendarCell)) _

                Then

                Throw New InvalidCastException("Must be a CalendarCell")

            End If

            MyBase.CellTemplate = value

        End Set

    End Property

End Class

Public Class CalendarCell

    Inherits DataGridViewTextBoxCell

     Public Sub New()

        ' Use the short date format.

        Me.Style.Format = "d"

    End Sub

     Public Overrides Sub InitializeEditingControl(ByVal rowIndex As Integer, _

        ByVal initialFormattedValue As Object, _

        ByVal dataGridViewCellStyle As DataGridViewCellStyle)

        ' Set the value of the editing control to the current cell value.

        MyBase.InitializeEditingControl(rowIndex, initialFormattedValue, _

            dataGridViewCellStyle)

        Dim ctl As CalendarEditingControl = _

            CType(DataGridView.EditingControl, CalendarEditingControl)

        If Not Me.Value Is DBNull.Value Then

            If Not Me.Value Is Nothing Then

                ctl.Value = CType(Me.Value, DateTime)

            End If

        End If

    End Sub

    Public Overrides ReadOnly Property EditType() As Type

        Get

            ' Return the type of the editing contol that CalendarCell uses.

            Return GetType(CalendarEditingControl)

        End Get

    End Property

    Public Overrides ReadOnly Property ValueType() As Type

        Get

            ' Return the type of the value that CalendarCell contains.

            Return GetType(DateTime)

        End Get

    End Property

    'Public Property CustomFocus() As Boolean

    '    Get

 

    '    End Get

    '    Set(ByVal value As Boolean)

    '        If CustomFocus Then

    '            ctl.Select()

    '            SendKeys.Send("%{Down}")

    '        End If

    '    End Set

    'End Property

End Class

Class CalendarEditingControl

    Inherits DateTimePicker

    Implements IDataGridViewEditingControl

    Private dataGridViewControl As DataGridView

    Private valueIsChanged As Boolean = False

    Private rowIndexNum As Integer

    Public Sub New()

        Me.Format = DateTimePickerFormat.Short

    End Sub

    Public Property EditingControlFormattedValue() As Object _

        Implements IDataGridViewEditingControl.EditingControlFormattedValue

        Get

            Return Me.Value.ToShortDateString()

        End Get

        Set(ByVal value As Object)

            If TypeOf value Is [String] Then

                Me.Value = DateTime.Parse(CStr(value))

            End If

        End Set

    End Property

    Public Function GetEditingControlFormattedValue(ByVal context _

        As DataGridViewDataErrorContexts) As Object _

        Implements IDataGridViewEditingControl.GetEditingControlFormattedValue

        Return Me.Value.ToShortDateString()

    End Function

    Public Sub ApplyCellStyleToEditingControl(ByVal dataGridViewCellStyle As  _

        DataGridViewCellStyle) _

        Implements IDataGridViewEditingControl.ApplyCellStyleToEditingControl

        Me.Font = dataGridViewCellStyle.Font

        Me.CalendarForeColor = dataGridViewCellStyle.ForeColor

        Me.CalendarMonthBackground = dataGridViewCellStyle.BackColor

    End Sub

    Public Property EditingControlRowIndex() As Integer _

        Implements IDataGridViewEditingControl.EditingControlRowIndex

        Get

            Return rowIndexNum

        End Get

        Set(ByVal value As Integer)

            rowIndexNum = value

        End Set

    End Property

    Public Function EditingControlWantsInputKey(ByVal key As Keys, _

        ByVal dataGridViewWantsInputKey As Boolean) As Boolean _

        Implements IDataGridViewEditingControl.EditingControlWantsInputKey

        ' Let the DateTimePicker handle the keys listed.

        Select Case key And Keys.KeyCode

            Case Keys.Left, Keys.Up, Keys.Down, Keys.Right, _

                Keys.Home, Keys.End, Keys.PageDown, Keys.PageUp

                Return True

            Case Else

                Return False

        End Select

    End Function

    Public Sub PrepareEditingControlForEdit(ByVal selectAll As Boolean) _

        Implements IDataGridViewEditingControl.PrepareEditingControlForEdit

        ' No preparation needs to be done.

    End Sub

    Public ReadOnly Property RepositionEditingControlOnValueChange() _

        As Boolean Implements _

        IDataGridViewEditingControl.RepositionEditingControlOnValueChange

        Get

            Return False

        End Get

    End Property

    Public Property EditingControlDataGridView() As DataGridView _

        Implements IDataGridViewEditingControl.EditingControlDataGridView

        Get

            Return dataGridViewControl

        End Get

        Set(ByVal value As DataGridView)

            dataGridViewControl = value

        End Set

    End Property

    Public Property EditingControlValueChanged() As Boolean _

        Implements IDataGridViewEditingControl.EditingControlValueChanged

        Get

            Return valueIsChanged

        End Get

        Set(ByVal value As Boolean)

            valueIsChanged = value

        End Set

    End Property

    Public ReadOnly Property EditingControlCursor() As Cursor _

        Implements IDataGridViewEditingControl.EditingPanelCursor

        Get

            Return MyBase.Cursor

        End Get

    End Property

    Protected Overrides Sub OnValueChanged(ByVal eventargs As EventArgs)

        ' Notify the DataGridView that the contents of the cell have changed.

        valueIsChanged = True

        Me.EditingControlDataGridView.NotifyCurrentCellDirty(True)

        MyBase.OnValueChanged(eventargs)

    End Sub

End Class


If there is any other better option,
Please share it here or mail to me arkaa4@gmail.com

Thank You,
Arka Gupta.

How to Create a Round Button in VB.Net

Hi, now I am discuss how we create a Circle shape Or Round shape Button in VB.Net
First we create a Class named CircleButton. Now paste the below code and use it in VB forms.

Imports System.Drawing.Drawing2D

Public Class CircleButton

    Inherits Button

    Protected Overrides Sub OnPaint(ByVal e As System.Windows.Forms.PaintEventArgs)

        Dim grPath As GraphicsPath = New GraphicsPath()

        grPath.AddEllipse(0, 0, ClientSize.Width, ClientSize.Height)

        Me.Region = New Region(grPath)

        MyBase.OnPaint(e)

    End Sub

End Class


If there is any other better option,
Please share it here or mail to me arkaa4@gmail.com

Thank You,
Arka Gupta.

Monday, May 18, 2020

How to Clear SQL Server Cache Memory

Sometimes there are issues due to what SQL Server has stored in its cache. Here are some possible reasons which may create caching performance issues.

  • Ad-hoc Query workload issues due to cache bloat
  • Excessive use of dynamic T-SQL code
  • Server has insufficient memory or not properly assigned to SQL instances
  • Memory pressure generated due to heavy long running transactions
  • Server has frequent recompilation events
To overcome these issues we may need to flush the plan cache or buffer cache.

Let's consider some tables to get the details information of Cache memory.


This query returns a row for each query plan that is cached by SQL Server for faster query execution.

SELECT * FROM sys.dm_exec_cached_plans;

It returns information about all the data pages that are currently in the SQL Server buffer pool.

SELECT * FROM sys.dm_os_buffer_descriptors;

It returns memory information from the operating system.

SELECT * FROM sys.dm_os_sys_memory
WHERE system_memory_state_desc = 'Available physical memory is low';

It returns a snapshot of the health of a cache in SQL Server and provides run-time information about the cache entries allocated, their use, and the source of memory for the cache entries.

SELECT * FROM sys.dm_os_memory_cache_counters

ORDER BY (pages_kb + pages_in_use_kb) DESC;

Check overall system memory status and query-execution memory reservations

SELECT * FROM sys.dm_exec_query_memory_grants;

Now we generate some queries: 

How many plans are in the cache

SELECT COUNT(*) FROM sys.dm_exec_cached_plans;

How much space is being used by the plan cache

SELECT (SUM(size_in_bytes)/1024)/1024 as 'MB' 

FROM sys.dm_exec_cached_plans;

Plans which have only been used once

SELECT * FROM sys.dm_exec_cached_plans

WHERE objtype = 'ADHOC' and usecounts < 2;

To find out what single-use plans have been stored for your instance issue this statement to get the text and size of the single-use plans in your plan cache:

SELECT K.size_in_bytes, K.usecounts, K.objtype, LEFT(sql.[text], 200) AS [text]

FROM   sys.dm_exec_cached_plans K 

        OUTER APPLY sys.dm_exec_sql_text(K.plan_handle) sql

--WHERE usecounts < 2 

ORDER BY size_in_bytes DESC;

How much space is being used by the single use plan cache

SELECT objtype AS 'Cached Object Type', count(*) AS 'Number of Programs',

sum(cast(size_in_bytes AS BIGINT))*8/1024 AS 'Plan Cache Size (MB)',

avg(usecounts) AS 'Avg Use Count' FROM sys.dm_exec_cached_plans

GROUP BY objtype;

Sample showing searching the cached size per database

SELECT (CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State',

              (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME(database_id) END) AS 'Database Name',

              COUNT(*) AS 'Page Count',

              COUNT(*)*8/1024 AS 'Cached Size (MB)'

FROM   sys.dm_os_buffer_descriptors

GROUP BY [database_id], [is_modified]                             

ORDER BY [database_id], [is_modified];

Finally we run the scrip to clear SQL Server cache memory:

DBCC FREESYSTEMCACHE ('ALL');

DBCC FREESESSIONCACHE;

DBCC DROPCLEANBUFFERS;

DBCC FREEPROCCACHE; (AVOID TO RUN IN LIVE SERVER)

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

EXEC sys.sp_configure

RECONFIGURE WITH OVERRIDE;


Source: https://www.mssqltips.com/sqlservertip/4714/different-ways-to-flush-or-clear-sql-server-cache/
https://logicalread.com/sql-server-minimize-single-use-plans-tl01/#.XsF-NZ4zbIU

If there is any other better option,
Please share it here or mail to me arkaa4@gmail.com

Thank You,
Arka Gupta.