ASP.NET

How to use mode "Windows"?

Change the authentication mode to Windows.

Windows Authentication mode provides the developer to authenticate a user based on Windows user accounts. This is the default authentication mode provided by ASP.Net. You can easily get the Identity of the user by using User.Identity.Name. This will return the computer name along with the user name. Windows authentication also provides IsInRole method to find the role of the user and than you can give permissions to the user depending on the role.

<authentication mode="Windows">

  <forms name=" AuthenticationDemo" loginUrl="logon.aspx" protection="All" path="/" timeout="30"/>

</authentication>

Deny access to the anonymous user in the <authorization> section as follows:

<authorization>

     <deny users ="?" />

    <allow users = "*" />

</authorization>

Other you can make a special client to access you project with windows authentication. Code like this (this case you can get value using 'User.Identity.Name', then you can use it to do other work you like.):

<authorization>

     <deny users ="?" />

</authorization>

How to use mode "Forms"?

Change the authentication mode to Forms.

Insert the <Forms> tag, and fill the appropriate attributes. (For more information about these attributes, refer to the MSDN documentation)

First you should specify a page and make sure all clients can found it. Code like this

<authentication mode="Forms">

    <forms name=" AuthenticationDemo" loginUrl="logon.aspx" protection="All" path="/" timeout="30"/>

</authentication>

Deny access to the anonymous user in the <authorization> section as follows:

<authorization>

    <deny users ="?" />

</authorization>

Second in that page you to validate the user's Id and Password. Code like this:

You can use one of two methods to generate the forms authentication cookie and redirect the user to an appropriate page in the cmdLogin_ServerClick event. Sample code is provided for both scenarios. Use either of them according to your requirement.

(1). Call the RedirectFromLoginPage method to automatically generate the forms authentication cookie and redirect the user to an appropriate page in the cmdLogin_ServerClick event:

private void cmdLogin_ServerClick(object sender, System.EventArgs e)

{

     If (ValidateUser(txtUserName.Value,txtUserPass.Value) )

     {

          FormsAuthentication.RedirectFromLoginPage(txtUserName.Value, false);

     }

     else

     {

          Response.Redirect("logon.aspx", true);

     }

}

(2). Generate the authentication ticket, encrypt it, create a cookie, add it to the response, and redirect the user. This gives you more control in how you create the cookie. You can also include custom data along with the FormsAuthenticationTicket in this case.

Private void cmdLogin_ServerClick(object sender, System.EventArgs e)

{

    if (ValidateUser(txtUserName.Value,txtUserPass.Value) )

    {

        FormsAuthenticationTicket tkt;

        string cookiestr;

        HttpCookie ck;

        tkt = new FormsAuthenticationTicket(1, txtUserName.Value, DateTime.Now,

DateTime.Now.AddMinutes(30), chkPersistCookie.Checked, "your custom data");

        cookiestr = FormsAuthentication.Encrypt(tkt);

        ck = new HttpCookie(FormsAuthentication.FormsCookieName, cookiestr);

       

        if (chkPersistCookie.Checked)

        ck.Expires=tkt.Expiration;   

        ck.Path = FormsAuthentication.FormsCookiePath;

        Response.Cookies.Add(ck);

        string strRedirect;

        strRedirect = Request["ReturnUrl"];

       

        if (strRedirect==null)

        strRedirect = "default.aspx";

        Response.Redirect(strRedirect, true);

    }

    else

    Response.Redirect("logon.aspx", true);

}

Windows Authentication 

Windows Authentication provider is the default authentication provider for ASP.NET applications. When a user using this authentication logs in to an application, the credentials are matched with the Windows domain through IIS. 

There are 4 types of Windows Authentication methods: 
1) Anonymous Authentication - IIS allows any user 
2) Basic Authentication - A windows username and password has to be sent across the network (in plain text format, hence not very secure). 
3) Digest Authentication - Same as Basic Authentication, but the credentials are encrypted. Works only on IE 5 or above 
4) Integrated Windows Authentication - Relies on Kerberos technology, with strong credential encryption 

Forms Authentication - This authentication relies on code written by a developer, where credentials are matched against a database. Credentials are entered on web forms, and are matched with the database table that contains the user information. 
-----------------------------------------------------------------------------------

• Session is used to store per-user information for the current Web session on the server. It supports using a database server as the back-end store.
• Cookie should be used to store per-user information for the current Web session or persistent information on the client, therefore client has control over the contents of a cookie.
• Cache object is shared between users in a single application. Its primary purpose is to cache data from a data store and should not be used as a primary storage. It supports automatic invalidation features.
• Application object is shared between users to store application-wide state and should be used accordingly.

Cookies:

There two type of cookies in ASP.NET

 Persistent cookies:

cookies are stored on your computer hard disk. They stay on your hard disk and can be accessed by web servers until they are deleted or have expired.

Non-persistent cookies:

cookies are saved only while your web browser is running. They can be used by a web server only until you close your browser. They are not saved on your disk.

------------------------------------------------------------------------------------

The default Global.asax file template includes five methods within a server-side <script> tag:

• Application_Start executes when the web application first starts
• Application_End runs when the application is shutting down
• Application_Error executes whenever an unhandled exception reaches the application
• Session_Start executes when a new session is created
• Session_End runs when a session is expired or abandoned

-------------------------------------------------------------------------------------

Difference between response.redirect and server.transfer 

Response.Redirect should be used when:

• we want to redirect the request to some plain HTML pages on our server or to some other web server
• we don't care about causing additional roundtrips to the server on each request
• we do not need to preserve Query String and Form Variables from the original request
• we want our users to be able to see the new redirected URL where he is redirected in his browser (and be able to bookmark it if its necessary)

Server.Transfer should be used when:

• we want to transfer current page request to another .aspx page on the same server
• we want to preserve server resources and avoid the unnecessary roundtrips to the server
• we want to preserve Query String and Form Variables (optionally)
• we don't need to show the real URL where we redirected the request in the users Web Browser
  ---------------------------------------------------------------------------------------------------------------------
  
  

  What is Data Cache?

  Data Cache is used to storing frequently used data in the Cache memory. It's much efficient to retrieve data from the data cache instead of database or other sources. We need use System.Web.Caching namespace. The scope of the data caching is within the application domain unlike "session". Every user can able to access this objects.

  How data caching is work?

  When client request to the server, server execute the stored procedure or function or select statements on the Sql Server database then it returns the response to the browser. If we run again same process will happen on the web server with sql server.

  In the very first time retrieve the records from the sql server database and stored into the Cache memory.  After that records will retrieve from Cache memory (IIS). We can set expiration period time after expiration time over the caching again it will hit the sql server database and stored into the cache memory and next retrieve will happen on the data cache memory.

  Advantages of Cookies

  Following are main advantages of using cookies in web application:

  • It's very simple to use and implement.
  • We can avoid database hitting.
  • We can reduce network traffic.

  How to create data cache? 

  Cache["Employee"] = "DataSet Name"

  We can create data caching use Cache Keyword. It's located in the System.Web.Caching namespace. It's just like assigning value to the variable

  How to Read data from Data Cache?

  Dataset dsEmployee = (Dataset) Cache ["Employee"]

  This is very similar to assigning object to the Dataset.

  Where does Data Cache are stored?

  This is one of the interesting things to find out the Data Caching in your local drive. First of all, From "IE Internet Option ", delete Browsing History to clear Cache. 

  How to remove a Data Cache? 

  We can remove Data Cache manually.
  

  //We need to specify the cache name    Cache.Remove(String key);

  Example:

  TimeSpan ts = new TimeSpan(0, 0, 10);        //If cache has null, it will create two cache and it will bind into the gridview        if (Cache["Employee"] == null) {            dtEmployee = new DataTable("Employee"); dtEmployee.Columns.Add("EmpID", typeof(int)); dtEmployee.Columns.Add("EmpName", typeof(string)); DataRow rs = dtEmployee.NewRow(); rs[0] = 10; rs[1] = "Annathurai"; dtEmployee.Rows.Add(rs); //To assign datatable to cache memory.            Cache["Employee"] = dtEmployee; Cache.Insert("Employee", dtEmployee, null, Cache.NoAbsoluteExpiration, ts); Response.Write("Its processing with Database hit");       }       else       {           //If cache has value, It retrive from cache memory and bind into the gridview           Response.Write("Its processing from cache");       } /Here we are converting cache into datatable        GridView1.DataSource = (DataTable)Cache["Employee"];       GridView1.DataBind();

Upload CSV file to SQL server (Bulk Upload)

In general you can achieve it in two steps

1) step is to read the CSV file and hold it as a DataTable.
2) step Store the retrieved file into SQL Table as a Bulk Entry

This is a function that returns CSV File Data as a Datatable. call and get it, and in the end do whatever you want

This function is going to return CSV Read file into DataTable.

private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
    {
        DataTable csvData = new DataTable();
        try
        {
          using(TextFieldParser csvReader = new TextFieldParser(csv_file_path))
             {
                csvReader.SetDelimiters(new string[] { "," });
                csvReader.HasFieldsEnclosedInQuotes = true;
                string[] colFields = csvReader.ReadFields();
                foreach (string column in colFields)
                {
                    DataColumn datecolumn = new DataColumn(column);
                    datecolumn.AllowDBNull = true;
                    csvData.Columns.Add(datecolumn);
                }
                while (!csvReader.EndOfData)
                {
                    string[] fieldData = csvReader.ReadFields();
                    //Making empty value as null
                    for (int i = 0; i < fieldData.Length; i++)
                    {
                        if (fieldData[i] == "")
                        {
                            fieldData[i] = null;
                        }
                    }
                    csvData.Rows.Add(fieldData);
                }
            }
        }
        catch (Exception ex)
        {
        }
        return csvData;    
  }

SQLBulkCopy - Use this function to insert the Retrieved DataTable into Sql Table

protected void InsertDataIntoSQLServerUsingSQLBulkCopy(DataTable csvFileData)
{    
         Con.Open();
         using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
         {
             s.DestinationTableName = "Your table name";
             foreach (var column in csvFileData.Columns)
             s.ColumnMappings.Add(column.ToString(), column.ToString());
             s.WriteToServer(csvFileData);
         }
        Con.Close();
 }

Ref:http://www.morgantechspace.com/2013/10/import-csv-file-into-sql-server-using.html

MY.NET Tutorials with different sample code and interview question tips

http://www.msdotnet.co.in/2014/01/how-to-use-web-services-in-aspnet.html#.U1DNFNE4fct

Read XML Value using html Agility pack, linq in c#

  private void getxmlvalue()
    {

        string[] xmlpath = Directory.GetFiles(fileLocation + "XML" + "/" );
        string id= string.Empty,  FileName = string.Empty, Extension = string.Empty,
        Authorname = string.Empty;;
        for (int i = 0; i <= xmlpath.Length - 1; i++)
        {
            FileInfo file = new FileInfo(xmlpath[i]);
            FileName = file.Name;
            Extension = file.Extension;
            if (Extension == ".xml")
            {
                StreamReader str = new StreamReader(xmlpath[i]);
                string strLings = str.ReadToEnd();
                str.Close();
                HtmlAgilityPack.HtmlDocument htmlDoc = new HtmlAgilityPack.HtmlDocument();
                htmlDoc.OptionFixNestedTags = true;
                htmlDoc.LoadHtml(strLings);
                if (htmlDoc.DocumentNode != null)
                {
                    try
                    {
                        if (strLings.Contains("id"))
                        {
                            foreach (HtmlNode nodepage in htmlDoc.DocumentNode.SelectNodes("//id"))
                            {
                                if (nodepage != null)
                                {
                                    id = nodepage.InnerHtml.Trim();
                                }
                            }
                        }
                        if (strLings.Contains("authername"))
                        {
                            foreach (HtmlNode nodepage in htmlDoc.DocumentNode.SelectNodes("//authername"))
                            {
                                if (nodepage != null)
                                {
                                    string lang = nodepage.GetAttributeValue("lang", "");
                                    if (lang == "EN")
                                    {
                                        Authorname += nodepage.InnerText.ToString() + "|";
                                    }
                                }
                                //  Authorname = Authorname.Remove(Authorname.LastIndexOf(";"), 1);
                            }
                        }
                       //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Read XML value from nested parent node and child node element

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
if (strLings.Contains("body"))
                        {
                            if (strLings.Contains("book-part"))
                            {
                                foreach (HtmlNode node1 in htmlDoc.DocumentNode.SelectNodes("//book-part"))
                                {
                                    strBook_ID = node1.GetAttributeValue("id", "");

                                    HtmlNode node2 = node1.SelectSingleNode(".//title");
                                    if (node2 != null)
                                        strTitle = node2.InnerHtml;

                                    HtmlNode node3 = node1.SelectSingleNode(".//volume-id"); // take current value
                                    if (node3 != null)
                                        strDOI = node3.InnerHtml;

                                  ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

                        DataRow dr = dtup.NewRow();
                        // Add value to datatable
                        dtup.Rows.Add(dr);            
                        id = "";                      
                        Authorname = "";
                     
                    }
                    catch (Exception ex)
                    {
                      ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "", "alert('Error Occur in getting value from XML File');", true);
                    }
                    finally
                    {
                    }
                }
            }
        }
       // save the value to database using table value parameter
    }

--------------------------------------------------------------------------------------------------

using LINQ :

<University>
  <School SchoolId="1" SchoolName="Languages">
    <Subjects>
      <Subject Id="1" SubjectName="English"/>
      <Subject Id="2" SubjectName="French"/>
      <Subject Id="3" SubjectName="Italian"/>
      <Subject Id="4" SubjectName="Spanish"/>
    </Subjects>
  </School>
  <School SchoolId="2" SchoolName="Sciences">
    <Subjects>
      <Subject Id="1" SubjectName="Biology"/>
      <Subject Id="2" SubjectName="Physics"/>
    </Subjects>
  </School>
</University>

Solution:

string fileLocation = WebConfigurationManager.AppSettings["FileUPDN"].ToString();
        string xmlpath = fileLocation + "XML" + "/" + Convert.ToString(txtxmlname.Text)
        XmlReader reader = XmlReader.Create(xmlpath[i]);
        XElement el = XElement.Load(reader);
        reader.Close();
            var items_list = from item in el.Elements("University").Descendants()
                            where (item.Name == "School")
                            select item;
        foreach (var node in items_list)
            {
                schoolId = (int)node .Attribute("SchoolId");
                schoolName = (string)node .Attribute("SchoolName");
                subjects = from subject in node .Element("Subjects").Elements("Subject")
                                       select (string)subject.Attribute("SubjectName");
            }

select into Temp table from dynamic sql

set @SQL=('SELECT * into #temp FROM TableName; SELECT * FROM #TEMP')
exec(@SQL)

or

set @SQL=('SELECT * into ##temp FROM TableName')
select * from ##temp
Drop Table  ##temp

or

CREATE TABLE #temp (iid int, strName varchar(100), strAge varchar(100), str varchar(100), strTitle varchar(100))

exec('insert into #temp SELECT * FROM tbl_XML_Test')

select * from #temp

drop table #temp

Note: we can, t use table variable  instead of temp table for above query.

Order By Day of Week

ORDER BY case [Day_Of_Week] when 'Monday' then 1
when 'Tuesday' then 2
when 'Wednesday' then 3
when 'Thursday' then 4
when 'Friday' then 5
when 'Saturday' then 6
when 'Sunday' then 7
else 8 end

SQL Server- View, temp table, variable table and Functions

View:

It is a virtual table.

Scenarios for Using Views:

Views can be used as security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view.

To Focus on Specific Data:
Views allow users to focus on specific data that interests them and on the specific tasks for which they are responsible. Unnecessary data can be left out of the view. This also increases the security of the data because users can see only the data that is defined in the view and not the data in the underlying table

Views allow different users to see data in different ways, even when they are using the same data concurrently.

Advantages of views:

1. View the data without storing the data into the object.

2. Restict the view of a table i.e. can hide some of columns in the tables.

3. Join two or more tables and show it as one object to user.

4. Restict the access of a table so that nobody can insert the rows into the table.

5. We can join Temp table and view , physical table

Disadvatages:

1. Can not use DML operations on this. (we have used more then one table when creating view)

2. When table is dropped view becomes inactive.. it depends on the table objects.

3. It is an object, so it occupies space.

4. we can't  create clustrered index

5. we can't create nonclustered index without create unique clustred index

6. we can't create index without schema binding

6. we can't create view with schema binding, without using dbo.tablename instead of tablename.

7. we can't create constraints for view

8. we drop unique clustered index, non clustered index also droped.

------------------------------------------------------------------------------------------------------------

Variable table and Temp Table

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.

1. we can create primary key
2. we can create indentity column
3. we can't create non clustered index
4. Table variables are not transactional
5. we can join with temp table or normal table or view
6.Maximum 128 characters.
7. collation- String columns inherit collation from current database.
8. index- Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the    DECLARE statement.
9. Constraints - PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the        creation of the table in    the DECLARE statement. FOREIGN KEY not allowed.
10. Post-creation DDL (indexes, columns) - Statements are not allowed.
11. Data insertion- INSERT statement (SQL 2000: cannot use INSERT/EXEC).
12. Truncate table- Not allowed. Allowed.
13. Rollbacks- Not affected (Data not rolled back).
14. Dynamic SQL Must declare table variable inside the dynamic SQL.
15. Can't define globally

Temp Table

1. we can create all constraints include identity column
2. temp tables are transactional and do roll back.
3. we can join with temp table or normal table or view
4. Maximum 116 characters.
5. collation-String columns inherit collation from tempdb database.
6. index- Indexes can be added after the table has been created.
7. Constraints -RIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after    the table has been created. FOREIGN KEY not allowed.
8. Post-creation DDL (indexes, columns) - Statements are allowed.
9. Data insertion- INSERT statement, including INSERT/EXEC.SELECT INTO statement.
10. Truncate table  Allowed.
11. Rollbacks- Affected (Data is rolled back).
12. Dynamic SQL-Can use temporary tables created prior to calling the dynamic sql.
13. Can't define globally

Reference:

• http://www.codeproject.com/Articles/415184/Table-Variable-V-S-Temporary-Table
• http://www.sqlserver-training.com/why-to-use-temporary-table-over-table-variable/-
• http://www.dotnet-tricks.com/Tutorial/sqlserver/X517150913-Difference-between-CTE-and-Temp-Table-and-Table-Variable.html

------------------------------------------------------------------------------------------------------------
Function:

1. input  must be used
2. can't create temp table or normal table with in function
3. we can create table variable with in function
4. DDL and DML operation canot not supported
5. we must return any value.

Limitations:

1. We cannot use temporary tables inside the UDF.
2. We cannot use Print command inside the UDF.
3. We can't use the Insert, update and delete command inside the function. We can only use these commands on the table variable(not physical table) defined inside the function.
4. We cannot use the try catch statement inside the function for the debugging purpose which makes it difficult to debug.
5. We can't use transaction inside the function.
6. We can't call the stored procedure from inside the function.
7. We can't return multiple record sets from a function as we can do in case of Stored Procedure.
8. Also performance of a SQL Function is low as compared to a stored procedure.
9. So if we can get the same result through a stored procedure, we should write a stored procedure instead of SQL Function.

-------------------------------------------------------------------------------

Basic Difference between Strored procedure and Functions

• Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
• Functions can have only input parameters for it whereas Procedures can have input/output parameters .
• Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters..

• Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference between Strored procedure and Functions

• Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
• Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
• Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
• Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables
• Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
• Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
• We can go for Transaction Management in Procedure whereas we can't go in Function.