Aspdotnet-Sharepoint: 12/1/11

Saturday, December 31, 2011

How to user Repeater in asp.net

Repeater control is a item Data bound control and is used to display small and Flexible repeated list of items.It is similar to data list control and doesn't provide built in structure.It provides set of templates to define required structure for presenting data.The Item Template used to display the records.The static method DataBinder.Eval is used to evaluate the data at run time using late binding.Here i will show you how to bind the data to Repeater with Item template.In this example i will bind the reports of teachers for everyday using repeater
Repeater.Aspx:


 
 <%#DataBinder.Eval(Container.DataItem,"Title")%>
 

Descrption:

<%#DataBinder.Eval(Container.DataItem, "Description")%>
Fom Date: <%#DataBinder.Eval(Container.DataItem, "fromdate")%>
Todate: <%#DataBinder.Eval(Container.DataItem, "todate")%>
Send To: <%#DataBinder.Eval(Container.DataItem, "teachername")%> 
(Teacher)On:<%#DataBinder.Eval(Container.DataItem, "date")%>
To CC:
 
Status: <%#DataBinder.Eval(Container.DataItem, "status")%>

The method onitemdatabound is get the data from database and bind to the repeater control.With out using this method we can get the data at the time of page loading
Code behind:

protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)
 {
  Label lbl2;
  StringBuilder ccc = new StringBuilder();
  lbl2 = (Label)e.Item.FindControl("lblcc");
  string strd = "select t.teachername,ut.utype,ut.utypeid from teachers t,usertype ut,leaves l  where l.utype=ut.utypeid and l.teacherid=t.teacherid  and l.date='"+lbl.Text+"' and l.studentid='"+ddlchildrens.SelectedValue+"' and l.branchid='"+lblbranch.Text+"' and l.asid='"+lblasid.Text+"'";
  cmd = new SqlCommand(strd, Con);
  Con.Open();
  dr = cmd.ExecuteReader();
  while (dr.Read())
   {
    utypeid = dr["utypeid"].ToString();
    if (utypeid != "3")
       {
        tname = dr["teachername"].ToString();
        utype = dr["utype"].ToString();
        ccc.Append(tname + "(" + utype + "), ");
       }
    }
  dr.Close();
  Con.Close();
  lbl2.Text = ccc.ToString();
 }

Friday, December 30, 2011

export gridview to excel in asp.net

Title:How to Export data from grid view to excel in c#

Description:
As per previous articles we have gone through some examples like Export grid view data to word document,Export grid view to PDF,Import excel data to Asp.net Grid view ,Export data to excel in asp.net.Here i would like to share how to export grid view data to Excel sheet in asp.net using c#.net.It can be done in two steps.First is render to HTML from grid view then the present HTML to Excel.

Example:

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GdvMainmasters" Runat="server" DataSourceID="OrdrDb">
<Columns>
<asp:BoundField DataField="OrderID" HeaderText="OrderID" 
SortExpression="OrderID" />
<asp:BoundField DataField="OrderName" HeaderText="OrderName" 
SortExpression="OrderName" />
<asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />
<asp:BoundField DataField="Address" HeaderText="Address" 
SortExpression="Address" />
<asp:BoundField DataField="Amount" HeaderText="Amount" 
SortExpression="Amount" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="OrdrDb" runat="server" 
ConnectionString="<%$ ConnectionStrings:TestConnectionString %>" 
SelectCommand="SELECT * FROM [Orders]"></asp:SqlDataSource>
<asp:Button ID="btnexportOrderstoWord" runat="server" Text="ExportExcel" 
onclick="btnexportOrderstoWord_Click" /></div>
</asp:GridView>
<asp:Lable id="LblMsg" runat="server"/>
<asp:Button id="BtnSendtoExcel" Text="Export" runat="server" OnClick="BtnSendtoExcel_Click"/>
</div>
</form>
</body>
</html>

CodeBehind:

protected void BtnSendtoExcel_Click(object sender, EventArgs e)
{
try
{
Excelbtn = "ABC";
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",attachment;filename=OrderDetails.xls");
Response.ContentType = "application/ms-excel";
StringWriter swr = new StringWriter();
HtmlTextWriter hwr = new HtmlTextWriter(sw);
GdvMainmasters.AllowPaging = false;
GdvMainmasters.AutoGenerateSelectButton = false;
GdvMainmasters.Enabled = false;
GdvMainmasters.DataBind();
GdvMainmasters.HeaderRow.Cells[0].BorderStyle = BorderStyle.Inset;
GdvMainmasters.HeaderRow.Cells[1].BorderStyle = BorderStyle.Inset;
GdvMainmasters.HeaderRow.Cells[2].BorderStyle = BorderStyle.Inset;
GdvMainmasters.HeaderRow.Cells[3].BorderStyle = BorderStyle.Inset;
GdvMainmasters.HeaderRow.Cells[3].BorderStyle = BorderStyle.Inset;
for (int j = 0; j < GdvMainmasters.Rows.Count; j++)
{
GridViewRow row = GdvMainmasters.Rows[j];
GdvMainmasters.RenderControl(hwr);
Response.Write(swr.ToString());
Response.Flush();
Response.End();
}
catch (Exception e3)
{
LblMsg.Text = e3.Message;
}
}

Note:While run the above code we may get the some errors RegisterForEventValidation can only be called during Render.If you get an error then need to override the control.

Wednesday, December 28, 2011

custom gridview in asp.net

Here i will show you how to create custom Data grid view and calculating total per entire column.For this i will go for create a data grid view control for accepting Products info and storing products write into Text file
code behind:

Imports System.IO
//form load event
DataGridView.RowCount=3
DataGridView.ColumnCount=3
DataGridView.Columns(0).HeaderText="desc"
DataGridView.Columns(1).HeaderText="Price"
DataGridView.Columns(2).HeaderText="Quantity"
DataGridView.Columns(3).HeaderText="Total"
End Sub

//calculating total Based on price,quantity
This requires "Cell Enter" event of DataGridView control,This event will provide current row index and column index

//This will executes when focus comes to cell
Private Sub dataGridView1_CellEnter(ByVal sender As Object, _
    ByVal e As DataGridViewCellEventArgs) _
    Handles dataGridView1.CellEnter

msgbox("row;"&e.RowIndex&&"col:"e.ColumnIndex)

if(e.ColumnIndex=3)Then
 With DataGridView1.Rows(e.RowIndex).cells(3).Value=Val(cells(2).Value)*val(cells(1).Value )
End With
total=quantity*price
with and end with will avoid repetition of DataGridiew.rows(e.RowIndex)

//logic for store into text file
private sub Button1_click(--)

Dim s As string
For i As Byte=0 to DataGridView1.Rows.Count-2
With DataGridView1.Rows(i)
s=s&DataGridView1.cells[0].Value&""&DataGridView1.cells[1].Value&""&DataGridView1.cells[2].Value&""&DataGridView1.cells[3].Value&
End With
Next

Dim sw As StreamWriter
sw=File.CreateText("Transinfo.txt)
sw.writeline("transeinfo "&vbCrLf&" "&"&vbCrLf&"&5
vbCrLf is visual basic carriage to return line feed[new line]
sw.Close()

End Sub

Change Grid view width Dynamically:

if (e.Row.RowType == DataControlRowType.Header) {
 for (i = 0; i <= e.Row.Cells.Count - 1; i++) {
  TableCell cell5 = e.Row.Cells(i);
  cell5.Width = new Unit("175px");
 }
}

Tuesday, December 27, 2011

sql bulk copy in .net

Title:How to use SQL bulk copy in Asp.net using C#.net

Description:
SQL bulk copy is a concept of copying records from source database to destination database in asp.net.Here the source database can be any data base.The destination should be SQL server.Because SQL bulk copy is compatible with sql server.The advantage of SQL bulk copy concept is copying records will be faster with less burden of of developer.This process requires three steps.Here i have taken one button to perform this operation
1.Creating data reader and write into source data-base table
2.Creating sqlbulkcopy component with destination database component
3.Providing data reader to bulk copy component for writing to destination database table.

Imports System.Data.Oledb
Imports System.Data.SqlClient
//button click event
private sub button1_Click
Dim cn As New OleDbConnection("provider=msdaora.1;user Id=scott;password=tiger")
Dim cmd As New OleDbCommand("select*from dept",cn)
Dim dr As OleDbDataReader
cn.Open()
dr=cmd.ExcuteReader()
//creating destination Database connection and bulk copy component
Dim Dcn As New OleDbConnection("user Id=sa;password=;Database=invoice")
Dcn.Open()
Dim Bcopy As New SqlBulkCopy(Dcn)
Bcopy.DestinationTableName="dept"
//provide data reader to bulk copy component for reading from source and copying destination
Dcopy.WriteToServe(dr)
//Writretoserver will read using Dr and write to sqlserver table
dr.Close()
Cn.Close()
Dcn.close()
MsgBox("Copied Succesfully")
End Sub
)

C#:

using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Data.Oledb;
using System.Data.SqlClient;


//button click event

OleDbConnection cn = new OleDbConnection("provider=msdaora.1;user Id=scott;password=tiger");
OleDbCommand cmd = new OleDbCommand("select*from dept", cn);
OleDbDataReader dr = default(OleDbDataReader);
cn.Open();
dr = cmd.ExcuteReader();
//creating destination Database connection and bulk copy component
OleDbConnection Dcn = new OleDbConnection("user Id=sa;password=;Database=invoice");
Dcn.Open();
SqlBulkCopy Bcopy = new SqlBulkCopy(Dcn);
Bcopy.DestinationTableName = "dept";
//provide data reader to bulk copy component for reading from source and copying destination
Dcopy.WriteToServe(dr);
//Writretoserver will read using Dr and write to sqlserver table
dr.Close();
Cn.Close();
Dcn.close();
Interaction.MsgBox("Copied Succesfully");

Monday, December 26, 2011

how to write to a text file in c#

In .net we have a various classes to create, write and perform some operations on a text file which are file .Here we are using create() method to create a text file and using some classes to reading and writing text files those are TextReader,StreamReader,StreamWriter,TextWriter classes.These are abstract classes .Here i have taken a logfilepath which is the physical path of the text file.If the file is existing in path then we will write message into this text file.If the file is not existing in the path then we will create a file in this path and write a message into it
Code Behind:

public static string path = AppDomain.CurrentDomain.BaseDirectory;
public static string LogfilePath = path + @"archives\error.txt";
public static void WriteError(string msg)
 {
  try
   {
    if (!File.Exists(LogfilePath))
       {
       File.Create(LogfilePath).Close();
       }
    using (StreamWriter w = File.AppendText(LogfilePath))
       {
        w.WriteLine("\r\nLog Entry : ");
        w.WriteLine("{0}",DateTime.Now.ToString(CultureInfo.InvariantCulture));
        string err = "Error Message:" + msg;
        w.WriteLine(err);
        w.Flush();
        w.Close();
       }
   }

Sunday, December 25, 2011

out and ref parameters

Dot net allows to pass the arguments in three ways.Here i will show how to use these parameters
1.call by Ref:Formal arguments are modified and if modification are reflected on actual arguments .This concepts are called as call by value.By default value the variable will be passed by Value.The Ref is a keyword which is required to pass the variable by ref.This keyword must be used along with actual and formal arguments.Ref variable must be initiated before passing the function.The fallowing example will show how to use this ref key word
Example:

class test
  {
    public void swap(int x,ref int y)
      {
        int t=x;
        x=y;
        y=t;
      }
   public void button1_click(object sender O,Event Args e)
     {
      test t=new test();
      int a=10,b=20;
      t.swap(a,ref b);
      message.show(a+""+b); 
     }

2.call by value:Formal arguments are modified and if modification are not reflected on actual arguments .This concepts are called as call by value
Example:

class test
  {
    public void print(int x)    
      {
        x=x+x;
      }
   public void button1_click(object sender O,Event Args e)
     {
      int a=10; 
      test t=new test();
      t.print(i);
      message.show(i); 
     }

3.call by out:The ref and out parameters are most same.Ref variable must be initialized before passing but Out variable passed with out initialized also.Even if Out variable is initialized.The value will not be passed.Finally
out=ref-initialization
Example:

class test
  {
    public void print(int a,int b,out int c)
      {
        c=a+b;
      }
   public void button1_click(object sender O,Event Args e)
     {
      int a=10,b=20,k;
      test t=new test();
      t.print(i,j,out k);
      message.show(k+""); 
     }

Saturday, December 24, 2011

Date functions in Sql Server

Date functions are the scalar functions that operate date type of data.Here i will show how to perform the all the operation using data function in sql server
Get the current date:
The GETDATE() returns the system date and time on server

Select GETDATE()

Get specified path from date:
The datepart(item,Date) is used to extract specified path from the date

select Datepart(day,GETDATE())
//get the day,quarter and week of the year
select Datepart(dw,GETDATE())returns day of the week
select Datepart(qq,GETDATE())returns quarter of the year
select Datepart(ww,GETDATE())returns week of the year for give date
select Datepart(hour,GETDATE())
select Datepart(month,GETDATE())returns day of the week
//*Here i will show an example to get the employee details who are joining in December 2001
select*from emp where Datepart(month,hireddate)=12 and Datepart(year,hireddate)=2001

Get the Difference between dates:
The Datediff(item,date1,date2) function is used to get the difference between give dates in the specified Item

select Datediff(day,'12/24/2011',GETDATE())
select Datediff(month,'12/24/2011',GETDATE())
select Datediff(year,'12/24/2011',GETDATE())
select Datediff(ww,'12/24/2011',GETDATE())
select Datediff(qq,'12/24/2011',GETDATE())
//*Here i will show an example to get the employee details who are having 10 years of experience in the company
select*from emp where Datediff(year,hireddate,GETDATE())>10

Add date:
DateAdd(item,n,date) function returns a date after adding the specified no of items to the given date

select DateAdd(day,'5',GETDATE())-->29/12/2011
select DateAdd(day,'-5',GETDATE())-->19/12/2011
select DateAdd(month,'5',GETDATE())-->29/5/2011

Friday, December 23, 2011

DataCaching in asp.net

All caching concepts are application based and Every user can access the content.Any type of caching will result in separate storage at server and awards processing of data of Request .Data Caching is implemented using asp.net cache object.Cache object are self locking objects and there are no of methods to explicitly log them.Cache objects lock specific accessed content but not the entire cache data.Cache objects can be created based on time which means after the time elapses the object will expired(like page output cache)
Another important feature of cache objects is dependency based on expiry in this model we can create cache object based on a resource and if the resource is modified then cache object will expire

Before going to example we have to know what is Application variable.If you have more application variable they can reduce the performance.Cache is self locking object ,no locking by user.Here i will show how to use cache object in application.For this i have taken a form with label,button and grid view

Example Code behind:

if(cache(dsdata==null)
  {
   ds=DataHelper.GetData("select*from emp");
    cache["dsdata"]=ds;
  }
   lbl.Text="from database";
else
  {
   ds=(Dataset)Cache["dsdata"];
   lbl.Text="From Cache";
  }
gvemp.DataSource=ds.Tabe[0];
gvemp.DataBind();

Note:Cache objects are any cache is maintained by run time not by user

Thursday, December 22, 2011

DataViews in Asp.net

The data view is read only data,The view is giving security to the database in front end.As per .net Technologies we can create views in the front end depending on the data table class.Using data views we can filter the data based on requirement.In data view class we can store data table copy.The MS given all privileges to the data table but not to the data view class. The data view class filtering the rows using row filter property ,depending on the requirement we can create any number of sub views.Here i will show how to use the data view and bind the different kind of to grid view.For this i have taken three button and one grid view.
code behind:

SqlConnection cn = new SqlConnection("Data Source=\\SQLEXPRESS;Initial catalog=user;Integrated Security=true;");
cn.Open();
SqlDataAdapter ad=new SqlDataAdapter ("select*from emp",cn) ;
DataSet ds = new DataSet();
Datatable dt=new Datatable();
Dataview dv;
//page loading
ad.fill(ds,"emp");
dt=ds.Tables["emp"];
dv=dt.Defaultview;
//button1_click
gvemp.DataSource=dv;
gvemp.DataBind
//button2_click
dv.RowFilter="ename like'a%';
gvemp.DataSource=dv;
//button3_click
dv.RowFilter="ename like'b%';
gvemp.DataSource=dv;

The data view hold the data which is filtered or required based on condition.The data view has some properties like AllowDelete,AllowEdit and AllowNew .It gives the customized view of data table

Wednesday, December 21, 2011

Checkbox in grid view in asp.net||Check/Uncheck all check boxes in gridview

In previous articles i explained delete multiple rows in gridview using checkbox,Export Gridview Excel ,Export Gridview Data to PDF document,Export gridview to Word in asp.net.Grid view is providing different types of field for customization such as bound field,check box,image field,Hyperlink,Command,Template fields.Bound field is the default field for grid view.Here i will show how to use the check box inside grid view and add the value of selected row into Context.

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<gridview autogeneratecolumns="False" id="gvOrdr" runat="server">
<columns>
<templatefield>
<itemtemplate>
<checkbox id="chkOrdr" runat="server"></checkbox>
</itemtemplate>
<HeaderTemplate>
<asp:CheckBox ID="chkMainheadr"
OnCheckedChanged="chkMainheadr_OnCheckedChanged"
AutoPostBack="true" runat="server" />
</HeaderTemplate>

</templatefield>

<boundfield datafield="UserID" headertext="User ID">
<boundfield datafield="UserName" headertext="User Name">
<boundfield datafield="Role" headertext="Role">
</boundfield>

</columns>
</gridview></div></form>
</body>
</html>

Code behind:
In grid view Find Control method is provided which is access web server control with in the Template field.This return Obj type,it should be Type casted to Check Box type.So that property can be accessed.Here we will checked one check box then the value of particular row in to string using on OnCheckedChanged event
Note:We can add that string value to Text property controls like Text box,Label etc
Checked &unchecked All:
Here i have one check box in header template in aspx page.if check the header check box then all the check box should be checked .if unchecked the header check box then all the check box should be unchecked

using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;

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


protected void chkMainheadr_CheckedChanged(object sender, EventArgs e)
{
checkBox chkall =(CheckBox)Gvemp.HeaderRow.FindControl("chkMainheadr");
if (chkMainheadr.Checked == true)
{
foreach (GridViewRow gvOrdr in gvOrdr.Rows)
{
CheckBox chkIndividual =(CheckBox)gvOrdr.FindControl("chkOrdr");
chkIndividual.Checked = true;
}
}
else
{
foreach (GridViewRow gvOrdr in gvOrdr.Rows)
{
CheckBox chkIndividual = (CheckBox)gvOrdr.FindControl("chkOrdr");
chkIndividual.Checked = false;
}
}
}
}

Get the Selected CheckBox value in Gridview:

public void chk_OnCheckedChanged(object sender, EventArgs e)
  {
    string s;
    CheckBox chsubmit=(CheckBox)Gvemp.Rows[i].FindControl("chkOrdr");
    if(chsubmit.Checked)
    s=s+gvemp.Rows[i].cells[2].Text+;

  }
//place s variable in to context
  context.items.Add("sps",s);

Tuesday, December 20, 2011

How to create smart form in Ektron CMS

might be think all why we using smart form? The same thought i had when working with smart form in ektron cms.In Ektron the Smart Forms used to separating Web content from presentation and avoid the duplicate content from users.The smart form can deliver the content to multiple devices and easy to share .There is two ways to create smart form in Ektron
1. WYSIWYG Data Designer
2. Xml files
Here i will shown how to create a smart form in work area and assign to particular folder in it.The fallowing steps will be needed to create smart form
Go to Work Area-->Settings-->Smart Form Configuration

There is a plus symbol at top most left to add the smart form to smart form configuration.After make a selection we will get a text box to give a name to smart form.Here i have given a name as "smart form1" .After add the name of the smart form we have to save this.For this there is option at left top most.

We will get XML configuration form to create an desirable deign.Here i have taken two check boxes in the form then updated.The below screen shot will shown how it was.u can edit the page if you are familiar with XML.For this we have an option at bottom of screen to

If we want see the smart form individually you should do the following steps.
settings-->smart form Configuration
whenever click on the configuration manager you can the total smart forms are available in work area.Then click on one of smart form you can see the properties of this.Here we can edit using edit button at top of the work area

How to assign the Smart form to Folder:
In work are each folder having the property smart form to attach desirable smart form.Here you can see the properties of folders in work area.For this i have taken personal folder in work area.

To give the desirable smart form to folder you have to unchecked the option inherit parent configuration then the drop down is enabled to select the smart forms

After add the smart form to particular folder you can see it in new tab option.the fallowing screen shot will describes where will it has added.

This smart form content is useful for many of content blocks in work area.

Monday, December 19, 2011

Cascading Dropdown lists in asp.net

we will used the seletedIndex change event when you working with populate dropdown list using another dropdown list selection in asp.net.By using this Event the page loading is occur .so every time page loading process gives the burden to application performance.For this we can use the Jquery to avoid the page loading concept.I will shown how to populate the drop down list based on selection of another drop down list.Here i have taken two drop down lists which are ddlattdoctor and ddlattdoctor.

var pageUrl = '<%=ResolveUrl("~/IPRegistration.aspx")%>'
function PopulateDepartments() {
 $("#<%=ddlattdoctor.ClientID%>").attr("disabled", "disabled");
if ($('#<%=ddlattdept.ClientID%>').val() == "0") {
   $('#<%=ddlattdoctor.ClientID %>').empty().append('No Doctor');
        }
        else {

   $('#<%=ddlattdoctor.ClientID %>').empty().append('Loading...');
              $.ajax({
                type: "POST",
                url: pageUrl + '/PopulateDoctors',
                data: '{deptid: ' + $('#<%=ddlattdept.ClientID%>').val() + '}',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: OnDoctorsPopulated,
                failure: function(response) {
                    alert(response.d);
                }
            });
        }
    }

    function OnDoctorsPopulated(response) {
        PopulateControl(response.d, $("#<%=ddlattdoctor.ClientID %>"));
    }


//this is for populate doctors dropdwon list
    function PopulateControl(list, control) {
        if (list.length > 0) {
            control.removeAttr("disabled");
            control.empty().append('(Please Select)');
            $.each(list, function() {
                control.append($("").val(this['Value']).html(this['Text']));
            });
        }
        else {

            control.removeAttr("disabled");
            control.empty().append('(No Doctor)');
           // control.empty().append('Not available');
        }
    }

Here i have created a static web method Then the server side process will take the Asp.net ajax.This web method is called as ASP.NET Ajax page method. PopulateDoctors() is return the list of items which are bind to drop down using jquery

[System.Web.Services.WebMethod]

public static ArrayList PopulateDoctors(int deptid)
 {
  SqlConnection con=newSqlConnection(ConfigurationManager.AppSettings["connection"].ToString());
  ArrayList list = new ArrayList();
  string strQuery = "select docname,docid from docmaster where deptid='" + deptid + "'";
  SqlCommand cmd = new SqlCommand(strQuery, con);
  con.Open();
  SqlDataReader sdr = cmd.ExecuteReader();
  while (sdr.Read())
   {
   list.Add(new ListItem(
   sdr["docname"].ToString(),
   sdr["docid"].ToString()
   ));
  }
   con.Close();
   return list;
}

Sunday, December 18, 2011

Unselectable javascript Text

Making text un selectable is a cross-browser method for preventing text selection using this attr .The no IE browsers also support this using some CSS.Here is the simple java script function it makes the controls in the page are unselected in IE browser

if($.browser.msie) {
this._mouseUnselectable=this.element.attr('unselectable');
this.element.attr('unselectable','on');
}
this.started=false;
}

If u want restore the text selection in IE the follow in java script can be helpful to allow selection in IE.I have used this script for allow the text selection in my jquery function

($.browser.msie
&& this.element.attr('unselectable', this._mouseUnselectable));
}

Saturday, December 17, 2011

Data set with Data Relation in asp.net

Here i will shown how to give the primary key and foreign key to tables .Data set having collection of data table and data relations.while generating relations in the data set at least we have to store two tables .while creating the tables we have to create tables with Primary key constraint and with foreign key constraint.The p.k constraint column table is master table and F.K constraint column table is child table.The P.K constraint column automatically controls F.K constraint column values

Data Relation:This class is used to generate relation in the database .This class takes constructor arguments
1.Relation name
2.Primary Key Constraint Column
3.Foreign Key Constraint Column
Example:

Using System.Data.Oledb;

OledbConnection cn=New OledbConnection("Provider=msdaora.1;user id=test;pasword=test");
OledbDataAdapter ad1;
OledbDataAdapter ad2;
Dataset ds=new Dataset();
DataRelation dr;
//under button click event
{
ad1=New OledbDataAdapter("select*from Employee",cn);
ad1=New OledbDataAdapter("select*from Department",cn);
ad1.Fill(ds,"department");
ad2.Fill(ds,"Employee");
DataColumn PK=ds.Tables["Employee"].columns["empid"];
DataColumn FK=ds.Tables["Department"].columns["empidid"];
dr=New DataRealtion("re1",PK,FK);
ds.Realtions.Add(dr);
gvmain.Datasource=ds;
}

Thursday, December 15, 2011

Data Table to Data base in c#

Title:How to insert the data in to database using data table in asp.net using c#

Description:
As per example i have to insert the data from CSV file. For this task i have done two steps.One is,Bind the data to data table and then iterate the each row of data table then we can insert the data in to Data Base .Here i have checked the whether the data is empty or null in column of data table.i have shown another query in below to insert the data with out check

Code Behind:

string insertQuery = "INSERT into [Table name](Id,field2,field3,field4,field5,field6,field7,field8,field9,field10,field11) Values({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},'{10}')";
foreach (DataRow dr in Datatable.Rows)
{
try
{
cmd.Connection = con;
cmd.CommandText = string.Format(insertQuery, string.IsNullOrEmpty(Convert.ToString(dr["Col1"]).Trim()) ? null : dr["Col1"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col2"]).Trim()) ? null : dr["Col2"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col3"]).Trim()) ? null : dr["Col3"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col4"]).Trim()) ? null : dr["Col4"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col5"]).Trim()) ? null : dr["Col5"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col6"]).Trim()) ? null : dr["Col6"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col7"]).Trim()) ? null : dr["Col7"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col8"]).Trim()) ? null : dr["Col8"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col9"]).Trim()) ? null : dr["Col9"].ToString().Trim(),
         string.IsNullOrEmpty(Convert.ToString(dr["Col10"]).Trim()) ? null : dr["Col10"].ToString().Trim(),DateTime.Now);
         cmd.ExecuteNonQuery();
         }

The second one is:

cmd.CommandText = string.Format(insertQuery, dr["col1"], dr["col2"], dr["col3"], dr["col4"], dr["col5"],dr["col6"];

Rows into Columns in Oracle

The oracle database 11g has some new features such as PIVOT and UNPIVOT clauses,By using these clauses we can rotate the rows into column in output from a query.PIVOT and UNPIVOT are useful to see in large amount of data such as trends data over a period of time.Hera Ive shown a simple query to get the income details for three in year

Example:

SELECT *FROM
(SELECT month,id,income FROM all_business WHERE year="2011" and id IN(1,2,3))
PIVOT
(SUM(income) FOR month IN(1 AS JUNE,2 AS JULY,3 AS AUGUST)) ORDER BY id

CREATE TABLE ALL_Income AS
SELECT *FROM
(SELECT month,id,income FROM all_business WHERE year="2011" and id IN(1,2,3))
PIVOT
(SUM(income) FOR month IN(1 AS JUNE,2 AS JULY,3 AS AUGUST)) ORDERBY id


SELECT *FROM ALL_Income
UNPIVOT(income FOR month IN(JUNE,JULY,AUGUST))ORDER BY id

Hide/show Div using Jquery/Json

Earlier i explained Jquery validate dropdown list,Auto complete texbox in jquery,Date format, Cascading dropdownlist in asp.net. Here i will show a simple way to hide/ show Div or Jquery Toggle.For this i have drop down list with two items which are "General" and "Credit".Then add the on change event to Dropdown list which is call the jquery function when make a selection on drop down list

Default.aspx:
<asp:dropdownlist id="ddlptype" onchange="PopulatePatient();" runat="server">
<asp:listitem>General</asp:listitem>
<asp:listitem>Credit</asp:listitem>
</asp:dropdownlist>
<div id="Organisation">
<table><tbody>
<tr><td><asp:label id="lblorgname" runat="server" text="Org name"></asp:label></td> <td><asp:textbox id="txtorgname" runat="server"></asp:textbox></td></tr>
<tr><td><asp:label id="lblEmpCode" runat="server" text="EmpCode"></asp:label></td> <td><asp:textbox id="txtempcode" runat="server"></asp:textbox></td> </tr>
</tbody></table>
</div>
Script:
A small comparison is perform between Dropdown list selected value with patient type (General and Credit) to make this .if the drop down selected item value is equal to general , I show the organization details div and if no is selected, I hide the div.

function PopulatePatient() {
        var value = $('#<%=ddlptype.ClientID%>').val();
        if (value == "General") {
            var t = document.getElementById("Organisation");
            $("#Organisation").fadeOut("slow");
        }
        else
            $("#Organisation").fadeIn("slow");
    }

Wednesday, December 14, 2011

link button in gridview in asp.net

Earlier we learned how to export Gridview data to word document,gridview to excel,paging and sorting in Gridview.bind data to dropdownlist in gridview in asp.net.Here i will shown how to perform the data modifications(edit,delete) using link button in grid view in asp.net.Basically we have auto generated options to do edit and delete in grid view .But here i am using link button control with custom functionality in grid view .
Grid view having The Template field(asp:template field) which is used to add the any server controls and Bound field is used to bind the particulate field (cityid,city name) from data base to grid view columns from database .The bound fields has property to add the desirable column to it i.e Data Field
default.aspx:

<div>
<asp:panel cssclass="panel_set " id="Panel2" runat="server">
</asp:panel>

<br />
<table align="center" tablecen="" valign="center ><tbody>
<tr>  <td><br />
<asp:label id="lblcityid" runat="server" text="CityID:"></asp:label></td> <td><br />
<asp:textbox id="Txtcityid" readonly="True" runat="server"></asp:textbox></td>  </tr>
<tr> <td><br />
<asp:label id="lblcityname" runat="server" text="CityName:"></asp:label></td> <td><br />
<asp:textbox id="txtcityname" runat="server"></asp:textbox></td> </tr>
<tr> <td><br />
<asp:button id="btnsave" onclick="btnsave_Click" runat="server" text="Save">      <asp:button id="btnupdate" onclick="btnupdate_Click" runat="server" text="Update">  </asp:button></asp:button></td> </tr>
</tbody> </table>
<asp:panel cssclass="pnel_grv" id="panelgridviews" runat="server">
<asp:gridview autogeneratecolumns="False" cssclass="grid_scroll " datakeynames="cityid" emptydatatext="There is No Records To Display" id="grvcity" onrowdeleting="DeleteRecord" runat="server">
<columns>

<asp:boundfield datafield="cityid" headertext="cityid">
<itemstyle horizontalalign="Center" width="20px"></itemstyle>
</asp:boundfield>

<asp:boundfield datafield="cityname" headertext="cityname">
<itemstyle horizontalalign="Center" width="20px"></itemstyle>
</asp:boundfield>

<asp:templatefield>
<headertemplate> </headertemplate>
<itemtemplate>
<asp:linkbutton id="Linkedit" onclick="Linkedit_click" runat="server">Edit</asp:linkbutton>
</itemtemplate>
</asp:templatefield>
<asp:templatefield headertext="Delete?">
<itemtemplate>

<asp:linkbutton causesvalidation="false" commandname="Delete" id="lnBD" runat="server" text="Delete"></asp:linkbutton>

</itemtemplate>
</asp:templatefield>
</columns>
        
<footerstyle backcolor="#99CCCC" forecolor="#003399">
<pagerstyle backcolor="#99CCCC" forecolor="#003399" horizontalalign="Left">
<selectedrowstyle backcolor="#009999" font-bold="True" forecolor="#CCFF99">
<headerstyle backcolor="#003399" font-bold="True" forecolor="#CCCCFF">       
</headerstyle></selectedrowstyle></pagerstyle></footerstyle></asp:gridview>
</asp:panel>
</div>

Code behind:
Here one pop up is coming to confirmation to delete the record when we click on the delete link button in grid view.one more thing will have to observe here is how to delete record event is raised.The OnRowDeleting command which has given to grid view properties(can see in aspx page) is used to delete the record in grid view

//edit 
protected void Linkedit_click(object sender, EventArgs e)
  {
   LinkButton lnkButton = sender as LinkButton;
   GridViewRow row = (GridViewRow)lnkButton.NamingContainer;
   lblgdeptid.Text = row.Cells[0].Text;
   cmd = new SqlCommand("editcity", con);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.Add("cityid", SqlDbType.NVarChar).Value = lblgdeptid.Text;
   con.Open();
   SqlDataReader dr = cmd.ExecuteReader();
   if (dr.HasRows)
      {
       while (dr.Read())
             {
                Txtcityid.Text = dr["cityid"].ToString();
                txtcityname.Text = dr["cityname"].ToString();
              }
              dr.Close();
              con.Close();
        }
        else
        {
//give an pop with "there is no data"
        }
    }

//Delete

 protected void DeleteRecord(object sender, GridViewDeleteEventArgs e)
    {
     int cityid = Int32.Parse(grvcity.DataKeys[e.RowIndex].Value.ToString());
     CityBAL3 pBAL = new CityBAL3();
     pBAL.City_Delete(cityid);
     grvcity.EditIndex = -1;
 
    }

Monday, December 12, 2011

How to call a Stored Procedure in asp.net

Title: How to use stored procedure in asp.net using c#.net

Description:
In this article i will shown how to call stored procedure in asp.net application.If u want to call a procedure it may have 2 types of parameters
                                       1.input
                                       2.Output
Input parameters are used for taking a value into procedure for Execution.Output parameters are used for sending a value out of the procedure after execution.If a procedure has parameters to it for each of every parameter of this procedure a matching parameter has to be created by us under .net application>a parameter under the .net application has five different attributes to it.Those are
                                      1.Name
                                      2.Size
                                      3.SqlDb Type
                                      4.Value
                                      5.Direction
Where direction can be input or output>to create magic input parameter under the .net application you need to specify the attributes name and value while creation..To create output parameter under .net application you need to specify the attributes Name,sqlDbtype,Size,Direction.After execution of the procedure it will return to the value
Example:
If you want to call a procedure that returns a value from our .net application the method that has to be applied on the command is Execution non query.

//under page load
SqlConnection cn=new SqlConnection("Userid=sa;Password=123;Database=employee");
SqlCommnad cmd=New SqlCoammand();
cmd.commandtype=Comandtype.storedprocedure;
//Under button click event
try
{ 
cmd.parameter.clear();
cmd.parameter.AddwithVlaue(@empId",txtempid.Text);
cmd.parameter.Add(@empId",sqlDbtype.Varchar)direction=Parameterdirection.output;
cmd.parameter["empname"].Size=50;
cmd.parameter.Add(@add",sqlDbtype.Varchar)direction=Parameterdirection.output;
cn.Open();
cmd.ExcuteNonQuery();
Textbox2.Text=cmd.parameters["@empname"].value.Tostring();
Textbox3.Text=cmd.parameters["@empname"].value.Tostring();
}
catch(exception ex)
{
messageBox.show(ex.message);
}
finally{
cn.Close();
}

Friday, December 9, 2011

GridView Sorting and Paging in Asp.net

Title : Paging and sorting in grid view in asp.net using c#

Description:
Recently While working with the grid view sorting ,i have noticed the the functionality and do the example on in grid view using c sharp.Here i will shown how sort the data and pagination in grid view.The below specified code will make sorting when clicking on grid view header .So first we have to do some properties set up i:e
Allow sorting-->True:-column headings will be provided with hyperlinks[link button]
Code behind:

//pageload
if(page.ispostback==false)
{
//ordinary request information will be displayed sorted based on the empname
Sqlconnection con=New Sqlconnection("userid=sa";password=;databse=emp");

SqlDataadaptor da=New SqlDataadapator("select*from Employee Orderby empname",con);

//orderby is used to retrieve records in sorted order
Dataset ds=New Dataset();

da.fill(ds,"Employee");

Gvemp.Datasource=ds.Table["Employee"];
Gvemp.DataBind();
}

Providing Logic For Grid view Event:-
When user clicks on the column header of grid view post back takes place,sorting event procedure of grid view will be executed.This Event procedure will be provided column name

protected void Gridview-sorting(Object sender,Event Args e)
{
Response.Write{"colname:"+e.SortExpression);
//e.SortExpression will provide colomn name selected by user
SqlDataadaptor da=New SqlDataadapator("select*from Employee Orderby empname"+e.Sortexpression,con);

Dataset ds=New Dataset();

da.fill(ds,"Employee");

Gvemp.Datasource=ds.Table["Employee"];

Gvemp.DataBind();
}

Note:The similar code is required in page load and Grid view-sorting event Process .To avoid repetition using fallowing subprogram to fill grid view

void fillgrid(cname);

Title: How to highlight the Grid view row in asp.net
Here i will shown how to high lighting the name according to country.This requires Row data bound event of grid view control.Row data bound event will be executed towards each row construction with data from data source.This will provide access to row constructed

protected void Gridview-rowdataBound(..,..)
{
Response.Write(e.row.cells[4].Text+"");

if(e.Row.cells[4].Text=="Usa")

e.Row.Backcolor=system.drawing.color.Red;
}

Wednesday, December 7, 2011

Difference between ADO and ADO.NET

Title:Difference between ADO and ADO.net

Below are some of the differences between ado.net and ado

ADO:
1.ADO are implemented using com technology
2.In the ADO we can store only one table in record set
3.Ado's doesn't support data relations
4.Ado's is not front end RDBMS
5.Ado's are connection oriented database management system
6.Using Ado's we can't integrate with XML
7.Using Ado's we can't generate sql statements
8.Ado,s are doesn't support data transactions

ADO.NET
1.ADO.NET are implemented using .net framework technology
2.In the ADO.NET we can store multiple tables in record set
3.Ado.NET support data relations
4.Ado.NET is not front end RDBMS
5.Ado.NET are connectionless oriented database management system
6.Using Ado.NET we can integrate with XML
7.Using Ado.NET we can generate sql statements
8.Ado.NET support data transactions

Top menu