@@TRANCOUNT

This variable returns the number of active transactions for the current connection.
The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1.
ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT.
COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.

OPENQUERY command and what all parameters you pass

OpenQuery Executes the specified pass-through query on the given linked server, which is an OLE DB data source
OPENQUERY ( linked_server , 'query' )
Example: EXEC sp_addlinkedserver 'OracleSvr', 'Oracle 7.3', 'MSDAORA', 'ORCLDB'
GO
SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')

Linked Servers

A linked server is a virtual server that has been defined to SQL Server with all the information needed to access an OLE DB data source.
A linked server name is defined using the sp_addlinkedserver system stored procedure. The linked server definition contains all the information needed to locate the OLE DB data source. Local SQL Server logins are then mapped to logins in the linked server using sp_addlinkedsrvlogin.
Remote tables can then be referenced by using the linked server name as the server name in a four-part name used as a table or view reference in a Transact-SQL statement. The other three parts of the name reference an object in the linked server that is exposed as a row set.
Remote tables can then be referenced by using the linked server as an input parameter to an OPENQUERY function. OPENQUERY sends the OLE DB provider a command to execute. The returned row set can then be used as a table or view reference in a Transact-SQL statement.

Steps to avoid drop a table

DROP TABLE permissions default to the table owner, and are not transferable. However, members of the sysadmin fixed server role or the db_owner and db_dlladmin fixed database roles can drop any object by specifying the owner in the DROP TABLE statement.
If even the above should not be able to delete a table, make the database as read only
EXEC sp_dboption 'databse_name', 'read only', 'TRUE'
Remove all the users accounts from db_ddladmin role. Even this can’t stop members of sysadmin fixes server role to drop the table.

Error handling in stored procedure

Using @@Error the error number of last transact SQL statement executed can be found. If the statement is executed successfully the value is set to 0. If any error occurred then @@ERROR returns the number of the error message.
Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later.

Error handling in stored procedure

Using @@Error the error number of last transact SQL statement executed can be found. If the statement is executed successfully the value is set to 0. If any error occurred then @@ERROR returns the number of the error message.
Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later.

Concept of NO LOCK

Nolock does not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.

Concept of NO LOCK

Nolock does not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.

Difference between EXEC and sp_executesql stored procedure

· sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation.
· Because the Transact-SQL statement itself remains constant and only the parameter values change, SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

Difference between EXEC and sp_executesql stored procedure

· sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement a number of times when the change in parameter values to the statement is the only variation.
· Because the Transact-SQL statement itself remains constant and only the parameter values change, SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

Shared Lock

Shared locks allow concurrent transactions to read a resource. No other transactions can modify the data while shared locks exist on the resource. Shared locks on a resource are released as soon as the data has been read, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared locks for the duration of the transaction. Used for operations that do not change or update data (read-only operations), such as a SELECT statement.

Concept of Locking

A lock is an object used to indicate that a user/process has some dependency on a resource. Placing a lock on a resource is called locking.
· Locking is used to ensure transactional integrity and database consistency.
· Locking prevents users from reading data being changed by other users, and prevents multiple users from changing the same data at the same time.
· If locking is not used, data within the database may become logically incorrect, and queries executed against that data may produce unexpected results.
· SQL Server locks are applied at various levels of granularity in the database. Locks can be acquired on rows, pages, keys, ranges of keys, indexes, tables, or databases.
· SQL Server dynamically determines the appropriate level at which to place locks for each Transact-SQL statement. The level at which locks are acquired can vary for different objects referenced by the same query.

Get all the tablename and columnname of a database

Think that database name is dbEmplyee

use dbEmplyee
select table_name,column_name from INFORMATION_SCHEMA.COLUMNS

Hope this helps you

Kumar

Display More than one month in Windows Month Calender

The MonthCalender control of Windows form by default displays one month at a time . if the client require us to display 2 months by default, then what to do.

Simple we have to set the CalenderDimensions to the MonthCalender

The below example will show how to display 2 months at a time by default in the MonthCalender control

monthCalendar1.CalendarDimensions = new System.Drawing.Size(2, 1);


Hope this may help you

Kumar

How to check entered value in textbox is numeric or not

There are number of ways to check whether the string that has entered in the textbox is numeric or not

1. using int.Parse
Ex:
function bool stringIsNumericOrNot(string txtBoxValue)
{
bool isNumericOrNot = true;
try
{
int.Parse(TextBox1.Text);
}
catch
{
isNumericOrNot = false;
}
return isNumericOrNot;
}

2. Using Regular Expression
public bool IsNumberOrNot(String strNumber)
{
Regex regNumericPattern= new Regex("[^0-9]");
return !regNumericPattern.IsMatch(strNumber);
}

Hope this may help you

Kumar

Replace the existing string with the new string

function replaceAllString( str, from, to )
{
var idx = str.indexOf( from );


while ( idx > -1 )
{
str = str.replace( from, to );
idx = str.indexOf( from );
}
}

if you want to replace all commas (,) in a string then the above function will work in javascript

example if the textbox value 1,24,573.00 and while storing into the database if should be just like 124573.00 then you have to replace all commas, so for that the user has to write
document.getElementById("txtBoxId").value = replaceAllStrings(document.getElementById("txtBoxId").value,",","");

Hope this helps you

Kumar

Adding Commas to the Control javascript

function addingCommasToControl(stringVariable)
{
    stringVariable += '';
    k = stringVariable.split('.');
    k1 = k[0];
    k2 = k.length > 1 ? '.' + k[1] : '';
    var rgx = /(\d+)(\d{3})/;
    while (rgx.test(x1)) {
        k1 = k1.replace(rgx, '$1' + ',' + '$2');
    }
return k1 + k2;
}

The above function will add commas to the control
Example :
Name : txtPrice
Control : TextBox

so in the html or in design part of .net you can write as
onblur of the txtPrice textbox the user can able to call the addingCommasToControl() function

the parameter the user should pass is the amount



so when the user enters 124573.00
then on blur of the textbox the output will be displayed in the textbox is 1,24,573.00


Hope this may helps you

Kumar

NameValueCollection Class

Defination : NameValueCollection Class is collection of keys and values that can be accessed either with keys or by index.

Namespace : System.Collections.Specialized

Description: It can able to store multiple string values under single key

Below Code Snippet will describe this clearly.

NameValueCollection keyValue = new NameValueCollection();
keyValue.Add("A","Apples");
keyValue.Add("B","Banana");
keyValue.Add("C","Cat") ;
keyValue.Add("A","Ant");

Response.Write("Key Value\n");
foreach(string strNameValue in keyValue.AllKeys)
{
Response.Write(strNameValue + " " + keyValue[strNameValue]);
Response.Write("\n");
}

//Output will be
Key Value
A Apple,Ant
B Banana
C Cat

Hope this article will help you

Kumar

Recursively calculate Fibonacci series

static int calFibnocci(int k)
{
if(k<=1)
{
return 1
}
else
{
return calFibnocci(k-1) + calFibnocci(k-2);
}
}

how to call this function
calFibnocci(3)

Hope this help you

Kumar

Arraylist

Arraylist implements from Ilist Interface using array and can be increased dynamically.
Arraylist uses the namespace System.Collections.
The Capacity property of Arraylist holds number of elements in the Arraylist. With the help of Add method you can able to add an item in the Array list.

C# Code
----------------------------------------------------
Arraylist arrList = new Arraylist();
arrList.Add("Kumar");
arrList.Add("Venky");
-----------------------------------------------------

As the elements are added to a Arraylist, the capacity is increased automatically through rellocation.It can be decreased with the help of TrimToSize()

Arraylist allows duplicate elements and accepts null value.
You must sort the Arraylist explicitly before performing operation such as Binary search for which sorting is needed.

IsFixedSize property gets a value indicating whether ArrayList object has a fixed size.

Don't mix the content of the arraylist. Below example helps you to understand

------------------
C# Code
ArrayList contentMixing = new ArrayList();

//Adding some numbers to the list
contentMixing.Add(10);
contentMixing.Add(20);

//Adding some text to the list
contentMixing.Add("Kumar");
contentMixing.Add("Venky");

//While looping gives error
foreach(string mixedText as contentMixing)
{
//Throws exception
}
------------------


Kumar