SQL Server 2005 : Split Function

CREATE FUNCTION dbo.Splitter(@strList VARCHAR(8000),@strSeperator VARCHAR(10))
Returns @tblSplit TABLE (splitValue VARCHAR(255))
AS
BEGIN
DECLARE @tmp_FnCommaSplitter TABLE (splitValue VARCHAR(255))
DECLARE @strTmp VARCHAR(5000)
DECLARE @pos INT
DECLARE @lenSeperator INT
SELECT @lenSeperator = LEN(@strSeperator)
SELECT @strTmp = @strList
SELECT @pos = PATINDEX('%'+@strSeperator+'%',@strTmp)
WHILE (@pos <> 0)
BEGIN
IF SUBSTRING(@strTmp,1,@pos-1) <> ''
INSERT @tmp_FnCommaSplitter VALUES (SUBSTRING(@strTmp,1,@pos-1))

SELECT @strTmp = SUBSTRING(@strTmp,@pos+@lenSeperator,5000)
SELECT @pos = PATINDEX('%'+@strSeperator+'%',@strTmp)
END
IF @strTmp <> ''
INSERT @tmp_FnCommaSplitter VALUES (LTRIM(RTRIM(@strTmp)))
INSERT @tblSplit SELECT * FROM @tmp_FnCommaSplitter
RETURN
END

SQL Datechecking

DECLARE @datestring varchar(8)
SET @datestring = '12/21/98'
SELECT ISDATE(@datestring)

Procedure to get the record Count instead of using count(*)

sp_spaceused '' for getting the counts.
This will be faster instead of using select count(*)

Cleaning the Transaction Logs in SQL Server2005

To Back up the transaction log file :
BACKUP LOG TO DISK = ''
Eg.
BACKUP LOG TestDB TO DISK='C:\TestDB1.bak'

To Shrink the transaction log file:

DBCC SHRINKFILE (, ) WITH NO_INFOMSGS

How to clean the transaction Log in SQL Server

use master
go
dump transaction with no_log
go
use
go
DBCC SHRINKFILE (, 100) -- where 100 is the size you may want to shrink it to in MB, change it to your needs
go
-- then you can call to check that all went fine
dbcc checkdb()

(or)

To Truncate the log file:

Backup the database
Detach the database, either by using Enterprise Manager or by executing : *Sp_DetachDB [DBName]*
Delete the transaction log file. (or rename the file, just in case)
Re-attach the database again using: *Sp_AttachDB [DBName]*
When the database is attached, a new transaction log file is created.
To Shrink the log file:

Backup log [DBName] with No_Log
Shrink the database by either:

Using Enterprise manager :- Right click on the database, All tasks, Shrink database, Files, Select log file, OK.

Using T-SQL :- *Dbcc Shrinkfile ([Log_Logical_Name])*

You can find the logical name of the log file by running sp_helpdb or by looking in the properties of the database in Enterprise Manager.

Replace the First Charater in SQL

Query for replacing the first character only.

--select replace(left('Mahesh',1),left('Mahesh',1),'H') + right('Mahesh',len('Mahesh') - 1)

Generics in .NET 2.0

What Are Generics?
Generics allow you to realize type safety at compile time. They allow you to create a data structure without committing to a specific data type. When the data structure is used, however, the compiler makes sure that the types used with it are consistent for type safety. Generics provide type safety, but without any loss of performance or code bloat. While they are similar to templates in C++ in this regard, they are very different in their implementation.

The short answer is this: "without generics, it is very difficult to create type-safe collections".

Creating Our First Generic Type:

public class Col {
T t;
public T Val{get{return t;}set{t=value;}}
}


public class ColMain {
public static void Main() {
//create a string version of our generic class
Col mystring = new Col();
//set the value
mystring.Val = "hello";

//output that value
System.Console.WriteLine(mystring.Val);
//output the value's type
System.Console.WriteLine(mystring.Val.GetType());

//create another instance of our generic class, using a different type
Col myint = new Col();
//load the value
myint.Val = 5;
//output the value
System.Console.WriteLine(myint.Val);
//output the value's type
System.Console.WriteLine(myint.Val.GetType());

}
}


When we compile the two classes above and then run them, we will see the following output:

hello
System.String
5
System.Int32

Generic Collections:

User.cs


namespace Rob {
public class User {
protected string name;
protected int age;
public string Name{get{return name;}set{name=value;}}
public int Age{get{return age;}set{age=value;}}
}
}


Main.cs

public class M {
public static void Main(string[] args) {
System.Collections.Generic.List users = new System.Collections.Generic.List();
for(int x=0;x<5;x++) {
Rob.User user = new Rob.User();
user.Name="Rob" + x;
user.Age=x;
users.Add(user);
}

foreach(Rob.User user in users) {
System.Console.WriteLine(System.String.Format("{0}:{1}", user.Name, user.Age));
}
System.Console.WriteLine("press enter");
System.Console.ReadLine();

for(int x=0;x System.Console.WriteLine(System.String.Format("{0}:{1}", users[x].Name, users[x].Age));
}

}
}


Output

Rob0:0
Rob1:1
Rob2:2
Rob3:3
Rob4:4
press enter

Rob0:0
Rob1:1
Rob2:2
Rob3:3
Rob4:4

Create A VB InputBox in C#

The below link will help out to know about the Inputbox in C#:

http://www.knowdotnet.com/articles/inputbox.html

Object Oriented Architecture Modeling

Why architecture with OO methods?
OO methods provide a set of techniques for analyzing, decomposing, and modularizing software system architectures. In general, OO methods are characterized by structuring the system architecture on the basis of its objects (and classes of objects) rather than the actions it performs
Main purpose of these design concepts is to manage software system complexity by improving software quality factors. This provides the following benefits
• Helps to focus on large-scale system design
• Separation of design concerns
• Can identify good domain-specific architectures
• Design abstraction
• Have a common language
o Managerial basis for cost estimation & process mgmt
o Reuse
o Consistency and dependency analysis
o Technical basis for design

OO Modeling of an Solution:
• Identifying Objects of Interest from the Model
• Associating Attributes with Objects of Interest
• Specify how the objects, and interactions and interrelationships among the objects, will effect a solution to the problem

Types Model
(a) Textual Models
These are textual descriptions of both individual objects and systems of objects.

(b) Graphical Models
These graphically represent the characteristics of individual objects or systems of objects.

(c) Class-Responsibility-Collaboration Cards (CRC Cards)

Where do Objects come from?
The following are potential sources of objects:
• The model itself, e.g.:
o Written documents
paragraph, the nouns, pronouns, noun phrases, adjectives, adjectival phrases, adverbs, and adverbial phrases will suggest candidate objects.
o Graphical model certain objects may be implied by such things as nodes and communication among the nodes.
• The information supplied by the OORA(Object Oriented Requirement Analysis) process
• The mind of the software engineer
• A study of existing objects in an object library
• Technical references, e.g., books and articles

Object Modeling using UML
There are three prominent aspects of the modeled system that are handled by UML:

• Functional Model
It describes the functionality of the system from the user's Point of View.
It’s through Use Case Diagrams.
• Object Model
It showcases the structure and substructure of the system using objects, attributes, operations, and associations.
It’s through Class Diagrams.
• Dynamic Model
It showcases the internal behavior of the system.
It’s through Sequence Diagrams, Activity Diagrams and Statechart Diagrams

Use Case model
• Provide an overview of all or part of the usage requirements for a system or organization in the form of an model or a business model
• Communicate the scope of a development project
• Model the analysis of the usage requirements in the form of a system use case model

Use Cases are used primarily to capture the high level user-functional requirements of a system. The Use Case model is about describing “what” the system will do at a high-level and with a user focus for the purpose of scoping the project and giving the application some structure.

Class Diagram
It shows the classes of the system, their inter-relationships, and the operations and attributes of the classes. Class diagrams are used to:
• Explore domain concepts in the form of a domain model
• Analyze requirements in the form of a conceptual/analysis model
• Depict the detailed design of object-oriented or object-based software

Object Sequence Diagram
Object Sequence Diagrams are about deciding and modeling HOW our system will achieve WHAT we described in the Use Case model.
It’s a dynamic modeling technique. UML sequence diagrams are typically used to:
• Validate and flesh out the logic of a usage scenario.
• Explore the design because they provide a way to visually step through invocation of the operations defined by the classes.
• To detect bottlenecks within an object-oriented design. By looking at what messages are being sent to an object, and by looking at roughly how long it takes to run the invoked method, you quickly get an understanding of where need to change the design to distribute the load within the system. In fact some CASE tools even enable to simulate this aspect of the software.
• Gives a feel for which classes in the application are going to be complex, which in turn is an indication that may need to draw state chart diagrams for those classes

Activity Diagram
This is used to explore the logic of the following
• A complex operation
• A complex business rule
• A single use case
• Several use cases
• A business process
• Software processes

Deployment Diagram
Deployment diagrams show the hardware for the system, the software that is installed on that hardware, and the middleware used to connect the disparate machines to one another. A deployment model is to:

• Explore the issues involved with installing the system into production.
• Explore the dependencies that your system has with other systems that are currently in, or planned for, the production environment.
• Depict a major deployment configuration of a business application.
• Design the hardware and software configuration of an embedded system.
• Depict the hardware/network infrastructure of an organization.

Reading the Mails from the Outlook 2003 using C# .NET

The following demostrates how to retreive data from items within an Outlook folder (called "MySubFolderName" under the Inbox folder) using .NET:

First add a reference to the Outlook COM object your project:

1. In VS.NET right click on References and choose Add Reference.
2. Select the COM tab Choose "Microsoft Outlook 11.0 Object Library" (this is for MS Office 2003 - I think 10.0 is for Office XP) and click Select.
3. Click OK.
Note that you can access any Outlook/Exchange object types, eg Appointments, Notes, Tasks, Emails etc - just use intellisense to select which one (eg Microsoft.Office.Interop.Outlook. ... - see definition of variable called 'item' below).

Here's the code:

Microsoft.Office.Interop.Outlook.Application app = null;
Microsoft.Office.Interop.Outlook._NameSpace ns = null;
Microsoft.Office.Interop.Outlook.PostItem item = null;
Microsoft.Office.Interop.Outlook.MAPIFolder inboxFolder = null;
Microsoft.Office.Interop.Outlook.MAPIFolder subFolder = null;

try
{
app = new Microsoft.Office.Interop.Outlook.Application();
ns = app.GetNamespace("MAPI");
ns.Logon(null,null,false, false);

inboxFolder = ns.GetDefaultFolder(Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderInbox);
subFolder = inboxFolder.Folders["MySubFolderName"]; //folder.Folders[1]; also works
Console.WriteLine("Folder Name: {0}, EntryId: {1}", subFolder.Name, subFolder.EntryID);
Console.WriteLine("Num Items: {0}", subFolder.Items.Count.ToString());

for(int i=1;i<=subFolder.Items.Count;i++)
{
item = (Microsoft.Office.Interop.Outlook.PostItem)subFolder.Items[i];
Console.WriteLine("Item: {0}", i.ToString());
Console.WriteLine("Subject: {0}", item.Subject);
Console.WriteLine("Sent: {0} {1}" item.SentOn.ToLongDateString(), item.SentOn.ToLongTimeString());
Console.WriteLine("Categories: {0}", item.Categories);
Console.WriteLine("Body: {0}", item.Body);
Console.WriteLine("HTMLBody: {0}", item.HTMLBody);
}
}
catch (System.Runtime.InteropServices.COMException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
ns = null;
app = null;
inboxFolder = null;
}

Note:If the Problem comes then change the PostItem into MailItem

Programmatically Complete PDF Form Fields using VB and the iTextSharp DLL

Use the below link to get the Idea:

http://www.vbdotnetheaven.com/UploadFile/scottlysle/PdfGenVB06162007031634AM/PdfGenVB.aspx

Caching in ASP .NET

ASP.NET supports multiple levels of caching:
1. Output caching
2. Fragment caching
3. Data caching

Benefits:
Can increase performance by reducing trips to the database

Challenges:
Requires correct design and enough memory to be effective

ASP.NET provides built-in caching support that enables re-use of work:
1. Full Page Caching (Vary by params, language, user-agent)
2. Partial Page Caching (Enables portions of pages to be cached)
3. Web Service Caching (Vary by parameters and methods)
4. Cache Engine: Extensible Cache API (Enables arbitrary objects to be cached)

Cache Dependencies:
1. File Based dependencies
2. Key Based dependencies
3. Time Based dependencies

How to do Caching:
Cache the content of an entire ASP.NET page
–Declarative:
<%@ OutputCache Duration="60" VaryByParam="None"%>
–Programmatic:
•Response.Cache.SetExpires(DateTime.Now.AddSeconds(60))
•Response.Cache.SetCacheability(HttpCacheability.Public)
•Response.Cache.SetSlidingExpiration(True)

Example of Output Cache:
<%@ OutputCache Duration="20" VaryByParam="none" %>



Now :<%= Now() %>


The content of the page will be same till 20 Sec.
of the creation of cache of this output.

Finding the Control in the Grid

To find the control whether is existing in the grid:
----------------------------------------------------

TextBox txtcomments;
txtcomments=(TextBox)e.Item.FindControl("txtcomments");
txtcomments.Attributes.Add("Onchange","javascript:fncallcheck("+txtcomments.ClientID+")");

String Array

For String array:
------------------

string[] strhistry = (string[])childdr["fldWFHistory"];

Place Holder

For Place Holder:
------------------

StringBuilder str = new StringBuilder();
str.Append("");
str.Append("strhistry[i].ToString()");
placecomments.Controls.Add(new LiteralControl(str.ToString()));

Formating the Date in C# .NET

The Date mm/dd/yyyy Can be changed in to dd/mm/yyyy using the below format:

String.Format("{0:dd/MM/yyyy}",Convert.ToDateTime("12/20/2008"));

.NET Web Application Setup & Deployemnt

Step 1: Create an ASP.NET Web application
1. Start Visual Studio .NET or Visual Studio 2005.
2. Use Visual C# .NET or Visual C# 2005 or use Visual Basic .NET or Visual Basic 2005 to create an ASP.NET Web Application project. Name the project Project1.

Note In Visual Studio 2005, create an ASP.NET Web Site project.
3. On the Build menu, click Build Solution.

Step 2: Add a Web Setup project to your solution
1. In Solution Explorer, right-click the Project1 solution, point to Add, and then click Add New Project. The New Project dialog box appears.
2. Under Project Types, click Setup and Deployment Projects.
3. Under Templates, click Web Setup Project.
4. In the Name text box, type WebSetupProject1.
5. In the Location text box, type C:\DeployFolder, and then click OK.

Step 3: Add the Web application files to Your Web Setup project
1. In the File System (WebSetupProject1) window, right-click Web Application Folder, point to Add, and then click File. The Add Files dialog box appears.
2. Locate the C:\Inetpub\wwwroot\Project1 folder.
3. Select all the files that are in the Project1 folder, and then click Open.
4. In the File System (WebSetupProject1) window, expand Web Application Folder.
5. Under Web Application Folder, right-click bin, point to Add, and then click File. The Add Files dialog box appears.
6. Locate the C:\Inetpub\wwwroot\Project1\bin folder.
7. Click the Project1.dll file, and then click Open.

Step 4: Configure the Bootstrapper URL for your Web Setup project
1. In Solution Explorer, right-click WebSetupProject1, and then click Properties. The WebSetupProject1 Property Pages dialog box appears.
2. In the Bootstrapper list box, click Web Bootstrapper. The Web Bootstrapper Settings dialog box appears.
3. In the Setup folder URL text box, type http://devserver/Bootstrap1, and then click OK.

Note devserver is a placeholder for the name of your Web server.
4. In the WebSetupProject1 Property Pages dialog box, click OK.
5. In Solution Explorer, right-click WebSetupProject1, and then click Build.

Step 5: Copy files to the bootstrapping application folder
1. In the C:\Inetpub\Wwwroot folder, create a folder that is named Bootstrap1.
2. Create a virtual directory that is named Bootstrap1, and then map this virtual directory to the C:\Inetpub\wwwroot\Bootstrap1 folder.
3. Copy the following files from the C:\DeployFolder\WebSetupProject1\debug folder to the C:\Inetpub\wwwroot\Bootstrap1 folder: • Setup.Exe
• WebSetupProject1.msi


Step 6: Run your Web Setup project on a remote (deployment) computer
1. Start Microsoft Internet Explorer.
2. Type the following URL in the address bar, and then press ENTER:
http://devserver/Bootstrap1/Setup.Exe
Note devserver is a placeholder for the name of your Web server.
3. In the File Download dialog box, click Open.
4. In the Security Warning dialog box, click Yes.
5. In the WebSetupProject1 dialog box, click Next.
6. Keep WebSetupProject1 in the Virtual directory text box. Click Next.
7. On the Confirm Installation page of the WebSetupProject1 dialog box, click Next.
8. On the Installation Complete page of the WebSetupProject1 dialog box, click Close.
9. Locate the C:\Inetpub\wwwroot\WebSetupProject1 folder.
10. Open the following file in a text editor such as Notepad:• If you are using Visual C# .NET or Visual C# 2005, open the Project1.csproj.webinfo file.
• If you are using Visual Basic .NET or Visual Basic 2005, open the Project1.vbproj.webinfo file.

11. Modify the URLPath element as follows:

Visual C# .NET or Visual C# 2005 code


Visual Basic .NET or Visual Basic 2005 code



12. Save the file as one of the following, depending on your project:• If you are using Visual C# .NET or Visual C# 2005, save the file as WebSetupProject1.csproj.webinfo.
• If you are using Visual Basic .NET or Visual Basic 2005, save the file as WebSetupProject1.vbproj.webinfo.

13. Open the following file, depending on your project:• If you are using Visual C# .NET or Visual C# 2005, open the Project1.csproj file.
• If you are using Visual Basic .NET or Visual Basic 2005, open the Project1.vbproj file.
You may receive the error message that appears in the "Symptoms" section of this article.

.NET: Generating a Random Number in C#

private int RandomNumber(int min, int max)
{
Random random = new Random();
return random.Next(min, max);
}

Copy the above code in your class where you want to use it and call like this:

int returnValue = RandomNumber(5, 20);

.NET: How to create a scrollable DataGrid?

Place the Data Grid with the Div Tag:

.NET: Redirect .NET tracing to a file?

The Debug and Trace classes both have a Listeners property, which is a collection of sinks that receive the tracing that you send via Debug.WriteLine and Trace.WriteLine respectively. By default the Listeners collection contains a single sink, which is an instance of the DefaultTraceListener class. This sends output to the Win32 OutputDebugString() function and also the System.Diagnostics.Debugger.Log() method. This is useful when debugging, but if you're trying to trace a problem at a customer site, redirecting the output to a file is more appropriate. Fortunately, the TextWriterTraceListener class is provided for this purpose.


How to use the TextWriterTraceListener class to redirect Trace output to a file:

Trace.Listeners.Clear();
FileStream fs = new FileStream( @"c:\log.txt", FileMode.Create, FileAccess.Write );
Trace.Listeners.Add( new TextWriterTraceListener( fs ) );
Trace.WriteLine( @"This will be writen to c:\log.txt!" );
Trace.Flush();