Wednesday, July 9, 2014

Create DataTable dynamically and bind to GridView in ASP.Net

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="grdview.aspx.cs" Inherits="grdview" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   <asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
    runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Id" HeaderText="Id" ItemStyle-Width="30" />
        <asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" />
        <asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />
    </Columns>
</asp:GridView>
    </div>
    </form>
</body>
</html>

 ---------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

public partial class grdview : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                            new DataColumn("Name", typeof(string)),
                            new DataColumn("Country",typeof(string)) });
            dt.Rows.Add(1, "Naresh", "India");
            dt.Rows.Add(2, "XXX", "India");
            dt.Rows.Add(3, "AAAA", "France");
            dt.Rows.Add(4, "MMMMM", "Russia");
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
    }
 
}


Displaying Column Data in Gridview With new line Separated By Symbol like ., @,&,#


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="grid.aspx.cs" Inherits="grid" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="grd" AutoGenerateColumns="false" runat="server" 
            DataKeyNames="description"
            onrowdatabound="Unnamed1_RowDataBound">
            <Columns>
                <asp:TemplateField HeaderText="id">
                    <ItemTemplate>
                        <asp:Label ID="lbl" runat="server" Text='<%#bind("id") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="fname">
                    <ItemTemplate>
                        <asp:Label ID="lbl" runat="server" Text='<%#bind("fname") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="lname">
                    <ItemTemplate>
                        <asp:Label ID="lbl" runat="server" Text='<%#bind("lname") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Description">
                    <ItemTemplate>
                        <%# Eval("description").ToString().Replace("&","</br>")%>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </div>
    </form>
</body>
</html>
 -----------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

public partial class grid : System.Web.UI.Page
{
    string xxx = ConfigurationManager.ConnectionStrings["aaa"].ToString();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            SqlConnection con = new SqlConnection(xxx);
            SqlCommand cmd = new SqlCommand("select * from Des");
            cmd.Connection = con;
            SqlDataAdapter dap = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            dap.Fill(ds);
            grd.DataSource = ds;
            grd.DataBind();
            //return ds;
           
        }
    }

 
}



ADO.NET Data Containers

DataAdapter
In ADO.NET, the data adapter object acts as a two-way bridge between a data source and the DataSet object. 
The DataSet is a disconnected container of data, and the adapter takes care of filling it and submitting its data back to a particular data source. 
From an abstract point of view, a data adapter is similar to a command and represents another way of executing a command against the data source. 
The big difference between commands and data adapters is just in the way each one returns the retrieved data. A query command returns a read-only, forward-only cursor—the data reader. The data adapter performs its data access, grabs all the data, and packs it into an in-memory  container—the DataSet or DataTable. 
DataSet
The dataset is a disconnected, in-memory representation of data. It can be considered as a local copy of the relevant portions of the database. The DataSet is persisted in memory and the data in it can be manipulated and updated independent of the database. When the use of this DataSet is finished, changes can be made back to the central database for updating. The data in DataSet can be loaded from any valid data source like Microsoft SQL server database, an Oracle database or from a Microsoft Access database.
Inside a DataSet, much like in a database, there are tables, columns, relationships, constraints, views, and so forth.
The ADO.NET DataSet is the core component of the disconnected architecture of ADO.NET. The DataSet is explicitly designed for data access independent of any data source. As a result it can be used with multiple and differing data sources, used with XML data, or used to manage data local to the application. The DataSet contains a collection of one or more DataTable objects made up of rows and columns of data, as well as primary key, foreign key, constraint, and relation information about the data in the DataTable objects.

Command Builder & DataTable

The ADO.NET object model not only allows you to define your own updating logic, but it also provides a dynamic updating logic generation similar to that of the ADO cursor engine, using the CommandBuilder object.  If you instantiate a CommandBuilder object and associate it with a DataAdapter object, the CommandBuilder will attempt to generate updating logic based on the query contained in the DataAdapter object’s SelectCommand.
The CommandBuilder can generate updating logic if all the following are true:
·          Your query returns data from only one table
·          That table has a primary key
·          The primary key is included in the results of your query
The disadvantages with using the CommandBuilder are: 
 ·          It doesn’t offer the best possible run-time performance.
·          You can supply your own updating logic in code in less time than it takes the CommandBuilder to request and process the metadata required to generate similar updating logic.
·          The CommandBuilder doesn’t offer options to let you control the updating logic that is generated.
·          You can’t specify the type of optimistic concurrency you want to use. 
A CommandBuilder will not help you submit updates using stored procedures.

Command Builder Example:
public static DataSet SelectSqlRows(string connectionString,
    string queryString, string tableName)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand(queryString, connection);
        SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

        connection.Open();

        DataSet dataSet = new DataSet();
        adapter.Fill(dataSet, tableName);

        //code to modify data in DataSet here

        builder.GetUpdateCommand();

        //Without the SqlCommandBuilder this line would fail
        adapter.Update(dataSet, tableName);

        return dataSet;
    }
}

A DataSet object is made up of a collection of tables, relationships, and constraints.  In ADO.NET, DataTable objects are used to represent the tables in a DataSet object.  A DataTable object represents one table of in-memory relational data.  The data is local to the .NET application in which it resides, however, can be populated from a data source such as SQL Server or VFP using a DataAdapter.
You can create and use a DataTable independently or as a member of a DataSet object.  DataTable objects can then be used by other .NET Framework objects, including the DataView object.  Access the collection of tables in a DataSet object through the DataSet object’s Tables property.
The schema, or structure, of a table is represented by columns and constraints.  Define the schema of a DataTable object using DataColumn objects, as well as ForeignKeyConstraint and UniqueConstraint objects.  The columns in a table can map to columns in a data source, contain calculated values from expressions, automatically increment their values, or contain primary key values.
If you populate a DataTable object from a database, it will inherit the constraints from the database so you do not have to do all of the work manually.  A DataTable object must also have rows in which to contain and order the data.  The DataRow class represents the actual data contained in the table.  As you access and change the data within a row, the DataRow object maintains both its current and original state.
You can create parent/child relationships between tables within a database, like SQL Server and VFP, using one or more related columns in the tables.  You can create a relationship between DataTable objects using a DataRelation object, which may then be used to return a row’s related child or parent rows.
DataTable Example:
  // Create a DataTable with 5 columns.
    //
    DataTable table = new DataTable();
    table.Columns.Add("Weight", typeof(int));
    table.Columns.Add("Name", typeof(string));
    table.Columns.Add("Breed", typeof(string));
    table.Columns.Add("Size", typeof(char));
    table.Columns.Add("Date", typeof(DateTime));

    //
    // Add data to the DataTable. [This will be dyanmically generated from your app.]
    //
    AddDogRow(table, 57, "Koko", "Shar Pei");
    AddDogRow(table, 130, "Fido", "Bullmastiff");
    AddDogRow(table, 92, "Alex", "Anatolian Shepherd Dog");
    AddDogRow(table, 25, "Charles", "Cavalier King Charles Spaniel");
    AddDogRow(table, 7, "Candy", "Yorkshire Terrier");
/// <summary>
/// Add dog data to the DataTable.
/// </summary>
static DataRow AddDogRow(DataTable table, int weight, string name, string breed)
{
    //
    // This method uses custom code to generate the size type.
    //
    return table.Rows.Add(weight, name, breed, GetSizeChar(weight), DateTime.Now);
}

/// <summary>
/// Get size code for dogs by weight.
/// </summary>
static char GetSizeChar(int weight)
{
    //
    // Custom method for getting size code.
    //
    if (weight > 100)
    {
       return 'B';
    }
    else if (weight > 50)
    {
       return 'M';
    }
    else
    {
       return 'S';
    }
}

Serialization in C#

The serialization and its uses are as follows.
Serialization is the process of converting an object into stream of bytes in order to store the object in a file, Memory, Database and in transferring the object over the network. The reverse process is called as De-Serialization. Please find the below Fig.
In order to achieve this serialization, the class should be encapsulated with [Serializable] attribute. Without this attribute, “Serialization” is not possible. If the base class is derived, then the both class should be encapsulate with[Serializable] attribute to achieve the serialization.
Given below the serialization example by using two classes.
namespace SerializationExample
{
    [Serializable]
    public class EmployeeSkill
    {
        public string Skill { getset; }
    }

    [Serializable]
    public class Employee : EmployeeSkill
    {
        public string Name { getset; }
        public int Experience { getset; }
    }
}
By using the above two classes I am going to show how to,

1.       Convert an object into bytes and vice versa.
2.       Serialize an object to save and retrieve the object from the text file.
3.       Serialize an object to save and retrieve the object from the XML file.

1.    Convert an object into bytes and vice versa.
namespace SerializationExample
{
    class Program
    {
        static void Main(string[] args)
        {
            var objSerializationObject = new Employee()
            {
                Name = "Employee",
                Experience = 3,
                Skill = ".Net"
            };
            var objConvertedToBytes = ConvertObjectToBytes(objSerializationObject);
            var objEmployee = (Employee)ConvertBytesToObject(objConvertedToBytes);
            Console.WriteLine(objEmployee.Name);
            Console.WriteLine(objEmployee.Experience);
            Console.WriteLine(objEmployee.Skill);
            Console.ReadLine();
        }
        #region Converting Object into Bytes
        /// <summary>
        /// To convert object into Bytes.
        /// </summary>
        /// <param name="objEmployee">Employee Object</param>
        /// <returns>Array Of Bytes</returns>
        public static byte[] ConvertObjectToBytes(Employee objEmployee)
        {
            if (objEmployee == nullreturn null;

            MemoryStream objMemoryStream = new MemoryStream();
            BinaryFormatter objBinaryFormatter = new BinaryFormatter();

            objBinaryFormatter.Serialize(objMemoryStream, objEmployee);
            return objMemoryStream.ToArray();
        }
        #endregion


        #region Converting Bytes into Original Object
        /// <summary>
        /// To convert bytes into Object.
        /// </summary>
        /// <param name="objArrayBytes">Array Of Bytes</param>
        /// <returns>Original Object</returns>
        public static Object ConvertBytesToObject(byte[] objArrayBytes)
        {
            MemoryStream objMemoryStream = new MemoryStream();
            BinaryFormatter objBinaryFormatter = new BinaryFormatter();
            objMemoryStream.Write(objArrayBytes, 0, objArrayBytes.Length);
            objMemoryStream.Seek(0, SeekOrigin.Begin);
            Object obj = (Object)objBinaryFormatter.Deserialize(objMemoryStream);
            return obj;
        }
        #endregion
    }
}

2.       Serialize  to save and retrieve an object from the text file.

namespace SerializationExample
{

  class Program
    {
        static void Main(string[] args)
        {
            var objSerializationObject = new Employee()
            {
                Name = "Employee",
                Experience = 3,
                Skill = ".Net"
            };


            #region Saving the object stream in the text file
            IFormatter objFormatter = new BinaryFormatter();
            Stream objStream = new FileStream("MyFile.txt"FileMode.Create, FileAccess.Write, FileShare.None);
            objFormatter.Serialize(objStream, objSerializationObject);
            objStream.Close();
            #endregion

            #region Read and Convert the Stream of bytes into original Object
            IFormatter objReadFormatter = new BinaryFormatter();
            Stream objReadStream = new FileStream("MyFile.txt"FileMode.Open, FileAccess.Read, FileShare.None);
            Employee objDeserialize = (Employee)objReadFormatter.Deserialize(objReadStream);
            objReadStream.Close();
            #endregion

            Console.WriteLine(objDeserialize.Name);
            Console.WriteLine(objDeserialize.Experience);
            Console.WriteLine(objDeserialize.Skill);
            Console.ReadLine();
        }
    }
}

3.       Serialize to save and retrieve an object from the XML file.


namespace SerializationExample
{
    class Program
    {
        static void Main(string[] args)
        {
            var objSerializationObject = new Employee()
            {
                Name = "Employee",
                Experience = 3,
                Skill = ".Net"
            };


            #region Saving the object stream in the XML file
            XmlSerializer objXMLSerializer = new XmlSerializer(typeof(Employee));

            StreamWriter objWriter = new StreamWriter("MyFile.XML");
            objXMLSerializer.Serialize(objWriter, objSerializationObject);
            objWriter.Close();
            #endregion

            #region Read the XML and Convert it into original Object
            StreamReader objReader = new StreamReader("MyFile.XML");
            Employee objDeserialize = (Employee)objXMLSerializer.Deserialize(objReader);
            objReader.Close();
            #endregion

            Console.WriteLine(objDeserialize.Name);
            Console.WriteLine(objDeserialize.Experience);
            Console.WriteLine(objDeserialize.Skill);
            Console.ReadLine();
        }
    }
}