SqlServer C# notes

1. Functions in Sqlserver
http://www.codeguru.com/columns/experts/article.php/c11769/

User-defined functions come in the following two flavors:

Scalar-valued UDFs can return almost any single data type.
Table-valued UDFs return a temporary table containing multiple rows.
You can use scalar UDFs inside any DML (INSERT, UPDATE, SELECT, and so forth) or T-SQL statement. Table UDFs have a few more limitations, and you typically use a table-valued UDF in the FROM part of a SELECT statement. This article focuses on the uses of table-valued UDFs.

Introduction to table-valued UDFs
There are two types of table-valued UDFs:

Inline table-valued functions return a TABLE datatype. They each contain a single T-SQL statement.
Multi-statement table-valued functions return a defined table. They can contain multiple T-SQL statements.
Both types of table-valued UDF return single result sets. The following is an example of an inline table-valued UDF definition:

CREATE FUNCTION dbo.TestInlineFunctionName
(

)
RETURNS TABLE
AS
RETURN
(
   SELECT 0 as RetVal,* from [Person].[Contact]
)
A multi-statement UDF looks similar to an inline UDF, but with one major difference: It contains table definition statements after the RETURNS directive, as in the following sample code:

RETURNS @retContactInformation TABLE
(
   — Columns returned by the function
   [ContactID] int PRIMARY KEY NOT NULL,
   [FirstName] [nvarchar](50) NULL,
   [LastName] [nvarchar](50) NULL,
   [JobTitle] [nvarchar](50) NULL,
   [ContactType] [nvarchar](50) NULL
)

======

<div style=”width:200px;height:500px;overflow:auto;”>
=============
sqlserver : with (nolock)
————–
Using DIV to make a collapsable panel

<%@ Page Language=”C#” MasterPageFile=”MasterPage.master” AutoEventWireup=”true”
    CodeFile=”Default.aspx.cs” Inherits=”CollapsableDiv._Default” Title=”Tesco Home” %>

<asp:Content ID=”Content1″ ContentPlaceHolderID=”Cont” runat=”Server”>

    <script language=”javascript” type=”text/javascript”>
 
  function ExpandCollapseDiv(divID)
{

    var elDiv;
    elDiv = document.getElementById(divID);
    if(document.getElementById(divID).style.display==’block’)
    {   
    elDiv.style.display=’none’;
    return;
    } 
   
    if(document.getElementById(divID).style.display==’none’)
    {

    elDiv.style.display=’block’;
    return;
    }
  
}

    </script>

   
                        <div id=”divone” width=”100%” onclick=”ExpandCollapseDiv(‘divtwo’)”
                            style=”background: url(‘images/grid-bg.jpg’) repeat-x; border: solid 1px #7ea5b9″>
                            <asp:Image ID=”Image1″ runat=”server” ImageUrl=”~/calendar.gif” />&nbsp;&nbsp; <font
                                color=”black”><strong>My Objective & Review Status &nbsp;&nbsp;</strong></font>
                            <asp:Label ID=”Label1″ runat=”server”></asp:Label>
                        </div>
                        <div id=”divtwo” width=”100%” style=”display: block; border: solid 1px #7ea5b9″>
                            <table cellpadding=”13″ cellspacing=”1″ border=”0″ bgcolor=”#ccefff” width=”98%”>
                                <tr>
                                    <td bgcolor=”#F9F9F9″>
       some junk some junk text                                      
       some junk some junk text
       some junk some junk text
       some junk some junk text
       some junk some junk text
                                    </td>
                                </tr>
                            </table>
                        </div>
                 
</asp:Content>

 
—————————————————————————-

 
ORACLE AQUALOGIC
K2 BlackPearl

==============

Visual Studio 2005 Debugging in Vista
Problem: I was trying to debug in Visual Studio 2005 in my new machine which came with Vista Home Premium. Surprisingly I found that none of the break points are touched and I cannot debug. I tried giving all types of permission to the folder but no luck.

 

Solution: After googling a bit, I found that I needed to run VisualStudio2005 as an Administrator, and everything started working as I expected.

So all I had to do is :
Right-click the Visual Studio icon and select the “Run as administrator” option from the context menu.

================

(newid()) is the default value for uniqueidentifier in sqlserver

for bit field default value should be given like this : ((0)) for false and ((1)) for true

it automatically creates a new guid whenever a row is created.

it is like guid.newid() in c#.
==============

—-
Different constructors
const and readonly
when to use private static (for inheritance)
wat is delay signing
garbage collection algorithm

==========
‘const’:
Can’t be static.
Value is evaluated at compile time.
Initiailized at declaration only.
‘readonly’:
Can be either instance-level or static.
Value is evaluated at run time.
Can be initialized in declaration or by code in the constructor.
2nd Code example :
http://www.c-sharpcorner.com/UploadFile/sayginteh/ConstAndReadOnly11112005005151AM/ConstAndReadOnly.aspx

This source code below is an an example of the difference between const and readonly. Say you created a file A.cs and then compiled it to A.dll. Then you write your main application called MyTest.cs. After compiling MyTest.cs with referencing A.dll, you run the MyTest.exe.

using System;
public class A
{
public const int X = 123;
}

csc /t:library /out:A.dll A.cs

using System;
public class MyTest
{
public static void Main()
{
Console.WriteLine(“X value = {0}”, A.X);
}
}

csc /r:A.dll MyTest.cs

To run: mytest
The output :
X value = 123

Then you install the program into your client computer. It runs perfectly.
One week later, you realised that the value of X should have been 812 instead of 123.
What you will need to do is to

1] Compile A (after making the changes)
csc /t:library /out:A.dll A.cs

2] Compile your application again
csc /r:A.dll MyTest.cs

This can be a little troublesome. However, if you used the readonly instead of const,the situation will be slightly different. You start with

using System;
public class A
{
public static readonly int X = 123;
}

csc /t:library /out:A.dll A.cs

using System;
public class MyTest
{
public static void Main()
{
Console.WriteLine(“X value = {0}”, A.X);
}
}
 
csc /r:A.dll MyTest.cs
To run: mytest
The output :
X value = 123

Now you realised, you have made a mistake. All you need to do is

1] Recompile A.cs (after making changes)
csc /t:library /out:A.dll A.cs

2] Copy the new dll to the client computer and it should run perfectly. There is no need to recompile your application MyTest.cs here
 
=====

different constructors
================
1.  http://www.c-sharpcorner.com/UploadFile/cupadhyay/StaticConstructors11092005061428AM/StaticConstructors.aspx

C# supports two types of constructor, a class constructor (static constructor) and an instance constructor (non-static constructor).

Static constructor is used to initialize static data members as soon as the class is referenced first time, whereas an instance constructor is used to create an instance of that class with <new> keyword. A static constructor does not take access modifiers or have parameters and can’t access any non-static data member of a class.

Since static constructor is a class constructor, they are guaranteed to be called as soon as we refer to that class or by creating an instance of that class.

You may say, why not initialize static data members where we declare them in the code. Like this :

private static int id = 10;
private static string name = “jack”;

Static data members can certainly be initialized at the time of their declaration but there are times when value of one static member may depend upon the value of another static member. In such cases we definitely need some mechanism to handle conditional initialization of static members. To handlesuch situation, C# provides static constructor.

Let me explain you with examples :

//File Name : Test.cs
using System;
namespace Constructor
{
class Test
{
//Declaration and initialization of static data member
private static int id = 5;
public static int Id
{
get
{
return id;
}
}
public static void print()
{
Console.WriteLine(“Test.id = ” + id);
}
static void Main(string[] args)
{
//Print the value of id
Test.print();
}
}
}

In the above example, static data member <id> is declared and initialized in same line. So if you compile and run this program your output would look similar to this :

Test.id = 5

Lets create one more class similar to class Test but this time the value of its static data member would depend on the value of static data member <id> of class Test.id.

//File Name : Test1.cs
using System;
namespace Constructor
{
class Test1
{
private static int id ;
//Static constructor, value of data member id is set conditionally here.
//This type of initialization is not possible at the time of declaration.
static Test1()
{
if( Test.Id < 10 )
{
id = 20;
}
else
{
id = 100;
}
Console.WriteLine(“Static<Class> Constructor for Class Test1 Called..”);
}
public static void print()
{
Console.WriteLine(“Test1.id = ” + id);
}
static void Main(string[] args)
{
//Print the value of id
Test1.print();
}
}
}

As you can see in the above static constructor, static data member <id> is initialized conditionally. This type of initialization is not possible at the time of declaration. This is where static constructor comes in picture. So if you compile and run this program your output would look similar to this :

Static<Class> Constructor for Class Test1 Called..
id = 20

Since <id> in class Test was initialized with a value of 5, therefore <id> in class Test1 got initialized to a value of 20.

Some important point regarding static constructor from C# Language Specification and C# Programmer’s Reference :

1) The static constructor for a class executes before any instance of the class is created.
2) The static constructor for a class executes before any of the static members for the class are referenced.
3) The static constructor for a class executes after the static field initializers (if any) for the class.
4) The static constructor for a class executes at most one time during a single program instantiation
5) A static constructor does not take access modifiers or have parameters.
6) A static constructor is called automatically to initialize the class before the first instance is created or any static members are referenced.
7) A static constructor cannot be called directly.
8) The user has no control on when the static constructor is executed in the program.
9) A typical use of static constructors is when the class is using a log file and the constructor is used to write entries to this file.

2….
http://www.codersource.net/published/view/333/understanding_constructors_in.aspx
Constructors are used for initializing the members of a class whenever an object is created with the default values for initialization.
 
If a class is not defined with the constructor then the CLR (Common Language Runtime) will provide an implicit constructor which is called as Default Constructor.

A class can have any number of constructors provided they vary with the number of arguments that are passed, which is they should have different signatures.

Constructors do not return a value.

Constructors can be overloaded.

If a class is defined with static and Non-static constructors then the privilege will be given to the Non-static constructors.

The following are the access modifiers for constructors,

Public : A constructor that is defined as public will be called whenever a class is instantiated.

Protected : A constructor is defined as protected in such cases where the base class will initialize on its own whenever derived types of it are created.

Private : A constructor is defined as private in such cases whenever a class which contains only static members has to be accessed will avoid the creation of the object for the class.

Internal : An internal constructor can be used to limit concrete implementations of the abstract class to the assembly defining the class. A class containing an internal constructor cannot be instantiated outside of the assembly.

External : When a constructor is declared using an extern modifier, the constructor is said to be an external constructor.

Types of Constructors
i) Static : Used for initializing only the static members of the class. These will be invoked for the very first time the class is being loaded on the memory. They cannot accept any arguments. Static Constructors cannot have any access modifiers.

Syntax —-

Static ClassName()
{

//Initialization statements;

}

ii) Non-Static : are used for initializing the Non-Static and Static members of a class. These will be invoked everytime a new object is defined for a class.

Syntax —

Public ClassName([argsInfo])
{

//Initialization statements;

}

Example :

Using System;

Class SampleConstructor
{

    public static int s;

    public int ns;

    static SampleConstructor()
    {

       s = 10;

      //ns = 20; — Error cannot be assigned like this

    }

    public SampleConstructor()
   {

      ns=100;

      s=200;

    }

}

Class UseSampleConstructor
{

    public static void Main()
    {

        SampleConstructor sc = new SampleConstructor();

        Console.WriteLine(“{0},{1}”,sc.s, SampleConstructor.s, sc.ns);

    } l

} l

Error(Cannot call like this)

If you observe in the above example the static variable `ns’ cannot be assigned a value in the static Constructor as it is a Non-static member of the class and a static constructor cannot initialize a Non-Static member.

Also as you see in the above code the Non-static constructor initializing the values for both the static and Non-Static members of class, when we say `sc’ we are creating an object for the class and using the object we do the call the methods, it is not valid to call a static member of class using the object of the class we have to call it through the method name/Constructor name through the member will be invoked.

=================
Access modifiers determine the extent to which a variable or method can be accessed from another class or object.

There are four basic access modifier types in C#:

public (No restrictions to access).
private (Access is limited to within the class definition; This is the default access modifier type if none is formally specified).
protected (Access is limited to within the class definition and any class that inherits from the class).
internal (Access is limited exclusively to classes defined within the current project assembly).

=======

garbage collection
Mark and compact algorithm
http://aspalliance.com/828
generation 0 short lived objects in generation 0 and long lived objects in gen 2

==
Triggers

Triggers are special type of stored procedures which are executed automatically based on occurrence of an event.

Stored Procedures
Need to Execute Manually
Takes Input and Output Parameters
RETURN keyword is supported to return success or failure message using an integer value.

Triggers
Executes automatically based on events
Doesn’t support any parameters
RETURN keyword is not supported.
Triggers are mainly divided into the following two types.

1.Implicit Triggers
For every DML statement an implicit trigger will create on it. It do the commit process after execution of the DML process.

2.Explicit Triggers
User has to create these type of triggers manually which are used to commit or rollback of a group of statements.

Explicit Triggers are of Two Types.

1.DML Triggers
Are raised only when there is an INSERT,UPDATE or DELETE action. But we can’t create these for Database Creation, Table Creation or for User Creation etc…

DML Triggers are of Two Types.

1. After Trigger
“After Triggers” fire after the operation that fires the trigger. More than one trigger can now be defined on a table for each Insert/Update/Delete. A sp_settriggerorder stored procedure allows you to set a trigger to be either the “first” or “last” to fire.

2. Instead of Trigger

Instead Of Triggers fire instead of the operation that fires the trigger.

After Trigger
Useful only for tables.
We can create multiple after triggers for an event.
We can create multiple after triggers for a table.

Instead of Trigger
Useful for both tables and views.
We can create only one trigger for each event.
We can create only 3 triggers for a table those are Instead of Insert, Instead of Update and Instead of Delete.
2.DDL Triggers
A new table, database or user being created raises a DDL event and to monitor those, DDL triggers were introduced in SQL Server 2005.

DDL triggers can be created either in the database or in the database server. Need to create Triggers in Database when we want to monitor table creations and table drops. Need to create in server when we want to monitor database creations.

Instead of DDL Triggers were not supported.
http://www.dotnetspider.com/resources/26419-Sql-Server-Triggers.aspx

===============

FOR SQL JOBS TO CREATE : yOU NEED SQLSERVER AGENT SERVICE IN RUNNING STATE.(SERVICES.MSC)

————————
Break Continue

======

DECLARE @count int
SELECT @count=11
WHILE @count > 0
BEGIN
 SELECT @count=@count-1
 
 IF @count=4
 BEGIN
  BREAK
 END
 IF @count=6
 BEGIN
  CONTINUE
 END
 PRINT @count
END
O/p:
10
9
8
7
5

=============

 <%# DataBinder.Eval(Container.DataItem, “Id”) %>,
\
EVAL
BIND

———-

MS Gridview Rows are Telerik Items

============

 
How to get Autoincrement column in gridview

<asp:TemplateField HeaderText=”SNo”>

<ItemTemplate>

<asp:Label ID=”Labels” runat=”server” >

<%# Container.DataItemIndex + 1 %>

</asp:Label>

</ItemTemplate>

</asp:TemplateField>
========================================================

In Datagrid I have bind property for columns

From db I am getting 1 or 0 .I want to display it as yes no

Use this:
<asp:Label Text='<%# (bool)Eval(“IsExpired”) ? “Yes” : “” %>’ runat=”server”></asp:Label>

===================
WYSIWYG : What you see is what you get

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: