Dot Net Examples: 2011

Wednesday, July 6, 2011

AllowEdit property

DataViewAllowEditProperty.aspx

    <%@ Page Language="C#" AutoEventWireup="true" %>
    <%@ 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">
        protected void Button1_Click(object sender, System.EventArgs e)
        {
            DataTable dt = new DataTable();
            dt.TableName = "Books";

            DataColumn dc1 = new DataColumn();
            dc1.ColumnName = "BookID";
            dc1.DataType = typeof(int);
            dc1.AllowDBNull = false;
            dc1.Unique = true;

            DataColumn dc2 = new DataColumn();
            dc2.ColumnName = "Category";
            dc2.DataType = typeof(string);

            DataColumn dc3 = new DataColumn();
            dc3.ColumnName = "BookName";
            dc3.DataType = typeof(string);

            DataColumn dc4 = new DataColumn();
            dc4.ColumnName = "Author";
            dc4.DataType = typeof(string);

            dt.Columns.AddRange(new DataColumn[] { dc1, dc2, dc3, dc4 });

            dt.Rows.Add(new object[] { 1, ".NET", "ASP.NET 3.5 Application Architecture and Design", "Vivek Thakur" });
            dt.Rows.Add(new object[] { 2, "Games", "Cocos2d for iPhone 0.99 Beginner's Guide", "Pablo Ruiz" });
            dt.AcceptChanges();

            GridView1.DataSource = dt.DefaultView;
            GridView1.DataBind();

            //this line create a new DataView
            DataView dView = new DataView(dt);
            dView.Sort = "BookName DESC" ;

            Label1.Text = "Here we create a new DataView<br />" +
                          "and set the sort order (BookName DESC)";

            GridView2.DataSource = dView;
            GridView2.DataBind();

            dView.AllowEdit = true;
            //this line edit a row in DataView which index is 0
            dView[0]["Category"] = ".NET";
            dView[0]["BookName"] = "Microsoft Windows Workflow Foundation 4.0 Cookbook";
            dView[0]["Author"] = "Andrew Zhu ";

            Label2.Text = "Here we edit a row in DataView, Now the DataView is...";
            GridView3.DataSource = dView;
            GridView3.DataBind();

            Label3.Text = "Now the source DataTable is...";
            GridView4.DataSource = dt;
            GridView4.DataBind();
        }
    </script>

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head id="Head1" runat="server">
        <title>Using AllowEdit property - how to edit update row in a DataView in ado.net</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <h2 style="color:DarkBlue; font-style:italic;">
                Using AllowEdit property - how to
                <br /> edit update row in a DataView in ado.net
            </h2>
            <hr width="450" align="left" color="CornFlowerBlue" />
            <asp:GridView
                ID="GridView1"
                runat="server"
                BorderColor="Snow"
                ForeColor="Snow"
                Width="700"
                Font-Names="Courier"
                >
                <HeaderStyle BackColor="DarkMagenta" Height="30" />
                <RowStyle BackColor="DarkKhaki" />
                <AlternatingRowStyle BackColor="DarkOliveGreen" />
            </asp:GridView>
            <asp:Label
                 ID="Label1"
                 runat="server"
                 Font-Size="Large"
                 ForeColor="DodgerBlue"
                 Font-Italic="true"
                 >
            </asp:Label>
            <asp:GridView
                ID="GridView2"
                runat="server"
                BorderColor="Snow"
                ForeColor="Snow"
                Width="700"
                Font-Names="Courier"
                >
                <HeaderStyle BackColor="Maroon" Height="30" />
                <RowStyle BackColor="DarkKhaki" />
                <AlternatingRowStyle BackColor="DarkOliveGreen" />
            </asp:GridView>
            <asp:Label
                 ID="Label2"
                 runat="server"
                 Font-Size="Large"
                 ForeColor="DodgerBlue"
                 Font-Italic="true"
                 >
            </asp:Label>
            <asp:GridView
                ID="GridView3"
                runat="server"
                BorderColor="Snow"
                ForeColor="Snow"
                Width="700"
                Font-Names="Courier"
                >
                <HeaderStyle BackColor="Maroon" Height="30" />
                <RowStyle BackColor="DarkKhaki" />
                <AlternatingRowStyle BackColor="DarkOliveGreen" />
            </asp:GridView>
            <asp:Label
                 ID="Label3"
                 runat="server"
                 Font-Size="Large"
                 ForeColor="DodgerBlue"
                 Font-Italic="true"
                 >
            </asp:Label>
            <asp:GridView
                ID="GridView4"
                runat="server"
                BorderColor="Snow"
                ForeColor="Snow"
                Width="700"
                Font-Names="Courier"
                >
                <HeaderStyle BackColor="DarkMagenta" Height="30" />
                <RowStyle BackColor="DarkKhaki" />
                <AlternatingRowStyle BackColor="DarkOliveGreen" />
            </asp:GridView>
            <asp:Button
                ID="Button1"
                runat="server"
                OnClick="Button1_Click"
                Text="Populate GridView"
                Height="45"
                Font-Bold="true"
                ForeColor="DodgerBlue"
                />
        </div>
        </form>
    </body>
    </html>

Sunday, May 15, 2011

DataView ToTable method to create a new DataTable in ado.net

DataViewToTableMethod.aspx

<%@ Page Language="C#" AutoEventWireup="true" %>
<%@ 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">
protected void Button1_Click(object sender, System.EventArgs e)
{
DataTable dt = new DataTable();
dt.TableName = "Books";

DataColumn dc1 = new DataColumn();
dc1.ColumnName = "BookID";
dc1.DataType = typeof(int);
dc1.AllowDBNull = false;
dc1.Unique = true;

DataColumn dc2 = new DataColumn();
dc2.ColumnName = "Category";
dc2.DataType = typeof(string);

DataColumn dc3 = new DataColumn();
dc3.ColumnName = "BookName";
dc3.DataType = typeof(string);

DataColumn dc4 = new DataColumn();
dc4.ColumnName = "Author";
dc4.DataType = typeof(string);

dt.Columns.AddRange(new DataColumn[] { dc1, dc2, dc3, dc4 });

dt.Rows.Add(new object[] { 1, "iPhone", "iPhone User Interface Cookbook: RAW", "Cameron Banga" });
dt.Rows.Add(new object[] { 2, "MySQL", "MySQL 5.1 Plugin Development", "Andrew Hutchings, Sergei Golubchik" });
dt.Rows.Add(new object[] { 3, "MySQL", "MySQL Admin Cookbook", "Daniel Schneller, Udo Schwedt" });
dt.AcceptChanges();

Label1.Text = "Source DataTable";
GridView1.DataSource = dt.DefaultView;
GridView1.DataBind();

//this line create a new DataView
DataView dView = new DataView(dt);
dView.RowFilter = "Category = 'MySQL'";

Label2.Text = "Here we create a new DataView<br />" +
"and set the RowFilter (Category = 'MySQL')";

GridView2.DataSource = dView;
GridView2.DataBind();

//this line create a new DataTable from DataView
DataTable dt2 = dView.ToTable();

Label3.Text = "Here we create a new DataTable from DataView";
GridView3.DataSource = dt2;
GridView3.DataBind();
}
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>How to use DataView ToTable method to create a new DataTable in ado.net</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h2 style="color:DarkBlue; font-style:italic;">
How to use DataView ToTable method
<br /> to create a new DataTable in ado.net
</h2>
<hr width="450" align="left" color="CornFlowerBlue" />
<asp:Label
ID="Label1"
runat="server"
Font-Size="Large"
ForeColor="DodgerBlue"
Font-Italic="true"
>
</asp:Label>
<asp:GridView
ID="GridView1"
runat="server"
BorderColor="Snow"
ForeColor="Snow"
Width="600"
Font-Names="Courier"
>
<HeaderStyle BackColor="DarkSlateBlue" Height="30" />
<RowStyle BackColor="MediumVioletRed" />
<AlternatingRowStyle BackColor="PaleVioletRed" />
</asp:GridView>
<br />
<asp:Label
ID="Label2"
runat="server"
Font-Size="Large"
ForeColor="DodgerBlue"
Font-Italic="true"
>
</asp:Label>
<asp:GridView
ID="GridView2"
runat="server"
BorderColor="Snow"
ForeColor="Snow"
Width="600"
Font-Names="Courier"
>
<HeaderStyle BackColor="DarkSlateBlue" Height="30" />
<RowStyle BackColor="MediumVioletRed" />
<AlternatingRowStyle BackColor="PaleVioletRed" />
</asp:GridView>
<br />
<asp:Label
ID="Label3"
runat="server"
Font-Size="Large"
ForeColor="DodgerBlue"
Font-Italic="true"
>
</asp:Label>
<asp:GridView
ID="GridView3"
runat="server"
BorderColor="Snow"
ForeColor="Snow"
Width="600"
Font-Names="Courier"
>
<HeaderStyle BackColor="DarkSlateBlue" Height="30" />
<RowStyle BackColor="MediumVioletRed" />
<AlternatingRowStyle BackColor="PaleVioletRed" />
</asp:GridView>
<asp:Button
ID="Button1"
runat="server"
OnClick="Button1_Click"
Text="Populate GridView"
Height="45"
Font-Bold="true"
ForeColor="DodgerBlue"
/>
</div>
</form>
</body>
</html>

Wednesday, April 13, 2011

Reduce DB Space

SpreadsheetGear: ASP.NET Excel Reporting

Easily create richly formatted Excel reports without Excel using the new generation of spreadsheet technology built from the ground up for scalability and reliability. Recently I have delete 2 million unwanted records from my sql server database table, what i realise is even after deleting records, space used by database is not reducing.

1) Whenever we delete records from table, sql server doesn't reduce size of database immediately.

2) Even after deleting table , sql server doesn't reduce size of database.

3) Instead of Freeing space for deleted records, sql server marks pages containing deleted records as free pages, showing that they belong to the table. When new data are inserted, they are put into those pages first. Once those pages are filled up, SQL Server will allocate new pages.

So In order to claim database space after deleting records in Table, go through following steps:

1) Check what is Size of your Database using following command?

Exec sp_spaceused

2) Delete Records from table, If you have already did that skip this step.

3) Run below command to claim unused database space.

DBCC SHRINKDATABASE(0)

DBCC SHRINKDATABASE command - Shrinks the size of the data and log files in the specified database.

Best Practise to use this command

A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.

If you have Created, Alter or Drop any Database table recently then run below command.

DBCC UPDATEUSAGE(0)

DBCC UPDATEUSAGE(0) - Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

Example showing how this command helps me to reduce size of my database after deleting records from table.

1) Take Backup of your Production Database.

2) Take Backup of Table Scripts of your Production Database.

3) Create Test Database in Local Environment.

4) Run Tables creation script.

5) Restore Production Database to Test Database in local environment

6) Check Size of your Database

Exec sp_spaceused

7) Run Update Usage command
DBCC UPDATEUSAGE(0)

8) Check Size of your Database
Exec sp_spaceused

9) Run Shrink Database command
DBCC SHRINKDATABASE(0)

10) Check Size of your Database
Exec sp_spaceused

If everything goes smooth then you would see that your database size is reduced.

Thursday, April 7, 2011

How to set change Chart Legend TitleSeparatorColor programmatically in asp.net

ChartLegendTitleSeparatorColor.aspx

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

<!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 Button1_Click(object sender, System.EventArgs e)
   {
   Chart1.Legends["Legend1"].TitleSeparatorColor = System.Drawing.Color.BlanchedAlmond;
   }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
   <title>How to set change Chart Legend TitleSeparatorColor programmatically in asp.net</title>
   <style type="text/css">
   h2
   {
   color:Crimson;
   font-style:italic;
   }
   </style>
</head>
<body>
   <form id="form1" runat="server">
   <div>
   <h2>Chart example and tutorial: How to set change Chart Legend<br /> TitleSeparatorColor programmatically in asp.net</h2>
   <hr width="625" align="left" color="IndianRed" />
   <br />
   <asp:Chart
   ID="Chart1"
   runat="server"
   Width="625"
   BackColor="DarkCyan"
   BorderlineColor="AliceBlue"
   BorderlineDashStyle="Solid"
   BorderlineWidth="2"
   >
   <Legends>
   <asp:Legend
   Name="Legend1"
   BackColor="DodgerBlue"
   ForeColor="Snow"
   BorderColor="MidnightBlue"
   Docking="Right"
   TableStyle="Wide"
   Title="Nokia Phone Legend"
   TitleForeColor="Snow"
   TitleSeparator="DoubleLine"
   >
   </asp:Legend>
   </Legends>
   <Series>
   <asp:Series
   Name="NokiaPhone"
   YValueType="Double"
   ChartArea="DefaultChartArea"
   ChartType="Pyramid"
   Palette="Excel"
   >
   <Points>
   <asp:DataPoint AxisLabel="Nokia N8" YValues="549" />
   <asp:DataPoint AxisLabel="Nokia N97 Mini Driver Ed." YValues="399.99" />
   <asp:DataPoint AxisLabel="Nokia E72 Driver Edition" YValues="349" />
   <asp:DataPoint AxisLabel="X6 16GB Driver Edition" YValues="329" />
   <asp:DataPoint AxisLabel="Nokia C6" YValues="309" />
   <asp:DataPoint AxisLabel="Nokia 5800 Driver Edition" YValues="289" />
   </Points>
   </asp:Series>
   </Series>
   <ChartAreas>
   <asp:ChartArea
   Name="DefaultChartArea"
   BorderDashStyle="Solid"
   BorderWidth="2"
   BorderColor="OliveDrab"
   BackColor="DarkOliveGreen"
   >
   <Area3DStyle Enable3D="true" LightStyle="Realistic" />
   </asp:ChartArea>
   </ChartAreas>
   </asp:Chart>
   <br />
   <asp:Button
   ID="Button1"
   runat="server"
   Text="Set Chart Legend TitleSeparatorColor BlanchedAlmond"
   Font-Bold="true"
   OnClick="Button1_Click"
   ForeColor="DarkBlue"
   Height="45"
   />
   </div>
   </form>
</body>
</html>

Wednesday, March 30, 2011

How to populate ComboBox from XmlDataSource in asp.net

ComboBoxXmlDataSource.aspx

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

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

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

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
   <title>Ajax ComboBox - How to populate ComboBox from XmlDataSource in asp.net</title>
</head>
<body>
   <form id="form1" runat="server">
   <div>
   <h2 style="color:DarkBlue; font-style:italic;">
   ASP.NET Ajax ComboBox - How to populate ComboBox
   <br /> from XmlDataSource programmatically in asp.net
   </h2>
   <hr width="525" align="left" color="LightBlue" />
   <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
   </asp:ToolkitScriptManager>
   <br /><br />
   <asp:ComboBox
   ID="ComboBox1"
   runat="server"
   DropDownStyle="DropDown"
   DataSourceID="XmlDataSource1"
   DataTextField="Name"
   DataValueField="ID"
   AutoCompleteMode="None"
   CaseSensitive="false"
   RenderMode="Block"
   AppendDataBoundItems="true"
   AutoPostBack="false"
   Font-Names="Comic Sans MS"
   Font-Size="Medium"
   ForeColor="Green"
   >
   </asp:ComboBox>
   <asp:XmlDataSource ID="XmlDataSource1" runat="server">
   <Data>
   <Colors>
   <Color ID="1" Name="Crimson" />
   <Color ID="2" Name="CornFlowerBlue" />
   <Color ID="3" Name="BlanchedAlmond" />
   <Color ID="4" Name="IndianRed" />
   <Color ID="5" Name="Coral" />
   </Colors>
   </Data>
   </asp:XmlDataSource>
   </div>
   </form>
</body>
</html>

Monday, March 28, 2011

How to use ComboBox RenderMode Block and Inline in asp.net

ComboBoxRenderMode.aspx

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

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

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

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
   <title>Ajax ComboBox - How to use ComboBox RenderMode Block and Inline in asp.net</title>
</head>
<body>
   <form id="form1" runat="server">
   <div>
   <h2 style="color:DarkBlue; font-style:italic;">
   ASP.NET Ajax ComboBox - How to use ComboBox
   <br /> RenderMode Block and Inline in asp.net
   </h2>
   <hr width="525" align="left" color="LightBlue" />
   <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
   </asp:ToolkitScriptManager>
   <asp:Label
   ID="Label1"
   runat="server"
   Font-Size="Large"
   ForeColor="DarkGreen"
   Font-Italic="true"
   Text="ComboBox RenderMode Inline"
   >
   </asp:Label>
   <br /><br />
   <asp:ComboBox
   ID="ComboBox1"
   runat="server"
   DropDownStyle="DropDown"
   AutoCompleteMode="None"
   CaseSensitive="false"
   RenderMode="Inline"
   AutoPostBack="false"
   Font-Names="Comic Sans MS"
   Font-Size="Medium"
   ForeColor="SaddleBrown"
   >
   <asp:ListItem Text="Red"></asp:ListItem>
   <asp:ListItem Text="Green"></asp:ListItem>
   <asp:ListItem Text="Yellow"></asp:ListItem>
   <asp:ListItem Text="White"></asp:ListItem>
   </asp:ComboBox>
   <br /><br /><br />
   <asp:Label
   ID="Label2"
   runat="server"
   Font-Size="Large"
   ForeColor="Crimson"
   Font-Italic="true"
   Text="ComboBox RenderMode Block"
   >
   </asp:Label>
   <br /><br />
   <asp:ComboBox
   ID="ComboBox2"
   runat="server"
   DropDownStyle="DropDown"
   AutoCompleteMode="None"
   CaseSensitive="false"
   RenderMode="Block"
   AutoPostBack="false"
   Font-Names="Comic Sans MS"
   Font-Size="Medium"
   ForeColor="SaddleBrown"
   >
   <asp:ListItem Text="IndianRed"></asp:ListItem>
   <asp:ListItem Text="Crimson"></asp:ListItem>
   <asp:ListItem Text="DarkBlue"></asp:ListItem>
   <asp:ListItem Text="Khaki"></asp:ListItem>
   </asp:ComboBox>
   </div>
   </form>
</body>
</html>