Use Application Wrapper Class To Access Web.Config Values

Use Application Wrapper Class To Access Web.Config Values

Make sure you have a Reference to the System.Configuration.DLL

using System;using System.Collections.Generic;using System.Configuration;using System.Web;

public static class ApplicationWrapper{    #region public members
    public static string DBConnection
    {
        get { return ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString; }
    }
    public static int MaxLoginAttempts
    {
        get { return int.Parse(ConfigurationManager.AppSettings["MaxLoginAttempts"]); }
    }
    public static string Company
    {
        get { return ConfigurationManager.AppSettings["Company"]; }
    }    #endregion}

SQL Server Where 1=0

SQL Server Where 1=0

Used in both static and dynamic query.

WHERE 1 = 0 (or any always false condition) 

It is used to create a table of identical structure, sans data: In the below query empty table is created in the same structure.

SELECT * INTO dbo.NewTable FROM dbo.SomeTable WHERE 0 = 1;

SQL Server Where 1=1

SQL Server Where 1=1


where 1=1 condition are most probably used in dynamic query in Sql Server.

where 1=1 equal to no where condition [i.e] always return the true value.

While building dynamic query some times there is no need of where condition, To handle this kind of situation this condition SQL Server Where 1=1 is used.

SET @SelectStatement = 'SELECT SomeData FROM dbo.SomeTable WHERE 1  = 1';
IF @Column1 IS NOT NULL SET @SelectStatement = @SelectStatement + ' AND Column1 = @Column1';
IF @Column2 IS NOT NULL SET @SelectStatement = @SelectStatement + ' AND Column2 = @Column2';

WHILE in Sqlserver

WHILE in Sqlserver


Sets a condition for the repeated execution of an SQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.

Example:

WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300
BEGIN
   UPDATE Production.Product
      SET ListPrice = ListPrice * 2
   SELECT MAX(ListPrice) FROM Production.Product
   IF (SELECT MAX(ListPrice) FROM Production.Product) > $500
      BREAK
   ELSE
      CONTINUE
END

PIVOT and UNPIVOT Query in Sql Server

PIVOT and UNPIVOT Query in Sql Server

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.


UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

Basic PIVOT Example

The following code example produces a two-column table that has four rows.

USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost 
FROM Production.Product
GROUP BY DaysToManufacture;

Here is the result set.



DaysToManufacture          AverageCost

0                          5.0885

1                          223.88

2                          359.10

The following code displays the same result, pivoted so that the DaysToManufacture values become the column headings. A column is provided for three [3] days, even though the results are NULL.










-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;







Here is the result set.

Cost_Sorted_By_Production_Days    0         1         2           3       4       

AverageCost                       5.0885    223.88    359.1082    NULL    949.4105

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

Endpoints: Addresses, Bindings, and Contracts in WCF

Endpoints: Addresses, Bindings, and Contracts in WCF

All communication with a Windows Communication Foundation (WCF) service occurs through the endpoints of the service. Endpoints provide clients access to the functionality offered by a WCF service.

Each endpoint consists of four properties:

• An address that indicates where the endpoint can be found.
• A binding that specifies how a client can communicate with the endpoint.
• A contract that identifies the operations available.
• A set of behaviors that specify local implementation details of the endpoint. 

The Structure of an Endpoint

Each endpoint consists of the following:

• Address: The address uniquely identifies the endpoint and tells potential consumers of the service where it is located. It is represented in the WCF object model by theEndpointAddress class. An EndpointAddress class contains:
  
  
  • A Uri property, which represents the address of the service.
    
  • An Identity property, which represents the security identity of the service and a collection of optional message headers. The optional message headers are used to provide additional and more detailed addressing information to identify or interact with the endpoint.
    
  For more information, see Specifying an Endpoint Address.
  
• Binding: The binding specifies how to communicate with the endpoint. This includes:
  
  
  • The transport protocol to use (for example, TCP or HTTP).
    
  • The encoding to use for the messages (for example, text or binary).
    
  • The necessary security requirements (for example, SSL or SOAP message security).
    
  For more information, see Windows Communication Foundation Bindings Overview. A binding is represented in the WCF object model by the abstract base class Binding. For most scenarios, users can use one of the system-provided bindings. For more information, see System-Provided Bindings.
  
• Contracts: The contract outlines what functionality the endpoint exposes to the client. A contract specifies:
  
  
  • What operations can be called by a client.
    
  • The form of the message.
    
  • The type of input parameters or data required to call the operation.
    
  • What type of processing or response message the client can expect.
    
  For more information about defining a contract, see Designing Service Contracts.
  
• Behaviors: You can use endpoint behaviors to customize the local behavior of the service endpoint. Endpoint behaviors achieve this by participating in the process of building a WCF runtime. An example of an endpoint behavior is the ListenUri property, which allows you to specify a different listening address than the SOAP or Web Services Description Language (WSDL) address. For more information, see ClientViaBehavior.

Calling Javascript from Server side of the code in Asp.net

Calling Javascript from Server side of the code in Asp.net

Methods:

1. Page.ClientScript.RegisterStartupScript( me.gettype(), "PopupScript", alertScript );

Asynchronous Postback:[If Ajax used in the page]

2. ScriptManager.RegisterStartupScript(this, GetType(), "YourUniqueScriptKey",
        "alert('This pops up');", true);

Parameters
page
Type: System.Web.UI.Page
The page object that is registering the client script block.

type
Type: System.Type
The type of the client script block. This parameter is usually specified by using the typeof operator (C#) or the GetType operator (Visual Basic) to retrieve the type of the control that is registering the script.

key
Type: System.String
A unique identifier for the script block.

script
Type: System.String

The script.
addScriptTags


Type: System.Boolean
true to enclose the script block with < scrip t> and < / script > tags; otherwise, false.

C# and VB.net Connectivity with Query String

C# and VB.net Connectivity with Query String


C# Example

//Execute Non Query for query string

private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}


VB.NET Example

Public Sub CreateCommand(ByVal queryString As String, _
  ByVal connectionString As String)
    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        command.Connection.Open()
        command.ExecuteNonQuery()
    End Using
End Sub


//
C# Example SQLReader Example

private static void ReadOrderData(string connectionString)
{
    string queryString =
        "SELECT OrderID, CustomerID FROM dbo.Orders;";

    using (SqlConnection connection =
               new SqlConnection(connectionString))
    {
        SqlCommand command =
            new SqlCommand(queryString, connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        // Call Read before accessing data.
        while (reader.Read())
        {
            Console.WriteLine(String.Format("{0}, {1}",
                reader[0], reader[1]));
        }

        // Call Close when done reading.
        reader.Close();
    }
}


VB.NET SQLReader Example


Private Sub ReadOrderData(ByVal connectionString As String)
    Dim queryString As String = _
        "SELECT OrderID, CustomerID FROM dbo.Orders;"

    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        ' Call Read before accessing data.
        While reader.Read()
            Console.WriteLine(String.Format("{0}, {1}", _
                reader(0), reader(1)))
        End While

        ' Call Close when done reading.
        reader.Close()
    End Using
End Sub



C# Connectivity for  ExecuteSchalar

static public int AddProductCategory(string newName, string connString)
{
    Int32 newProdID = 0;
    string sql =
        "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); "
        + "SELECT CAST(scope_identity() AS int)";
    using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@Name", SqlDbType.VarChar);
        cmd.Parameters["@name"].Value = newName;
        try
        {
            conn.Open();
            newProdID = (Int32)cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
    return (int)newProdID;
}

VB.NET Connectivity for  ExecuteSchalar

Public Function AddProductCategory( _
  ByVal newName As String, ByVal connString As String) As Integer
    Dim newProdID As Int32 = 0
    Dim sql As String = _
     "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); " _
       & "SELECT CAST(scope_identity() AS int);"

    Using conn As New SqlConnection(connString)
        Dim cmd As New SqlCommand(sql, conn)
        cmd.Parameters.Add("@Name", SqlDbType.VarChar)
        cmd.Parameters("@Name").Value = newName
        Try
            conn.Open()
            newProdID = Convert.ToInt32(cmd.ExecuteScalar())
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Using

    Return newProdID
End Function

C# Example


//Execute Non Query for query string

private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}


VB.NET Example

Public Sub CreateCommand(ByVal queryString As String, _
  ByVal connectionString As String)
    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        command.Connection.Open()
        command.ExecuteNonQuery()
    End Using
End Sub


//C# Example SQLReader Example

private static void ReadOrderData(string connectionString)
{
    string queryString =
        "SELECT OrderID, CustomerID FROM dbo.Orders;";

    using (SqlConnection connection =
               new SqlConnection(connectionString))
    {
        SqlCommand command =
            new SqlCommand(queryString, connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        // Call Read before accessing data.
        while (reader.Read())
        {
            Console.WriteLine(String.Format("{0}, {1}",
                reader[0], reader[1]));
        }

        // Call Close when done reading.
        reader.Close();
    }
}


VB.NET SQLReader Example


Private Sub ReadOrderData(ByVal connectionString As String)
    Dim queryString As String = _
        "SELECT OrderID, CustomerID FROM dbo.Orders;"

    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        ' Call Read before accessing data.
        While reader.Read()
            Console.WriteLine(String.Format("{0}, {1}", _
                reader(0), reader(1)))
        End While

        ' Call Close when done reading.
        reader.Close()
    End Using
End Sub



C# Connectivity for  ExecuteSchalar

static public int AddProductCategory(string newName, string connString)
{
    Int32 newProdID = 0;
    string sql =
        "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); "
        + "SELECT CAST(scope_identity() AS int)";
    using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@Name", SqlDbType.VarChar);
        cmd.Parameters["@name"].Value = newName;
        try
        {
            conn.Open();
            newProdID = (Int32)cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
    return (int)newProdID;
}

VB.NET Connectivity for  ExecuteSchalar

Public Function AddProductCategory( _
  ByVal newName As String, ByVal connString As String) As Integer
    Dim newProdID As Int32 = 0
    Dim sql As String = _
     "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); " _
       & "SELECT CAST(scope_identity() AS int);"

    Using conn As New SqlConnection(connString)
        Dim cmd As New SqlCommand(sql, conn)
        cmd.Parameters.Add("@Name", SqlDbType.VarChar)
        cmd.Parameters("@Name").Value = newName
        Try
            conn.Open()
            newProdID = Convert.ToInt32(cmd.ExecuteScalar())
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Using

    Return newProdID
End Function

To display the list of stored procedure containing text in Sql Server

To display the list of  stored procedure containing text in Sql Server


DECLARE @Search varchar(255) SET @Search='CNC_EmailResults_Other'
SELECT DISTINCT o.name AS Object_Name,o.type_desc  FROM sys.sql_modules  m    INNER JOIN sys.objects
o ON m.object_id=o.object_id     WHERE m.definition Like '%'+@Search+'%'     ORDER BY 2,1

To find the text in the stored procedure in Sql server

To find the text in the stored procedure in Sql server



declare @newString varchar(205)
Set @newString = '%' + 'CNC_EmailResults_Other' + '%'
-- Insert statements for procedure here
select name from sysobjects where id in
(select id from syscomments where text like @newString)
order by name

Debug the Windows service in Setup

Debug the Windows service in Setup

1. Window service setup should be in debug mode.

2. Insert the code in the appropriate place System.Diagnostics.Debugger.Launch()

3. Now start debugging the windows service.

C# and VB.Net Connectivity using DataReader with Stored Procedure

C# and VB.Net Connectivity using  DataReader with Stored Procedure

static void GetSalesByCategory(string connectionString,
    string categoryName)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandText = "SalesByCategory";
        command.CommandType = CommandType.StoredProcedure;

        // Add the input parameter and set its properties.
        SqlParameter parameter = new SqlParameter();
        parameter.ParameterName = "@CategoryName";
        parameter.SqlDbType = SqlDbType.NVarChar;
        parameter.Direction = ParameterDirection.Input;
        parameter.Value = categoryName;

        // Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter);

        // Open the connection and execute the reader.
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }
        reader.Close();
    }
}

VB.Net Connectivity for  DataReader

Shared Sub GetSalesByCategory(ByVal connectionString As String, _
    ByVal categoryName As String)

    Using connection As New SqlConnection(connectionString)

        ' Create the command and set its properties.
        Dim command As SqlCommand = New SqlCommand()
        command.Connection = connection
        command.CommandText = "SalesByCategory"
        command.CommandType = CommandType.StoredProcedure

        ' Add the input parameter and set its properties.
        Dim parameter As New SqlParameter()
        parameter.ParameterName = "@CategoryName"
        parameter.SqlDbType = SqlDbType.NVarChar
        parameter.Direction = ParameterDirection.Input
        parameter.Value = categoryName

        ' Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter)

        ' Open the connection and execute the reader.
        connection.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()

        If reader.HasRows Then
            Do While reader.Read()
                Console.WriteLine("{0}: {1:C}", _
                  reader(0), reader(1))
            Loop
        Else
            Console.WriteLine("No rows returned.")
        End If
    End Using
End Sub

Generics in Dotnet

Generics  in Dotnet

·         In generic class, you can create a collection that is type-safe at compile-time.

   

      Generics are the most powerful feature of C# 2.0. Generics allow you to define type-safe data structures, without committing to actual data types. This results in a significant performance boost and higher quality code, because you get to reuse data processing algorithms without duplicating type-specific code. In concept, generics are similar to C++ templates, but are drastically different in implementation and capabilities. This article discusses the problem space generics address, how they are implemented, the benefits of the programming model, and unique innovations, such as constrains, generic methods and delegates, and generic inheritance. You will also see how generics are utilized in other areas of the .NET Framework such as reflection, arrays, collections, serialization, and remoting.

            If the items are value types, they must be boxed when they are added to the list, and unboxed when they are retrieved. 

·       

            Both the casting and the boxing and unboxing operations decrease performance; the effect of boxing and unboxing can be very significant in scenarios where you must iterate over large collections.

    

      The other limitation is lack of compile-time type checking; because an ArrayList casts everything to Object, there is no way at compile-time to prevent client code from doing something such as this:

    System.Collections.ArrayList list = new System.Collections.ArrayList();

    // Add an integer to the list.

    list.Add(3);

    // Add a string to the list. This will compile, but may cause an error later.

    list.Add("It is raining in Redmond.");

    int t = 0;

    // This causes an InvalidCastException to be returned.

    foreach (int x in list)

    {   t += x;

    }

Boxing and Unboxing in Dotnet

Boxing and Unboxing  in Dotnet


Boxing is the process of converting a value type to the type object
When the CLR boxes a value type, it wraps the value inside a System.Object and stores it on the managed heap.
Unboxing extracts the value type from the object. 

int i = 123;
object o = (object)i;  // boxing

The object o can then be unboxed and assigned to integer variable i: 

o = 123;
i = (int)o;  // unboxing

Disadvantages of Boxing

Performance

Boxing and unboxing are computationally expensive processes. When a value type is boxed, an entirely new      object must be created. This can take up to 20 times longer than an assignment. When unboxing, the casting     process can take four times as long as an assignment.