ASP.NET – How to work with GridView

Today we are going to see how to work with Grid View Control.

Here is an example how are we going to get a repeater after we finished coding.

Here I am using AdventureWorks in SQL SERVER which can be downloaded from codeplex.

The table I am using is DimCustomer and fields are :

1)FirstName

2)MiddleName

3)LastName

4)BirthDate

5)CustomerKey

Step 1 :  Add the control in the <body> tag in page in .aspx side let it be grdView.aspx

</pre>
<form id="frmExmGrdView" runat="server">
    <asp:GridView ID="grd" runat="server" CssClass="gdview" HeaderStyle-CssClass="hd" AutoGenerateColumns="false" BorderStyle="None">
    <Columns>
    <asp:BoundField DataField="FirstName" HeaderText="Login" ItemStyle-Width="40" ItemStyle-HorizontalAlign="Left" ></asp:BoundField>
    <asp:BoundField DataField="MiddleName" HeaderText="Initial" ItemStyle-Width="20" ItemStyle-HorizontalAlign="Left" ></asp:BoundField>
    <asp:BoundField DataField="LastName" HeaderText="Name" ItemStyle-Width="60" ItemStyle-HorizontalAlign="Left" ></asp:BoundField>
    <asp:BoundField DataField="BirthDate" HeaderText="DateAdded" ItemStyle-Width="40" ItemStyle-HorizontalAlign="Left" HtmlEncode="false" DataFormatString={0:d}></asp:BoundField>
    </Columns>
    </asp:GridView>
    </form>
<pre>

NOTE: Dont forget to make the form tag as runat = “server “

otherwise an error will occur because you are trying to insert the data from .cs

side but u can only access it when u make runat = “server “

STEP 2:  Now insert the data from the code behind (.cs side) in Page_Load event

 

protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Server=localhost;UID=sa;PWD=dilip;Database=AdventureWorksDW");
        string sSQL = "Select top 5 * from dbo.DimCustomer";
        SqlCommand cmd = new SqlCommand(sSQL, con);
        con.Open();
        SqlDataReader dtrClient = cmd.ExecuteReader();
        grd.DataSource = dtrClient;
        grd.DataBind();
        con.Close();
    }

The last 2 lines binding the data to the repeater and starting remaining lines is to establish connection to database.

the output will come as shown above .

Reference : Dilip Kumar Jena ( https://mstechexplore.wordpress.com )

Advertisements

ASP.NET How to work with Repeater

Today we are going to see how to work with Repeater Control.

Here is an example how are we going to get a repeater after we finished coding.

Here I am using AdventureWorksDW2008R2 in SQL SERVER which can be downloaded from http://www.codeplex.com.

The table I am using is DimCustomer and fields are :

1)FirstName

2)MiddleName

3)LastName

4)BirthDate

5)CustomerKey

Step 1 :  Upload the control in the page in .aspx side let it be default.aspx

<form id=”frmExample” runat=”server”>

<asp:Repeater ID=”rptList” runat=”server” OnItemDataBound=”rptList_ItemDataBound”>

<HeaderTemplate>

<table border=”1″ cellpadding=”0″ cellspacing=”0″>

12px; color:White;”>

<th>

<asp:Label ID=”Label14″ Text=”Login” runat=”server”></asp:Label>

</th>

<th>

<asp:Label ID=”Label1″ Text=”First Name” runat=”server”></asp:Label>

</th>

<th>

<asp:Label ID=”Label2″ Text=”Last Name” runat=”server”></asp:Label>

</th>

<th>

<asp:Label ID=”Label3″ Text=”Date Added” runat=”server”></asp:Label>

</th>

<th>

<asp:Label ID=”Label6″ Text=”Delete” runat=”server”></asp:Label>

</th>

</tr>

</HeaderTemplate>

<ItemTemplate>

<tr>

<td>

<asp:LinkButton ID=”lbnLogin” Text='<%# Eval(“FirstName”) %>’ runat=”server”</asp:

OnClick=”lnkClient_Click” CommandArgument='<%# Eval(“CustomerKey“).ToString() %>’>

LinkButton>

</td>

<td>

<asp:LinkButton ID=”lbnFirstName” Text='<%# Eval(“MiddleName”).ToString() %>'</asp:

runat=”server”

OnClick=”lnkClient_Click” CommandArgument='<%# Eval(“CustomerKey“).ToString() %>’>

LinkButton>

</td>

<td>

<asp:LinkButton ID=”LinkButton2″ Text='<%# Eval(“LastName”).ToString() %>'</asp:

runat=”server”

OnClick=”lnkClient_Click” CommandArgument='<%# Eval(“CustomerKey“).ToString() %>’>

</asp:LinkButton>

</td>

<td>

<asp:LinkButton ID=”LinkButton1″ Text='<%# string.Format(“{0:d}”, Eval(“BirthDate”)) %>’ runat=”server”

OnClick=”lnkClient_Click” CommandArgument='<%# Eval(“CustomerKey“).ToString() %>’>

</asp:LinkButton>

</td>

<td>

<asp:LinkButton ID=”lbnDelete” Text=”delete” runat=”server”

OnClick=”lbnDelete_Click” CommandArgument='<%# Eval(“CustomerKey“).ToString() %>’>

</asp:LinkButton>

</td>

</tr>

</ItemTemplate>

<FooterTemplate>

</table>

</FooterTemplate>

</asp:Repeater>

</form>

NOTE: Dont forget to make the form tag as runat = “server “

otherwise an error will occur because you are trying to insert the data from .cs

side but u can only access it when u make runat = “server “

STEP 2:  Now insert the data from the code behing

int Page_Load event

SqlConnection con = new SqlConnection(“Server=localhost;UID=xx;PWD=xx;

Database=AdventureWorksDW2008R2″);

string sSQL = “Select top 5 * from dbo.DimCustomer”;

SqlCommand cmd = new SqlCommand(sSQL, con);

con.Open();

SqlDataReader dtrClient = cmd.ExecuteReader();

rptList.DataSource = dtrClient;

rptList.DataBind();

con.Close();

/*along with it*/ write these dummy function later you can implement them as you like .

protected void lnkClient_Click(object sender, EventArgs e)

{ }

protected void lbnDelete_Click(object sender, EventArgs e)

{ }

protected void rptList_ItemDataBound(object sender, RepeaterItemEventArgs e)

{ }

The last 2 lines binding the data to the repeater and starting remaining lines is to establish connection to database.

the output will come as shown above .

Reference : Dilip Kumar Jena ( https://mstechexplore.wordpress.com )

Dot Net – How to upload excel sheet into databse using Dataset in ASP.NET

I thought of writing this blog post because there can a number of occasion where in you might need to upload something from word file or excel sheet or it can be any kind of  file need to be inserted into the database what we can do many things.

But probably what I feel good and follow most of the time is doing this way so that we can preserve the statements for future reference what we have actually used and where it is going to exactly affect.

Step 1: Create  a website and then add default page on to it.

Step 2: Write the following code in Page_Load(object sender, EventArgs e) function

DataSet ds = new DataSet();

OleDbConnection myCon = new OleDbConnection(@”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=folderpath\sheet.xls;Extended Properties=Excel 8.0;”);

OleDbCommand myComm = new OleDbCommand(“select * from [sheet1$] where State<>””, myCon);

OleDbDataAdapter da = new OleDbDataAdapter(myComm);

da.Fill(ds);

for (int i = 0; i < ds.Tables[0].Rows.Count;i++ )

{

Response.Write(“insert into dbo.Temp2 values(‘” + ds.Tables[0].Rows[i][0].ToString() + “‘,'” + ds.Tables[0].Rows[i][1].ToString() + “‘,'” + ds.Tables[0].Rows[i][2].ToString() + “‘,'” + ds.Tables[0].Rows[i][3].ToString() + “‘)<br />”);

}

myCon.Close();

Step 3: Create a table in SQL Server names Temp2 where in u want insert the excel sheet into.

NOTE: The table name can be any name  you like its just an example never take it as standard, its for learing.

create table Temp2(Id varchar(10),country varchar(100),natAnimal varchar(50),lang varchar(50))

Step 4: Create or if u have  excel sheet just make sure that that contains header for each column it will be easy to understand

EX:

State Country Animal Language
1 Australia Kangroo English
2 India Peacock Hindi
3 USA American Biston English

This is a sample excel sheet name it as sheet and inside as Sheet1 it will be applied by default so just hit the save button.

So all set!!!

now save the excel sheet in the folder of your website and run the web site the expected Output will be something like this will appear in your broswer

insert into dbo.Temp2 values(‘1′,’Australia’,’Kangroo’,’English’)

insert into dbo.Temp2 values(‘2′,’India’,’Peacock ‘,’Hindi’)

insert into dbo.Temp2 values(‘3′,’USA’,’American Biston’,’English’)

then copy it and paste in ssms (SQL server Query Editor and hit F5 to execute) Finally records are inserted into the database.

There can be many more ways I am sure but this is also handy when you have production and development section seperate.

Reference : Dilip Kumar Jena ( https://mstechexplore.wordpress.com )