Microsoft Visual Studio 2010 is a great IDE and everyday I am discovering something new about it. Today I am also going to explain new feature of Visual Studio 2010. In this post I am going to Transact SQL Editor feature in Visual Studio 2010.

Visual Studio 2010 Ultimate Edition provides this great feature. You can run you SQL Queries in visual studio 2010 itself with all intellisense and all the stuff that SQL Server provides. Let’s explore in details.
To connect the database of your SQL Server in Visual studio Go to Data->Transact SQL Editor –>New Query connection like following.

 

Now once you click it will open the dialog box for connection SQL server like it. If you don’t have install anything it will install SQL Server 2008 express edition with Visual Studio 2010 Ultimate Edition. The dialog appear like following.




Once you are done with the connection it will open full fledge Query editor windows with intellisesnse like following.



Also it can connect to any databases including your SQL Azure databases also. Isn’t that great. You don’t need SQL management studio at all for the basic database operations. That’s it. Hope you like it. Stay tuned for more.. Till then happy programming.

Before sometime Microsoft has launched SQL Server Denali CTP a new version of SQL Server. I have downloaded it and install it on my machine. I have found variety of new features in SQL Server. Today I am going to explore some of the new features in SQL Server Denali Management Studio. Following are some of the new features of SQL Server Denali Management Studio.

New Font and New Colour Scheme:

First thing you notice when you start SQL Server Management Studio is the colour scheme it has nice blue theme just like Visual Studio 2010. Also if you see that now the default font is ‘Consolas’. Just like following.

Multi monitor Support:

Now SQL Server management Studio also can be work in multi monitor also. You can drag each window and set it for another monitor.Now you can each window can appeared out of shell of SSMS and You can drag with each window in different monitor like following.

Code Snippets:

Now there are lots of code snippets are available and you can that code snippets via right Click Query window-> Insert snippets like following.



Once you click Insert snippets it will open lots of built in snippets and create syntax for you directly.

Zoom Functionality:

Now you can zoom the query editor window. There is a dropdown given in left bottom corner of the window and you can zoom the query editor windows as you need like following.



There are many more features in SQL Server Denali Management studio. I have just explore few of them. For more details please visit following link which contains lots of list of new features.

http://msdn.microsoft.com/en-us/library/ms174219(v=sql.110).aspx

That’s it. Hope you like it.. Stay tuned for more.. Happy programming.

 

Today I have downloaded the SQL Server 2011 CTP and I am very excited about the new features provided by it. I am going to post new features of the SQL Server 2011(Denali) CTP in future post. Today I am going to post about how install SQL Server 2011(Denali) CTP on Windows7 machine.

First you need to download the SQL Server 2001(Denali) CTP setup from the following URL. You need download the setup as per your operating system version 32bit or 64 bit version.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9

After once you download the setup. You need to extract files to a location and from that you have to click setup.exe.  After clicking on setup.exe first screen will appear like following.



Once this screen appear you need to click on installation. Once you click the Installation following screen will appear.



Above screen will give you few options about installing SQL Server. As I want to install Stand Alone installation I have clicked on Stand alone installation. After that it will perform several test like system requirement and other stuff and then if successfully passed all the test then following screen will appear.



Then Click OK to continue to setup. Then following screen will appear.



As I want to install the express edition I have selected the express with advance service. After that terms and conditions screen Like following will appear.



Accept the licence terms and then click Next and Install. It will start installing the it. After that it will checked about rules and will bring following screen.



Click on Next and then you select the checkbox for SQL Server features and then following screen will appear.



Select the features you wan to install then click next then following screen will appear.



As I have to install default instance I have selected default instance and then click next. Then again one validation screen will appear you need to click next on that and then service account screen will appear you need to select the account via clicking on Use the same service account for SQL Server services like following.



Once you are done click next and then SQL Server Authentication mode screen will appear like following.



As I have to select mixed mode which will allow both windows authentication as well normal sql server account. Click on Next there will be several screens which are there for validation once you click next next and then Install it will start installing the SQL Server. Once you are dong with installing SQL Server following screen will appear.



That’s it You have installed SQL Serve 2011 Denali!!. Hope you liked it. Stay tuned for more..

One of the my friend Jacob Sebastian running a SQL Server TSQL quiz on his site beyondrelational.com. This is a great opportunity to learn TSQL and win great price Like Apple IPad and other lots of cool stuff. So if you are expert and if you learning TSQL then its a great way to test your knowledge.

For whole month of march selected quiz master will ask a question and you have to answer all this question day by day and at the end of month you will have great chance to win Apple Ipad.

For more details you can visit following link:

http://beyondrelational.com/quiz/SQLServer/TSQL/2011/default.aspx

Hope you liked it.Stay tuned for more..

Recently one of my friend ask me when I should go for GUID and When I should go for Int as primary key in table. So decided to write a blog post for it. Here are advantages and disadvantage of the GUID and INT.

INT Data Type:

Advantages:

1. Its required small space in terms of the storage it will only allocates 4 bytes to store data.
2. Insert and update performance will be faster then the GUID. It will increase the performance of the application.
3. Easy to index and Join will give best performance with the integer.
4. Easy to understand and remember
5. Support of function that will give last value generated like Scope_Indentity()

Disadvantages:

1. If you are going to merge table frequently then there may be a chance to duplicated primary key.
2. Limited range of uniqueness if you are going to store lots of data then it may be chance to run out of storage for INT data type.
3. Hard to work with distributed tables.

GUID Data Type:

Advantages:

1. It is unique for the current domains. For primary key is uniquely identifies the table.
2. Less chances of for duplication.
3. Suitable for inserting and updating large amount of data.
4. Easy for merging data across servers.

Disadvantages:

1. Bigger storage size (16bytes) will occupy more disk size then integer.
2. Hard to remember and lower performance with Join then integer.
3. Don’t have function to get last uniquely generated primary key.
4. A GUID primary Key will added to all the other indexes on tables. So it will decrease the performance.

Conclusion:

From above the advantages and disadvantages we can conclude that if you are having very large amount of data in table then go for the GUID as primary key in database. Otherwise INT will give best performance. Hope this will help you. Please post your opinion as comment.

Technorati Tags: GUID,INT,SQL Server2008,SQL Server

I have used data source control many times and its great it provides us great features for declarative binding. LinqDataSource Control is a great control and it allows us to bind linq queries without writing any code declaratively. Let’s create a example in that example I am not going to write a single line of code and we are going to create view,Update and Delete functionality.

So first we need a table which will have data. So, I am going to use the same table which I have used in my old posts. Below is the table structure for this example.

Let’s insert some data for that table structure. I have already inserted it in previous example. Just like below.

Now, To bind a linqdatasource we need a Linq-To-SQL Data context class Let’s create it via Project->Right Click->Add New Item –>Go to data tab->Linq-To-SQL classes Just like following.

After that I have just dragged user data to my data context just like following.

After creating Our Linq-To-SQL Classes Let’s just Add the A grid View control to my default.aspx page and apply some default format like this.

Now Let’s add a LinqDataSource from the toolbox like following.

Now select the data source and click configure data source as we can see as below.

After clicking on the Configure Data source a wizard will appear which will allow us to select Linq-to-SQL Context class just like following.

After clicking on next it will allow us to select the Linq-To-SQL Table. In our case it is a Users table so select user table just like following and select * for all columns.

Click finish now our Linq Data Source is Ready Now select the grid view and select Linq Data source we just created like below.

Now our grid view is ready We just need to select Enable Sorting and Enable Paging to give default sorting and paging functionality to grid view. Now do to aspx file and you will see that grid view control is created. I have set two more properties AutoGenerateDeleteButton and AutoGenerateEditButton property of grid view to true as we need to create update and delete functionality also. Just like below.

<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333"
GridLines="None" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="LinqDataSource1"
AutoGenerateDeleteButton="True" AutoGenerateEditButton="True"
DataKeyNames="UserId" >
  <AlternatingRowStyle BackColor="White" />
  <Columns>
     <asp:BoundField DataField="UserId" HeaderText="UserId"
            SortExpression="UserId" ReadOnly="True" />
     <asp:BoundField DataField="UserName" HeaderText="UserName"
            SortExpression="UserName" />
     <asp:BoundField DataField="FirstName" HeaderText="FirstName"
            SortExpression="FirstName" />
     <asp:BoundField DataField="LastName" HeaderText="LastName"
            SortExpression="LastName" />
   </Columns>
   <EditRowStyle BackColor="#2461BF" />
   <FooterStyle BackColor="#507CD1" Font-Bold="True"
    ForeColor="White" />
   <HeaderStyle BackColor="#507CD1" Font-Bold="True"
 ForeColor="White" />
   <PagerStyle BackColor="#2461BF" ForeColor="White"
 HorizontalAlign="Center" />
   <RowStyle BackColor="#EFF3FB" />
   <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True"
    ForeColor="#333333" />
   <SortedAscendingCellStyle BackColor="#F5F7FB" />
   <SortedAscendingHeaderStyle BackColor="#6D95E1" />
   <SortedDescendingCellStyle BackColor="#E9EBEF" />
   <SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>

Then go to Linq Data source and set EnableDelete and EnableUpdate property to true as we need this functionality. Just like following.

<asp:LinqDataSource ID="LinqDataSource1" runat="server"
  ContextTypeName="Blog.MyBlogDataContext"
  EntityTypeName="" TableName="Users"
  EnableDelete="True" EnableUpdate="True">
</asp:LinqDataSource>

That’s it now everything is ready lets run the example and see how its works here is the update example and its working fine as should.

So It’s very easy to create this kind of functionality. Hope this help you.. Happy Programming..

Technorati Tags: Linq-To-Sql,LinqDataSource,Linq,ASP.NET,Datasource

I have already written several post about Linq its a great ORM that we can use in various way. The purpose of this post to demonstrate How we can bind custom entity to stored procedure result with use of Linq-To-SQL. Let’s go through it and you will realize that how easy it will be to bind a Custom Entity to Stored Procedure result.

Let’s first create a simple table to which will hold the user data. It will contain four field like UserId,UserName,FirstName and LastName like following.

Now let’s insert some data into the table like following.

Now let’s create a stored procedure which will return the table data and a new field called Full Name like following. Here full name is a combination of first name and last name

CREATE PROCEDURE dbo.GetAllUsers
 
AS
 SET NOCOUNT ON
 SELECT
  UserId,
  UserName,
  FirstName,
  LastName,
  FirstName + ' ' + LastName AS [FullName]

 FROM dbo.Users

After creating a stored procedure it time to create a Linq-To-SQL Right Click Project->Add New Item and Go To->Data and Add LINQ to SQL Classes called MyBlogDataContext.dbml.After creating datacontext class for Linq just drag above store procedure to Linq-To-SQL classes and it will create a function like following.

Now let’s add a New Entity Class called UserInfo into Linq-To-SQL DataContext via Right Click Add New Class Just like following.

After adding class I have added same property as its having in user table and Hence our UserInfo Class will look like following.

Now everything is ready Custom Entity Class called UserInfo and we have Our Function ready which will return Stored Procedure output. Here Linq-To-SQL Provides a property called ReturnType If you select function which we have created via dragging a stored procedure in data context class. We just need to select our UserInfo class there just like following and it will bind the stored procedure with that particular UserInfo class. here only condition should be satisfied that Our Custom Entity class should contain all the field with compatible .NET Data types which will return the data. Below is the property which we are talking about.

Now let’s add grid view to default.aspx page like following and Let’s bind output of stored procedure to that grid view.

<asp:GridView ID="grdUserList" runat="server">
</asp:GridView> 

After placing the Grid View in page here is the code for biding grid view in default.aspx page_load event.

protected void Page_Load(object sender, EventArgs e)
{
  if (!Page.IsPostBack)
  {
    using (MyBlogDataContextDataContext myContext =
      new MyBlogDataContextDataContext())
    {
         List<UserInfo> MyUserList =
    myContext.GetAllUsers().ToList<UserInfo>();
         grdUserList.DataSource = MyUserList;
         grdUserList.DataBind(); 
     }
  }
}

And here is the output which we get in browser after running our web application.

That’s it its very easy.. Hope this will help you…

Technorati Tags: Linq-To-Sql,Stored Procedure,SQLServer

Recently I have been working on a article for my blog for that I just tried to add a database file on my solution with visual studio and I have received following error.

An error has occurred while establishing a connection to the server.
(provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)
An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2008, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

After checking my configuration I have found that in my machine there was more then one instance of SQL Server and the Default Instance was not properly configured and that’s why I am getting this error. This error was occurred because my Default Instance of SQL Server Express was not having TCP/IP Protocol Enabled. So I have enabled it just like following.

Go to All Programs->Microsoft SQL Server 2008-> Configuration Tools –>SQL Server configuration manager. It will open up windows like following.

After that Go To SQL Server Network Configuration and Select Protocols for your default instances and then enabled TCP/IP like following and that’s it. Now error is resolved.

Hope this will help you…

Technorati Tags: SQLServer2008,Configuration