BYPsoft on .NET & SQL

How to hide form on startup in .NET

Full Text Search Tool

If you need to search for a particular word(s) or number(s) in a whole database you can use DBTYP.NET Database Full Text Search module which has been just added to the DBTYP.NET Studio.

Database Full Text Search Dialog for SQL Server, Oracle and MySQL

It performs searches on all data types except binary, xml and date/time values. So you are free to search for whole words, part of words using SQL LIKE wild-cards or even for the numbers.

SQL Server Configuration Options

If you need to review configuration options of some SQL Server instance, you can get them out of the SYS.CONFIGURATIONS catalog view. So the select statement like

will give you back all configuration options of SQL Server instance.

Prevent Row Deletions/Updates on SQL Server

If you reach a requirements to prevent some table for deletions or/and updates you may wonder how to reach that. The simple answer is to use INSTEAD OF  triggers. INSTEAD OF triggers override the standard action of triggering statement: INSERT, UPDATE or DELETE. On that way, INSTEAD OF trigger can ignore parts of the batch, not process part of the batch or taking an alternative action. The major difference to the AFTER triggers is that INSTEAD OF triggers can be defined on a views as well as on a tables.

So, a solution of the problem, preventing a row deletions on the table can be the following INSTEAD OF trigger:

CREATE TRIGGER trgPreventRowDeletion ON Test_table INSTEAD OF DELETE
AS
BEGIN
REISEERROR (‘Deletions are not allowed from the Test_table’, 16, 1)
END

On a similar way it can be defined INSTEAD OF INSERT and INSTEAD OF UPDATE triggers.

Visual Studio: Remove Items from Recent Project List

Identifying Index Fragmentation on SQL Server

The first thing you are doing when identifying poor query performances is to take a look at execution plan and indexes usage. Even that you see, everything is fine, your index can be fragmented so that SQL Server needs to make unnecessary data reads to execute query. At the end, your query is slow.
At the moment when index is created, little or no fragmentation is present. During the time, when updates, inserts and deletes occur indexes get fragmented what is a real bottleneck in a SQL Server performances.

There are two ways how to fix fragmented indexes: reorganizing or rebuilding them. Which operation is necessary depends on the level of fragmentation. Reorganization of index is suggested if fragmentation level is less than 30%. If it is more than 30% than rebuilding index is better choice.

Identifying fragmented indexes

Starting from version 2005, SQL Server contains a number of DMVs and DMFs qhich allow us to retrieve informations about SQL Server health and performances and identifying problems. One of them, allow us to take a look at the index fragmentation level – sys.dm_db_index_physical_stats DMF. It is important to said here that it places intent shared lock (IS) on the affected tables during execution.

sys.dm_db_index_physical_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | NULL | 0 | -1 | DEFAULT }
, { partition_number | NULL | 0 | DEFAULT }
, { mode | NULL | DEFAULT }
)

The following query will get fragmentation of all indexes in the database

DECLARE @dbId int

SET @dbId = db_id(‘YOUR_DB_NAME’)

SELECT s.[name] AS SchemaName, t.[name] AS TableName, i.[name] AS IndexName, p.[index_type_desc], p.[avg_fragmentation_in_percent]
FROM [sys].[dm_db_index_physical_stats](@dbId, NULL, NULL, NULL , ‘DETAILED’) p
INNER JOIN [sys].[tables] t  ON p.[object_id] = t.[object_id]
INNER JOIN [sys].[schemas] s  ON t.[schema_id] = s.[schema_id]
INNER JOIN [sys].[indexes] i  ON p.[object_id] = i.[object_id] AND p.index_id = i.index_id
WHERE t.[is_ms_shipped] = 0

To get more proper candidates for rebuilding or reorganizing indexes it is necessary to consult other fields returned back from [sys].[dm_db_index_physical_stats] like avg_page_space_used_in_percent which indicates on average how full each page in index is. The higher number is better while but it is necessary here to balance fullness against the
number of inserts into index pages in order to keep the number of page splits to the absolute minimum. This exceeds the topic of this blog and requires adjustments of index fillfactor and monitoring of page splits.

CheckBox header column for DatagridView

It is very common to have a list of items in DataGridView with a check box in the first column where your later action will depend on user selection. This can be very easy done having a first column defined as DataGridViewCheckBoxCell object. But, how can your customer select all items in the list (let’s say you are working on an email client app and user wants to delete all of his 100 spams)?
Unfortunately, .NET framework does not have a header class similar to DataGridViewCheckBoxColumn. Such a problem lead us to the idea to generate a class which will have a check box item in header where developer can have a full control after user check/uncheck item in the header. Common action is to check/uncheck all items in the DataGridView depending if header is checked/unchecked.
Whole article and class definition is available at http://www.codeproject.com/useritems/CheckBoxHeaderCell.asp

Changing default column values – SQL Server

A very common mistake in T-SQL is trying to alter column definition trying to alter DEFAULT value (constraint) for that column. Probably, developers expect to have this because it is possible to assign default value for a column during table definition:
CREATE TABLE Table1 (
col1 INT NOT NULL DEFAULT (0),
col2 INT NOT NULL
)
Due to possibility to change column type from int to varchar(20) or null-ability with
ALTER TABLE Table1 ALTER COLUMN col2 VARCHAR(20) NULL
a lot of developers expect to be able to change a column default value with
ALTER TABLE Table1 ALTER COLUMN col1 DEFAULT (1)
Error message is displayed trying to execute this: “Incorrect syntax near the keyword ‘DEFAULT’”.
Mistake.
With a create table syntax mentioned earlier, SQL Server creates default constraint with auto generated name like “DF__Table1__col1__57DD0BE4″ and stores default value in [text] column of syscomments table.
So, to change a default value for a column you should change a constraint definition and the only way to do that is to drop current constraint and create the new one:
ALTER TABLE table1 DROP CONSTRAINT DF__Table1__col1__57DD0BE4
ALTER TABLE table1 WITH NOCHECK ADD CONSTRAINT [Df_test_col1] DEFAULT (1) FOR col1

Compare databases with the fastest tool – DBTYP.NET

Formatting bounded DateTime fields in ASP.NET

If you ever tried to work with BoundField object bound to a DateTime field with the DataFormatString, it must happen, that first time you didn’t get your DateTime value proeprly formatted.
<asp:BoundField DataField=”MyNotFormattedDate” DataFormatString=”{0:MM/dd/yyyy}” />

Looks like that you have done everything correct but still getting your value formatted using its ToString() method like “11/13/2007 10:05:12 PM”. So, the problem is not in your definition but in ASP.NET which tries to prevent cross site scripting attacks, the field value is HTMLEncoded. And HTMLEncoding occurs before applying any formatting, making your formatting string without effects. To Get your field formatted as you define, you should tell object not to use HTMLEncoding like
<asp:BoundField DataField=”MyFormattedDate” DataFormatString=”{0:MM/dd/yyyy}” HtmlEncode=”false”/>

Compare databases with the best tool – DBTYP.NET Studio.

Handling Miliseconds in .NET and SQL Server

Have you taken a look at number of such a questions in online community? It looks like that a lot of .NET developers have a problems reading and updating milliseconds value from SQL Server databases. So what is a real problem here?
Let’s take a look at common scenario. Developers read data from a table which has Datetime column ColDateTime. Let’s say they are storing values in some DataTable table1. Suddenly, if you want just to print a Value of that field, with

table1.Rows[0].Cells["ColDateTime"].Value.ToString()

you will get value like ‘2008/02/26 2:26:53 PM’. But you value in database is actually ‘2008/02/26 14:26:53.3480′. So milliseconds are missing. The only way to get them is to convert value from database in DateTime variable and then print out that variable:

DateTime dtDbValue = System.Covert.ToDateTime(table1.Rows[0].Cells["ColDateTime"]);
dtDbValue.ToString();

Now, you have a real value: ‘2008/02/26 14:26:53.3480′

Connected to this is also reverse process, where people lose milliseconds during update. Take a look at following statements:

DateTame dtValue = DateTime.Now;

string sql = "update table1 set ColDateTime = ‘" + dtValue.ToString() + "’";

– create DataCommand, assign CommandText and execute.

You will see that your column has been updated with the wrong value. Again milliseconds cut.

People try very often to format ToString method call to looks like:
string sql = "update table1 set ColDateTime = ‘" + dtValue.ToString("yyyy/MM/dd hh:mm:ss.ffff") + "’";

but this cause an exception on SQL Server side saying that varchar value can not be converted to Datetime type. Strange, and maybe lack of documentation, but the solution is to format your DateTime value with different format – using 3 f and not 4:

string sql = "update table1 set ColDateTime = ‘" + dtValue.ToString("yyyy/MM/dd hh:mm:ss.fff") + "’";

Compare databases fast, safe, free… DBTyP.NET – in version 2008 supports Oracle besides SQL Server and MySQL. DBTyP.NET 2008 is coming… in 3 weeks.