Indexer In C#

Introduction
C# introduces a new concept known as Indexers which are used for treating an object as an array. The indexers are usually known as smart arrays in C# community. Defining a C# indexer is much like defining properties. We can say that an indexer is a member that enables an object to be indexed in the same way as an array.
{
get
{
// Get codes goes here
}
set
{
// Set codes goes here
}
}
Where the modifier can be private, public, protected or internal. The return type can be any valid C# types. The 'this' is a special keyword in C# to indicate the object of the current class. The formal-argument-list specifies the parameters of the indexer. The formal parameter list of an indexer corresponds to that of a method, except that at least one parameter must be specified, and that the ref and out parameter modifiers are not permitted. Remember that indexers in C# must have at least one parameter. Other wise the compiler will generate a compilation error.
The following program shows a C# indexer in action
using System;
using System.Collections;
class MyClass
{
private string []data = new string[5];
public string this [int index]
{
get
{
return data[index];
}
set
{
data[index] = value;
}
}
}
class MyClient
{
public static void Main()
{
MyClass mc = new MyClass();
mc[0] = "Mithilesh";
mc[1] = "C-125";
mc[2] = "Noida";
mc[3] = "U.P.";
mc[4] = "India";
Console.WriteLine("{0},{1},{2},{3},{4}",mc[0],mc[1],mc[2],mc[3],mc[4]);
}
}

Get Ip Address Of system in Asp.net

public partial class Login : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

protected void Button1_Click(object sender, EventArgs e)
{
Label1.Text = GetIP();
}
private string GetIP()
{
string strHostName = "";
strHostName = System.Net.Dns.GetHostName();

IPHostEntry ipEntry = System.Net.Dns.GetHostEntry(strHostName);

IPAddress[] addr = ipEntry.AddressList;

return addr[addr.Length - 1].ToString();

}
}

How to Deal Temporary Table

Introduction

SQL Server provides the 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 tempdb database.

In this article, I am just going to give a quick overview for beginners on those temporary tables. Please give your valuable suggestions and feedback to improve this article.
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

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.
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.
Creating Temporary Table in SQL Server 2005

As I have already discussed, there are two types of temporary tables available. Here I am going to describe each of them.
Local Temporary Table

The syntax given below is used to create a local Temp table in SQL Server 2005:
Collapse | Copy Code

CREATE TABLE #LocalTempTable(
UserID int,
UserName varchar(50),
UserAddress varchar(150))

The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a general table like:
Collapse | Copy Code

insert into #LocalTempTable values ( 1, 'Abhijit','India');

Now select records from that table:
Collapse | Copy Code

select * from #LocalTempTable

After execution of all these statements, if you close the query window and again execute "Insert" or "Select" Command, it will throw the following error:
Collapse | Copy Code

Msg 208, Level 16, State 0, Line 1
Invalid object name '#LocalTempTable'.

This is because the scope of Local Temporary table is only bounded with the current connection of current user.
Global Temporary Table

The scope of Global temporary table is the same for the entire user for a particular connection. We need to put "##" with the name of Global temporary tables. Below is the syntax for creating a Global Temporary Table:
Collapse | Copy Code

CREATE TABLE ##NewGlobalTempTable(
UserID int,
UserName varchar(50),
UserAddress varchar(150))

The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a general table like:
Collapse | Copy Code

insert into ##NewGlobalTempTable values ( 1, 'Abhijit','India');

Now select records from that table:
Collapse | Copy Code

select * from ##NewGlobalTempTable

Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.
Storage Location of Temporary Table

Temporary tables are stored inside the Temporary Folder of tempdb. Whenever we create a temporary table, it goes to Temporary folder of tempdb database.
TempTableLocation.JPG

Now, if we deeply look into the name of Local Temporary table names, a 'dash' is associated with each and every table name along with an ID. Have a look at the image below:
LocalTable.JPG - Click to enlarge image

SQL server does all this automatically, we do not need to worry about this; we need to only use the table name.
When to Use Temporary Tables?

Below are the scenarios where we can use temporary tables:

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.
When we are having a complex join operation.

Points to Remember Before Using Temporary Tables

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.

Alternative Approach: Table Variable

Alternative of Temporary table is the Table variable which can do all kinds of operations that we can perform in Temp table. Below is the syntax for using Table variable.
Collapse | Copy Code

Declare @TempTableVariable TABLE(
UserID int,
UserName varchar(50),
UserAddress varchar(150))

The below scripts are used to insert and read the records for Tablevariables:
Collapse | Copy Code

insert into @TempTableVariable values ( 1, 'Abhijit','India');

Now select records from that tablevariable:
Collapse | Copy Code

select * from @TempTableVariable

Bind Simple Listview in asp.net

on aspx.cs page

    protected void Page_Load(object sender, EventArgs e)
       {
           if (!Page.IsPostBack)
           {
               binddrop(); bindgrid();

               SqlConnection source_conn = new SqlConnection("Data Source=BSZ123\\SQLEXPRESS;Initial Catalog=Ecom_DB;Integrated Security=True");
               SqlCommand cmd = new SqlCommand("SELECT * FROM Cart", source_conn);
               da = new SqlDataAdapter(cmd);

            
               da.Fill(ds);
               list.DataSource = ds.Tables[0].DefaultView;
               list.DataBind();
            

           }
       }  
        




on aspx page

                
                    

                      ID
                  
                    

                      city
                  
                
                
                
            
      
    
      
     
       
        
              
        
        
              '>
        
        
        
      
      
     
        
              
        
        
              
        
        
      
      
      
No Data Present
      
    
  

Gridview new Row insert using commandbuilder object in ado.net

public DataSet dsa;
SqlDataAdapter da;
public DataSet ds=new DataSet();
public DataSet ds1;
UserRegistration Obj = new UserRegistration();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
//binddrop();
bindgrid();
}
}



public void bindgrid()
{
SqlConnection source_conn = new SqlConnection("Data Source=BSZ123\\SQLEXPRESS;Initial Catalog=Ecom_DB;Integrated Security=True");
SqlCommand cmd = new SqlCommand("SELECT * FROM emp", source_conn);
da = new SqlDataAdapter(cmd);


da.Fill(ds);
GridView1.DataSource = ds.Tables[0].DefaultView;
DataBind();
// ViewState["ad"] = da;
ViewState["chan"] = ds;
}



protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
//bindgrid();
if (e.CommandName == "add")
{
GridView1.ShowFooter = true;

bindgrid();


}
else if (e.CommandName == "insert")
{
//postbind();
//GridView1.DataBind();

//da = ((SqlDataAdapter)(ViewState["ad"]));
SqlConnection source_conn = new SqlConnection("Data Source=BSZ123\\SQLEXPRESS;Initial Catalog=Ecom_DB;Integrated Security=True");
SqlCommand cmd = new SqlCommand("SELECT * FROM emp", source_conn);
da = new SqlDataAdapter(cmd);


da.Fill(ds);
//ds=((DataSet)(ViewState["chan"]));
DataRow dr = ds.Tables[0].NewRow();

dr[0] = Convert.ToInt32( (((TextBox)GridView1.FooterRow.FindControl("TextBox4")).Text));
dr[1] = ((TextBox)GridView1.FooterRow.FindControl("TextBox40")).Text;
dr[2] = ((TextBox)GridView1.FooterRow.FindControl("TextBox41")).Text;


ds.Tables[0].Rows.Add(dr);


SqlCommandBuilder ba = new SqlCommandBuilder(da);
da.Update(ds.Tables[0]);
GridView1.DataSource = ds.Tables[0].DefaultView;
DataBind();


//bindgrid();



}
GridView1.ShowFooter = false;

}

___________ .aspx page code

__________-snapshot

Use of SqlBulkcopy for BulkCopy with ado.net

///code of copy all data of emp table in other table named emp1

on button click{
SqlConnection source_conn = new SqlConnection("Data Source=BSZ123\\SQLEXPRESS;Initial Catalog=Ecom_DB;Integrated Security=True");

SqlConnection destination_conn = new SqlConnection("Data Source=BSZ123\\SQLEXPRESS;Initial Catalog=Ecom_DB;Integrated Security=True");



SqlCommand cmd = new SqlCommand("SELECT * FROM emp", source_conn);
source_conn.Open();
destination_conn.Open();


SqlDataReader reader = cmd.ExecuteReader();
// Create SqlBulkCopy

SqlBulkCopy bulkData = new SqlBulkCopy(destination_conn);
// Set destination table name

bulkData.DestinationTableName = "";
// Write data

bulkData.WriteToServer(reader);
// Close objects

bulkData.Close();
destination_conn.Close();
source_conn.Close();
}

What is Normalization and denormalization

Normalization

• It is the process of organizing data into related table.
• To normalize database, we divide database into tables and establish relationships between the tables.
• It reduces redundancy. It is done to improve performance of query.

Steps of normalization:

First Normal form
Entities of the table must have unique identifier or entity key.
Second Normal Form
All the attributes of the table must depend on the entity key for that entity.
Third Normal Form
All attributes that are not part of the key must not depend on any other non-key attributes.

De-normalization

The process of adding redundant data to get rid of complex join, in order to optimize database performance. This is done to speed up database access by moving from higher to lower form of normalization.

Example:----

Rules for First Normal Form (1NF)

Eliminate repeating groups. This table contains repeating groups of data in the Software column.

Computer	Software
1	Word
2	Access, Word, Excel
3	Word, Excel

To follow the First Normal Form, we store one type of software for each record.

Computer	Software
1	Word
2	Access
2	Word
3	Excel
3	Word
3	Excel

Rules for second Normal Form (2NF)

Eliminate redundant data plus 1NF. This table contains the name of the software which is redundant data.

Computer	Software
1	Word
2	Access
2	Word
3	Excel
3	Word
3	Excel

To eliminate the redundant storage of data, we create two tables. The first table stores a reference SoftwareID to our new table that has a unique list of software titles.

Computer	SoftwareID
1	1
2	2
2	1
3	3
3	1
3	3

SoftwareID	Software
1	Word
2	Access
3	Excel

Rules for Third Normal Form (3NF)

Eliminate columns not dependent on key plus 1NF and 2NF. In this table, we have data that contains both data about the computer and the user.

Computer	User Name	User Hire Date	Purchased
1	Joe	4/1/2000	5/1/2003
2	Mike	9/5/2003	6/15/2004

To eliminate columns not dependent on the key, we would create the following tables. Now the data stored in the computer table is only related to the computer, and the data stored in the user table is only related to the user.

Computer	Purchased
1	5/1/2003
2	6/15/2004

User	User Name	User Hire Date
1	Joe	5/1/2003
2	Mike	6/15/2004

Computer	User
1	1
2	1

What does normalization have to do with SQL Server?

To be honest, the answer here is nothing. SQL Server, like any other RDBMS, couldn't care less whether your data model follows any of the normal forms. You could create one table and store all of your data in one table or you can create a lot of little, unrelated tables to store your data. SQL Server will support whatever you decide to do. The only limiting factor you might face is the maximum number of columns SQL Server supports for a table.

SQL Server does not force or enforce any rules that require you to create a database in any of the normal forms. You are able to mix and match any of the rules you need, but it is a good idea to try to normalize your database as much as possible when you are designing it. People tend to spend a lot of time up front creating a normalized data model, but as soon as new columns or tables need to be added, they forget about the initial effort that was devoted to creating a nice clean model.

To assist in the design of your data model, you can use the DaVinci tools that are part of SQL Server Enterprise Manager.

Advantages of normalization

1. Smaller database: By eliminating duplicate data, you will be able to reduce the overall size of the database.
2. Better performance:

a. Narrow tables: Having more fine-tuned tables allows your tables to have less columns and allows you to fit more records per data page.
b. Fewer indexes per table mean faster maintenance tasks such as index rebuilds.
c. Only join tables that you need.

Disadvantages of normalization

1. More tables to join: By spreading out your data into more tables, you increase the need to join tables.
2. Tables contain codes instead of real data: Repeated data is stored as codes rather than meaningful data. Therefore, there is always a need to go to the lookup table for the value.
3. Data model is difficult to query against: The data model is optimized for applications, not for ad hoc querying.

Use Of Try and Catch in sql server

Use of Try and Catch i n sql server

SQL Server 2005 has a number of new system error functions for returning error information. These new functions can be used in a CATCH block to help identify why an error occurred. Below is a list of the error functions available to CATCH blocks in SQL Server 2005, and the information each of these functions return:

ERROR_NUMBER(): Returns a number associated with the error.

ERROR_SEVERITY(): Returns the severity of the error.

ERROR_STATE(): Returns the error state number associated with the error.

ERROR_PROCEDURE(): Returns the name of the stored procedure or trigger in which the error occurred.

ERROR_LINE(): Returns the line number inside the failing routine that caused the error.

ERROR_MESSAGE(): Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

These error functions can only be used within the scope of a CATCH block. If these functions are used outside a CATCH block, they will return a NULL value.

Examples:--by mithilesh

--just try to enter value in cart table which CartId column is primary key ,,,
--CartId 10 is already present in Cart table

begin try

insert into Cart(CartId) values(10)

end try

begin catch

select Error_message(),error_line(),error_number(),error_severity()

end catch

output:-

PRIMARY KEY constraint 'PK_Cart'.not insert duplicate key in object 'dbo.Cart'. 4 2627 14

/// using divide by zero error

BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

output:--

8134 16 1 NULL 3 Divide by zero error encountered.

Different type of Date Time Format

//////////diffrent type of datetime format



DateTime dt = new DateTime(2008, 3, 9, 16, 5, 7, 123); String.Format("{0:d dd ddd dddd}", dt); // "9 09 Sun Sunday" day String.Format("{0:h hh H HH}", dt); // "4 04 16 16" hour 12/24 String.Format("{0:m mm}", dt); // "5 05" minute String.Format("{0:s ss}", dt); // "7 07" second
String.Format("{0:y yy yyy yyyy}", dt); // "8 08 008 2008" year String.Format("{0:t tt}", dt); // "P PM" A.M. or P.M.
String.Format("{0:M MM MMM MMMM}", dt); // "3 03 Mar March" month
// month/day numbers without/with leading zeroes String.Format("{0:M/d/yyyy}", dt); // "3/9/2008" String.Format("{0:MM/dd/yyyy}", dt); // "03/09/2008"
String.Format("{0:f ff fff ffff}", dt); // "1 12 123 1230" sec.fraction String.Format("{0:z zz zzz}", dt); // "-6 -06 -06:00" time zone // day/month names String.Format("{0:ddd, MMM d, yyyy}", dt); // "Sun, Mar 9, 2008" String.Format("{0:dddd, MMMM d, yyyy}", dt); // "Sunday, March 9, 2008" // two/four digit year String.Format("{0:MM/dd/yy}", dt); // "03/09/08" String.Format("{0:F FF FFF FFFF}", dt); // "1 12 123 123" without zeroes
String.Format("{0:MM/dd/yyyy}", dt); // "03/09/2008"