The Circle of Dotnet: Histogram chart with Asp.net,C#,Sql server

Html code:

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

<%@ Register Assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"

Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %>

<!DOCTYPE html>

.auto-style1 {

color: #FF6600;

text-align: center;

}

</style>

</head>

<body>

<div>

<tr>

<td>

<table>

<tr>

</tr>

<tr>

<strong style="font-size: x-large;">Region Details</strong></td>

</tr>

<tr>

</tr>

<tr>

<td>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowCommand="GridView1_RowCommand" OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" ShowFooter="True" AllowPaging="True" AllowSorting="True" OnPageIndexChanging="GridView1_PageIndexChanging" PageSize="5" CellPadding="3" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" Width="600px">

<asp:TemplateField HeaderText="Id">

<asp:Label ID="lblRId" runat="server" Text='<%# Eval("RId")%>'></asp:Label>

</ItemTemplate>

<asp:Label ID="txtEditRId" runat="server" Text='<%# Eval("RId")%>'></asp:Label>

</EditItemTemplate>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Region">

<asp:Label ID="lblRegion" runat="server" Text='<%# Eval("Region")%>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtEditRegion" runat="server" Text='<%# Eval("Region")%>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtftRegion" runat="server"></asp:TextBox>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Sales">

<asp:Label ID="lblSales" runat="server" Text='<%# Eval("Sales")%>'></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtEditSales" runat="server" Text='<%# Eval("Sales")%>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="txtftSales" runat="server"></asp:TextBox>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Edit">

<asp:LinkButton ID="hlEdit" runat="server" CommandName="Edit">Edit</asp:LinkButton>  

<asp:LinkButton ID="hlDelete" runat="server" CommandName="Delete">Delete</asp:LinkButton>  

</ItemTemplate>

<asp:LinkButton ID="hlUpdate" runat="server" CommandName="Update">Update</asp:LinkButton>  

<asp:LinkButton ID="hlCancel" runat="server" CommandName="Cancel">Cancel</asp:LinkButton>

</EditItemTemplate>

<asp:LinkButton ID="hlInsert" runat="server" CommandName="Insert">Insert</asp:LinkButton>

</FooterTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</td>

</tr>

</table>

</td>

</tr>

<tr>

<td>

<tr>

<strong style="font-size: x-large;">Histogram chart </strong></td>

</tr>

<tr>

<td>

<asp:Chart ID="Chart1" runat="server" Height="300px" Width="600px">

<asp:Title ShadowOffset="3" Name="Items" />

</Titles>

<asp:Legend Alignment="Center" Docking="Bottom" IsTextAutoFit="False" Name="Default"

LegendStyle="Row" />

</Legends>

<asp:Series Name="Default" />

</Series>

<asp:ChartArea Name="ChartArea1" BorderWidth="0" />

</ChartAreas>

</asp:Chart>

</td>

</tr>

</table>

</td>

</tr>

</table>

</div>

</form>

</body>

</html>

C# Code:

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.DataVisualization.Charting;

using System.Web.UI.WebControls;

public partial class Region : System.Web.UI.Page

{

//Declaring Sql Connection string -- Please change this

SqlConnection connectionString = new SqlConnection("Database=Sample;server=TLIHYDDT065;uid=sa;password=developers");

//Page Load Event

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

BindGrid();

BindChart();

}

//Method to bind the Chart Control

protected void BindChart()

{

try

{

DataSet ds = new DataSet();

DataTable dt = new DataTable();

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.Connection = connectionString;

sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.CommandText = "Proc_Region";

sqlCmd.Parameters.Add("@Type", SqlDbType.VarChar, 50).Value = "selectchart";

//connectionString.Open();

//string cmdstr = "SELECT Region,Sales FROM tblSalesByRegion";

//SqlCommand cmd = new SqlCommand(sqlCmd, connectionString);

SqlDataAdapter adp = new SqlDataAdapter(sqlCmd);

adp.Fill(ds);

dt = ds.Tables[0];

string[] x = new string[dt.Rows.Count];

int[] y = new int[dt.Rows.Count];

for (int i = 0; i < dt.Rows.Count; i++)

{

x[i] = dt.Rows[i][0].ToString();

y[i] = Convert.ToInt32(dt.Rows[i][1]);

}

Chart1.Series[0].Points.DataBindXY(x, y);

Chart1.Series[0].ChartType = SeriesChartType.Column;

Chart1.ChartAreas["ChartArea1"].Area3DStyle.Enable3D = false;

Chart1.Legends[0].Enabled = true;

//connectionString.Close();

}

catch (Exception err)

{

ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert(" + err + ");", true);

}

finally

{

// connectionString.Close();

}

//Method to bind the Gridview -- Dynamic data Database

public void BindGrid()

{

try

{

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.Connection = connectionString;

sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.CommandText = "Proc_Region";

sqlCmd.Parameters.Add("@Type", SqlDbType.VarChar, 50).Value = "select";

SqlDataAdapter da = new SqlDataAdapter(sqlCmd);

DataSet ds = new DataSet();

da.Fill(ds, "TableTest");

GridView1.DataSource = ds;

GridView1.DataBind();

BindChart();

}

catch (Exception err)

{

ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert(" + err + ");", true);

}

finally

{

// connectionString.Close();

}

//Index changing event

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

BindGrid();

GridView1.PageIndex = e.NewPageIndex;

GridView1.DataBind();

}

//Row edit cancelling event

protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

GridView1.EditIndex = -1;

BindGrid();

}

//Row command event

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

{

try

{

if (e.CommandName == "Insert")

{

GridViewRow row = (GridViewRow)(((Control)e.CommandSource).NamingContainer);

// TextBox txtTestId = (TextBox)row.FindControl("txtTestId");

TextBox txtRegion = (TextBox)row.FindControl("txtftRegion");

TextBox txtSales = (TextBox)row.FindControl("txtftSales");

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.Connection = connectionString;

sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.CommandText = "Proc_Region";

sqlCmd.Parameters.Add("@Type", SqlDbType.VarChar, 50).Value = "insert";

//sqlCmd.Parameters.Add("@testId", SqlDbType.Int).Value = txtTestId.Text;

sqlCmd.Parameters.Add("@Region", SqlDbType.VarChar, 50).Value = txtRegion.Text;

sqlCmd.Parameters.Add("@Sales", SqlDbType.Int).Value = txtSales.Text;

connectionString.Open();

int i = sqlCmd.ExecuteNonQuery();

connectionString.Close();

GridView1.EditIndex = -1;

BindGrid();

}

catch (Exception err)

{

ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert(" + err + ");", true);

}

finally

{

// connectionString.Close();

}

//Row level event to Delete a Record

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

try

{

Label lblTestId = (Label)GridView1.Rows[e.RowIndex].FindControl("lblRId");

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.Connection = connectionString;

sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.CommandText = "Proc_Region";

sqlCmd.Parameters.Add("@Type", SqlDbType.VarChar, 50).Value = "delete";

sqlCmd.Parameters.Add("@RId", SqlDbType.Int).Value = lblTestId.Text;

SqlDataAdapter da = new SqlDataAdapter(sqlCmd);

DataSet ds = new DataSet();

da.Fill(ds, "TableTest1");

GridView1.EditIndex = -1;

BindGrid();

}

catch (Exception err)

{

ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert(" + err + ");", true);

}

finally

{

// connectionString.Close();

}

//Row level event to Edit a Record--opens

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)

{

GridView1.EditIndex = e.NewEditIndex;

BindGrid();

}

//Row level event to Update a Record

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

try

{

Label txtRId = (Label)GridView1.Rows[e.RowIndex].FindControl("txtEditRId");

TextBox txtRegion = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtEditRegion");

TextBox txtSales = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtEditSales");

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.Connection = connectionString;

sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.CommandText = "Proc_Region";

sqlCmd.Parameters.Add("@Type", SqlDbType.VarChar, 50).Value = "update";

sqlCmd.Parameters.Add("@RId", SqlDbType.Int).Value = txtRId.Text;

sqlCmd.Parameters.Add("@Region", SqlDbType.VarChar, 50).Value = txtRegion.Text;

sqlCmd.Parameters.Add("@Sales", SqlDbType.Int).Value = txtSales.Text;

SqlDataAdapter da = new SqlDataAdapter(sqlCmd);

DataSet ds = new DataSet();

da.Fill(ds, "TableTest");

GridView1.EditIndex = -1;

BindGrid();

}

catch (Exception err)

{

ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert(" + err + ");", true);

}

finally

{

// connectionString.Close();

}

Web.config file:

<?xml version="1.0"?>

<!--

For more information on how to configure your ASP.NET application, please visit

http://go.microsoft.com/fwlink/?LinkId=169433

-->

</appSettings>

<system.webServer>

</handlers>

</system.webServer>

<system.web>

</httpHandlers>

<pages>

</controls>

</pages>

</system.web>

</configuration>

Sql Procedure:

CREATE PROCEDURE Proc_Region(

@RId int=null,

@Region varchar(50)=null,

@Sales int=null,

@type varchar(50)=null)

BEGIN

if(@type='select')

BEGIN

SELECT * FROM tblSalesByRegion

END

if(@type='insert')

BEGIN

INSERT INTO tblSalesByRegion(Region,Sales) values(@Region,@Sales)

END

if(@type='update')

BEGIN

UPDATE tblSalesByRegion SET Region=@Region,Sales=@Sales WHERE RId=@RId

END

if(@type='delete')

BEGIN

DELETE FROM tblSalesByRegion WHERE RId=@RId

END

if(@type='selectchart')

BEGIN

SELECT Region,Sales FROM tblSalesByRegion

END

The Circle of Dotnet

Search This Blog

Friday, 25 July 2014

Histogram chart with Asp.net,C#,Sql server

No comments:

Post a Comment