Friday, February 29, 2008

GridView color change depending on inner value

rotected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
  {
      if (e.Row.RowType == DataControlRowType.DataRow)
      {
          // This line will get the reference to the underlying row
          DataRowView _row = (DataRowView)e.Row.DataItem;
          if (_row != null)
          {
              // get the field value which you want to compare and
               // convert to the corresponding data type
               // i assume the fieldName is of int type
              int _field = Convert.ToInt32(_row.Row["fieldName"]);
              if (_field == 7)
                  e.Row.BackColor = System.Drawing.Color.Green;
              else
                  e.Row.BackColor = System.Drawing.Color.Red;
          }
      }
  }

How To Create Bound Column Dynamically (Data Grid)

Step 1: Add the GridView to your page.

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

<!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>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:DataGrid ID="datagrid1" runat="server" AutoGenerateColumns="false" OnDataBinding="DataGrid1_DataBinding"
                OnItemCommand="DataGrid1_ItemCommand">
                <Columns>
                </Columns>
            </asp:DataGrid>
        </div>
       <asp:Label ID="lblStatus" runat="server"></asp:Label> 
    </form>
</body>
</html>

2.Create DataTable
private DataTable CreateTable()
    {
        DataTable ret = new DataTable();
        ret.Columns.Add(new DataColumn("ID", typeof(int)));
        ret.Columns.Add(new DataColumn("Column1", typeof(Int32)));
        ret.Columns.Add(new DataColumn("Column2", typeof(Int32)));
        //Make some rows and put some sample data in
        DataRow dr;
        int i;
        for (i = 1; i <= 10; i++)
        {
            dr = ret.NewRow();
            dr[0] = i;
            dr[1] = i * 25;
            dr[2] = i * i;
            //add the row to the datatable
            ret.Rows.Add(dr);
        }
        return ret;
    }

3. Bind GridView (notice that for dynamically created controls within the datagrid to fire up the
       itemcommand event, the datagrid needs to be created during the initialization stage
       of the page's lifecycle)

protected void Page_Init(object sender, System.EventArgs e)
    {
        
        datagrid1.DataSource = CreateTable();
        datagrid1.DataBind();
    }

4. Now  we will loop through the columns on the datatable and build a tempalte column that has one imagebutton in it
      add it to the grid.columns collection then create another boundcolumn that would display the value in the column
protected void DataGrid1_DataBinding(object sender, EventArgs e)
    {
         tbl = CreateTable();
        
        foreach (DataColumn dc in tbl.Columns)
        {
         
            TemplateColumn tc = new TemplateColumn();
            ((DataGrid)sender).Columns.Add(tc);
            tc.ItemTemplate = new MyTemplate(ListItemType.Item, dc.ColumnName);

            BoundColumn bc = new BoundColumn();
            //add a column to contain the value of the field
            bc.DataField = dc.ColumnName;
            bc.DataFormatString = "{0:C}";
            bc.HeaderText = dc.ColumnName;
            ((DataGrid)sender).Columns.Add(bc);

        }
    }

protected void DataGrid1_ItemCommand(object sender, DataGridCommandEventArgs e)
  {
      //I saved the ImageButton ID in the CommandAgument to be able to search by it
      ImageButton btnImage = e.Item.FindControl(e.CommandArgument.ToString()) as ImageButton;
   
      //toggle the image
      if (btnImage.ImageUrl == "images/left.gif")
      {
          btnImage.ImageUrl = "images/right.gif";
      }
      else
      {
          btnImage.ImageUrl = "images/left.gif";
      }
      lblStatus.Text = "commandName = " + e.CommandName + "  at row = " + e.Item.ItemIndex + "
button image = " + btnImage.ImageUrl;
  }

Confirm GridView Deletes with the ModalPopupExtender

  Step 1: Add the GridView to your page, placing it inside an UpdatePanel.



<asp:UpdatePanel ID="updatePanel" runat="server" UpdateMode="Conditional">
           <ContentTemplate>
               <asp:Label ID="lblTitle" runat="server" Text="User List" BackColor="lightblue" Width="95%" />
               <asp:GridView ID="gvUsers" runat="server" AutoGenerateColumns="false" Width="95%">
                   <AlternatingRowStyle BackColor="aliceBlue" />
                   <HeaderStyle HorizontalAlign="Left" />
                   <Columns>
                       <asp:BoundField DataField="ID" HeaderText="ID" />
                       <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
                       <asp:BoundField DataField="LastName" HeaderText="LastName" />
                       <asp:BoundField DataField="Address" HeaderText="Address" />
                   </Columns>
               </asp:GridView>
           </ContentTemplate>
       </asp:UpdatePanel>
Nothing out of the ordinary here, just a basic GridView contained in an UpdatePanel (don't forget to set UpdateMode to Conditional) Step 2: Add a TemplateField with a Delete button. Wire up an event handler for the button click.

  <asp:TemplateField ControlStyle-Width="50px" HeaderStyle-Width="60px" ItemStyle-HorizontalAlign="Center">
                   <ItemTemplate>
                       <asp:Button ID="btnDelete" runat="server" OnClientClick="showConfirm(this); return false;"
                           OnClick="BtnDelete_Click" Text="Delete" />
                   </ItemTemplate>
               </asp:TemplateField>
You can either explicitly use the OnClick event of the delete button or add the CommandName attribute and implement the RowDeleting event. I chose the former for this sample. Step 3: Add the HTML markup for the modal popup.
<div id="div" runat="server" align="center" class="confirm" style="display: none">
           <img align="absmiddle" src="Img/warning.jpg" />Are you sure you want to delete this
           item?
           <asp:Button ID="btnOk" runat="server" Text="Yes" Width="50px" />
           <asp:Button ID="btnNo" runat="server" Text="No" Width="50px" />
       </div>
Step 4: Add the ModalPopupExtender to the page.
<ajaxToolkit:ModalPopupExtender ID="ModalPopupExtender2" runat="server" BehaviorID="mdlPopup"
           TargetControlID="div" PopupControlID="div" OkControlID="btnOk" CancelControlID="btnNo"
           BackgroundCssClass="modalBackground" />
Step 5: Add an OnClientClick for the delete button
<asp:TemplateField ControlStyle-Width="50px" HeaderStyle-Width="60px" ItemStyle-HorizontalAlign="Center">
                   <ItemTemplate>
                       <asp:Button ID="btnDelete" runat="server" OnClientClick="showConfirm(this); return false;"
                           OnClick="BtnDelete_Click" Text="Delete" />
                   </ItemTemplate>
               </asp:TemplateField>
Step 6: Add the supporting javascript <script type="text/javascript"> // keeps track of the delete button for the row // that is going to be removed var _source; // keep track of the popup div var _popup; function showConfirm(source){ this._source = source; this._popup = $find('mdlPopup'); // find the confirm ModalPopup and show it this._popup.show(); } function okClick(){ // find the confirm ModalPopup and hide it this._popup.hide(); // use the cached button as the postback source __doPostBack(this._source.name, ''); } function cancelClick(){ // find the confirm ModalPopup and hide it this._popup.hide(); // clear the event source this._source = null; this._popup = null; } </script>

Here is the complete listing for this page.


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

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajaxToolkit" %>
<!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 id="head" runat="server">
   <title>Delete Confirm Example</title>

   <script runat="server">

       public class User
       {
           private int _id;
           private string _firstName;
           private string _lastName;
           private string _Address;
           public User(int id, string firstname, string lastname, string address)
           {
               this._id = id;
               this._firstName = firstname;
               this._lastName = lastname;
               this._Address = address;
           }
           public int ID
           {
               get { return this._id; }
           }
           public string FirstName
           {
               get { return this._firstName; }
           }

           public string LastName
           {

               get { return this._lastName; }

           }
           public string Address
           {
               get { return this._Address; }
           }
       }
       private System.Collections.Generic.List<User> Users
       {
           get
           {
               System.Collections.Generic.List<User> item = this.Session["Users"] as System.Collections.Generic.List<User>;
               if (item == null)
               {
                   item = new System.Collections.Generic.List<User>();
                   item.Add(new User(1, "santosh", "kumar", "Chandigarh"));
                   item.Add(new User(2, "Vinod", "kumar", "HP"));
                   item.Add(new User(3, "Ajay", "Chawla", "PB"));
                   item.Add(new User(4, "MandeepInder", "Singh", "Chandigarh"));
                   this.Session["Users"] = item;
               }
               return item;
           }
       }
       /// <summary>
       /// </summary>
       /// <param name="sender"></param>
       /// <param name="e"></param>
       protected void Page_Load(object sender, EventArgs e)
       {
           if (!this.IsPostBack)
           {
               this.gvUsers.DataSource = this.Users;
               this.gvUsers.DataBind();
           }

       }


       /// <summary>
       /// </summary>
       /// <param name="sender"></param>
       /// <param name="e"></param>
       protected void BtnDelete_Click(object sender, EventArgs e)
       {
           //  get the gridviewrow from the sender so we can get the datakey we need
           Button btnDelete = sender as Button;
           GridViewRow row = (GridViewRow)btnDelete.NamingContainer;
           //  find the item and remove it
           User itemToRemove = this.Users[row.RowIndex];
           this.Users.Remove(itemToRemove);
           //  rebind the datasource
           this.gvUsers.DataSource = this.Users;
           this.gvUsers.DataBind();
       }

   </script>
   <script type="text/javascript">
    //  keeps track of the delete button for the row
    //  that is going to be removed
    var _source;
    // keep track of the popup div
    var _popup;
    function showConfirm(source){
        this._source = source;
        this._popup = $find('mdlPopup');
        //  find the confirm ModalPopup and show it 
        this._popup.show();
    }
    function okClick(){
        //  find the confirm ModalPopup and hide it 
        this._popup.hide();
        //  use the cached button as the postback source
        __doPostBack(this._source.name, '');

    }

    function cancelClick(){
        //  find the confirm ModalPopup and hide it
        this._popup.hide();
        //  clear the event source
        this._source = null;
        this._popup = null;

    }

   </script>
   <style>

    .modalBackground {

        background-color:Gray;

        filter:alpha(opacity=70);

        opacity:0.7;

    }

    .confirm{

       background-color:White;

       padding:10px;

       width:370px;

    }

</style>
</head>
<body>
   <form id="form" runat="server" style="font-family: Trebuchet MS;">
       <asp:ScriptManager ID="scriptManager" runat="server" />
       <div>
           <asp:UpdatePanel ID="updatePanel" runat="server" UpdateMode="Conditional">
               <ContentTemplate>
                   <asp:Label ID="lblTitle" runat="server" Text="User List" BackColor="lightblue" Width="95%" />
                   <asp:GridView ID="gvUsers" runat="server" AutoGenerateColumns="false" Width="95%">
                       <AlternatingRowStyle BackColor="aliceBlue" />
                       <HeaderStyle HorizontalAlign="Left" />
                       <Columns>
                           <asp:BoundField DataField="ID" HeaderText="ID" />
                           <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
                           <asp:BoundField DataField="LastName" HeaderText="LastName" />
                           <asp:BoundField DataField="Address" HeaderText="Address" />
                           <asp:TemplateField ControlStyle-Width="50px" HeaderStyle-Width="60px" ItemStyle-HorizontalAlign="Center">
                               <ItemTemplate>
                                   <asp:Button ID="btnDelete" runat="server" OnClientClick="showConfirm(this); return false;"
                                       OnClick="BtnDelete_Click" Text="Delete" />
                               </ItemTemplate>
                           </asp:TemplateField>
                       </Columns>
                   </asp:GridView>
               </ContentTemplate>
           </asp:UpdatePanel>
           <ajaxToolkit:ModalPopupExtender ID="ModalPopupExtender1" BehaviorID="mdlPopup" runat="server"
               TargetControlID="div" PopupControlID="div" OkControlID="btnOk" OnOkScript="okClick();"
               CancelControlID="btnNo" OnCancelScript="cancelClick();" BackgroundCssClass="modalBackground" />
           <div id="div" runat="server" align="center" class="confirm" style="display: none">
               <img align="absmiddle" src="Img/warning.jpg" />Are you sure you want to delete this
               item?
               <asp:Button ID="btnOk" runat="server" Text="Yes" Width="50px" />
               <asp:Button ID="btnNo" runat="server" Text="No" Width="50px" />
           </div>
       </div>
   </form>
</body>
</html>

Thursday, February 28, 2008

Creating a DataTable programmatically in ASP.NET

This article will show how to create a DataTable object at runtime and binding it to a DataGrid web control

The various data bound controls available in ASP.NET allow you to bind themselves with objects that implements IList interface of System.Collections namespace. The famous DataList and DataGrid controls can be bound to more complex data sources like the DataSet and DataTable. Usually you fetch data from a database and fill these data containers from this data and then bind the DataGrid or DataList with these objects.

Some times it becomes necessary that the data not come from a data base but comes from some other source, like from some where in your program you are reading the file system and want to display that data. In such scenarios you can populate the DataSet or more commonly a DataTable in your ASP.NET web page and then bind the web control with this data object.

Here I’ve used a very simple method to dynamically create a DataTable and populated it in the function with some rows and then bind this to a datagrid named dgOne in the ASP.NET page.

Here is the sample web page


public DataTable GetCustomMadeDataTable()
   {
       //Create a new DataTable object
       DataTable objDataTable = new DataTable();
       //Create three columns with string as their type
       objDataTable.Columns.Add("Column 1", typeof(string));
       objDataTable.Columns.Add("Column 2", typeof(string));
       objDataTable.Columns.Add("Column 3", typeof(string));
       //Adding some data in the rows of this DataTable
       objDataTable.Rows.Add(new string[] { "Row1 - Column1", "Row1 - Column2", "Row1 - Column3" });
       objDataTable.Rows.Add(new string[] { "Row2 - Column1", "Row2 - Column2", "Row2 - Column3" });
       objDataTable.Rows.Add(new string[] { "Row3 - Column1", "Row3 - Column2", "Row3 - Column3" });
       return objDataTable;

   }
   

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

<!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:DataGrid ID="dgOne" runat="server" AutoGenerateColumns="true"/>
   </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 _Default : System.Web.UI.Page
{
   protected void Page_Load(object sender, EventArgs e)
   {
       DataTable dtObject = GetCustomMadeDataTable() as DataTable;
       dgOne.DataSource = dtObject;
       dgOne.DataBind();

   }
   public DataTable GetCustomMadeDataTable()
   {
       //Create a new DataTable object
       DataTable objDataTable = new DataTable();
       //Create three columns with string as their type
       objDataTable.Columns.Add("Column 1", typeof(string));
       objDataTable.Columns.Add("Column 2", typeof(string));
       objDataTable.Columns.Add("Column 3", typeof(string));
       //Adding some data in the rows of this DataTable
       objDataTable.Rows.Add(new string[] { "Row1 - Column1", "Row1 - Column2", "Row1 - Column3" });
       objDataTable.Rows.Add(new string[] { "Row2 - Column1", "Row2 - Column2", "Row2 - Column3" });
       objDataTable.Rows.Add(new string[] { "Row3 - Column1", "Row3 - Column2", "Row3 - Column3" });
       return objDataTable;

   }




}

Wednesday, February 27, 2008

Printing GridView from ASp.net

<%@ Page Language="C#" %>

<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
  private DataTable GetDataTable()
  {
      //create table
      DataTable dt = new DataTable("Product");
      dt.Columns.Add("ProductID", Type.GetType("System.Int32"));
      dt.Columns.Add("ProductName", Type.GetType("System.String"));

      //create fields
      DataColumn[] pk = new DataColumn[1];
      pk[0] = dt.Columns["ProductID"];
      dt.PrimaryKey = pk;
      dt.Columns["ProductID"].AutoIncrement = true;
      dt.Columns["ProductID"].AutoIncrementSeed = 1;
      dt.Columns["ProductID"].ReadOnly = true;

      //fill rows
      DataRow dr;
      for (int x = 1; x <= 10; x++)
      {
          //make every other one different
          if (Math.IEEERemainder(x, 2) == 0)
          {
              dr = dt.NewRow();
              dr["ProductName"] = "Riss";

              dt.Rows.Add(dr);
          }
          else
          {
              dr = dt.NewRow();
              dr["ProductName"] = "Product";

              dt.Rows.Add(dr);

          }
      }

      return dt;
  }

  protected void Page_Load(object sender, EventArgs e)
  {
      if (!IsPostBack)
      {

          GridView1.DataSource = GetDataTable();
          GridView1.DataBind();
      }
  }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title>Untitled Page</title>

  <script type="text/javascript">
  function CallPrint(strid)
  {
      var prtContent = document.getElementById(strid);
      var WinPrint = window.open('','','letf=0,top=0,width=400,height=400,toolbar=0,scrollbars=0,status=0');
      WinPrint.document.write(prtContent.innerHTML);
      WinPrint.document.close();
      WinPrint.focus();
      WinPrint.print();
      WinPrint.close();

}
  </script>

</head>
<body>
  <form id="form1" runat="server">
      <div id="divPrint">
          <asp:GridView ID="GridView1" runat="server" />
      </div>
      <input type="button" value="print " id="btnPrint" runat="Server" onclick="javascript:CallPrint('divPrint')" />
  </form>
</body>
</html>



use html (<input type="button">)  button instead of <asp:button>>. 

Thursday, February 21, 2008

How To Open Modal PopUp Inside Gridview

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data"%>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ccl" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    protected void Grd_SelectedIndexChanged(object sender, EventArgs e)
    {
        GridViewRow row = ((GridView)sender).SelectedRow;

        if (row == null) return;

        ModalPopupExtender extender = row.FindControl("extPerson") as ModalPopupExtender;


        if (extender != null)
        {
            extender.Show();
        }

    }
    private DataSet CreateDS()
    {
        DataSet ds = new DataSet();
        if (Session["ds"] == null)
        {
            DataTable dt = new DataTable("PersonData");
            DataRow dr;
            dt.Columns.Add(new DataColumn("Person_ID", typeof(Int32)));
            dt.Columns.Add(new DataColumn("PersonName", typeof(string)));
            dt.Columns.Add(new DataColumn("Company", typeof(string)));
            for (int i = 1; i < 10; i++)
            {
                dr = dt.NewRow();
                dr[0] = i;
                dr[1] = "Person " + i;
                dr[2] = "Company " + i;
                dt.Rows.Add(dr);
            }
            DataColumn parentCol;
            parentCol = dt.Columns["Person_ID"];

            ds.Tables.Add(dt);
            DataColumn[] PrimaryKeyColumns = new DataColumn[1];
            PrimaryKeyColumns[0] = dt.Columns["Person_Id"];
            dt.PrimaryKey = PrimaryKeyColumns;

            Session["ds"] = ds;
        }
        else
        {
            ds = (DataSet)Session["ds"];
        }
        return ds;

    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            this.Grd.DataSource = CreateDS();
            this.Grd.DataBind();
        }

    }


</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:ScriptManager ID="ScriptManger1" runat="Server"></asp:ScriptManager>
        <div>
            <asp:GridView ID="Grd" runat="server" AutoGenerateColumns="false" OnSelectedIndexChanged="Grd_SelectedIndexChanged">
                <Columns>
                    <asp:TemplateField>
                        <ItemTemplate>
                            <asp:LinkButton ID="lblPerson" runat="server" Text='<%# Eval("PersonName") %>' />
                            <asp:Panel ID="pnlPerson" runat="server" Style="display: none" Width="400px" Height="400px"
                                BackColor="CadetBlue">
                                <div style="float: right;">
                                    <asp:LinkButton ID="lnkClose" runat="server">Close</asp:LinkButton>
                                </div>
                                <asp:TextBox ID="txt" runat="server"></asp:TextBox>
                            </asp:Panel>
                            <ccl:ModalPopupExtender ID="extPerson" runat="server" TargetControlID="lblPerson"
                                PopupControlID="pnlPerson" DropShadow="true" CancelControlID="lnkClose" />
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
        </div>
    </form>
</body>
</html>

Wednesday, February 20, 2008

How to locate control nested inside of another control

Finding controls within a Page's control hierarchy can be painful but if you know how the controls are nested you can use the lesser known "$" shortcut to find controls without having to write recursive code. The following example shows how to use the DefaultFocus property to set the focus on a textbox that is nested inside of a FormView control. Notice that the "$" is used to delimit the nesting:
     

<form id="form1" DefaultFocus="formVw$txtName" runat="server">

    <div>

       <asp:FormView ID="formVw" runat="server">

          <ItemTemplate>

             Name:

             

          </ItemTemplate>

       </asp:FormView>

    </div>

</form>

This little trick can also be used on the server-side when calling FindControl():

TextBox tb = this.FindControl("form1$formVw$txtName") as TextBox;

if (tb != null)

{

    //Access TextBox control

}

Monday, February 18, 2008

Adding Auto Suggest Box in the search Box

Adding Auto suggest box using Ajax(Microsoft ASP.NET Ajax extension) was very easy. You need a web service (returning the list of words), the autocompleteextender to show the panel and a text box. The webservice is required to return an array list. The code to use the autocompleteextender is very simple
<ajaxtoolkit:autocompleteextender runat="server"
id="AutoCompleteExtender1" enabled="true"
minimumprefixlength="1" servicepath="~/SuggestionService.asmx"
servicemethod="GetAllNames"
targetcontrolid="txtSearch" />
Here MinimumPrefixLength is the number of words after which the the suggestion will be shown. Service path is the path to the web service and service method is the webmethod that will be used. The target control ID is the id of the textbox. That Is the syntax of the extender control. We also have to make a web service. I decided to keep the values in a array list . The web service is like this
SuggestionService.asmx

     [WebMethod]
    [System.Web.Script.Services.ScriptMethod]
    public string[] GetAllNames(string prefixText, int count)
    {
        ArrayList filteredList = new ArrayList();
        string s2 = "\n";
        char[] ch = s2.ToCharArray();
        string[] names ={ "India", "UK", "US", "China", "Nepal" };

        foreach (string name in names)
        {
            if (name.ToLower().StartsWith(prefixText.ToLower()))
            {
                filteredList.Add(name);
            }
        }
        return (string[])filteredList.ToArray(typeof(string));
    }

Tuesday, February 12, 2008

Read Excel files from ASP.NET

Read Excel files from ASP.NET: This page provides a simple example of how to query an Excel spreadsheetfrom an ASP.NET page using either C# or VB.NET. Check it out!This code was written in response to a message posted on one ofCharles Carroll''s ASP.NET lists. You can ... This page provides a simple example of how to query an Excel spreadsheet from an ASP.NET page using either C# or VB.NET. Check it out!
<%@ Page Language="C#" %>

<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System" %>

<script language="C#" runat="server">
    protected void Page_Load(Object Src, EventArgs E)
    {
        string strConn;
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
        "Data Source=C:\\exceltest.xls;" +
        "Extended Properties=Excel 8.0;";
        //You must use the $ after the object you reference in the spreadsheet
        OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

        DataSet myDataSet = new DataSet();
        myCommand.Fill(myDataSet, "ExcelInfo");
        DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;
        DataGrid1.DataBind();
    }
</script>

<html>
<head>
</head>
<body>
    <p>
        <asp:Label ID="Label1" runat="server">SpreadSheetContents:</asp:Label></p>
    <asp:DataGrid ID="DataGrid1" runat="server" />\
</body>
</html>

How To Add Columns (BoundColumn and HyperLinkColumn) to DataGrid Programmatically in C#

Sometimes we need to create column in DataGrid dynamically. By using the following code you can create BoundColumn and HyperLinkColumn to DataGrid Programmatically using C#. Adding BoundColumn and HyperLinkColumn to DataGrid
  public void AddboundandHyperLinkColumn()
    {
        // First add a simple bound column
        BoundColumn nameColumn = new BoundColumn();
        nameColumn.DataField = "ProductName";
        nameColumn.DataFormatString = "{0}";
        nameColumn.HeaderText = "Product";

        // Now add the HyperLink column
        HyperLinkColumn linkColumn = new HyperLinkColumn();
        linkColumn.DataTextField = "ProductName";
        linkColumn.DataTextFormatString = "{0} Details";
        linkColumn.DataNavigateUrlField = "ProductID";
        linkColumn.DataNavigateUrlFormatString = "/MyApp/ProductDetails.aspx={0}";
        linkColumn.HeaderText = "Details";

        // Add the link in a BoundColumn
        // where the text can be the same for all rows
        BoundColumn blinkColumn = new BoundColumn();
        blinkColumn.DataField = "ProductID";
        blinkColumn.DataFormatString = "Details";
        blinkColumn.HeaderText = "Details";

        DataGrid1.Columns.Add(nameColumn);
        DataGrid1.Columns.Add(linkColumn);
        DataGrid1.Columns.Add(blinkColumn);
        DataGrid1.AutoGenerateColumns = false;

        DataTable dt = GetNorthwindProductTable();
        DataGrid1.DataSource = dt;
        DataGrid1.DataBind();
    }
Note that I added three columns. The first was a simple text column (BoundColumn) with the product name. For the second column I added a link to a product details page using the HyperLinkColumn. For the third column I showed an alternate way of adding a link column if the link text can be the same for all rows, such as "Details". Just added the HTML link tag as text to the BoundColumn. It will be rendered as an HTML link when you view the page.

Using SqlBulkCopy to Import Excel Spreadsheet Data into SQL Server

Let's take an Excel Workbook with a worksheet, called Data, that contains 1000 rows of data broken into 2 columns, ID and Data. I want to copy this data into a SQL Server Database Table, called ExcelData, with the same schema. Just a little bit of code transfers the data from the Excel Spreadsheet into the SQL Server Database Table:
// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft
.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
Properties=""Excel 8.0;HDR=YES;""";

// Create Connection to Excel Workbook
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("Select ID,Data FROM [Data$]", connection);

connection.Open();

// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=.;
Initial Catalog=Test;Integrated Security=True";

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "ExcelData";
bulkCopy.WriteToServer(dr);
}
}
}

How To Copy Table Data Between SQL Servers at High Speeds - Through SqlBulkCopy

SqlBulkCopy is a new feature in ADO.NET 2.0 that gives you DTS-like speeds when you need to programmatically copy data from one database to another. Late last night I needed to harness SqlBulkCopy when for some unknown reason my beloved Red Gate Tools kept hanging when trying to transfer data from SQL Server 2000 to SQL Server 2005. Lucky for me my situation was simple. I had identical tables on both databases. I just needed to populate those empty tables on SQL Server 2005 with the data on SQL Server 2000. It took me about 20 minutes to write the code as SqlBulkCopy does the bulk of the work. Shown below is my slapped-together CopyData Class that accepts 2 connection strings, one for the source database and one for the destination database. A single method CopyTable is called with the name of the table whose data needs to be transferred from one database to another. /// /// CopyData /// public class CopyData { string _sourceConnectionString; string _destinationConnectionString; public CopyData(string sourceConnectionString, string destinationConnectionString) { _sourceConnectionString = sourceConnectionString; _destinationConnectionString = destinationConnectionString; } public void CopyTable(string table) { using (SqlConnection source = new SqlConnection(_sourceConnectionString)) { string sql = string.Format("SELECT * FROM [{0}]", table); SqlCommand command = new SqlCommand(sql, source); source.Open(); IDataReader dr = command.ExecuteReader(); using (SqlBulkCopy copy = new SqlBulkCopy(_destinationConnectionString)) { copy.DestinationTableName = table; copy.WriteToServer(dr); } } } } It assumes you passed in valid connection strings, the databases actually exist, and the table exists at both databases. If not, you can count on an unhandled SqlException being thrown at you. SqlBulkCopy has the option of accepting an IDataReader as input, so the CopyTable method opens up a connection to the source database and does a “SELECT *” on the source table using the SqlCommand object's ExecuteReader. An instance of SqlBulkCopy is created and passed the connectionstring of the destination database in its constructor. The name of the destination table is provided and WriteToServer takes all the information from the IDataReader and puts it in the empty destination table. Copying table data is as simple as: CopyData copier = new CopyData(".ConnectionString1.", ".ConnectionString2."); copier.CopyTable(".TableName.");

Saturday, February 9, 2008

Nested DataList to show Master/Details relationship in ASP.NET 2.0

This will help you to nest grdiviews inside ASP.NET. There may be many ways to do this, but this is my way( May not be the best, let me know if you have any other

1. First drop a DataList to the page, also set its DataKeyNames to your primary key

2. Add a new template column to this DataList

3. Place another DataList this template column.

<%@ Page Language="C#" %>

<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
 private DataSet CreateDS()
 {
     DataSet ds = new DataSet();
     if (Session["dsEvents"] == null)
     {
         DataTable dt = new DataTable("PersonData");
         DataRow dr;
         dt.Columns.Add(new DataColumn("Person_ID", typeof(Int32)));
         dt.Columns.Add(new DataColumn("PersonName", typeof(string)));
         dt.Columns.Add(new DataColumn("Company", typeof(string)));
         for (int i = 1; i < 10; i++)
         {
             dr = dt.NewRow();
             dr[0] = i;
             dr[1] = "Person " + i;
             dr[2] = "Company " + i;
             dt.Rows.Add(dr);
         }
         DataColumn parentCol;
         parentCol = dt.Columns["Person_ID"];

         ds.Tables.Add(dt);
         DataColumn[] PrimaryKeyColumns = new DataColumn[1];
         PrimaryKeyColumns[0] = dt.Columns["Person_Id"];
         dt.PrimaryKey = PrimaryKeyColumns;

         dt = new DataTable("Orders");
         dt.Columns.Add(new DataColumn("PrimaryKey", typeof(Int32)));
         dt.Columns.Add(new DataColumn("ForeignKey", typeof(Int32)));
         dt.Columns.Add(new DataColumn("Order", typeof(string)));
         for (int i = 1; i < 60; i++)
         {
             dr = dt.NewRow();
             dr[0] = i;
             dr[1] = 1 + i % 9;
             dr[2] = "Order # " + i;
             dt.Rows.Add(dr);
         }
         ds.Tables.Add(dt);

         DataColumn childCol;
         childCol = dt.Columns["ForeignKey"];
         DataRelation relation1;
         relation1 = new DataRelation("CustomersOrders", parentCol, childCol);
         // Add the relation to the DataSet.
         ds.Relations.Add(relation1);

         Session["dsEvents"] = ds;
     }
     else
     {
         ds = (DataSet)Session["dsEvents"];
     }
     return ds;

 }
 protected DataView Function(int pid)
 {

     DataView dv = new DataView(CreateDS().Tables[1]);
     dv.RowFilter = "ForeignKey=" + pid;
     return dv;
 }

 protected void Page_Load(object sender, EventArgs e)
 {
     if (!IsPostBack)
     {

         DataList1.DataSource = CreateDS();
         DataList1.DataBind();

     }

 }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
 <title>Untitled Page</title>
</head>
<body>
 <form id="form1" runat="server">
     <div>
         <asp:DataList ID="DataList1" runat="server" Width="100%">
             <ItemTemplate>
                 <asp:LinkButton ID="lbtnTeamPage" runat="server" CommandArgument='<%#Eval("Person_ID") %>'
                     ForeColor="Black" Font-Underline="True" Text='<%#Eval("Person_ID")%>' />
                 <asp:DataList ID="DataList2" runat="server" Width="100%" DataSource='<%#Function(Convert.ToInt32(Eval("Person_ID")))%>'>
                     <ItemTemplate>
                         <asp:LinkButton ID="Lnk1" runat="server" Font-Underline="True" ForeColor="Black"
                             Text='<%#Eval("Order")%>' />
                     </ItemTemplate>
                 </asp:DataList>
             </ItemTemplate>
         </asp:DataList>
     </div>
 </form>
</body>
</html>
This child DataList has to show the content based on the primary of the row thats binded to main DataList . Here in my case “pId” is the primary key. The nesting happens here
<asp:DataList ID="DataList2" runat="server" Width="100%" DataSource='<%#Function(Convert.ToInt32(Eval("Person_ID")))%>'>
what I have done is that, I have called function "Function" and passed the primary key to that function. This function will return a datatable. So each time a row is bound to the main DataList , the child DataList also bound with the corresponding values. The functions is like this protected DataView Function(int pid) { return }

Wednesday, February 6, 2008

How To Convert Movie File To FLV Format using ffmpeg

 protected void convert_Click(object sender, EventArgs e)
   {


       Process ffmpeg; // creating process

       string video;
       string mpg;
       video = Page.MapPath("FreeHugs.wmv"); // setting video input name with path
       mpg = Page.MapPath("") + "\\video.flv"; // thumb name with path !
       ffmpeg = new Process();

       ffmpeg.StartInfo.Arguments = " -i " + video + " -s 480*360 -deinterlace -ab 32 -r 15 -ar 22050 -ac 1 " + mpg; // arguments !
       ffmpeg.StartInfo.FileName = Page.MapPath("ffmpeg.exe");

       ffmpeg.Start(); // start !
       ffmpeg.WaitForExit();
       ffmpeg.Close();

   }
   public void ExtractImage()
   {
       Process ffmpeg; // creating process
       string video;
       string thumb;
       video = Page.MapPath("video.flv"); // setting video input name with path
       thumb = Page.MapPath("") + "\\frame.jpg"; // thumb name with path !
       ffmpeg = new Process();

       ffmpeg.StartInfo.Arguments = " -i \"" + video + "\" -s 108*180 -vframes 1 -f image2 -vcodec mjpeg \"" + thumb + "\""; // arguments !
       ffmpeg.StartInfo.FileName = Page.MapPath("ffmpeg.exe");
       ffmpeg.Start(); // start !

   }