Monday, 30 December 2013

Create a .exe for asp.net web application

Steps:

1. Right Click on solution --> add new project --->Select web set up project. and give the name mysetup.

2. Right click on mysetup-->select View-->File System.

3.Left side panel we get the web application folder

4.right click on web application folder -->Add-->project output-->ok

5.inside web application folder bin folder is there , right click-->Add-->project output-->ok

6.Build the websetup.

7. Build the whole solution.

8.Goto mysetup folder location (physical path) inside debug you will find setup

9.Run the set up , you will find the application in IIS.

Thursday, 19 December 2013

Create table with Foriegn key

CREATE TABLE Users
(
Id int Primary key,
UserName varchar(250),
Group_id int,
AppName varchar(250),
URL varchar(250),
CONSTRAINT fk_Users_List FOREIGN KEY (Group_id)
REFERENCES Group(id)
)

Create table with Auto increment field in oracle

There is no such thing as "auto_increment" or "identity" columns in Oracle. However, you can model it easily with a sequence and a trigger:

Table definition:
CREATE TABLE departments (
  ID           NUMBER(10)    NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq;

Trigger definition:

CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW

BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;

UPDATE: IDENTITY column is now available on Oracle 12c version, see this:

CREATE TABLE t1 (c1 NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
                   c2 VARCHAR2(10));

Tuesday, 17 December 2013

Get users based on LDAP group

  public DataTable getUsers(string username)
    {
        DataTable dtUsers = new DataTable();
        dtUsers.Columns.Add("users");
        try
        {
            // create domain context
            PrincipalContext ctx = new PrincipalContext(ContextType.Domain);
            // fetch your group
            GroupPrincipal group = GroupPrincipal.FindByIdentity(ctx, username);
            // enumerate over the group's members
            foreach (Principal p in group.Members)
            {
                DataRow row = dtUsers.NewRow();
                row[0] = p.Name;
                dtUsers.Rows.Add(row);
                //Console.WriteLine("Principal '{0}', type '{1}'", p.Name, p.StructuralObjectClass);
            }

        }
        catch (Exception ex)
        {
            ErrorLog.Log(ex);
        }
        return dtUsers;
    }

Monday, 16 December 2013

Get List of groups and users From LDAP



using System.DirectoryServices;
using System.DirectoryServices.AccountManagement;
using System.DirectoryServices.ActiveDirectory;

public partial class Admin : System.Web.UI.Page
{

    Common common = new Common();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
                //Getting the Current user name
                string username = common.ExtractUserName(User.Identity.Name);
                UserPrincipal user = UserPrincipal.FindByIdentity(new PrincipalContext(ContextType.Domain), IdentityType.SamAccountName, username);
                ArrayList adminGroups = common.GetAdminGroups();//Get the Admin Groups
                ArrayList groups = common.getGroupsByPrincipal(user);//Get the User Groups
                groups.Add(user.DisplayName.ToLower().Trim());
                groups.Add(user.SamAccountName.ToLower().Trim());
                getUsersAndGroup();
                bool redirect = true;
//Check wheather user is admin or not

                if (adminGroups.Count > 0)
                {
                    foreach (string adminGroup in adminGroups)
                    {
                        if (groups.Contains(adminGroup))
                        {
                            redirect = false;
                            break;
                        }
                    }
                    if (redirect)
                    {
                        Response.Redirect("Default.aspx", true);
                    }
                }
        }
    }

    private void getUsersAndGroup()
    {
        try
        {
            ArrayList allUsers = this.getAllUsers();
            ArrayList allGroups = this.getAllGroups();

            DataTable dtGroups = new DataTable();
            dtGroups.Columns.Add("Groups");
            for (int i = 0; i < allGroups.Count; i++)
            {
                DataRow row = dtGroups.NewRow();
                row[0] = allGroups[i];

                dtGroups.Rows.Add(row);
            }

            ddlGroup.DataSource = dtGroups;
            ddlGroup.DataTextField = "Groups";
            ddlGroup.DataValueField = "Groups";
            ddlGroup.DataBind();
        }
        catch (Exception ex)
        {
            ErrorLog.Log(ex);
        }
    }


    public DataTable getUsers(string username)
    {
        DataTable dtUsers = new DataTable();
        dtUsers.Columns.Add("users");
        try
        {
            // create domain context
            PrincipalContext ctx = new PrincipalContext(ContextType.Domain);
            // fetch your group
            GroupPrincipal group = GroupPrincipal.FindByIdentity(ctx, username);
            // enumerate over the group's members
            foreach (Principal p in group.Members)
            {
                DataRow row = dtUsers.NewRow();
                row[0] = p.Name;
                dtUsers.Rows.Add(row);
                //Console.WriteLine("Principal '{0}', type '{1}'", p.Name, p.StructuralObjectClass);
            }

        }
        catch (Exception ex)
        {
            ErrorLog.Log(ex);
        }
        return dtUsers;
    }


    private ArrayList getAllUsers()
    {
        return getADObjectsForClass("User");
    }

    private ArrayList getAllGroups()
    {
        return getADObjectsForClass("Group");
    }


    private ArrayList getADObjectsForClass(string objectClass)
    {
        ArrayList list = new ArrayList();
        try
        {
            DirectoryEntry dirEntry = new DirectoryEntry("LDAP://" + System.Configuration.ConfigurationManager.AppSettings["DomainName"]); //Enter the domain name here
            DirectorySearcher dirSearcher = new DirectorySearcher();
            dirSearcher.SearchRoot = dirEntry;
            dirSearcher.Filter = "(objectClass=" + objectClass + ")";
            dirSearcher.PropertiesToLoad.Add("cn");
            SearchResultCollection res = dirSearcher.FindAll();
            foreach (SearchResult objectUnit in res)
            {
                list.Add(objectUnit.Properties["cn"][0].ToString());
            }
        }
        catch (Exception ex)
        {
            ErrorLog.Log(ex);
        }
        return list;
    }


}

Wednesday, 9 October 2013

IF condition in Select Statement SQL Server



The CASE statement is the closest to IF Else in SQL and is supported on all versions of SQL Server

SELECT CAST(
             CASE 
                  WHEN Obsolete = 'N' or InStock = 'Y' 
                     THEN 1 
                  ELSE 0 
             END AS bit) as Salable, * 
FROM Product

You only need to do the CAST if you want the result as a boolean value, if you are happy with an int, this works:

SELECT CASE 
            WHEN Obsolete = 'N' or InStock = 'Y' 
               THEN 1 
               ELSE 0 
       END as Salable, * 
FROM Product
CASE statements can be embedded in other CASE statements and even included in aggregates.

Tuesday, 8 October 2013

Multiple Records Insert into SQL Server Database


Multiple Records Insert

Step1: Created Table in Database

CREATE TABLE Person
(
PersonId INT PRIMARY KEY,
PersonName VARCHAR(100)
)

Step2: Created One StoredProcedure

CREATE PROCEDURE sp_BatchInsert ( @PersonId INT, @PersonName VARCHAR(100) ) 
AS 
BEGIN 
            INSERT INTO Person VALUES ( @PersonId, @PersonName); 

END

Step3: 
private void btnBatchInsert_Click(object sender, EventArgs e)
        {
            // Get the DataTable with Rows State as RowState.Added
            DataTable dtInsertRows = GetDataTable();
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = new SqlCommand("sp_BatchInsert", connection);
            command.CommandType = CommandType.StoredProcedure;
            command.UpdatedRowSource = UpdateRowSource.None;

            // Set the Parameter with appropriate Source Column Name
            command.Parameters.Add("@PersonId", SqlDbType.Int, 4, dtInsertRows.Columns[0].ColumnName);          
            command.Parameters.Add("@PersonName", SqlDbType.VarChar, 100, dtInsertRows.Columns[1].ColumnName);
           
            SqlDataAdapter adpt = new SqlDataAdapter();
            adpt.InsertCommand = command;
            // Specify the number of records to be Inserted/Updated in one go. Default is 1.
            adpt.UpdateBatchSize = 2;          
            connection.Open();
            int recordsInserted = adpt.Update(dtInsertRows);          
            connection.Close();
            MessageBox.Show("Number of records affected : " + recordsInserted.ToString());

        }


Another Method

Use SQLBulkCopy of ADO.NET 2.0  
Valid for Environment: NET 2.0 or above on SQL Server 2005 database or above
With ADO.NET 2.0 we got the programming interface for Bulk Copy which provides quite simple and straight forward mechanism to transfer the data from one SQL server instance to another, from one table to another, from DataTable to SQL Server 2005 database, from DataReader to SQL Server 2005 database and many more.
SqlBulkCopy belongs to System.Data.SqlClient namespace and it is as simple as ADO.NET Command object when it comes to programming it. Let us see it working:

  private void btnSQLBulkCopyInsert_Click(object sender, EventArgs e)
        {
            // Get the DataTable 
            DataTable dtInsertRows = GetDataTable();
            
            using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString))
            {
                sbc.DestinationTableName = "Person";
                
                // Number of records to be processed in one go
                sbc.BatchSize = 2;

                // Map the Source Column from DataTabel to the Destination Columns in SQL Server 2005 Person Table
                sbc.ColumnMappings.Add("PersonId", "PersonId");
                sbc.ColumnMappings.Add("PersonName", "PersonName");

                // Number of records after which client has to be notified about its status
                sbc.NotifyAfter = dtInsertRows.Rows.Count;

                // Event that gets fired when NotifyAfter number of records are processed.
                sbc.SqlRowsCopied+=new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied);


                // Finally write to server
                sbc.WriteToServer(dtInsertRows);
                sbc.Close();
            }

The code above is very simple and quite self explanatory.
 Key Notes :
1.            BatchSize and NotifyAfter are two different properties. Former specify the number of records to be processed in one go while later specifies the number of records to be processed after which client needs to be notified.
 Reference:
1.            No better place than MSDN. Refer to http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx for details on SqlBulkCopy
2.            Refer http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy_properties.aspx to get details on all properties on SqlBulkCopy.


Thursday, 19 September 2013

Get Datatable from SqlDataSource

DataView dv = new DataView();
DataTable dt = new DataTable();
dv = mySQLDataSource.Select(DataSourceSelectArguments.Empty) as DataView;
dt = dv.ToTable();

Monday, 19 August 2013

How to create a sample WPF Sample Application using C#.Net.


XAML Code :

<Window x:Class="simple_sample_App.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="525">
    <Grid>
        <Label Content="Name" HorizontalAlignment="Left" Margin="157,56,0,0" VerticalAlignment="Top"/>
        <Label Content="Location" HorizontalAlignment="Left" Margin="157,87,0,0" VerticalAlignment="Top"/>
        <TextBox x:Name="txtName" HorizontalAlignment="Left" TextWrapping="Wrap" VerticalAlignment="Top"Margin="233,58,0,0" Width="120"/>
        <TextBox x:Name="txtLocation" HorizontalAlignment="Left" TextWrapping="Wrap" VerticalAlignment="Top"Margin="233,89,0,0" Width="120"/>
        <Button x:Name="btnSubmit" Content="Submit" HorizontalAlignment="Left" Margin="233,126,0,0"VerticalAlignment="Top" Width="75" Click="btnSubmit_Click"/>
        <Label Content="Name" HorizontalAlignment="Left" VerticalAlignment="Top" Margin="157,211,0,0"/>
        <Label Content="Location" HorizontalAlignment="Left" VerticalAlignment="Top" Margin="157,242,0,0"/>
        <Label x:Name="lblName" Content="Label" HorizontalAlignment="Left" VerticalAlignment="Top"Margin="233,211,0,0"/>
        <Label x:Name="lblLocation" Content="Label" HorizontalAlignment="Left" VerticalAlignment="Top"Margin="233,242,0,0"/>
        <Label Content="Result :" HorizontalAlignment="Left" Margin="157,180,0,0" VerticalAlignment="Top"/>
        <Label Content="WPF Sample Application" HorizontalAlignment="Left" VerticalAlignment="Top" Margin="157,10,0,0"FontFamily="Arial Black" FontSize="16"/>

    </Grid>
</Window>

XAML.CS Code :
using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace simple_sample_App
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private void btnSubmit_Click(object sender, RoutedEventArgs e)
        {
            lblName.Content = txtName.Text;
            lblLocation.Content = txtLocation.Text;
        }
    }
}




 output: