DOT NET Basics and Latest Updates

Friday, May 4, 2012

Facts about Temporary table and table variables.

· Both are stored in TempDB.
Most o people think table variables does not store in database anywhere.

· Temporary table will have better performance compared to table variables why because sql server uses statistics while working with the queries which contains Temporary tables but this would not happen for table variables.

Table variables will get expired once the execution is done but Temporary tables exist till the current session is closed

· In functions we cannot use temporary tables If you use you will get compilation errors. In this case we need to go with table variables.

In dynamic queries also we cannot use table variables but we can use temporary tables.

· Guide lines says that it is better to use table variables if the data is minimal. If your query is dealing with large amount of data it is better to go ahead with Temporary tables why because we will benefit from the statistics and we can create indexes on temporary tables if required.

Adding a serial number column to C# datatable.

DataTable dt = new DataTable();

DataColumn col = new DataColumn("#", typeof(int));

col.AutoIncrement = true;

col.AutoIncrementSeed = 1;

dt.Columns.Add(col);

Converting an array into ',' seperated values

string[] ids = {"2343","2344","2345"};

string idString = String.Join(",",ids);

Response.Write(idString);

Passing multiple command arguments in a gridview rowcommand event

<asp:GridView ID="GridView1" runat="Server"

 AutoGenerateColumns="False"

 OnRowCommand="GridView1_RowCommand">

          <Columns>

            <asp:BoundField DataField="carid" HeaderText="Card Id" />

               <asp:BoundField DataField="Year" HeaderText="year" />

               <asp:TemplateField>

                      <ItemTemplate>

                       <asp:Button ID="btnTest" runat="Server"

 CommandName="Test" Text="Select"

CommandArgument='<%#Eval("carid") + ","+Eval("year") %>' />

                     </ItemTemplate>

                   </asp:TemplateField>

               </Columns>

           </asp:GridView>

In code behind

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

        if (e.CommandName == "Test")

          string[] commandArgs =

 e.CommandArgument.ToString().Split(new char[]

{ ',' });

       Label1.Text= commandArgs[0];

       Label2.Text = commandArgs[1];

Converting a List to C# datatable object.

While coding some times we come across situations where we need to convert the list<T> data to a data table. The below method will be handy if you need to conver an Ilist to datatable.

public static DataTable ToDataTable<T>(this IList<T> data)
      {
          PropertyDescriptorCollection props =
              TypeDescriptor.GetProperties(typeof(T));
          DataTable table = new DataTable();
          for (int i = 0; i < props.Count; i++)
          {
              PropertyDescriptor prop = props[i];
               table.Columns.Add(prop.Name, prop.PropertyType);
          }
          object[] values = new object[props.Count];
          foreach (T item in data)
          {
              for (int i = 0; i < values.Length; i++)
              {
                  values[i] = props[i].GetValue(item);
               }
               table.Rows.Add(values);
          }
          return table;
      }

Export data to excel work book using InteropServices

Exporting data to an excel workbook is become a common application functionality now. By using the below code we can export data to multiple sheets in an excel work book. In the below code it will export data to an excel workbook and place that excel workbook in the root folder. Here one table data will placed in one sheet.

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

<!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:Button ID="btnExport" runat="server" onclick="btnExport_Click"
            Text="Export" />
    </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;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Runtime.InteropServices;

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

    }
    protected void btnExport_Click(object sender, EventArgs e)
    {
        GenerateData();
    }

    private void GenerateData()
    {
        System.Data.DataSet rescDS = new System.Data.DataSet();

        System.Data.DataSet studiesDS = new System.Data.DataSet();

        // Get data

        rescDS = GenerateDataTable();

        studiesDS = GenerateSecondDataTable();
        // Create Excel Application, Workbook, and WorkSheets
        Microsoft.Office.Interop.Excel.Application xlExcel = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbooks xlBooks = default(Microsoft.Office.Interop.Excel.Workbooks);
                Microsoft.Office.Interop.Excel.Workbook xlBook = default(Microsoft.Office.Interop.Excel.Workbook);
        //Microsoft.Office.Interop.Excel.Workbook xlBook = new Microsoft.Office.Interop.Excel.Workbook();

        //Microsoft.Office.Interop.Excel.Sheets xlSheets = default(Excel.Sheets);
        Microsoft.Office.Interop.Excel.Sheets xlSheets = default(Microsoft.Office.Interop.Excel.Sheets);
        Microsoft.Office.Interop.Excel.Worksheet stdSheet = default(Microsoft.Office.Interop.Excel.Worksheet);
        Microsoft.Office.Interop.Excel.Range xlCells = default(Microsoft.Office.Interop.Excel.Range);

        string sFile = null;

        string sTemplate = null;

        Microsoft.Office.Interop.Excel.Worksheet rescSheet = default(Microsoft.Office.Interop.Excel.Worksheet);

        //string filename=strin

        //sFile = Server.MapPath(Request.ApplicationPath) + "\\Excel.xls";
        sFile = Path.Combine(Server.MapPath(Request.ApplicationPath), string.Format("{0}_{1}.xls", "ExcelWorkBook", DateTime.Now.ToShortDateString()));
        //File.CreateText(Server.MapPath(Request.ApplicationPath)
        // Formatted template the way you want.

        // If you want to change the format, change this template

        // sTemplate = Server.MapPath(Request.ApplicationPath) + "\\XLTemplate.xls";

        xlExcel.Visible = false;
        xlExcel.DisplayAlerts = false;
        // Get all workbooks and open first workbook
        xlBooks = xlExcel.Workbooks;
        xlBooks.Open(Server.MapPath(Request.ApplicationPath) + "\\XLTemplate.xls");
        //xlBook = xlBooks.Item(1);
        xlBook = xlBooks.Item[1];
        // Get all sheets available in first book
        xlSheets = xlBook.Worksheets;
        // Get first sheet, change its name and get all cells
        stdSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Item[1];
        stdSheet.Name = "First Sheet";
        xlCells = stdSheet.Cells;
        // Fill all cells with data
        GenerateExcelFile(studiesDS.Tables[0], xlCells);
        //Fill in the data
        // Get second sheet, change its name and get all cells
        rescSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Item[2];
        rescSheet.Name = "Second Sheet ";
        xlCells = rescSheet.Cells;
        // Fill all cells with data
        GenerateExcelFile(rescDS.Tables[0], xlCells);
        // Save created sheets as a file
        xlBook.SaveAs(sFile);
        // Make sure all objects are disposed
        xlBook.Close();
        xlExcel.Quit();
        Marshal.ReleaseComObject(xlCells);
        Marshal.ReleaseComObject(stdSheet);
        Marshal.ReleaseComObject(xlSheets);
        Marshal.ReleaseComObject(xlBook);
        Marshal.ReleaseComObject(xlBooks);
        Marshal.ReleaseComObject(xlExcel);
        xlExcel = null;
        xlBooks = null;
        xlBook = null;
        xlSheets = null;
        stdSheet = null;
        xlCells = null;
        rescSheet = null;
        // Let GC know about it
        //GC.Collect();
        // Export Excel for download
        try
        {
            Response.Redirect(sFile, false);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    private void GenerateExcelFile(System.Data.DataTable table, Microsoft.Office.Interop.Excel.Range xlCells)
    {

        DataRow dr = null;
        object[] ary = null;
        int iRow = 0;
        int iCol = 0;
        //Output Column Headers
        xlCells.Font.Bold = true;
        for (iCol = 0; iCol <= table.Columns.Count - 1; iCol++)
        {
            //xlCells.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
            xlCells[1, iCol + 1] = table.Columns[iCol].ToString();
        }
        //Output Data
        for (iRow = 0; iRow <= table.Rows.Count - 1; iRow++)
        {
            dr = table.Rows[iRow];

            ary = dr.ItemArray;

            //xlCells.Font.Bold = false;
            for (iCol = 0; iCol < ary.Length; iCol++)
            {
                xlCells[iRow + 2, iCol + 1] = ary[iCol].ToString();

                Response.Write(ary[iCol].ToString() + System.Environment.NewLine);

            }

        }

    }

    private DataSet GenerateDataTable()
    {
        DataSet ds = new DataSet();
        System.Data.DataTable dt = new System.Data.DataTable();
        dt.Columns.Add("SNo");
        dt.Columns.Add("Name");
        for (int i = 0; i < 10; i++)
        {
            DataRow dr = dt.NewRow();
            dr["SNo"] = i;
            dr["Name"] = "Sheet1" + i;
            dt.Rows.Add(dr);
        }
        ds.Tables.Add(dt);
        return ds;
    }

    private DataSet GenerateSecondDataTable()
    {
        DataSet ds = new DataSet();
        System.Data.DataTable dt = new System.Data.DataTable();
        dt.Columns.Add("SNo");
        dt.Columns.Add("Name");
        for (int i = 0; i < 100; i++)
        {
            DataRow dr = dt.NewRow();
            dr["SNo"] = i;
            dr["Name"] = "Sheet2" + i;
            dt.Rows.Add(dr);
        }
        ds.Tables.Add(dt);
        return ds;
    }
}

Displaying the controls dynamically and read the data from controls.

In some scenarios we will get the situations where we need to generate the controls dynamically and get the values from those dynamically generated controls on different page post backs. The below example will explain how we can generate the text boxes dynamically on change of drop down selection and data which is there in text boxes will be displayed when user click on Read data button.

---------------------Design page-----------------------------
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DynamicTextboxes.aspx.cs"
    Inherits="DynamicTextboxes" %>

<!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:DropDownList ID="ddlControls" AutoPostBack="true" runat="server"
            onselectedindexchanged="ddlControls_SelectedIndexChanged">
            <asp:ListItem Value="-1">--select--</asp:ListItem>
            <asp:ListItem Value="0">1</asp:ListItem>
            <asp:ListItem Value="1">2</asp:ListItem>
            <asp:ListItem Value="2">3</asp:ListItem>
            <asp:ListItem Value="3">4</asp:ListItem>
        </asp:DropDownList>
        <br />
        <asp:PlaceHolder ID="phTextBoxes" runat="server"></asp:PlaceHolder>
        <asp:Button ID="btnRead" runat="server" onclick="btnRead_Click"
            Text="Read Data" />
    </div>
    </form>
</body>
</html>
-------------------------End-------------------------------------------
--------------------------------Code behind code---------------------------------

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

public partial class DynamicTextboxes : System.Web.UI.Page
{
    string strValue = string.Empty;
    public int NumberOfControls
    {
        get
        {
            if (ViewState["Count"] == null)
            {
                return 0;
            }
            return (int)ViewState["Count"];
        }
        set
        {
            ViewState["Count"] = value++;
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        NumberOfControls = 10;
    }

    protected override void CreateChildControls()
    {
        if (ddlControls.SelectedValue != "-1")
        {
            // Here we are recreating controls to persist the ViewState on every post back
            if (Page.IsPostBack)
            {
                //NumberOfControls += 1;
                CreateTextBoxes(NumberOfControls);
            }
            else
            {
                CreateTextBoxes(NumberOfControls);
                // Increase the control value to 1
                //NumberOfControls = 0;
            }
        }

    }

    private void CreateTextBoxes(int noOfTextboxes)
    {
        phTextBoxes.Controls.Clear();
        for (int counter = 0; counter <= noOfTextboxes; counter++)
        {
            TextBox tb = new TextBox();
            tb.Width = 150;
            tb.Height = 18;
            tb.TextMode = TextBoxMode.SingleLine;
            tb.ID = "TextBoxID" + (counter + 1).ToString();
            // add some dummy data to textboxes
            //tb.Text = "Enter Title " + counter;
            phTextBoxes.Controls.Add(tb);
            phTextBoxes.Controls.Add(new LiteralControl("<br/>"));

        }

    }

    private void ReadTextBoxes()
    {
        strValue = string.Empty;
        int n = NumberOfControls;

        for (int i = 0; i <= NumberOfControls; i++)
        {

            string boxName = "TextBoxID" + (i + 1).ToString();
            TextBox tb = phTextBoxes.FindControl(boxName) as TextBox;
            if (tb != null)
                strValue += tb.Text + "\n";

        }
        Response.Write(strValue);

    }
    protected void btnRead_Click(object sender, EventArgs e)
    {
        ReadTextBoxes();
    }
    protected void ddlControls_SelectedIndexChanged(object sender, EventArgs e)
    {
        NumberOfControls = Convert.ToInt32(ddlControls.SelectedItem.Text);
        CreateTextBoxes(NumberOfControls);
    }
}
-----------------------------------End-----------------------------------------

By this way we can dynamically create the controls and get the data from the controls.