Asp.net
SET FOCUS ON PARTICULAR CONTROL IN ASP.NET PAGE---
SetFocus(txtForumReply);
DateDiff in Sql server---
http://msdn2.microsoft.com/en-us/library/aa258269(sql.80).aspx
DATEDIFF ( datepart , startdate , enddate )
Datepart
Abbreviations
Year
yy, yyyy
quarter
qq, q
Month
mm, m
dayofyear
dy, y
Day
dd, d
Week
wk, ww
Hour
hh
minute
mi, n
second
ss, s
millisecond
ms
pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
USE OF SPLIT FUNCTION
string str = "manpreet,Firoz";
string[] sas=str.Split(',');
str = sas[0];
Query string--
if (Request.QueryString["Id"] != null && Request.QueryString["Id"] != "")
Session Variable always strores object To access it we have to convert it in specified
type
Session.Abandon();----End the Current Session.
Session.Clear();----Remove all keys and values strores in Session
if (Session["myucod"] == null)----Null is compared with objects
Convert.ToInt32(Session["CountryId"].ToString()),
Difference between Convert.tostring() and .tostring()
.To string() give an error if null stored in variable
Convert.tostring convert null value to string
Access key value from Web.config---
int intRevLen = Convert.ToInt32(ConfigurationManager.AppSettings["LengthHomeContent"]);
Substring()
1.str.substring(starting index);
2.str.substring(starting index,length of string);
3.
strImage.Substring(i + 1);
strImage.LastIndexOf('\\');
strSub.Length;
strExtension.ToLower();
strEmails.Replace(",", ",\n");
.CS page ---->
public partial class MasterPage : System.Web.UI.MasterPage
In Dropdownlist add a value after binding
ListItem item1 = new ListItem("Please Select", "0");
ddlCountry.Items.Insert(0, item1);
File Upload----->
System.IO.Path.GetDirectoryName
System.IO.Path.GetFileName
System.IO.Path.GetExtension
System.IO.Path.GetFileNamewithoutExtension
string strImage = FileUpload1.PostedFile.FileName;---Give the full path of File
Thumbnail(Adjust the size of image)----
public static Bitmap CreateThumbnail(HttpPostedFile upFile, int width, int height)
{
Bitmap postedFile = new Bitmap(upFile.InputStream, true);
System.Drawing.Bitmap bmpOut;
ImageFormat Format = postedFile.RawFormat;
decimal Ratio;
int NewWidth;
int NewHeight;
//*** If the image is smaller than a thumbnail just return it
if (postedFile.Width < width && postedFile.Height < height)
{
return postedFile;
}
if ((postedFile.Width > postedFile.Height))
{
Ratio = Convert.ToDecimal(Convert.ToDecimal(width) / Convert.ToDecimal(postedFile.Width));
NewWidth = width;
decimal Temp = postedFile.Height * Ratio;
NewHeight = Convert.ToInt32(Temp);
}
else
{
Ratio = Convert.ToDecimal(Convert.ToDecimal(height) / Convert.ToDecimal(postedFile.Height));
NewHeight = height;
decimal Temp = postedFile.Width * Ratio;
NewWidth = Convert.ToInt32(Temp);
}
bmpOut = new Bitmap(NewWidth, NewHeight);
Graphics g = Graphics.FromImage(bmpOut);
g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.HighQualityBicubic;
g.FillRectangle(Brushes.White, 0, 0, NewWidth, NewHeight);
g.DrawImage(postedFile, 0, 0, NewWidth, NewHeight);
postedFile.Dispose();
return bmpOut;
}
#endregion
Call this Function
Bitmap bmp = CreateThumbnail(FileUpload1.PostedFile, 75, 75);
try
{
bmp.Save(strfinalGalleryImageLocationNew, System.Drawing.Imaging.ImageFormat.Jpeg);
}
finally
{
bmp.Dispose();
}
string strfinalGalleryImageLocationNew="http://localhost/SelectSociety/ImageThumbnail/"/;
Ques----A DataSet Contains 1000 rows But We have to show only 10 rows on front End.how We can Do it?
DataSet dswallpost = obj.Getwallpost(pobj);----Dataset Contain 1000 rows
DataTable dt = dswallpost.Tables[0].Clone();---We make the Clone of Table From Datset means we make the structure and constraint of dataset.
DataView dv = dswallpost.Tables[0].DefaultView;---Get a customised view of tablethat may include a filtered view
for (int i = 0; i < count; i++)---Suppose count=10
{
dt.ImportRow((dv[i].Row));--It will imports 10 rows from dataview dv
}
Customized Paging---
Pending
How to View string value upto limited character?
public string returnvalue1(string lblvalue)
{
if (lblvalue == "")
{
lblvalue = "Not Mentioned";
}
int intLength = lblvalue.Length;
if (intLength > 40)
{
lblvalue = lblvalue.Substring(0, 7) + "...";
return lblvalue;
}
else
{
return lblvalue;
}
Math.Ceiling----
using System;
string noofpages = Math.Ceiling((double)maxvalue / 10).ToString();
Math.Ceiling function gives 10/3=4
Use of DataGrid ItemDataBound Event (C#)
By Ranjit Thayyil
A DataGrid ItemDataBound event is raised each time an item (row) is data bound to the DataGrid. Once this event is raised, an argument of type DataGridItemEventArgs is passed to the event handling method and the data relevant to this event is available. This data is is no longer available once your application exits the event handling method. The eventhandler for ItemDataBound is OnItemDataBound.We can specify the method that should handle the event which takes the event source object (in our case the datagrid) and the DataGridItemEventArgs as arguments.
This can be explained easily with the help of an example. Consider the case where you want to calculate the total of a column in a datagrid.
First, let us create a data table and add rows to it with the following C# code:
protected DataTable dt = new DataTable("itemprice");
dt.Columns.Add("item",typeof (String));
dt.Columns.Add("price",typeof(float));
DataRow dr; dr = dt.NewRow();
dr["item"]="pen";
dr["price"]=3.5;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["item"]="camera";
dr["price"]=10.8;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["item"]="coffee maker";
dr["price"]=40.2;
dt.Rows.Add(dr);
dg.DataSource=dt.DefaultView;
dg.DataBind();< /FONT >
After binding to the datgrid, the display looks like this:
item
price
pen
3.5
camera
10.8
coffee maker
40.2
Since we are using ItemDataBound event to to total up the price column in the data grid, we have to specify on the web form (.aspx) page, the method that would handle the ItemDataBound event (Event wiring). This is acheived by mentioning within the datagrid control tag that on the occurence of the event
(OnItemDataBound), itemDataBound method should be called.
On the code behind file (.aspx.cs), we define the 'itemDataBound' method for handling the ItemDataBound Event.
float total = 0; protected void itemDataBound(object sender, DataGridItemEventArgs e) { if (e.Item.ItemType!=ListItemType.Header && e.Item.ItemType!=ListItemType.Footer) {
&nbs p; total += float.Parse(e.Item.Cells[1].Text);
e.Item.ForeColor = System.Drawing.Color.Blue;
}
else if (e.Item.ItemType == ListItemType.Footer)
{
e.Item.Cells[0].Text = "Total";
e.Item.Cells[1].Text = total.ToString();
}
}
The new datagrid looks like this when rendered on the page:
item
price
pen
3.5
camera
10.8
coffee maker
40.2
Total
54.5
How does this code work? The it
emDataBound method is called as each row(item) is bound to the datagrid.
Within the method, we check if the current row is a header or a footer row.To do this we use ListItemType enumeration.
For now, all we need to know is that ListItemType enumeration contains different types of items(header, footer,item, alternating item etc. ) that can be included in a list control (in our case, a datagrid). Therefore, if we want to check if the item bound is a datagrid footer, a simple if statement like this would do the trick:
if (e.Item.ItemType == ListItemType.Footer) { // your code }
In our method, we add the value in the price column to the total if the datagrid item is not a header or footer. Finally, when the datagrid footer is encountered, we display our total on the footer row of the datagrid.This is possible because ItemDataBound event is the last oppurtunity to access the data item before it is displayed in the browser.
Pick the Controls From DataList----
Label lblposterid = (Label)e.Item.FindControl("lblreplierid");
LinkButton lnkdel = (LinkButton)e.Item.FindControl("lnkdelete");
To Do some action on a control of Master page in any page
LinkButton LogOut = (LinkButton)(Master.FindControl("LinkButton1"));
LogOut.Visible = true;
In a DataList how u fire a Event on a control---
protected void imgThumbnails_Click(object sender, ImageClickEventArgs e)
{
if (Session["myucod"] != null)
{
ImageButton ibtnGalleryLogo = sender as ImageButton;
if (ibtnGalleryLogo != null)
{
Int32 intGalleryId = Convert.ToInt32(ibtnGalleryLogo.Attributes["GalleryId"]);
Int32 intImageId = Convert.ToInt32(ibtnGalleryLogo.Attributes["ImageId"]);
if (intGalleryId == 0)
{
Int32 GalleryId = Convert.ToInt32(Request.QueryString["GalleryId"]);
objproperties.NewUserGalleryId = GalleryId;
DataSet dsImages = new DataSet();
dsImages = objmethods.ExtractSelectedGalleryImages(objproperties);
imgMainImage.ImageUrl = strGetGalleryImagePath + dsImages.Tables[0].Rows[0]["ImageName"].ToString();
}
else
{
objproperties.NewUserGalleryImageId = intImageId;
DataSet dsImage = new DataSet();
dsImage = objmethods.ExtractSelectedMainImage(objproperties);
imgMainImage.ImageUrl = strGetImagePath + dsImage.Tables[0].Rows[0]["ImageName"].ToString();
}
}
}
else
{
Session["RedirectToPage"] = Request.Url.ToString();
Session["LoginMessage"] = "You must login to view fullsize images";
Response.Redirect("loginnew.aspx");
}
}
How We can Acces .cs variable in .aspx Page
ToolTip='<%# tooltip(Convert.ToBoolean(Eval("Guest"))) %>'
public String tooltip(Boolean a)
{
string strMessage;
if (a == true)
{
strMessage = "Booking Available";
return strMessage;
}
else
strMessage = "Booking Not Available";
return strMessage;
}
Focus on any a control in Page
ddlAllVenues.Focus();
Class Libraries
Procedure of building a Class Libraries---
1.Right click project name—Add--New project—Class Library—Name it
2.MaKe 4 Folders init
BaseClass,BuisnessLogic,interface,PropertyClass
In Base Class---Right click the Folder—Add--New class—connection.cs
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.SqlClient;
namespace SelectSocietyConnectionClass
{
public abstract class Connection
{
protected SqlConnection sqlcon = new SqlConnection();
---- sqlconnection class exist in System.Data.SqlClient
public Connection()
{
sqlcon.ConnectionString=Convert.ToString(ConfigurationSettings.AppSettings["cn"].ToString());
}
}
}
Abstract Methods and Classes
An abstract class is a class that is declared abstract—it may or may not include abstract methods. Abstract classes cannot be instantiated, but they can be subclassed.
An abstract method is a method that is declared without an implementation (without braces, and followed by a semicolon), like this:
abstract void moveTo(double deltaX, double deltaY);
If a class includes abstract methods, the class itself must be declared abstract, as in:
public abstract class GraphicObject {
// declare fields
// declare non-abstract methods
abstract void draw();
}
When an abstract class is subclassed, the subclass usually provides implementations for all of the abstract methods in its parent class. However, if it does not, the subclass must also be declared abstract.
Note: All of the methods in an interface (see the Interfaces section) are implicitly abstract, so the abstract modifier is not used with interface methods (it could be—it's just not necessary).
Abstract Classes versus Interfaces
Unlike interfaces, abstract classes can contain fields that are not static and final, and they can contain implemented methods. Such abstract classes are similar to interfaces, except that they provide a partial implementation, leaving it to subclasses to complete the implementation. If an abstract class contains only abstract method declarations, it should be declared as an interface instead.
Multiple interfaces can be implemented by classes anywhere in the class hierarchy, whether or not they are related to one another in any way. Think of Comparable or Cloneable, for example.
By comparison, abstract classes are most commonly subclassed to share pieces of implementation. A single abstract class is subclassed by similar classes that have a lot in common (the implemented parts of the abstract class), but also have some differences (the abstract methods).
An Abstract Class Example
In an object-oriented drawing application, you can draw circles, rectangles, lines, Bezier curves, and many other graphic objects. These objects all have certain states (for example: position, orientation, line color, fill color) and behaviors (for example: moveTo, rotate, resize, draw) in common. Some of these states and behaviors are the same for all graphic objects—for example: position, fill color, and moveTo. Others require different implementations—for example, resize or draw. All GraphicObjects must know how to draw or resize themselves; they just differ in how they do it. This is a perfect situation for an abstract superclass. You can take advantage of the similarities and declare all the graphic objects to inherit from the same abstract parent object—for example, GraphicObject, as shown in the following figure.
Classes Rectangle, Line, Bezier, and Circle inherit from GraphicObject
First, you declare an abstract class, GraphicObject, to provide member variables and methods that are wholly shared by all subclasses, such as the current position and the moveTo method. GraphicObject also declares abstract methods for methods, such as draw or resize, that need to be implemented by all subclasses but must be implemented in different ways. The GraphicObject class can look something like this:
abstract class GraphicObject {
int x, y;
...
void moveTo(int newX, int newY) {
...
}
abstract void draw();
abstract void resize();
}
Each non-abstract subclass of GraphicObject, such as Circle and Rectangle, must provide implementations for the draw and resize methods:
class Circle extends GraphicObject {
void draw() {
...
}
void resize() {
...
}
}
class Rectangle extends GraphicObject {
void draw() {
...
}
void resize() {
...
}
}
When an Abstract Class Implements an Interface
In the section on Interfaces , it was noted that a class that implements an interface must implement all of the interface's methods. It is possible, however, to define a class that does not implement all of the interface methods, provided that the class is declared to be abstract. For example,
abstract class X implements Y {
// implements all but one method of Y
}
class XX extends X {
// implements the remaining method in Y
}
In this case, class X must be abstract because it does not fully implement Y, but class XX does, in fact, implement Y.
Class Members
An abstract class may have static fields and static methods. You can use these static members with a class reference—for example, AbstractClass.staticMethod()—as you would with any other class.
Question
What is an abstract class, and when should it be used?
Answer
Abstract classes are classes that contain one or more abstract methods. An abstract method is a method that is declared, but contains no implementation. Abstract classes may not be instantiated, and require subclasses to provide implementations for the abstract methods. Let's look at an example of an abstract class, and an abstract method.
Suppose we were modeling the behavior of animals, by creating a class hierachy that started with a base class called Animal. Animals are capable of doing different things like flying, digging and walking, but there are some common operations as well like eating and sleeping. Some common operations are performed by all animals, but in a different way as well. When an operation is performed in a different way, it is a good candidate for an abstract method (forcing subclasses to provide a custom implementation). Let's look at a very primitive Animal base class, which defines an abstract method for making a sound (such as a dog barking, a cow mooing, or a pig oinking).
public abstract Animal
{
public void eat(Food food)
{
// do something with food....
}
public void sleep(int hours)
{
try
{
// 1000 milliseconds * 60 seconds * 60 minutes * hours
Thread.sleep ( 1000 * 60 * 60 * hours);
}
catch (InterruptedException ie) { /* ignore */ }
}
public abstract void makeNoise();
}
Note that the abstract keyword is used to denote both an abstract method, and an abstract class. Now, any animal that wants to be instantiated (like a dog or cow) must implement the makeNoise method - otherwise it is impossible to create an instance of that class. Let's look at a Dog and Cow subclass that extends the Animal class.
public Dog extends Animal
{
public void makeNoise() { System.out.println ("Bark! Bark!"); }
}
public Cow extends Animal
{
public void makeNoise() { System.out.println ("Moo! Moo!"); }
}
Now you may be wondering why not declare an abstract class as an interface, and have the Dog and Cow implement the interface. Sure you could - but you'd also need to implement the eat and sleep methods. By using abstract classes, you can inherit the implementation of other (non-abstract) methods. You can't do that with interfaces - an interface cannot provide any method implementations.
//////////////////////////
In interfaces------Right click the Folder—Add--New class—INewsMethods.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace SelectSociety.News
{
public interface INewsMethods
{
int insertNews(SelectSociety.News.PClassNews p);
DataSet extractAllNews();
DataSet extractTopOneNewsForHomePage();
}
}
In Propertyclass------Right click the Folder—Add--New class—PClassNews.cs
using System;
using System.Collections.Generic;
using System.Text;
namespace SelectSociety.News
{
public class PClassNews:SelectSocietyConnectionClass.Connection,SelectSociety.News.INewsFields
{
#region INewsFields Members
private DateTime _Date;
private String _Subject, _NewsBodyHTML, _NewsBodyText;
public DateTime Date
{
get
{
return _Date;
}
set
{
_Date = value;
}
}
public string Subject
{
get
{
return _Subject;
}
set
{
_Subject = value;
}
}
public string NewsBodyHTML
{
get
{
return _NewsBodyHTML;
}
set
{
_NewsBodyHTML = value;
}
}
public string NewsBodyText
{
get
{
return _NewsBodyText;
}
set
{
_NewsBodyText=value;
}
}
#endregion
}
}
/////////////////////
In BuisnessLogic------Right click the Folder—Add--New class—MainClassNews.cs
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.SqlClient;
using System.Collections.Generic;
using Microsoft.ApplicationBlocks.Data;
namespace SelectSociety.News
{
public class MainClassNews:SelectSocietyConnectionClass.Connection,SelectSociety.News.INewsMethods
{
#region INewsMethods Members
public int insertNews(PClassNews p)
{
SqlParameter[] param = new SqlParameter[5];
param[0] = new SqlParameter("@Date", p.Date);
param[1] = new SqlParameter("@Subject", p.Subject);
param[2] = new SqlParameter("@NewsBodyHTML", p.NewsBodyHTML);
param[3] = new SqlParameter("@NewsBodyText", p.NewsBodyText);
param[4] = new SqlParameter("@ReturnNumber", SqlDbType.Int);
param[4].Direction = ParameterDirection.ReturnValue;
SqlHelper.ExecuteNonQuery(sqlcon, CommandType.StoredProcedure, "insertnews", param);
return Convert.ToInt32(param[4].Value);
}
#endregion
#region INewsMethods Members
public DataSet extractAllNews()
{
return SqlHelper.ExecuteDataset(sqlcon, CommandType.StoredProcedure, "ExtractAllNews");
}
#endregion
#region INewsMethods Members
public DataSet extractTopOneNewsForHomePage()
{
return SqlHelper.ExecuteDataset(sqlcon, CommandType.StoredProcedure, "extractnewsForHomePage");
}
#endregion
}
}
Sqlhelper class include in Microsoft.ApplicationBlocks.Data.dll
Before use this Class you can copy this dll in the bin of your Application Folder
Web.Config
Be aware that when you set the customErrors mode to “Off” all visitors of your web site will see the detailed error message.
Value
Description
off
All callers receive complete exception information.
on
All callers receive filtered exception information.
remoteOnly
Local callers receive complete exception information; remote callers receive filtered exception information.
On Mac or Safari Some Time There is a Crash on saving Some Entry
EnableViewStateMac="true"
What is This?
How To Count the Original Size of uploaded image ?
System.Drawing.Image UploadedImage = System.Drawing.Image.FromStream(FileUpload1.PostedFile.InputStream);
//Determine width and height of uploaded image
float UploadedImageWidth = UploadedImage.PhysicalDimension.Width;
float UploadedImageHeight = UploadedImage.PhysicalDimension.Height;
2.3 How to find out what version of ASP.NET I am using on my machine?
C#
Response.Write(System.Environment.Version.ToString() );
2.6 What is a ViewState?
In classic ASP, when a form is submitted the form values are cleared. In some cases the form is submitted with huge information. In such cases if the server comes back with error, one has to re-enter correct information in the form. But submitting clears up all form values. This happens as the site does not maintain any state (ViewState).
In ASP .NET, when the form is submitted the form reappears in the browser with all form values. This is because ASP .NET maintains your ViewState. ViewState is a state management technique built in ASP.NET. Its purpose is to keep the state of controls during subsequent postbacks by the same user. The ViewState indicates the status of the page when submitted to the server. The status is defined through a hidden field placed on each page with a
2.15 How to catch the 404 error in my web application and provide more useful information?
In the global.asax Application_error Event write the following code
C#
Exception ex = Server.GetLastError().GetBaseException();
if (ex.GetType() == typeof(System.IO.FileNotFoundException))
{
Response.Redirect ("err404.aspx");
}
else
{
//your code
}
2.16 Is there a method similar to Response.Redirect that will send variables to the destination page other than using a query string or the post method?
Server.Transfer preserves the current page context, so that in the target page you can extract values and such. However, it can have side effects; because Server.Transfer doesnt' go through the browser, the browser doesn't update its history and if the user clicks Back, they go to the page previous to the source page.
Another way to pass values is to use something like a LinkButton. It posts back to the source page, where you can get the values you need, put them in Session, and then use Response.Redirect to transfer to the target page. (This does bounce off the browser.) In the target page you can read the Session values as required.
2.21 How To work with TimeSpan Class?
C#
DateTime adate = DateTime.Parse("06/24/2003");
DateTime bdate = DateTime.Parse("06/28/2003");
TimeSpan ts = new TimeSpan (bdate.Ticks - adate.Ticks);
Response.Write(ts.TotalDays.ToString () + "
");
Response.Write(ts.TotalHours.ToString() + ":" + ts.TotalMinutes.ToString() + ":" + ts.TotalSeconds.ToString() + ":" + ts.TotalMilliseconds.ToString() );
2.34 Is it possible to use a style sheet class directly on a control instead of using inline or page-level formatting ?
Every WebControl derived control has a CssClass property which allows you to set it's format to a style sheet.
Caching with ASP.NET
ASP.NET supports three types of caching for Web-based applications
Page Level Caching (called Output Caching)
Page Fragment Caching (called Partial-Page Output Caching)
Programmatic or Data Caching
Output Caching
Page level, or output caching, caches the HTML output of dynamic requests to ASP.NET Web pages. The way ASP.NET implements this (roughly) is through an Output Cache engine. Each time an incoming ASP.NET page request comes in, this engine checks to see if the page being requested has a cached output entry. If it does, this cached HTML is sent as a response; otherwise, the page is dynamically rendered, it's output is stored in the Output Cache engine.
Output caching is easy to implement. By simply using the @OuputCache page directive, ASP.NET Web pages can take advantage of this powerful technique. The syntax looks like this:
%@OutputCache Duration="60" VaryByParam="none" %
The Duration parameter specifies how long, in seconds, the HTML output of the Web page should be held in the cache. When the duration expires, the cache becomes invalid and, with the next visit, the cached content is flushed, the ASP.NET Web page's HTML dynamically generated, and the cache repopulated with this HTML. The VaryByParam parameter is used to indicate whether any GET (QueryString) or POST (via a form submit with method="POST") parameters should be used in varying what gets cached. In other words, multiple versions of a page can be cached if the output used to generate the page is different for different values passed in via either a GET or POST.
The VaryByParam is a useful setting that can be used to cache different "views" of a dynamic page whose content is generated by GET or POST values. For example, you may have an ASP.NET Web page that reads in a Part number from the QueryString and displays information about a particular widget whose part number matches the QueryString Part number. Imagine for a moment that Output Caching ignored the QueryString parameters altogether (which you can do by setting VaryByParam="none"). If the first user visited the page with QueryString /ProductInfo.aspx?PartNo=4, she would see information out widget #4. The HTML for this page would be cached. The next user now visits and wished to see information on widget #8, a la /ProductInfo.aspx?PartNo=8. If VaryByParam is set to VaryByParam="none", the Output Caching engine will assume that the requests to the two pages are synonymous, and return the cached HTML for widget #4 to the person wishing to see widget #8! To solve for this problem, you can specify that the Output Caching engine should vary its caches based on the PartNo parameter by either specifying it explicitly, like VaryByParam="PartNo", or by saying to vary on all GET/POST parameters, like: VaryByParam="*".
Partial-Page Output Caching
More often than not, it is impractical to cache entire pages. For example, you may have some content on your page that is fairly static, such as a listing of current inventory, but you may have other information, such as the user's shopping cart, or the current stock price of the company, that you wish to not be cached at all. Since Output Caching caches the HTML of the entire ASP.NET Web page, clearly Output Caching cannot be used for these scenarios: enter Partial-Page Output Caching.
Partial-Page Output Caching, or page fragment caching, allows specific regions of pages to be cached. ASP.NET provides a way to take advantage of this powerful technique, requiring that the part(s) of the page you wish to have cached appear in a User Control. One way to specify that the contents of a User Control should be cached is to supply an OutputCache directive at the top of the User Control. That's it! The content inside the User Control will now be cached for the specified period, while the ASP.NET Web page that contains the User Control will continue to serve dynamic content. (Note that for this you should not place an OutputCache directive in the ASP.NET Web page that contains the User Control - just inside of the User Control.)
Data Caching
Sometimes, more control over what gets cached is desired. ASP.NET provides this power and flexibility by providing a cache engine. Programmatic or data caching takes advantage of the .NET Runtime cache engine to store any data or object between responses. That is, you can store objects into a cache.
Realize that this data cache is kept in memory and "lives" as long as the host application does. In other words, when the ASP.NET application using data caching is restarted, the cache is destroyed and recreated.
To store a value in the cache, use syntax like this:
Cache["foo"] = bar; // C#
Cache("foo") = bar ' VB.NET
To retrieve a value, simply reverse the syntax like this:
bar = Cache["foo"]; // C#
bar = Cache("foo") ' VB.NET
Note that after you retrieve a cache value in the above manner you should first verify that the cache value is not null prior to doing something with the data. Since Data Caching uses an in-memory cache, there are times when cache elements may need to be evicted. That is, if there is not enough memory and you attempt to insert something new into the cache, something else has gotta go! The Data Cache engine does all of this scavenging for you behind the scenes, of course. However, don't forget that you should always check to ensure that the cache value is there before using it. This is fairly simply to do - just check to ensure that the value isn't null/Nothing. If it is, then you need to dynamically retrieve the object and restore it into the cache.
For example, if we were storing a string myString in the cache whose value was set from some method named SetStringToSomething(), and we wanted to read the value of myString from the cache, we'd want to:
Read the myString from the cache:
str = Cache("myString")
Ensure that str wasn't null/Nothing. If it was, we'd want to get the value of str from SetStringToSomething(), and then put it in the cache, like so:
'Try to read the cache entry MyString into str
str = Cache("myString")
'Check if str is Nothing
If str is Nothing then
'If it is, populate str from SetStringToSomething()
str = SetStringToSomething()
'Now insert str into the cache entry myString
Cache("myString") = str
End If
Besides using the dictionary-like key/value assignment, as shown in the example above, you can also use the Insert or Add method to add items to the cache. Both of these methods are overloaded to accommodate a variety of situations. The Add and the Insert methods operate exactly the same except the Add method returns a reference to the object being inserted to the cache.
For example to simply add an instance of the object bar to the cache named foo, use syntax like this:
Cache.Insert("foo", bar); // C#
Cache.Insert("foo", bar) ' VB.NET
(Note that this is synonymous to using the Cache("foo") = bar syntax we looked at earlier.)
Note that with inserting items into the Data Cache using the Cache(key) = value method or the Cache.Insert(key, value) we have no control over when (if ever) the items are evicted from the cache. However, there are times when we'd like to have control over when items leave the cache. For example, perhaps we want to have an inserted item in the cache to only live for n seconds, as with Output Caching. Or perhaps we'd like to have it exit the cache n seconds after it's last accessed. With Data Caching, you can optionally specify when the cache should have a member evicted.
Additionally, you can have an item evicted from the cache when a file changes. Such an eviction dependency is called a file dependency, and has many real-world applications, especially when working with XML files. For example, if you want to pull data out of an XML file, but you don't want to constantly go to disk to read the data, you can tell the ASP.NET caching engine to expire the cached XML file whenever the XML file on disk is changed. To do this, use the following syntax:
Cache.Insert("foo", bar, new CacheDependancy(Server.MapPath("temp.xml")))
By using this syntax, the cache engine takes care of removing the object bar from the cache when temp.xml file is changed. Very cool! There are also means to have the inserted cache value expire based on an interval, or at an absolute time, as discussed before.
A Cached XML File Example Hopefully by now you'll agree that one of the most interesting and useful uses of the Cache.Insert method involves using the version of the method that takes advantage of the CacheDependancy parameter. By using this parameter, developers can create web pages that contain "semi-static" data. In other words, the rendering of the pages is based on configuration-like data which can be stored in an XML file (or anywhere really, I just like to use XML for this type of data). But I digress. The point is, why go back to disk or, worse yet, a SQL Server just to retrieve data that only changes periodically when I can have it done automatically
Conclusion
The .NET caching services will prove to be one of the most powerful ways to squeeze performance out of new Web-based applications. Whether using Output Caching, Fragment Caching, or the Cache object directly, your web applications will see dramatic improvements in throughput and scalability through the technique of caching expensive dynamic data computations.
Sending email with an embedded image
Dim msg As New MailMessage("sender mail address", "recipient mail address")
msg.Subject = "This is my first email through program"
msg.IsBodyHtml = True
Dim View As AlternateView
Dim resource As LinkedResource
Dim client As SmtpClient
Dim msgText As New StringBuilder
msgText.Append("Hi there,")
msgText.Append("Welcome to the new world programming.")
msgText.Append("Thanks")
msgText.Append("With regards,")
msgText.Append("")
'create an alternate view for your mail
View = AlternateView.CreateAlternateViewFromString(msgText.ToString(),
Nothing, "text/html")
'link the resource to embed
resource = New LinkedResource((Server.MapPath("Images\007jvr.gif")))
'name the resource
resource.ContentId = "Image1"
'add the resource to the alternate view
View.LinkedResources.Add(resource)
'add the view to the message
msg.AlternateViews.Add(View)
client = New SmtpClient()
client.Host = "smtp.gmail.com" 'specify your smtp server name/ip here
'client.EnableSsl = True
'enable this if your smtp server needs SSL to communicate
client.Credentials = New Net.NetworkCredential("username", "pwd")
client.Send(msg)
Sql Server
Is there a command to list all the tables and their associated filegroups?
There is no built-in command just to list all the tables along with their file groups. sp_help is the closest you can get to. The following query lists all the tables and the filegroups those tables belong to:
SELECT OBJECT_NAME(id) [Table Name], FILEGROUP_NAME(groupid) AS [Filegroup Name]
FROM sysindexes
WHERE indid IN (0, 1) AND
OBJECTPROPERTY(id, 'IsMSShipped') = 0
How to change or alter a user defined data type?
Unfortunately, there is no easy way to alter or modify a user defined data type. To modify a user defined data type, follow these steps:
Alter all the tables, that are referencing this user defined data type (UDT), using ALTER TABLE...ALTER COLUMN command and change the data type of the referencing column to an equivalent (or the intended) base data type.
Drop the user defined data type using sp_droptype.
Recreate the user defined datatype with the required changes using sp_addtype.
Again, use the ALTER TABLE...ALTER COLUMN syntax to change the column's datatype to the user defined data type
I forgot/lost the sa password. What to do?
Forgot or lost your sa password? Don't worry, there is a way out :)
Login to the SQL Server computer as the Administrator of that computer. Open Query Analyzer and connect to SQL Server using Windows NT authentication. Run sp_password as show below to reset the sa password:
sp_password @new = 'will_never_forget_again', @loginame = 'sa'
I have only the .mdf file backup and no SQL Server database backups. Can I get my database back into SQL Server?
Yes. The system stored procedures sp_attach_db and sp_attach_single_file_db allow you to attach .mdf files to SQL Server. In the absence of the log file (.ldf), SQL Server creates a new log file.
How to add a new column at a specific position (say at the beginning of the table or after the second column) using ALTER TABLE command?
ALTER TABLE always adds new columns at the end of the table and will not let you add new columns at a specific position. If you must add a column at a specific position, use Enterprise Manager. In Enterprise Manager, right click on the table, select 'Design Table'. Right click on the desired location and select 'Insert Column'. Mind you, Enterprise Manager drops and recreates the table to add a column at a specific location. So it might take a long time if your table is huge.
How to restore single tables from backup in SQL Server 7.0/2000, like we did in SQL Server 6.5?
Support for restoring individual tables from backup is discontinued in SQL Server 7.0/2000. If you need this functionality, here are some roundabout ways:
Restore the complete database onto a new database with a different name. Copy the required tables (using T-SQL or DTS) into the actual database and drop the new database that you just created
You could place the required tables onto specific filegroups and implement filegroup backup and restore. But filegroup backup will not backup the transaction log. So there is a chance of losing some data when you restore the filegroups. See SQL Server Books Online for more information
How to reset or reseed the IDENTITY column?
See DBCC CHECKIDENT in SQL Server Books Online.
A quick and dirty way to reset the IDENTITY column would be to run TRUNCATE TABLE command on that table. TRUNCATE TABLE will delete all the rows from the table and reset the IDENTITY column. However, you will not be able to run TRUNCATE TABLE on a table referenced by foreign keys.
Re: Difference between char and varchar
Performance wise it doesn't matter which you use (according to the docs)
some db's have performance differences between char, varchar and text -
postgresql doesn't.
The choice would mainly depend on what data will be stored and what
considerations for disk usage you may have. You really only need to use char
or varchar if you want to limit the amount of data stored, although it is
considered better practice to use data types that closely match the data to
be stored. Meaning if you want to store 10-20 characters use a char(20) not
a text field even if it makes no difference in the end.
char and varchar can technically store up to 1GB of text but best/common
practice is to only use char or varchar for up to about 200 characters and
text for anything above that.
eg
A char(100) will always store 100 characters even if you only enter 5, the
remaining 95 chars will be padded with spaces.
Storing 5 characters in a varchar(100) will save 5 characters.
If this is the main table and you have say 10 char fields and expect 200,000
records it will add up to a lot of extra disk usage.
Of course there is also some overhead to identify/find the data in the disk
file etc.
So if you want to allow up to 50 characters and you know that maybe 20%
could be as little as 5 characters with an average around 30 then char(50)
would use more disk space than a varchar(50). If you don't want to restrict
the length entered and it may possibly be lengthier then you may want to use
a text field instead.
Difference Between NULL and Blank in SQL
Yes, a very big difference! Be carefully if you have NULL valued fields. If
you do a compare and one or both are NULL, then the result is always NULL,
never true or false. Even comparing two fields which are both NULL will
give NULL as result, not true! Or if you have something like "select
sum(field) from ..." and one or more are NULL, then the result will be
NULL. Use always "if field is NULL ..." for NULL checking and for safety
maybe something like "select sum( IsNull(field,0) ) from ...". Check the
function ISNULL() in the manual.
The result of the comparison "NULL = NULL" also results in UNKNOWN.
[color=blue]
> Or if you have something like "select
> sum(field) from ..." and one or more are NULL, then the result will be
> NULL.[/color]
NULL values are excluded from aggregates. If one or more NULL values are
encountered, SQL Server will issue a warning stating that these rows are
disregarded. The only exception is the aggregate COUNT(*)
[color=blue]
> Use always "if field is NULL ..." for NULL checking and for safety
> maybe something like "select sum( IsNull(field,0) ) from ...".[/color]
This only good advice if you want a NULL row to be treated as 0 in an
aggregation (for example the calculation of an average).
[color=blue]
> Check the function ISNULL() in the manual.
>
> bye,
> Helmut[/color]
In addition to Helmut's warnings, note that NULLs are promoted in
expressions. So if you write SELECT A + B AS sum_of_A_and_B and either A
or B is NULL, then sum_of_A_and_B will be NULL.
///////////////////////////////////////////////////////////////////////////////
Sql Server Important Concepts
/////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////
ALter Table Command in Sql Server
//////////////////////////////////////////////////////
Alter table table alter column columnname(new datatype)
///////////////////////////////////////////////
Use of Isnull in Sql server
///////////////////////////////////////////
SELECT
ISnull(RemedyPlan,'') AS RemedyPlan ,
ISnull(Convert(VARCHAR(12),RemedyImplementationDate,107),Getdate()) AS RemedyDate ,
ISnull(Justification,'') AS Justification ,
ISnull(ReasonForNonCompliance,'') AS ReasonForNonCompliance
FROM
tblRemedy
///////////////////////////////////////////////////////
Use of @@RowCount in Sql server
///////////////////////////////////////////////////////////
Select * From tblSurveyApprovalLevel
declare @count int
set @count=@@RowCount
select @count
/////////////////////////////////////////////////////////////
How to Give Comment in Sql server
/////////////////////////////////////////////////////////////
/*
Author : MANPREET sINGH
Date : 24 Nov 2004
PROCEDURE dbo. : PROCEDURE dbo. to check n send mails to survey or survey approval defaulters
*/
//////////////////////////////////////////////////////////////////////
How to Declare variable in Sql server
////////////////////////////////////////////////////////////
CREATE Procedure dbo.USPSentSurveyMails
AS
Declare @MailDate DateTime
Declare @StartDate DateTime
Declare @DaysBefore INT
Declare @QuarterID INT
////////////////////////////////////////////////////////////
How to set value to a variable in Sql server
///////////////////////////////////////////////////////////
SET @QuarterID=0
////////////////////////////////////////////////////////////
How to do mathematical operation
////////////////////////////////////////////////////////////
Select
@MailDate = @StartDate + DaysAfter + DaysBefore +1
from
tblSurveyRollout
///////////////////////////////////////////////////////
Table type variable and its use
//////////////////////////////////////////////////////
Declare @Mails Table //// @Mails ia table type variable
(
ID INT Identity,
Days INT,
Priority INT,
Subject Varchar(100),
Matter Varchar(500),
ApproverLevelId INT,
Recipients Varchar(7000)
)
INSERT INTO @Mails(Days,Priority,Subject,Matter,ApproverLevelId,Recipients)
Select NumberOfDays,Priority,Subject,Matter,ApproverLevelId,Recipients from tblSurveyMailer
////////////////////////////////////////////////////////////
While loop in Sql server
////////////////////////////////////////////////////////////
-----------1
WHILE (@LoopCount <= @Counter)
BEGIN
SELECT @SurveyResultId = SurveyResultId FROM #Temp WHERE TempId = @LoopCount
UPDATE #Temp SET UserId =(SELECT UserId FROM tblSurveyResult WHERE SurveyResultId = @SurveyResultId)
WHERE TempId = @LoopCount
SET @LoopCount = @LoopCount + 1
END
-------------2
Declare @Loop INT
Declare @Count INT
Set @Loop=0
INSERT INTO @Mails(Days,Priority,Subject,Matter,ApproverLevelId,Recipients)
Select NumberOfDays,Priority,Subject,Matter,ApproverLevelId,Recipients from tblSurveyMailer
SET @Count = @@Rowcount
WHILE @Loop<>@Count
Begin
SET @Loop =@Loop +1
Declare @Days INT
Declare @ApproverLevelId INT
Select @Days = Days,@ApproverLevelId=ApproverLevelId,@Subject=Subject,@Matter=Matter,@Recipients=Recipients From @Mails Where Id = @Loop
Select 'Final Mail Date' , Convert(Varchar,@MailDate - @Days,107)
IF Convert(Varchar,@MailDate - @Days,107)=Convert(Varchar,Getdate(),107)
BEGIN
IF @ApproverLevelId=1
BEGIN
Insert Into @Email(Email)
Select Email From tblUser
Where UserId In (Select UserId FRom UDFGetSurveyDefaulters())
SET @MCount = @@Rowcount
Set @MLoop=0
While @MLoop<>@MCount
BEGIN
SET @MLoop = @MLoop + 1
SELECT @TO = EMail From @Email Where ID=@MLoop
EXEC sp_SMTPemail 'rupa.s.kolnurkar@gsk.com',@To,@Subject, @Matter
END
END
ELSE
BEGIN
Insert Into @Email(Email)
Select Email From tblUser
Where UserId In (
select UserId FRom UDFGetSurveyApprovalDefaulters())
SET @MCount = @@Rowcount
Set @MLoop=0
While @MLoop<>@MCount
BEGIN
SET @MLoop = @MLoop + 1
SELECT @TO = EMail From @Email Where ID=@MLoop
EXEC sp_SMTPemail 'rupa.s.kolnurkar@gsk.com',@To,@Subject, @Matter
END
END
END
End
////////////////////////////////////////////////////////////////////////////////////
Pick value from one table and insert into other table
////////////////////////////////////////////////////////////////////////////////
Insert Into @Email(Email)
Select Email From tblUser Where UserId In (Select UserId FRom UDFGetSurveyDefaulters())
////////////////////////////////////////////////////////////
send mail through Sqlserver
////////////////////////////////////////////////////////////
Syntax--EXEC sp_SMTPemail 'from','To','Subject', 'Matter'
EXEC sp_SMTPemail 'rupa.s.kolnurkar@gsk.com',@To,@Subject, @Matter
////////////////////////////////////////////////////////////
TEMPORARY TABLE IN sQL SERVER
////////////////////////////////////////////////////////////
EVERY TEMPORARY TABLE IF NOT DELETE AFTER USE STORE IN THE TEMPDB NAMED DATABASE
IF WE WANT TO DELETE ALL THE TEMPORAY TABLE FROM THESE Database Following statements write
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE type = 'U' and NAME = @TempTableName)
BEGIN
EXEC('DROP TABLE tempdb..' + @TempTableName)
END
CREATE TABLE ##TempTable
(
LocProcId INT ,
LocId INT ,
ProcId INT ,
ProcessDesc VARCHAR(500),
Title VARCHAR(500),
ParentId INT ,
IsDelete INT
)
INSERT ##TempTable EXEC USPProcessHierarchy @LocProcId
-------After using temporay table it should be dropped by following statement
DROP table ##TempTable
//////////////////////////////////////
USPProcessHierarchy
//////////////////////////////////////////////
CREATE PROCEDURE dbo. USPProcessHierarchy
(
@LOCPROCID INT
)
AS
SET NOCOUNT ON
SET CURSOR_CLOSE_ON_COMMIT ON
BEGIN TRAN
DECLARE @ProcID INT
DECLARE @LocID INT
SET @PROCID=0
SET @LOCID=0
--Fetching Process Id and Location Id for Filtering
SELECT @ProcID=ProcID,@LocID=LocID From tblMapLocationProcess
Where LocProcId = @LocProcId
DECLARE @CUR_Name VARCHAR(40)
DECLARE @TableName VARCHAR(40)
--Getting Random Cursor & Table Name String
EXEC USPGenerateRandomString @CUR_Name OutPut
EXEC USPGenerateRandomString @TableName OutPut
--Calling Main PROCEDURE dbo.
EXEC uspProcessTree @ProcID,@CUR_Name,@TableName
DECLARE @Query NVARCHAR(1500)
SET @Query = N'Select MLP.LocProcId, MLP.LocId, MLP.ProcId, P.ProcessDesc, P.Title, P.ParentId, MLP.IsDelete From ##' + @TableName +
N' P,tblMapLocationProcess MLP Where P.ProcID = MLP.ProcID AND IsDelete=0
And MLP.LocID = ' + CONVERT(VARCHAR, @LocID)
EXECUTE sp_executesql @Query
--Droping Temp Table After Select
DECLARE @Query2 NVARCHAR(200)
SET @Query2 = N'Drop Table ##' + @TableName
EXECUTE sp_executesql @Query2
COMMIT TRAN
GO
fUNCTION RETURN DAY OF WEEK
Create FUNCTION DayOfWeek
(
@Date as Datetime
)
Returns varchar(30)
AS
BEGIN
Declare @Return varchar(30)
select @Return=case datepart(dw,@Date)
When 7 Then 'Saturday'
When 1 Then 'Sunday'
When 2 Then 'Monday'
When 3 Then 'Tuesday'
When 4 Then 'Wednesday'
When 5 Then 'Thrusday'
When 6 Then 'Friday'
End
return @Return
END
GO
////////////////////////////////////////////////////////////
CASE AND CAST STATEMENT
////////////////////////////////////////////////////////////
CAST( CASE
When MLU.UserID<> 0 Then
1
Else
0
End
AS BIT) AS [User],
CAST(COLUMN NAME AS DATATYPE)
-------2
CASE SR.Result
WHEN 1 THEN ISNULL(R.ReasonForNonCompliance, '')
WHEN 2 THEN ISNULL(SR.Justification, '')
WHEN 3 THEN IsNull(SR.NoProofJustification,'')
ELSE ''
END AS ReasonForNonCompliance,
---------3
CASE
WHEN(SR.Result = 3 AND IsNull(SR.NoProofJustification,'') = '') THEN 'Available'
WHEN(SR.Result = 3 AND IsNull(SR.NoProofJustification,'') <> '') THEN 'Not Available'
ELSE ''
END AS ProofOfCompliance
////////////////////////////////////////////////////////////
INNER JOIN AND OUTER JOIN IN sQL SERVER
////////////////////////////////////////////////////////////
FROM
TABLE1 T1
INNER JOIN TABLE2 T2
ON T1.COLNAME = T2.COLNAME
INNER JOIN TABLE T3
ON T1.COLNAME = T3.COLNAME,
tblRemedy R
////////////////////////////////////////////////////////////
PICK THE CURRENT DATE
////////////////////////////////////////////////////////////
getdate()
////////////////////////////////////////////////////////////mmmmmmmmmmmmmmmmmmmmmmmmmm
How to ACCESS A TABLE INFORMATION RESTORED IN OTHER DATABASE
////////////////////////////////////////////////////////////mmmmmmmmmmmmmmmmmmmmmmmmmmmm
SELECT * FROM DATABASENAME..OBJECTNAME
////////////////////////////////////////////////////////////mmmmmmm
use cancatination operation in sqlserver
////////////////////////////////////////////////////////////mmmmmmmm
select
U.Fname + ' ' + U.LName+', '+U.Designation as PrimResp
from tablename
////////////////////////////////////////////////////////////mmmmmmm
IF STATEMENT
////////////////////////////////////////////////////////////mmmmmmmm
IF ((SELECT IsSubmit FROM tblSurveyResult WHERE SurveyResultId=@SurveyResultId)=0)
BEGIN
END
ELSE
BEGIN
END
//////////////////////////////////////////////////////////////////////////////////////////////////////
EXECUTE QUERY IN STORED PROCEDURE!OR EXEC() FUNCTION
///////////////////////////////////////////////////////////////////////////////////////////////////////////////
SET @QueryApproved = 'UPDATE tblCentralPlans
SET IsDelete = 0
WHERE CentralPlanId IN ('+@CentralPlansApproved+')'
EXEC (@QueryApproved)
////////////////////////////////////////////////////////////
USING CURSOUR IN sQL SERVER
////////////////////////////////////////////////////////////
UDFAdmin_New_LocDrillDown in Gsk_latest(Database)
////////////////////////////////////////////////////////////////////////////////
Column with user Defined datatype
//////////////////////////////////////////////////////////////////////////////////
CREATE TABLE [dbo].[Profileuser](
[age] AS (datepart(year,getdate())-datepart(year,[birthday])),
use cancatination operation in sqlserver
////////////////////////////////////////////////////////////mmmmmmmm
////////////////////////////////////////////////////////////mmmmmmmmmmmmmmmmmmm
pick the time only from column of datatype datetime
////////////////////////////////////////////////////////////mmmmmmmmmmmmmmmmmmm
select convert(varchar,Replydate,108) as replydate from ForumReply
//////////////////////////////////////////
Best Example of Cursor
//////////////////////////////////
/*set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[ForumPost_Extract]
as*/
--declare @CountReply int
--set @CountReply=select count(*) from ForumReply where ForumPostId in
--Insert into ForumPost values(@ForumTypeId,@ForumTopic,@ForumBody,@PosterId,@PostDate)
--select count(*) from ForumReply where ForumPostid in(Select Distinct ForumPostid from forumreply)
Declare @replies int
Declare @forumpostid int
Declare @ForumTopic varchar(50)
Declare @postdate varchar(100)
Declare @createdby varchar(100)
Declare @Image varchar(100)
Declare @repcount int
declare @Table table
(
forumpostid int,
ForumTopic varchar(50),
postdate varchar(100),
createdby varchar(100),
[Image] varchar(100),
replies int
)
Declare cur_survey cursor Fast_Forward for
select forumpostid, ForumTopic,username as createdby,convert(varchar,Postdate,108)+' | '+ convert(varchar,Postdate,107)
as postdate,[Image],0 from Forumpost
inner join users on ForumPost.PosterId=users.userid
order by postdate desc
OPEN cur_survey
FETCH NEXT FROM cur_survey INTO @forumpostid,@ForumTopic,@createdby,@postdate,@Image,@replies
WHILE @@FETCH_STATUS = 0
BEGIN
set @repcount=0
select @repcount = count(*) from forumreply where forumpostid=@forumpostid
insert into @Table values(@forumpostid,@ForumTopic,@postdate,@createdby,@Image,@repcount)
FETCH NEXT FROM cur_survey INTO @forumpostid,@ForumTopic,@postdate,@createdby,@Image,@replies
end
CLOSE cur_survey
DEALLOCATE cur_survey
select * from @Table
mmmmmmmmmmmmmmmmmmmmmmm
substring in Sqlserver
mmmmmmmmmmmmmmmmmmmmmmm
Select substring('Manpreet Singh Bhatia',0,10)
Ans-Manpreet
/////////////////////////////////////////////////////////////////////////////
Division operation in Sqlserver
//////////////////////////////////////////////////////////////////////////////////
Floor(DATEDIFF(day, '03/12/2007',GetDate())/365.25)
////////////////////////////////////////////////////////////mmmmmmmmmmm
Unique Identifier Datatype in SqlServer2005
////////////////////////////////////////////////////////////mmmmmmmmmmm
First off, for those of you not familiar with the uniqueidentifier datatype, here's the lowdown:
Uniqueidentifiers are also referred to as GUIDs. (Globally Unique IDentifier)
That is, the API call that returns a GUID is guaranteed to always return a unique value across space and time. I don't know the full mechanics of creating a GUID, but I seem to remember that it has something to do with the MAC address on your network card and the system time.
To get a GUID in SQL Server (7.0+), you call the NEWID() function.
The uniqueidentifier data type in SQL Server is stored natively as a 16-byte binary value.
This is an example of a formatted GUID: B85E62C3-DC56-40C0-852A-49F759AC68FB.
////////////////////////////////////////////////////////////mmmmmmmmmmm
Identity Column in SqlServer
////////////////////////////////////////////////////////////mmmmmmmmmmm
Creating an Identity Column
In it's simplest form an identity column creates a numeric sequence for you. You can specify a column as an identity in the CREATE TABLE statement:
CREATE TABLE dbo.Yaks ( YakID smallint identity(7,2), YakName char(20) )The identity clause specifies that the column YakID
is going to be an identity column. The first record added will automatically be assigned a value of 7 (the seed) and each
subsequent record will be assigned a value 2 higher (the increment) than the previous inserted row. Most identity columns
I see are specified as IDENTITY(1,1) but I used IDENTITY(7,2) so the difference would be clear. If you don't specify the
identity and seed they both default to 1. Identity columns can be int, bigint, smallint, tinyint, or decimal or numeric
with a scale of 0 (i.e. no places to the right of the decimal).
////////////////////////////////////////////////////////////
Finding the Identity Value that was Inserted
////////////////////////////////////////////////////////////
SELECT SCOPE_IDENTITY() as NewRec
or
SELECT @@identity
///////////////////////////////////////////////////////////
How to Insert Values into an Identity Column in SQL Server
////////////////////////////////////////////////////////////
SET IDENTITY_INSERT IdentityTable ON
INSERT IdentityTable(TheIdentity, TheValue)
VALUES (3, 'First Row')
SET IDENTITY_INSERT IdentityTable OFF
////////////////////////////////////////////////////////////mmmmmm
Identity columns are bad because...
////////////////////////////////////////////////////////////mmmmmm
They're not standard SQL. Most products have it but there's no consistent implementation.
They can't be updated. This violates the relational data model (not fatal, but not good either). Duplicates can be accidentally inserted (fatal).
They only create numeric values. GUID/NewID() are also numeric only, and are hard to read.
Numeric values are not meaningful in many tables, and adding them complicates relationships between other tables.
Table variables are only allowed in SQL Server 2000+, with compatibility level set to 80 or higher.
You cannot use a table variable in either of the following situations:
INSERT @table EXEC sp_someProcedure
SELECT * INTO @table FROM someTable
You cannot truncate a table variable.
Table variables cannot be altered after they have been declared.
You cannot explicitly add an index to a table variable, however you can create a system index through a PRIMARY KEY CONSTRAINT, and you can add as many indexes via UNIQUE CONSTRAINTs as you like. What the optimizer does with them is another story.
DECLARE @myTable TABLE
(
CPK1 int,
CPK2 int,
CONSTRAINT myPK PRIMARY KEY (CPK1, CPK2)
)
-- yields:
Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'CONSTRAINT'.
-- yet the following works:
DECLARE @myTable TABLE
(
CPK1 int,
CPK2 int,
PRIMARY KEY (CPK1, CPK2)
)
You cannot use a user-defined function (UDF) in a CHECK CONSTRAINT, computed column, or DEFAULT CONSTRAINT.
You cannot use a user-defined type (UDT) in a column definition.
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////m
Limitation of Temprory variable as compared to temporary table...
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////m
mmmmmmmmmmTemporary table mmmmmmmmmmmmm
Create table #table
(
ID int identity(2,2),
ename varchar(50)
)
insert into #table values('manpreet')
insert into #table values('Amandeep')
insert into #table values('Gagan')
mmmmmmmmmmTemporary variable mmmmmmmmmmmmm
Create @table table
(
ID int identity(2,2),
ename varchar(50)
)
insert into @table values('manpreet')
insert into @table values('Amandeep')
insert into @table values('Gagan')
Unlike a #temp table, you cannot drop a table variable when it is no longer necessary—you just need to let it go out of scope.
You cannot generate a table variable's column list dynamically, e.g. you can't do this:
SELECT * INTO @tableVariable
-- yields:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@tableVariable'.
You also can't build the table variable inside dynamic SQL, and expect to use it outside that scope, e.g.:
DECLARE @colList VARCHAR(8000), @sql VARCHAR(8000)
SET @colList = 'a INT,b INT,c INT'
SET @sql = 'DECLARE @foo TABLE('+@colList+')'
EXEC(@sql)
INSERT @foo SELECT 1,2,3
-- this last line fails:
Server: Msg 137, Level 15, State 2, Line 5
Must declare the variable '@foo'.
This is because the rest of the script knows nothing about the temporary objects created within the dynamic SQL. Like other local variables, table variables declared inside of a dynamic SQL block (EXEC or sp_executeSQL) cannot be referenced from outside, and vice-versa. So you would have to write the whole set of statements to create and operate on the table variable, and perform it with a single call to EXEC or sp_executeSQL.
The system will not generate automatic statistics on table variables. Likewise, you cannot manually create statistics (statistics are used to help the optimizer pick the best possible query plan).
An INSERT into a table variable will not take advantage of parallelism.
A table variable will always have a cardinality of 1, because the table doesn't exist at compile time.
Table variables must be referenced by an alias, except in the FROM clause. Consider the following two scripts:
CREATE TABLE #foo(id INT)
DECLARE @foo TABLE(id INT)
INSERT #foo VALUES(1)
INSERT #foo VALUES(2)
INSERT #foo VALUES(3)
INSERT @foo SELECT * FROM #foo
SELECT id
FROM @foo
INNER JOIN #foo
ON @foo.id = #foo.id
DROP TABLE #foo
The above fails with the following error:
Server: Msg 137, Level 15, State 2, Line 11
Must declare the variable '@foo'.
This query, on the other hand, works fine:
SELECT id
FROM @foo f
INNER JOIN #foo
ON f.id = #foo.id
Table variables are not visible to the calling procedure in the case of nested procs. The following is legal with #temp tables:
CREATE PROCEDURE faq_outer
AS
BEGIN
CREATE TABLE #outer
(
letter CHAR(1)
)
EXEC faq_inner
SELECT letter FROM #outer
DROP TABLE #outer
END
GO
CREATE PROCEDURE faq_inner
AS
BEGIN
INSERT #outer VALUES('a')
END
GO
EXEC faq_outer
Results:
letter
------
a
(1 row(s) affected)
However, you cannot do this with table variables. The parser will find the error before you can even create it:
CREATE PROCEDURE faq_outer
AS
BEGIN
DECLARE @outer TABLE
(
letter CHAR(1)
)
EXEC faq_inner
SELECT letter FROM @outer
END
GO
CREATE PROCEDURE faq_inner
AS
BEGIN
INSERT @outer VALUES('a')
END
GO
Results:
Server: Msg 137, Level 15, State 2, Procedure faq_inner, Line 4
Must declare the variable '@outer'.
For more information about sharing data between stored procedures, please see this article by Erland Sommarskog.
--------------------------------------------------------------------------------
Conclusion
Like many other areas of technology, there is no "right" answer here. For data that is not meant to persist beyond the scope of the procedure, you are typically choosing between #temp tables and table variables. Your ultimate decision should depend on performance and reasonable load testing. As your data size gets larger, and/or the repeated use of the temporary data increases, you will find that the use of #temp tables makes more sense. Depending on your environment, that threshold could be anywhere — however you will obviously need to use #temp tables if any of the above limitations represents a significant roadblock.
Session Management in SqlServer Mode
C:\Program Files\Microsoft Visual Studio 8\VC>aspnet_regsql -S SERVERNAME -U USERNAME –P PASSWORD -ssadd -sstype c -d talentState
For developers using SQL server 2005
Dear team members,
There are number of SQL server clauses that we have not even heard of, which results in unnecessary complexity in the stored procedures (SQL statements) written by us. Today I came across a very useful SQL clause that I would like to share with all of you.
OUTPUT Clause
Have you ever come across a situation where you need to delete data from one table and keep the deleted records in another table? Basically what I mean to say here is, many times, we require the changes in our physical data to be tracked some where. I.e. I want to keep track of affected records. I can store these affected records in either
1.A table data type variable: so that I can do some more manipulations on that.
2.A physical table: for maintaining log information
SQL Server 2005 provides a new clause known as "Output". Output operates on similar lines as that of a trigger i.e. it provides you the details about affected records in logical tables named deleted and inserted.
As mentioned, Output provides you access to inserted and deleted logical tables. The data that has been inserted into the table OR the data after update statement has been executed, is available in inserted logical table. Similarly, data deleted using delete statement OR as a result of updation(i.e. data that was there prior to updation) is available in deleted logical table
Let’s take one simple example now (simple because I feel that now you have a fair idea of what output is all about). We wish to update the name of an employee and we want to get both the old and the new value of the employee name:
UPDATE Employees
SET [Name] = 'Sanjeev'
OUTPUT inserted.EmployeeID, deleted.[name] as OldName, inserted.[Name] as NewName
WHERE [Name] = 'Gourav'
Following is the output of the above mentioned query:
EmployeeID
OldName
NewName
1
8
Gourav
Sanjeev
You can see that we have the name that was updated, obtained from deleted logical table in column named "OldName" and the new value for the name in "NewName" from inserted logical table.
One last thing, which I discussed in the beginning of this article: storing the affected records in a table data type variable. Let’s take the example of updating the department IDs of employees:
-- Declare a temporary table to hold the updated records
DECLARE @TempTable Table
(
EmployeeID INT,
[Name] VARCHAR(50),
DepartmentID INT,
ManagerID INT
)
--This is the main usage of Output Clause
-- Update the table and store the affected records in @tempTable via output
UPDATE Employees
SET DepartmentID = 2
OUTPUT inserted.EmployeeID, inserted.[Name], inserted.DepartmentID, inserted.ManagerID INTO @TempTable
WHERE ManagerID = 2 AND DepartmentID = 3
-- Select all the records to see which were updated
SELECT * FROM @TempTable
Introduction of Output gives a lot of opportunities to developers like me to write a better and manageable code. I think you must have already thought of a couple of places where you can use this... So what are you waiting for..give it a shot!
There are few enhancements in the TOP clause, (SQL Server 2005). I have pointed out few, which I would like to share with you all. It’s very important to keep ourselves updated with the new features and enhancements, so keep reading when ever you get time.
1.In SQL 2005, the delete and update operations can also be performed using the TOP clause
Update top (2) MyTable2 set authors_name = 'Mr. ' + authors_name
The query will update the top two rows. Similarly we can use it with DELETE.
2. Another additional feature of the TOP clause is the number specified after the clause TOP can be substituted by a variable. Using this feature it is easy to change the number of limiting rows on the fly.
Example:
Declare @n int
Set @n=3
Select top (@n) * from MyTable2
3. The TOP clause also comes with another feature called TIES. If you would like to include similar criteria together when using TOP clause, then TIES can be used.
For example the following query returns the TOP 10 % of the table.
Select top 10 percent * from mytable2 order by au_id
Here is the result set.
au_id
authors_name
12
Sanjeev sharma
But we know that there is another row with the same au_id values.
Now let us include the TIES option in the query.
Select top 10 percent with ties * from mytable2 order by au_id
Here is the result set.
au_id
authors_name
12
Sanjeev Sharma
12
Gourav Verma
///////////////////////////////////
Problem
When inserting a row into a database table that contains an identity column, I need a way to capture the identity value generated by the database engine after it inserts the row into this table. What can I use to capture this value while also making sure this value is accurate?
Solution
SQL Server provides three different functions for capturing the last generated identity value on a table that contains an identity column:
@@IDENTITY
SCOPE_IDENTITY()
IDENT_CURRENT(‘tablename’)
All three functions return the last value inserted into an identity column by the database engine. However, the three differ in functionality depending on the scope (or source) of the insert (i.e. a stored procedure or a trigger) and the connection that inserted the row.
Function @@IDENTITY returns the last generated table identity value for the current connection across all scope (i.e. any called stored procedures and any fired triggers). This function is not table specific. The value returned will be for the last table insert where an identity value was generated.
Function SCOPE_IDENTITY() is identical to @@IDENTITY with the following very notable exception: the value returned is limited to the current scope (i.e. the executed stored procedure).
Finally, function IDENT_CURRENT spans all scope and all connections to retrieve the last generated table identity value. Unlike @@IDENTITY and SCOPE_IDENTITY(), it is table specific and takes a tablename as a parameter.
Disadvantage of @@IDENTITY: It spans scope. What this means is that it will return the last identity value generated from any stored procedure that was called by the main procedure or by any trigger that was fired - whichever generates an identity value last prior to the function being invoked
Disadvantage of IDENT_CURRENT(‘tablename’) : IDENT_CURRENT not only spans scope, but it also spans connections. In other words, the value generated by IDENT_CURRENT is not confined to the processing done within your connection, but also spans all connections across the entire database. As a result, even in a moderately active OLTP environment there is a real concern about reliability of the value returned by this function. The value you capture may not necessarily be accurate which could lead to data corruption issues
My opinion is that SCOPE_IDENTITY() is the safest function of the three and should be your default choice to be used over @@IDENTITY and IDENT_CURRENT. By using SCOPE_IDENTITY() you can safely add triggers and sub procedures without inadvertently corrupting your data. Since SCOPE_IDENTITY() does not span scope and is relegated to only capturing values based on execution of the current procedure (the current scope).
We always get confused between SELECT and SET when assigning values to variables, and make mistakes. Here in this article, I will try to highlight all the major differences between SET and SELECT, and things you should be aware of, when using either SET or SELECT.
Differences start from here….
You can use SELECT to assign values to more than one variable at a time. SET allows you to assign data to only one variable at a time.
Example:
/* Declaring variables */
DECLARE @Variable1 AS int, @Variable2 AS int
/* Initializing two variables at once */
SELECT @Variable1 = 1, @Variable2 = 2
/* The same can be done using SET, but two SET statements are needed */
SET @Variable1 = 1
SET @Variable2 = 2
Most important one!
When using a query to populate a variable, SET will fail with an error, if the query returns more than one value. But SELECT will assign one of the returned rows and hides the fact that the query returned more than one row. As a result, bugs in your code could go unnoticed with SELECT, and these types of bugs are hard to track down too.
Example:
/* Consider the following table with two rows */
SET NOCOUNT ON
CREATE TABLE #Test (i int, j varchar(10))
INSERT INTO #Test (i, j) VALUES (1, 'First Row')
INSERT INTO #Test (i, j) VALUES (1, 'Second Row')
GO
/* Following SELECT will return two rows, but the variable gets its value from one of those rows, without an error.
This may not be what you were expecting. Since no error is returned,
you will never know that two rows existed for the condition, WHERE i = 1 */
DECLARE @j varchar(10)
SELECT @j = j FROM #Test WHERE i = 1
SELECT @j
GO
/* If you rewrite the same query, but use SET instead, for variable initialization, you will see the following error */
DECLARE @j varchar(10)
SET @j = (SELECT j FROM #Test WHERE i = 1)
SELECT @j
Server: Msg 512, Level 16, State 1, Line -1074284106
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Based on the above results, when using a query to populate variables, I suggest you always use SET, if you want to be sure that only one row is returned.
Is there any performance difference between SET and SELECT? Is one faster or slower than the other?
There is hardly any performance difference between SET and SELECT, when initializing/assigning values to variables. BUT, as you all know, one single SELECT statement can be used to assign values to multiple variables. This very feature of SELECT makes it a winner over SET, when assigning values to multiple variables. A single SELECT statement assigning values to 3 different variables is much faster than 3 different SET statements assigning values to 3 different variables. In this scenario, using a SELECT is at least twice as fast, compared to SET.
So, the conclusion is, if you have a loop in your stored procedure that manipulates the values of several variables, and if you want to squeeze as much performance as possible out of this loop, then do all variable manipulations in one single SELECT statement (or group the related variables into few SELECT statements)
//////////////////////////////
This article covers all the basics of User Defined Functions. It discusses how (and why) to create them and when to use them. It talks about scalar, inline table-valued and multi-statement table-valued functions. (This article has been updated through SQL Server 2005.)
With SQL Server 2000, Microsoft has introduced the concept of User-Defined Functions that allow you to define your own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.
What Kind of User-Defined Functions can I Create?
There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table-Valued and Multi-statement Table-valued.
How do I create and use a Scalar User-Defined Function?
A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value. Below is an example that is based in the data found in the NorthWind Customers Table.
CREATE FUNCTION whichContinent
(@Country nvarchar(15))
RETURNS varchar(30)
AS
BEGIN
declare @Return varchar(30)
select @return = case @Country
when 'Argentina' then 'South America'
when 'Belgium' then 'Europe'
when 'Brazil' then 'South America'
when 'Canada' then 'North America'
when 'Denmark' then 'Europe'
when 'Finland' then 'Europe'
when 'France' then 'Europe'
else 'Unknown'
end
return @return
end
Because this function returns a scalar value of a varchar(30) this function could be used anywhere a varchar(30) expression is allowed such as a computed column in a table, view, a T-SQL select list item. Below are some of the examples that I was able to use after creating the above function definition. Note that I had to reference the dbo in the function name.
print dbo.WhichContinent('USA')
select dbo.WhichContinent(Customers.Country), customers.*
from customers
create table test
(Country varchar(15),
Continent as (dbo.WhichContinent(Country)))
insert into test (country)
values ('USA')
select * from test
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Country Continent
--------------- ------------------------------
USA North America
Stored procedures have long given us the ability to pass parameters and get a value back, but the ability to use it in such a variety of different places where you cannot use a stored procedure make this a very powerful database object. Also notice the logic of my function is not exactly brain surgery. But it does encapsulate the business rules for the different continents in one location in my application. If you were to build this logic into T-SQL statements scattered throughout your application and you suddenly noticed that you forgot a country (like I missed Austria!) you would have to make the change in every T-SQL statement where you had used that logic. Now, with the SQL Server User-Defined Function, you can quickly maintain this logic in just one place.
How do I create and use an Inline Table-Value User-Defined Function?
An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.
CREATE FUNCTION CustomersByContinent
(@Continent varchar(30))
RETURNS TABLE
AS
RETURN
SELECT dbo.WhichContinent(Customers.Country) as continent,
customers.*
FROM customers
WHERE dbo.WhichContinent(Customers.Country) = @Continent
GO
SELECT * from CustomersbyContinent('North America')
SELECT * from CustomersByContinent('South America')
SELECT * from customersbyContinent('Unknown')
Note that the example uses another function (WhichContinent) to select out the customers specified by the parameter of this function. After creating the user-defined function, I can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets. Also note that I do not have to reference the dbo in my reference to this function. However, when using SQL Server built-in functions that return a table, you must now add the prefix :: to the name of the function.
Example from Books Online: Select * from ::fn_helpcollations()
What are the benefits of User-Defined Functions?
The benefits to SQL Server User-Defined functions are numerous. First, we can use these functions in so many different places when compared to the SQL Server stored procedure. The ability for a function to act like a table (for Inline table and Multi-statement table functions) gives developers the ability to break out complex logic into shorter and shorter code blocks. This will generally give the additional benefit of making the code less complex and easier to write and maintain. In the case of a Scalar User-Defined Function, the ability to use this function anywhere you can use a scalar of the same data type is also a very powerful thing. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User-Defined function a very powerful tool.
How do I create and use a Multi-statement Table-Value User-Defined Function?
A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, I can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.
Top 10 new features in SQL Server 2005
In the business world, everything is about being "better, faster and cheaper" than the competition -- and SQL Server 2005 offers many new features to save energy, time and money. From programming to administrative capabilities, this version of SQL Server tops all others and it enhances many existing SQL Server 2000 features. Here I'll outline the 10 most significant new features in order of importance:
1. T-SQL (Transaction SQL) enhancements
T-SQL is the native set-based RDBMS programming language offering high-performance data access. It now incorporates many new features including error handling via the TRY and CATCH paradigm, Common Table Expressions (CTEs), which return a record set in a statement, and the ability to shift columns to rows and vice versa with the PIVOT and UNPIVOT commands.
More information from Microsoft.
2. CLR (Common Language Runtime)
The next major enhancement in SQL Server 2005 is the integration of a .NET compliant language such as C#, ASP.NET or VB.NET to build objects (stored procedures, triggers, functions, etc.). This enables you to execute .NET code in the DBMS to take advantage of the .NET functionality. It is expected to replace extended stored procedures in the SQL Server 2000 environment as well as expand the traditional relational engine capabilities.
More information from Microsoft.
3. Service Broker
The Service Broker handles messaging between a sender and receiver in a loosely coupled manner. A message is sent, processed and responded to, completing the transaction. This greatly expands the capabilities of data-driven applications to meet workflow or custom business needs.
More information from Microsoft.
4. Data encryption
SQL Server 2000 had no documented or publicly supported functions to encrypt data in a table natively. Organizations had to rely on third-party products to address this need. SQL Server 2005 has native capabilities to support encryption of data stored in user-defined databases.
More information from Microsoft.
5. SMTP mail
Sending mail directly from SQL Server 2000 is possible, but challenging. With SQL Server 2005, Microsoft incorporates SMTP mail to improve the native mail capabilities. Say "see-ya" to Outlook on SQL Server!
More information from Microsoft.
6. HTTP endpoints
You can easily create HTTP endpoints via a simple T-SQL statement exposing an object that can be accessed over the Internet. This allows a simple object to be called across the Internet for the needed data.
More information from Microsoft.
7. Multiple Active Result Sets (MARS)
MARS allow a persistent database connection from a single client to have more than one active request per connection. This should be a major performance improvement, allowing developers to give users new capabilities when working with SQL Server. For example, it allows multiple searches, or a search and data entry. The bottom line is that one client connection can have multiple active processes simultaneously.
More information from Microsoft.
8. Dedicated administrator connection
If all else fails, stop the SQL Server service or push the power button. That mentality is finished with the dedicated administrator connection. This functionality will allow a DBA to make a single diagnostic connection to SQL Server even if the server is having an issue.
More information from Microsoft.
9. SQL Server Integration Services (SSIS)
SSIS has replaced DTS (Data Transformation Services) as the primary ETL (Extraction, Transformation and Loading) tool and ships with SQL Server free of charge. This tool, completely rewritten since SQL Server 2000, now has a great deal of flexibility to address complex data movement.
For more information from Microsoft.
10. Database mirroring
It's not expected to be released with SQL Server 2005 at the RTM in November, but I think this feature has great potential. Database mirroring is an extension of the native high-availability capabilities. So, stay tuned for more details…. For now, here's
///////////////////////////////////////////////////////////////////////////////////////////
UserDefined Function that splits the words from its delimiters
/////////////////////////////////////////////////////////////////
CREATE FUNCTION dbo.fnSplit(
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
CREATE FUNCTION dbo.customersbycountry ( @Country varchar(15) )
RETURNS
@CustomersbyCountryTab table (
[CustomerID] [nchar] (5), [CompanyName] [nvarchar] (40),
[ContactName] [nvarchar] (30), [ContactTitle] [nvarchar] (30),
[Address] [nvarchar] (60), [City] [nvarchar] (15),
[PostalCode] [nvarchar] (10), [Country] [nvarchar] (15),
[Phone] [nvarchar] (24), [Fax] [nvarchar] (24)
)
AS
BEGIN
INSERT INTO @CustomersByCountryTab
SELECT [CustomerID],
[CompanyName],
[ContactName],
[ContactTitle],
[Address],
[City],
[PostalCode],
[Country],
[Phone],
[Fax]
FROM [Northwind].[dbo].[Customers]
WHERE country = @Country
DECLARE @cnt INT
SELECT @cnt = COUNT(*) FROM @customersbyCountryTab
IF @cnt = 0
INSERT INTO @CustomersByCountryTab (
[CustomerID],
[CompanyName],
[ContactName],
[ContactTitle],
[Address],
[City],
[PostalCode],
[Country],
[Phone],
[Fax] )
VALUES ('','No Companies Found','','','','','','','','')
RETURN
END
GO
SELECT * FROM dbo.customersbycountry('USA')
SELECT * FROM dbo.customersbycountry('CANADA')
SELECT * FROM dbo.customersbycountry('ADF')
What are the benefits of User-Defined Functions?
The benefits to SQL Server User-Defined functions are numerous. First, we can use these functions in so many different places when compared to the SQL Server stored procedure. The ability for a function to act like a table (for Inline table and Multi-statement table functions) gives developers the ability to break out complex logic into shorter and shorter code blocks. This will generally give the additional benefit of making the code less complex and easier to write and maintain. In the case of a Scalar User-Defined Function, the ability to use this function anywhere you can use a scalar of the same data type is also a very powerful thing. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User-Defined function a very powerful tool.
Understanding IDENTITY Column in SQL Server
This article covers everything I know about them. I'll cover creating them, populating them, resetting them and a few other goodies.
Creating an Identity Column
In its simplest form an identity column creates a numeric sequence for you. You can specify a column as an identity in the CREATE TABLE statement:
CREATE TABLE tblUser(UserID int identity(1,1), Name varchar(20) )
The identity clause specifies that the column UserID is going to be an identity column. The first record added will automatically be assigned a value of 1 (the seed) and each subsequent record will be assigned a value 1 higher (the increment) than the previous inserted row. Identity columns can be int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0 (i.e. no places to the right of the decimal).
Populating the Table
When you insert into a table with an identity column you don't put a value into the identity column.
insert tblUser(Name) values ('sanjeev')
insert tblUser (Name) values ('sharma')
select * from TblUser
returns
UserID Name
------ --------------------
1 sanjeev
2 sharma
The value for UserID was automatically filled in. If you do try to fill in a value for an identity column it will give you an error:
insert tblUser (UserID, Name) values (3, 'Test')
returns
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'TblUser' when IDENTITY_INSERT is set to OFF.
Finding your Identity
If you want to see what identity value was just inserted you can use @@IDENTITY.
insert TblUser (Name) values ('Samjha)
select @@identity as NewRec
returns
NewRec
----------------
3
@@IDENTITY contains the last identity value generated by your statement. If you insert into a table that runs a trigger and generates another identity value, you will get back the last value generated in any table. To solve this problem you'll need to use SCOPE_IDENTITY to return the inserted value. Every procedure, trigger, function and batch is its own scope. SCOPE_IDENTITY shows the most recently inserted IDENTITY in the current scope (which ignores any triggers that might fire).
Select SCOPE_IDENTITY() as SameRecord
returns
SameRecord
----------------
3
How can I reset an Identity column and not start where it left?
If you delete all the records from a table it won't reset the identity.
Delete From tblUser
Insert TblUser (Name) Values ('New’)
select @@identity
returns the inserted identity as 4.
To reset the identity seed you need to use a DBCC command.
Delete From tblUser
DBCC CHECKIDENT('TblUser', RESEED, 0)
You can also run DBCC CHECKIDENT without specifying a reseed value. If the current seed is lower than the highest value in the table, the seed is updated to the highest value in the table.
SQL Server makes no attempt to guarantee sequential gap-free values in identity columns. If records are deleted SQL Server won't go back and populate using those values.
Default Button in ASP.NET
Many times we require setting a default button for a web form. Such as for search forms or login page where users enter a value into the textbox, then press the enter button. If you are developing in ASP.NET 1.x, the page will refresh, but nothing will happen. The buttons click event wont fire UNLESS; you explicitly enable a default button.
In ASP.NET 1.x:
You can set a default button by simply adding the following line to your page's Load event, replacing "btnSearch" with the name of your button. It uses a hidden Page method called RegisterHiddenField and works splendidly:
Page.RegisterHiddenField("__EVENTTARGET", "btnSearch")
In ASP.NET 2.0:
You can do this very easily, unfortunately its not that widely used, much less known. The HtmlForm object has a property DefaultButton. This property gets or sets the control that that causes the post back when the ENTER key is pressed.
Example:
Posted by sanjeev sharma at 4:29 AM 1 comments
Labels: ASP.NET
Saturday, June 23, 2007
Understanding Strings in C#
Strings are immutable, which means that they cannot be changed in memory. So if we append something to a string, the .Net Framework will actually reserve memory for a new string of the total desired length and then copy the original string and the new string into it. So long as the original strings are still reference, they will not be garbage-collected. We never think of this while appending to a string!!
I suggest the following approach for concatenating the strings.
// AVOID using this method. This is used by most of us.
string s = "good";
s += " morning";
s += ", everyone";
// this is better. Use this.
StringBuilder sb = new StringBuilder(50); // just a guess at the length
sb.Append( " good" );
sb.Append( " morning" );
sb.Append( ", everyone" );
// or you can also use this...
StringBuilder sb = new StringBuilder(50); // guess at the length
sb.Append( "good" ).Append( " morning" ).Append( ", everyone" );
// or best of all:
String s = "hello there, everyone";
NOTE: Why I suggest guessing at the total length of the string?
This is because a StringBuilder, by default, initializes with a total string capacity of 16 bytes, and it’s adjusted automatically (by doubling the capacity) if the string you're building exceeds this capacity. Adjusting this capacity takes the computer a little extra time, which means that it can actually be less efficient than the "avoid this" method above for short strings, unless you set the initial capacity when you construct it.
Checking for Emptystring
s = "hello";
// Test if a string is neither null nor empty.
// This has to be a static member of the String class because the object may be null!
if ( !String.IsNullOrEmpty( s ) ) ...
// Test if a string is empty.
if ( s.Length != 0 ) ...
// Test if a string is empty. (Another way, more explicit way to do it)
if ( s != String.Empty ) ...
// AVOID doing it this way.
//This causes the Framework to create a new blank
// string before comparing it to the original. This is not efficient.
if ( s != "" ) ... // AVOID THIS METHOD
String.Format()
This is a fast and efficient way to build complicated strings. Using Format () it’s always easier to keep an eye for errors like missing spaces, etc.
String name = "Sanjeev";
int number = 0;
String s = String.Format( "{0} has {1} child{2}.", name, number, (number > 1) ? "ren”: String.Empty);
Posted by sanjeev sharma at 10:06 AM 5 comments
Labels: ASP.NET
Verify a single instance of your application.
Our opponents (QA people) sometimes try to run multiple instances of a single application on a single machine. It can make you uncomfortable. To get rid of it write the following lines of code in your Main().
Mutex object is used to make sure its the single instance.
using System.Threading;
static void Main()
{
bool bFirstInstance;
oMutex = new Mutex(true, "Global\\" + “APPLICATION_NAME”, out bFirstInstance);
if(bFirstInstance)
Application.Run(new formYOURAPP() or classYOURAPP());
else
MessageBox.Show("The message you want to go for…",
"Startup warning",
MessageBoxButtons.OK, MessageBoxIcon.Exclamation,
MessageBoxDefaultButton.Button1);
}
NOTE: The '\\Global' ensures that the app should be a single instance on the machine, not just for this user's session.
Posted by sanjeev sharma at 9:54 AM 0 comments
IP Address From Host Name
Following function can be used to get the IP address of a host, given its name..Very usefull
using System.Net;
public string GetIPAddress(string sHostName)
{
IPHostEntry ipEntry = Dns.GetHostByName(sHostName);
IPAddress [] addr = ipEntry.AddressList;
string sIPAddress = addr[0].ToString();
return sIPAddress;
}
To get the IP address of your local machine, use the following method before invoking GetIPAddress():
string sHostName = Dns.GetHostName();
Posted by sanjeev sharma at 9:46 AM 0 comments
Labels: ASP.NET
Friday, June 22, 2007
Factoring Web.Config Configuration
There are many things which are there, but you didn’t quite know existed!
You might not have heard of a little known attribute called configSource, which can be specified on a section which allows the definition of the section to live in another actual file.
Consider the profile feature that allows you to specify a bunch of named properties that should be managed per-user, along with type information and other metadata. This information is currently written within the
This always sounds me odd. I have always thought it would be better if this information was in a separate .profile kind of file. Well, with configSource, you can do just that...
For example, see how can I use it into my web.config:
...
...
...
...
Once I've done that, I can now add a profile.config file into my web site as follows:
Everything continues to work as before. Essentially I've cut out the actual profile section from web.config and moved it into a separate file, which might help manage config a bit better, simply by splitting it out, as well as perhaps make me a bit happier that profile information isn't mixed with configuration! Note that you should probably still name these additional files as .config, so they aren't served out.
Comments