Wednesday, December 1, 2010

Replication of DB:


Database replication is the creation and maintenance of multiple copies of the same database. In most implementations of database replication, one database server maintains the master copy of the database and additional database servers maintain slave copies of the database. Database writes are sent to the master database server and are then replicated by the slave database servers. Database reads are divided among all of the database servers, which results in a large performance advantage due to load sharing. In addition, database replication can also improve availability because the slave database servers can be configured to take over the master role if the master database server becomes unavailable.

Database replication can be performed in at least three different ways:

  1. Snapshot replication: Data on one database server is plainly copied to another database server, or to another database on the same server.
  2. Merging replication: Data from two or more databases is combined into a single database.
  3. Transactional replication: Users obtain complete initial copies of the database and then obtain periodic updates as data changes.

Multi-master replication, where modifications can be tendered to any database server, and then flow through to further database servers, is frequently preferred. However, it establishes considerably bigger expenses and intricacy which may make it not viable in several circumstances. The universal dispute that exists in multi-master replication is transactional inconsistency avoidance or resolution. Most synchronous or keen replication systems do inconsistency avoidance, while asynchronous systems have to do inconsistency resolution. The resolution of such an inconsistency may be based on a timestamp of the transaction, on the ladder of the source servers or on much more intricate reason, which decides every time on all servers.

Database replication turns out to be complicated when it increases in size and magnitude. Typically, the increase relates with two dimensions; horizontal and vertical. Horizontal increase has extra data copies, vertical increase has data copies situated remotely. Troubles conceive by horizontal increase can be lessened by a multi-layer multi-view access protocol. Vertical increase is dashes into less trouble because internet dependability and performance are becoming better.

Tuesday, November 30, 2010

Attaching & Detaching

Detach:
USE master
GO
sp_detach_db 'ApressFinancial'

Attach:
CREATE DATABASE ApressFinancial
ON (FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\
Data\ApressFinancial.MDF')
FOR ATTACH

Database backup and restoring....

Database BackUp:

BACKUP DATABASE ApressFinancial
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\
ApressFinancial.bak'
WITH NAME = 'ApressFinancial-Full Database Backup',
SKIP,
NOUNLOAD,
STATS = 10

BackUp Transaction Logs:
BACKUP LOG ApressFinancial
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\
ApressFinancial.bak'
WITH NAME = 'ApressFinancial-Transaction Log Backup',
SKIP,
NOUNLOAD,
STATS = 10

Restore Database:
USE Master
GO
RESTORE DATABASE [ApressFinancial]
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\
ApressFinancial.bak' WITH FILE = 3,
NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO

Restore Logs:
RESTORE LOG [ApressFinancial]
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\
ApressFinancial.bak' WITH FILE = 5,
NOUNLOAD, STATS = 10

Friday, November 19, 2010

Differences between 2000,2005 & 2008..

SQL SERVER 2000:
1.Query Analyser and Enterprise manager are separate.
2.No XML datatype is used.
3.We can create maximum of 65,535 databases.
4.Nill
5.Nill
6.Nill
7.Nill
8.Nill
9.Nill
10.Nill
11.Nill
12.Nill
13.cant compress the tables and indexes.
14.Datetime datatype is used for both date and time.
15.No varchar(max) or varbinary(max) is available.
16.No table datatype is included.
17.No SSIS is included.
18.CMS is not available.
19.PBM is not available.

SQL SERVER 2005:

1.Both are combined as SSMS(Sql Server management Studio).
2.XML datatype is introduced.
3.We can create 2(pow(20))-1 databases.
4.Exception Handling
5.Varchar(Max) data type
6.DDL Triggers
7.DataBase Mirroring
8.RowNumber function for paging
9.Table fragmentation
10.Full Text Search
11.Bulk Copy Update
12.Cant encrypt
13.Can Compress tables and indexes.(Introduced in 2005 SP2)
14.Datetime is used for both date and time.
15.Varchar(max) and varbinary(max) is used.
16.No table datatype is included.
17.SSIS is started using.
18.CMS is not available.
19.PBM is not available.

SQL SERVER 2008:
1.Both are combined as SSMS(Sql Server management Studio).
2.XML datatype is used.
3.We can create 2(pow(20))-1 databases.
4.Exception Handling
5.Varchar(Max) data type
6.DDL Triggers
7.DataBase Mirroring
8.RowNumber function for paging
9.Table fragmentation
10.Full Text Search
11.Bulk Copy Update
12.Can encrypt the entire database introduced in 2008.
--check it(http://technet.microsoft.com/en-us/library/cc278098(SQL.100).aspx)
(http://www.sqlservercentral.com/articles/Administration/implementing_efs/870/)
(http://www.kodyaz.com/articles/sql-server-2005-database-encryption-step-by-step.aspx)
(http://www.sql-server-performance.com/articles/dev/encryption_2005_1_p1.aspx)
(http://geekswithblogs.net/chrisfalter/archive/2008/05/08/encrypt-documents-with-sql-server.aspx)
13.Can compress tables and indexes.
-http://www.mssqltips.com/tip.asp?tip=1582
14.Date and time are seperately used for date and time datatype,geospatial and timestamp with internal timezone
is used.
15.Varchar(max) and varbinary(max) is used.
16.Table datatype introduced.
17.SSIS avails in this version.
18.Central Management Server(CMS) is Introduced.
-http://msdn.microsoft.com/en-us/library/bb934126.aspx
-http://www.sqlskills.com/BLOGS/KIMBERLY/post/SQL-Server-2008-Central-Management-Servers-have-you-seen-these.aspx
19.Policy based management(PBM) server is Introduced.
-http://www.mssqltips.com/tip.asp?tip=1492
-http://msdn.microsoft.com/en-us/library/bb510667.aspx

Thursday, October 7, 2010

Here are the some good tips when creating a store procedure

(1)Always use ANSI92 syntax avoid to use the old syntax.

(2)Use SQL keyword in capital letters to increase readability.

(3)Use few possible variables to increase cache memory.

(4)Try to avoid dynamic queries if we are not using dynamic query there is no recompilation of execution plan but on the other side if we are using dynamic query every time we need recompile of plan.

(5)Use SET NOCOUNT ON this will helps us to get number of row effected without network traffic.

(6)To avoid recompilations use OPTION KEEPFIXED PLAN.

(7)In Select and Set use select to assign value to variable it is much faster than multiple set statement.

(8)Try to avoid IN. IN counts Null values so use EXISTA there. EXISTS which return only boolean value and IN return heavier result than EXISTS.

(9)In CAST and CONVERT always try to use CAST it is ASNI92 standard.Use convert in case of datetime.

(10)Avoid Distinct and Order by clause.These class needs extra space.

(11)Avoid cursor so use while loop for that and temparory tables.

(12)Avoid to use * in select statement.

(13)Avoid correlated sub queries.

(14)Avoid select * into for large tables it locks the system objects.

(15)Avoid temporary tables because it will recompile the procedure.

Wednesday, September 1, 2010

Convert gridview to excel sheet.

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class ExportGridView : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
GridView1.DataSource = BindData();
GridView1.DataBind();
}
}



private string ConnectionString
{

get { return @"Server=localhost;Database=Northwind;
Trusted_Connection=true"; }

}



private DataSet BindData()
{
// make the query
string query = "SELECT * FROM Categories";
SqlConnection myConnection =
new SqlConnection(ConnectionString);
SqlDataAdapter ad =
new SqlDataAdapter(query, myConnection);
DataSet ds =
new DataSet();
ad.Fill(ds, "Categories");
return ds;

}




protected void Button1_Click(object sender, EventArgs e)
{
Response.Clear();

Response.AddHeader("content-disposition", "attachment;
filename=FileName.xls");

Response.Charset = "";

// If you want the option to open the Excel file without saving than

// comment out the line below

// Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.ContentType = "application/vnd.xls";

System.IO.StringWriter stringWrite =
new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter htmlWrite =
new HtmlTextWriter(stringWrite);

GridView1.RenderControl(htmlWrite);

Response.Write(stringWrite.ToString());

Response.End();

}

public override void VerifyRenderingInServerForm(Control control)
{

// Confirms that an HtmlForm control is rendered for the
specified ASP.NET server control at run time.

}
}

Sunday, August 1, 2010

Is it possible to read Web.config files from browser?

No. It is not possible . As Machine.Config file has configuration settings which has entries that maps to CONFIG files, ASAX file with an HTTP handler "HttpForbiddenHandler", which prevents to retrieve the associated file.


immutable and mutable
String is immutable
i.e. Strings cannot be altered. When you alter a string (by adding to it for example), you are actually creating a new string.

But StringBuilder is not immutable
so if u have to alter a string many times, such as mutliple concatenations then use StringBuilder.

Tuesday, July 27, 2010

stuff function

STUFF (SQL Server Compact Edition)

Deletes a specified length of characters and inserts another set of characters at a specified starting point.

Syntax


STUFF ( character_expression, start, length, character_expression )

ROW_NUMBER :

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Syntax


ROW_NUMBER ()     OVER ( [  ]  )

Friday, July 23, 2010

Dynamic menu control....

In aspx page:

Take asp:menu control....And add bellow code in code behind...

Code behind:

private void loadCategory()
{
DataTable category = new DataTable();

category = CategoryService.Gettopcategory();

DataTable subcat = new DataTable();
subcat = CategoryService.Getsubcategory();

DataView categoryView = new DataView(category);
DataView childCategoryView = new DataView(subcat);



if (categoryView.Count > 0)
{
foreach (DataRowView categoryRowView in categoryView)
{
MenuItem parentMenu = new MenuItem();
parentMenu.Text = categoryRowView["maincategory"].ToString();
parentMenu.Value = categoryRowView["categoryid"].ToString();

childCategoryView.RowFilter = "categoryid=" + (int)categoryRowView["categoryid"];


foreach (DataRowView childcategoryRow in childCategoryView)
{

MenuItem childMenu = new MenuItem();
childMenu.Text = childcategoryRow["subcategory"].ToString();
childMenu.Value = childcategoryRow["subid"].ToString();
//childMenu.NavigateUrl = "~/Default.aspx?CategoryID=" + childMenu.Value;
parentMenu.ChildItems.Add(childMenu);
//}
}
Menu2.Items.Add(parentMenu);
}

}


}

Thursday, July 15, 2010

.Net 2.0,3.0 and 3.5

NET framework 2.0:

It brings a lot of evolution in class of the framework and refactor control including the support of

Generics
Anonymous methods
Partial class
Nullable type
The new API gives a fine grain control on the behavior of the runtime with regards to multithreading, memory allocation, assembly loading and more
Full 64-bit support for both the x64 and the IA64 hardware platforms
New personalization features for ASP.NET, such as support for themes, skins and webparts.
.NET Micro Framework


.NET framework 3.0:

Also called WinFX,includes a new set of managed code APIs that are an integral part of Windows Vista and Windows Server 2008 operating systems and provides

Windows Communication Foundation (WCF), formerly called Indigo; a service-oriented messaging system which allows programs to interoperate locally or remotely similar to web services.
Windows Presentation Foundation (WPF), formerly called Avalon; a new user interface subsystem and API based on XML and vector graphics, which uses 3D computer graphics hardware and Direct3D technologies.
Windows Workflow Foundation (WF) allows for building of task automation and integrated transactions using workflows.
Windows CardSpace, formerly called InfoCard; a software component which securely stores a person's digital identities and provides a unified interface for choosing the identity for a particular transaction, such as logging in to a website


.NET framework 3.5:

It implement Linq evolution in language. So we have the folowing evolution in class:

Linq for SQL, XML, Dataset, Object
Addin system
p2p base class
Active directory
ASP.NET Ajax
Anonymous types with static type inference
Paging support for ADO.NET
ADO.NET synchronization API to synchronize local caches and server side datastores
Asynchronous network I/O API
Support for HTTP pipelining and syndication feeds.
New System.CodeDom namespace.

Connection Pooling Basics:

Connection Pooling Basics




Opening a database connection is a resource intensive and time consuming operation. Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. Connection pool manager maintains a pool of open database connections. When a new connection requests come in, the pool manager checks if the pool contains any unused connections and returns one if available. If all connections currently in the pool are busy and the maximum pool size has not been reached, the new connection is created and added to the pool. When the pool reaches its maximum size all new connection requests are being queued up until a connection in the pool becomes available or the connection attempt times out.

Connection pooling behavior is controlled by the connection string parameters. The following are four parameters that control most of the connection pooling behavior:

  • Connect Timeout - controls the wait period in seconds when a new connection is requested, if this timeout expires, an exception will be thrown. Default is 15 seconds.
  • Max Pool Size - specifies the maximum size of your connection pool. Default is 100. Most Web sites do not use more than 40 connections under the heaviest load but it depends on how long your database operations take to complete.
  • Min Pool Size - initial number of connections that will be added to the pool upon its creation. Default is zero; however, you may chose to set this to a small number such as 5 if your application needs consistent response times even after it was idle for hours. In this case the first user requests won't have to wait for those database connections to establish.
  • Pooling - controls if your connection pooling on or off. Default as you may've guessed is true. Read on to see when you may use Pooling=false setting.

Wednesday, July 14, 2010

Difference between Web Server and Application Server.

Difference between AppServer and a Web server
(1) Webserver serves pages for viewing in web browser, application server provides exposes businness logic for client applications through various protocols

(2) Webserver exclusively handles http requests.application server serves bussiness logic to application programs through any number of protocols.

(3) Webserver delegation model is fairly simple,when the request comes into the webserver,it simply passes the request to the program best able to handle it(Server side program). It may not support transactions and database connection pooling.

(4) Application server is more capable of dynamic behaviour than webserver. We can also configure application server to work as a webserver.Simply applic! ation server is a superset of webserver.


Web Server serves static HTML pages or gifs, jpegs, etc., and can also run code written in CGI, JSP etc. A Web server handles the HTTP protocol. Eg of some web server are IIS or apache.

An Application Server is used to run business logic or dynamically generated presentation code. It can either be .NET based or J2EE based (BEA WebLogic Server, IBM WebSphere, JBoss).

A J2EE application server runs servlets and JSPs (infact a part of the app server called web container is responsible for running servlets and JSPs) that are used to create HTML pages dynamically. In addition, J2EE application server can run EJBs - which are used to execute business logic.

An Application server has a 'built-in' web server, in addition to that it supports other modules or features like e-business integration, independent management and security module, portlets etc.


Difference between Web Server and Application Server.

A Web Server understands and supports only HTTP protocol whereas an Application Server supports HTTP,TCP/IP and many more protocols. Also many more features such as Caches,Clusters,Load Balancing are there in Application Servers which are not available in Web Servers. We can also Configure Application Servers to work as Web Server. In short, Applicaion Server is a super set of which Web Server is a sub set.

You can't run EJB or any business logic in Javawebserver. An application server should have the capability to run business logic in it. That's why we can't say that JWserver a application server.

The Web server:

A Web server handles the HTTP protocol. When the Web server receives an HTTP request, it responds with an HTTP response, such as sending back an HTML page. To process a request, a Web server may respond with a static HTML page or image, send a redirect, or delegate the dynamic response generation to some other program such as CGI scripts, JSPs (JavaServer Pages), servlets, ASPs (Active Server Pages), server-side JavaScripts, or some other server-side technology. Whatever their purpose, such server-side programs generate a response, most often in HTML, for viewing in a Web browser.

Understand that a Web server's delegation model is fairly simple. When a request comes into the Web server, the Web server simply passes the request to the program best able to handle it. The Web server doesn't provide any functionality beyond simply providing an environment in which the server-side program can execute and pass back the generated responses. The server-side program usually provides for itself such functions as transaction processing, database connectivity, and messaging.

While a Web server may not itself support transactions or database connection pooling, it may employ various strategies for fault tolerance and scalability such as load balancing, caching, and clustering—features oftentimes erroneously assigned as features reserved only for application servers.

Eg: Apache HTTP Server, Sun ONE Web Server, iPlanet Web Server

The application server:

As for the application server, according to our definition, an application server exposes business logic to client applications through various protocols, possibly including HTTP. While a Web server mainly deals with sending HTML for display in a Web browser, an application server provides access to business logic for use by client application programs. The application program can use this logic just as it would call a method on an object (or a function in the procedural world).

Such application server clients can include GUIs (graphical user interface) running on a PC, a Web server, or even other application servers. The information traveling back and forth between an application server and its client is not restricted to simple display markup. Instead, the information is program logic. Since the logic takes the form of data and method calls and not static HTML, the client can employ the exposed business logic however it wants.

In most cases, the server exposes this business logic through a component API, such as the EJB (Enterprise JavaBean) component model found on J2EE (Java 2 Platform, Enterprise Edition) application servers. Moreover, the application server manages its own resources. Such gate-keeping duties include security, transaction processing, resource pooling, and messaging.

Like a Web server, an application server may also employ various scalability and fault-tolerance techniques.

Few imp points in C++ and C#

1)Memory Management


The other difference is that there is no equivalent to C++ delete operator in C#. Instead, with C#, the .Net garbage collector periodically comes in and scans through the refrences in your code in order to identify which areas of the heap are currently in use by our program. It is then automatically able to remove all the objects that are no longer in use. This technique effectively saves you from having to free up nay memory yourself on the heap.

2)New control flow statement- for each

C# provides an additional flow control statement, for each. For each loops across all items in array or collection without requiring explicit specification of the indices.
Syntax:

Foreach(double someElement in MyArray)
{
Console.WriteLine(someElement);
}

3) Classes

Classes in C# follow much the same principles as in C++, though there are a few differences in both features and syntax.

Class MyClass : MyBaseClass
{
Private string SomeFiels;
Public in SomeMethod()
{
Return;
}

Classes defined in C# using what at first sight looks like much the same syntax as in C++, but there are numerous differences:

There is no access modifier on the name of the base class. Inheritance is always public.

A class can only be derived from one base class. If no base class is explicitly specified, then the class will automatically be derived from System.Object, which will give the class all the functionality of System.Object, the most commnly used of which is ToString().

In C++, the only types of class members are variables, functions, constructors, destructors and operator overloads, C# also permits delegates, events and properties.

The access modifiers public, private and protected have the same meaning as in C++ but there are two additional access modifiers available:


i. Internal
ii. Protected internal

C++ requires a semicolon after the closing brace at the end of a class definition. This is not required in C#.

Wednesday, March 31, 2010

What is DNN?

- DNN means Dot Net Nuke Its a Web Content Management System for Microsoft.Net.

- allows customers to administer all aspects of their internet, intranet or extranet website, including page creation, content, layout and security

Open Source:

- DNN is an open source application.


What does this mean?

- First (and perhaps most important), DNN is free. There are no licensing fees or per registered user fees associated with it. Simply download it and it’s yours. In addition, the code to the application is open and available to all. Need to make a modification to fit your particular organization? You have full access to the source code to custom fit DNN to your needs.

Monday, January 18, 2010

Difference between javascripts,jquery and ajax

1. Javascript

A language that comes with your browser. It allows you to show an alert box, the one that you need to press Ok, validate forms, on the client, or without change the page. Usually, it is stopped by an alert, confirm, etc box.

2. jQuery

A library code made with Javascript, that make easier to program Javascript and AJAX. You can download it on jquery.com .

3. AJAX

Asynchronous Javascript And XML. Which means that with a help from a programming language like PHP, ASP.NET and etc. it can work changing data without refresh the whole page, just the part of the data will be refreshed.

Friday, January 8, 2010

Sql Syntax

Select Statement
SELECT "column_name" FROM "table_name"

Distinct
SELECT DISTINCT "column_name"
FROM "table_name"

Where
SELECT "column_name"
FROM "table_name"
WHERE "condition"

And/Or
SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{[AND|OR] "simple condition"}+

In
SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ('value1', 'value2', ...)

Between
SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2'

Like
SELECT "column_name"
FROM "table_name"
WHERE "column_name" LIKE {PATTERN}

Order By
SELECT "column_name"
FROM "table_name"
[WHERE "condition"]
ORDER BY "column_name" [ASC, DESC]

Count
SELECT COUNT("column_name")
FROM "table_name"

Group By
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"

Having
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
HAVING (arithematic function condition)

Create Table Statement
CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )

Drop Table Statement
DROP TABLE "table_name"

Truncate Table Statement
TRUNCATE TABLE "table_name"

Insert Into Statement
INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)

Update Statement
UPDATE "table_name"
SET "column_1" = [new value]
WHERE {condition}

Delete From Statement
DELETE FROM "table_name"
WHERE {condition}

Thursday, January 7, 2010

Substring

SUBSTRING(expression,start,length)

DECLARE @SampleString VARCHAR(11)


SELECT SUBSTRING(@SampleString,-11,22)
Start = -11
Length = 22
Result is : (-11 + 22) - 1 = 10, So that it returns 10 Chanacters : Dotnetfund

Difference bewtween SCOPE_IDENTITY() and @@IDENTITY

Both SCOPE_IDENTITY() and @@IDENTITY will return the last identity value generated in the table. but there is some difference between the two:

SCOPE_IDENTITY() will return the Identity value generated in a table that is currently in scope.

@@IDENTITY will return the Identity value generated in a table irrespective of the scope.

Example
Let us suppose we have two tables named table1 & table2... and we have one trigger defined on table1 that is insert a record in table2 when new record will be inserted into table1.

in this case the Output of Both SCOPE_IDENTITY() and @@IDENTITY will be different.
SCOPE_IDENTITY() will return the identity value of table1 that is in current scope.
while @@IDENTITY will return the identity value of table2.