Tuesday, 8 October 2013

Remove duplicate rows from table

By mistakenly , non-primary key table data inserted into SQL Table. what you will do ?

We dun have any option without deleting it. If we not used Savepoint , then how to rollback inserted data ?

Remove table , --- > No

Remove rows one by one --> No

Use CTE ( Common Table Expression)

Query :

I have inserted following data two times

insert into [Test].[dbo].[Student] values(1,'nihar','pune')
insert into [Test].[dbo].[Student] values(2,'rohan','nashik')
insert into [Test].[dbo].[Student] values(3,'vishal','pabal')


Remove duplicate rows :

Query :

 
 WITH OrderedResults AS
(
SELECT [studno]
, ROW_NUMBER() OVER (PARTITION BY [studno] ORDER BY [studno]) AS RowNumber
FROM [Test].[dbo].[Student]

)
delete
from OrderedResults
WHERE RowNumber!= 1   Select whole query and execute it, Duplicate entries will be removed from table.Think over it.



 

Friday, 4 October 2013

Temporary tables in SQL Server

Hi friends , Yesterday I thought about temp. tables in database . After searching over same query , I got through some links regarding
"Temp Table " ,
"global temporary table ",
"Local Temp Table",
"#"  etc.
  • Temporary table created on tempdb of SQL Server. This is a separate database. So, this is an   additional overhead and can causes performance issues.
  • Number of rows and columns need to be as minimum as needed.
  • Tables need to be deleted when they are done with their work. 

  • Till the date I am using simple method to do so . But this concept will save my time and don't confuse between oracle and sql server. Its different.

    Concept : Temporary tables in Database

    Concept of temporary table which helps the developer in a great way. These tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside system database/tempdb/temporary tables (in SQL Server) database.

    Different Types of Temporary Tables

    SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:
    • Local Temp Table
    • Global Temp Table

        1) Local Temp Table

               Local temp tables are only available to the current connection for the user; and they are             automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.

        2) Global Temp Table

              Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

    If you want see all opearation under one page , simple go for procedure. So your concept will gets clear.

    Steps To use it

    Step 1:

    (As shown in picture)
    Create procedure with parameters and write code .



    Step 2:

    First check your temp table created or not .
    Refresh the database. In System database - > tempdb ->  temporary table
    if done , go for executing stored procedure.



    Step 3:
    Please check result.





    Drop temp table
      
     drop table #LocalTempTable
    or
    Prefer Link
     
     
    It will be helpful when ,
     
    - we are doing large number of row manipulation in stored procedures.
    - This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.
    - we are having a complex join operation.


    For Oracle ,

    Some Important links need to refer.
    1) temp Table in Oracle
    2) Problems with temp table in Oracle


    Quote :
    Be a gentle friend to trees and they will give you back beauty, cool and fragrant shade, and many birds, singing ]



     










     

    Wednesday, 2 October 2013

    ButtonField class in ASP.NET

    ButtonField is very well known field of ASP.NET Gridview control.
    We have seen how to use element to display select, edit and delete buttons in a GridView control. However, if we wish to provide buttons which perform additional custom functionalities, we use the element in a GridView control.
     
    It's part of Data events like DetailView and Gridview under 
    <Column>
    <asp:ButtonField ButtonType...... />
    </Column> 

    Description With Example :

    In the below example, we have used the ButtonField element to add Details button in a row of GridView control. The element displays the button as a link or a button or an Image.

    Step 1:

    Declare a GridView control and set the AutoGenerateColumns property to 'false' and set the event handler for the RowCommand event.

    Step 2:

    When we click on the Details button, the GridView controls row command event is raised. In this example, the event RowCommand is handled by the GridView1_RowCommand event.
    <asp:GridView ID="GridView1"  DataSourceId="MyDataSource"
    DataKeyNames="BlogId,FollowerName" ShowFooter="true" AutoGenerateColumns="false"
    AutoGenerateEditButton="true" AutoGenerateDeleteButton="true"
    OnRowCommand="GridView1_RowCommand"
    runat="server">
    <Columns>
     <asp:BoundField  DataField="BlogId" HeaderText="Blog Code" />
        <asp:BoundField  DataField="FollowerName" HeaderText="FollowerName" />
     <asp:BoundField  DataField="FollowerDetails" HeaderText="Description" />
     <asp:buttonfield  buttontype="button" Text="Row Details" commandname="Details"  />
    </Columns>
    </asp:GridView>


     



    Step 3:

    For row command , use following code or use can use RowCreated , RowUpdating etc gridviewrow events

    protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
     {
            int index = Int32.Parse((string)e.CommandArgument);
            string Code = (string)GridView1.DataKeys[index].Values["BlogId"];
            string Name = (string)GridView1.DataKeys[index].Values["FollowerName"];
     }

    Above post is just is simple example for beginner to use it.. ...
    We can do onclick events by ModelPopupExtender of Ajax toolkit...
     Enjoy..

    [ Quote : "For the unlearned, old age is winter; for the learned it is the season of the harvest." ]








     
























     

    BaseDataList Control in ASP.NET


    Today I seen BaseDataList in MSDN Tutorial. But I am confuse about BaseDataLIst control / class. Is it concept or web form control ?
    or
    Is it base class for DataControls in asp.NET ?

    According to msdn they were showing its similar to DataList and DataGrid controls.
    BaseDataList description also available from .Net 2.0 to till now.

    Don't search for BaseDataList in Toolbox of Visual Studio because its a Class and DataList concept.

    It is not a control, it's an abstract class meaning that it provides base functionality that must be inherited by another class to be used. In this case it provides common functionality for the DataList and DataGrid controls so that methods like GetData and events like SelectedIndexChanged are available to both.

    Constructor :

    A BaseDataList object is not created directly. This constructor is commonly called in the constructor of a derived class to initialize the properties defined in the BaseDataList class.

    Use :

    Use the Controls collection to manage the child controls contained in a data listing control. You can add controls, remove controls, or iterate through the server controls in the collection
    BaseDataList contains following properties that enable formatting the gridview and its content
     -  Cellpadding
     -  Cellspacing etc..

    Instance of BasedataList class are not directly created.The constructor is called during construction of derived classes to initialize properties defined in BaseDataList class.

    Example :

    Creating BaseDatalist in C#


    [Note : Links are provided for more information]

    [Discussion is an exchange of knowledge, argument is an exchange of ignorance....!]
     

    Error while running webparts ??? Failure to connect to sql server

    Its strange , that while running only aspx page ,its showing error of SQL Server .
    How ?
    What ?
    Why ?. ....


    Error :

    The webpartmanager is looking for SQL Express 2005 which, by default, is the personalization provider. We can work around this but if you are really eager to see your page displayed you can set the Personalization-Enabled="false" in the webpartmanager. This will render your page but it will also defeat any purpose of using webparts. For the real solution read on.

    Solution :

    1) Open your visual studio command prompt located in "Start Menu\Programs\Microsoft Visual Studio 2005\Visual Studio Tools\Visual Stuido 2005 Command Prompt" and type in aspnet_regsql.exe. This will launch a wizard so that you can either create a DB or add tables to an existing database for storing personalization infromation. Click Next, Next, then enter in your DBServer Name. Lets leave the DB as "default" for now, click next, next, finish. By leaving the db as default the tool will create a database named aspnetdb

    2) Now we have a database so we will need a connection string to access it from our Personalization Provider in Step 3. The connection string will go into your web.config and it will be similar to the one show below:
    < connectionStrings>
    <remove name="LocalSqlServer" />
    <add name="DBConn" connectionString="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=aspnetdb;Data Source=DBServer" providerName="System.Data.SqlClient" />
    < /connectionStrings>

    Note: The "remove" tag is used to inform Visual Studio that we will not be using SQL Express
    3) The final step is to add our own personalization provider in the <system.web> section of the webConfig. The personalization provider will point to the store we created by using the connection string we provide (Dbconn).
    < script></script> <webParts>
    <personalization defaultProvider="AspNetSqlPersonalizationProvider">
    <providers>
    <remove name="AspNetSqlPersonalizationProvider" />
    <add name="AspNetSqlPersonalizationProvider"
    type="System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider"
    connectionStringName="DBConn"
    applicationName="/" />
    </providers>
    </personalization>
    < /webParts>

    ALL DONE !!! Congrats you should be up and running.
    Be green ... Ever Green... Keep Green....

    Unable to attach to application 'WebDev.WebServer.EXE'


    Type :

    Its error , while executing application from Visual Studio 2008

    Description :

     
    Some times in VS 2008 an error is observed before debugging an application and some times
     
    When I choose yes, the default browser loads forever.
    Even if I set IE as the default browser, the same error on VWD2005 occurs.
    Some
     
    Solutions :
     
    A quick solution is to press CTRL+ALT+DEL to open task manager,
    In processes tab end WebDev.WebServer.EXE
    or
    Restart application :)  :)
     

    Tuesday, 1 October 2013

    Custom Validation Attribute in ASP.NET

    Custom Property Validation Attribute

    Above mentioned value defined as " User defined custom attribute "

    Normally .Net developers used following types of attribute

    - Compare
    - Range
    - Required
    - RegularExpression
    - StringLength
    - Authorize
    etc.....

    MSDN defined namespace
    - using System.ComponentModel.DataAnnotations;

    Step 1: But we are creating our own defined attributes in C#

    Consider , we want to check weight can't be more than 100 to be entered by end user on web site

    [Note: Just for your code beautification make folder in solution as Infrastructure or any]
    I created folder as MVC Infrastructure in solution.
     
    namespace BlogManagement.Infrastructure
    {    public class RanegDieIdAttribute : RequiredAttribute
       {         public override bool IsValid(object value)
            {
                        return base.IsValid(value) && ((double)value) > 100; 
             }
        }
    }


    Step 2:
    In  Models of MVC project , create class as IDValidation.cs

     
     using System;
     using System.Collections.Generic;
     using System.Linq;
     using System.Web; using System.ComponentModel.DataAnnotations; using System.Globalization;
    using BlogManagement.Infrastructure;

     namespace BlogManagement.Models
    {
       
    [MetadataType(typeof(IdMast_validation))]
    public partial class IdMast
    {
    }
    public class IdMast_validation
    {     
           
      [Required(ErrorMessage = "Please enter ID")]       
      [RegularExpression("^[0-9]+$", ErrorMessage = "Field of age is not a number")]
      public string DiId
      {     get;set;
       }  
     [Required(ErrorMessage = "Please enter Weight ")]       
     [RangeDieId(ErrorMessage="Weight can not be more than 100")]       
      public double CutWt
      {    get;set;
       }

    }

    Please check output as shown in image...

     
     
    happy Coding...!!  
     
    Live green,...say green, ...  keep green...