Run batch file from asp.net c#

 string batpath = " Batch file path ";
 System.Diagnostics.Process proc = new System.Diagnostics.Process();
 proc.StartInfo.FileName = "batch file name";
 proc.StartInfo.WorkingDirectory = batpath;
 proc.StartInfo.UseShellExecute = true;
 proc.Start();
 proc.Close();

Run EXE with parameters from asp.net c#

 ProcessStartInfo startInfo = new ProcessStartInfo("exe path");
 startInfo.WindowStyle = ProcessWindowStyle.Minimized;
 startInfo.UseShellExecute = true;

 //set user and password if needed
 //startInfo.UserName = "chetan";
 //startInfo.Password =  "Password";
 //startInfo.Domain = "MyDomain";

// pass parameter to exe
 startInfo.Arguments = strInputPath + "@" + strOutputPath;
 Process.Start(startInfo);

Manage IIS Using Asp.net

You can do operation with IIS using asp.net C#. below is the step for it.
1st you need ServerManager dll which you can copy from :
"C:\Windows\System32\inetsrv" (or as per your system)

=> Working with the ServerManager
ServerManager server = new ServerManager();


=> Getting Sites Associated with the Server
SiteCollection sites = server.Sites;
foreach (Site site in sites)
{
    ApplicationDefaults defaults = site.ApplicationDefaults;

    //get the name of the ApplicationPool under which the Site runs
    string appPoolName = defaults.ApplicationPoolName;

    ConfigurationAttributeCollection attributes =  defaults.Attributes;
    foreach (ConfigurationAttribute configAttribute in attributes)
    {
        //put code here to work with each ConfigurationAttribute
    }

    ConfigurationAttributeCollection attributesCollection = site.Attributes;
    foreach (ConfigurationAttribute attribute in attributesCollection)
    {
        //put code here to work with each ConfigurationAttribute
    }

    //Get the Binding objects for this Site
    BindingCollection bindings = site.Bindings;
    foreach (Microsoft.Web.Administration.Binding binding in bindings)
    {
        //put code here to work with each Binding
    }

    //retrieve the State of the Site
    ObjectState siteState = site.State;

    //Get the list of all Applications for this Site
    ApplicationCollection applications = site.Applications;
    foreach (Microsoft.Web.Administration.Application application in applications)
    {
        //put code here to work with each Application
    }
}

=> Getting Applications Associated with each Site
ApplicationCollection applications = site.Applications;
foreach (Microsoft.Web.Administration.Application application in applications)
{
    //get the name of the ApplicationPool
    string applicationPoolName = application.ApplicationPoolName;

    VirtualDirectoryCollection directories = application.VirtualDirectories;
    foreach (VirtualDirectory directory in directories)
    {
        //put code here to work with each VirtualDirectory
    }
}

=> Getting Virtual Directories Associated with each Application
VirtualDirectoryCollection directories = application.VirtualDirectories;
foreach (VirtualDirectory directory in directories)
{
    ConfigurationAttributeCollection attribues = directory.Attributes;
    foreach (ConfigurationAttribute attribute in attributes)
    {
        //put code here to work with each attribute
    }

    ConfigurationChildElementCollection childElements = directory.ChildElements;
    foreach (ConfigurationElement element in childElements)
    {
        //put code here to work with each ConfigurationElement
    }

    //get the directory.Path
    string path = directory.Path;

    //get the physical path
    string physicalPath = directory.PhysicalPath;
}

=> Getting Application Pools Associated with a Server

ServerManager server = new ServerManager();

ApplicationPoolCollection applicationPools = server.ApplicationPools;
foreach (ApplicationPool pool in applicationPools)
{
    //get the AutoStart boolean value
    bool autoStart = pool.AutoStart;

    //get the name of the ManagedRuntimeVersion
    string runtime = pool.ManagedRuntimeVersion;

    //get the name of the ApplicationPool
    string appPoolName = pool.Name;

    //get the identity type
    ProcessModelIdentityType identityType = pool.ProcessModel.IdentityType;

    //get the username for the identity under which the pool runs
    string userName = pool.ProcessModel.UserName;

    //get the password for the identity under which the pool runs
    string password = pool.ProcessModel.Password;
}

=> Creating a New Application Pool
ServerManager server = new ServerManager();

ApplicationPool myApplicationPool = null;

//we will create a new ApplicationPool named 'MyApplicationPool'
//we will first check to make sure that this pool does not already exist
//since the ApplicationPools property is a collection, we can use the Linq FirstOrDefault method
//to check for its existence by name
if (server.ApplicationPools != null && server.ApplicationPools.Count > 0)
{
    if (server.ApplicationPools.FirstOrDefault(p => p.Name == "MyApplicationPool") == null)
    {
        //if we find the pool already there, we will get a referecne to it for update
        myApplicationPool = server.ApplicationPools.FirstOrDefault(p => p.Name == "MyApplicationPool");
    }
    else
    {
        //if the pool is not already there we will create it
        myApplicationPool = server.ApplicationPools.Add("MyApplicationPool");
    }
}
else
{
    //if the pool is not already there we will create it
    myApplicationPool = server.ApplicationPools.Add("MyApplicationPool");
}

if (myApplicationPool != null)
{
    //for this sample, we will set the pool to run under the NetworkService identity
    myApplicationPool.ProcessModel.IdentityType = ProcessModelIdentityType.NetworkService;

    //we set the runtime version
    myApplicationPool.ManagedRuntimeVersion = "v4.0";

    //we save our new ApplicationPool!
    server.CommitChanges();
}

if (myApplicationPool != null)
{
    //for this sample, we will set the pool to run under the identity of a specific user
    myApplicationPool.ProcessModel.IdentityType = ProcessModelIdentityType.SpecificUser;
    myApplicationPool.ProcessModel.UserName = UserName;
    myApplicationPool.ProcessModel.Password = Password;

    //we set the runtime version
    myApplicationPool.ManagedRuntimeVersion = "v4.0";

    //we save our new ApplicationPool!
    server.CommitChanges();
}

=>Creating a New Site

ServerManager server = new ServerManager();

if (server.Sites != null && server.Sites.Count > 0)
{
    //we will first check to make sure that the site isn't already there
    if (server.Sites.FirstOrDefault(s => s.Name == "MySite") == null)
    {
        //we will just pick an arbitrary location for the site
        string path = @"c:\MySiteFolder\";

        //we must specify the Binding information
        string ip = "*";
        string port = "80";
        string hostName = "*";

        string bindingInfo = string.Format(@"{0}:{1}:{2}", ip, port, hostName);

        //add the new Site to the Sites collection
        Site site = server.Sites.Add("MySite", "http", bindingInfo, path);

        //set the ApplicationPool for the new Site
        site.ApplicationDefaults.ApplicationPoolName = myApplicationPool.Name;

        //save the new Site!
        server.CommitChanges();
    }
}

Export data into excel and storing location excel outside network - sql

Below for the query for the export excel but make sure your network path will be in sharing
"\\192.168.1.11\test\dir\"

Insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\192.168.1.11\test\dir\test.xls;',
'SELECT * FROM [SheetName$]') select * from SQLTable

Update a row and returning a value with one query - SQL

This will return single value which is updated
Inside can return multiple value also

You want the OUTPUT clause

UPDATE data SET count = count + 1
OUTPUT INSERTED.count
WHERE Id = @Id

Difference between CTE and Temp Table and Table Variable - sql server

TEMP 

Temp Table or Table variable or CTE are commonly used for storing data temporarily in SQL Server. In this article, you will learn the differences among these three.

CTE

CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike temporary table its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with semicolon.

A sub query without CTE is given below :

SELECT * FROM (

 SELECT Addr.Address, Emp.Name, Emp.Age From Address Addr

 Inner join Employee Emp on Emp.EID = Addr.EID) Temp

WHERE Temp.Age > 50

ORDER BY Temp.NAME

By using CTE above query can be re-written as follows 

;With CTE1(Address, Name, Age)--Column names for CTE, which are optional

AS

(

SELECT Addr.Address, Emp.Name, Emp.Age from Address Addr

INNER JOIN EMP Emp ON Emp.EID = Addr.EID

)

SELECT * FROM CTE1 --Using CTE 

WHERE CTE1.Age > 50

ORDER BY CTE1.NAME

When to use CTE

This is used to store result of a complex sub query for further use.

This is also used to create a recursive query.

Temporary Tables

In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-

Local Temp Table

Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash ("#") sign.

The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.

CREATE TABLE #LocalTemp

(

 UserID int,

 Name varchar(50), 

 Address varchar(150)

)

GO

insert into #LocalTemp values ( 1, 'Shailendra','Noida');

GO

Select * from #LocalTemp

Global Temp Table

Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.

CREATE TABLE ##GlobalTemp

(

 UserID int,

 Name varchar(50), 

 Address varchar(150)

)

GO

insert into ##GlobalTemp values ( 1, 'Shailendra','Noida');

GO

Select * from ##GlobalTemp

temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.

Table Variable

This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.

Note

Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.

CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.

Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory

Remove duplicate records from table - sql server

CREATE TABLE dbo.Employee
(
   EmpID int IDENTITY(1,1) NOT NULL, Name varchar(55) NULL, Salary decimal(10, 2) NULL,      Designation varchar(20) NULL
)


WITH TempEmp
 (
    Name,duplicateCount) AS ( SELECT Name,ROW_NUMBER() OVER(PARTITION by Name,         Salary ORDER BY Name) AS duplicateCount FROM dbo.Employee
)

--PARTITION  will  gives you duplicate records count
 --Now Delete Duplicate Records
DELETE FROM TempEmp WHERE duplicateCount > 1