Code For Geeks: Connection String

Templates

In this article, We will learn about templates and also how to make project using Repeater

#Templates

Collection of html and web control. We have total 15 templates.

#The names are as follow:

ItemTemplate
AlternatingTemplate
HeaderTemplate
FooterTemplate
SeparatorTemplate
SelectedItemTemplate
EditItemTemplate
PagerTemplate
EmptyDataTemplate
InsertItemTemplate
LayoutTemplate
GroupTemplate
GroupSeparatorTemplate
ItemSeparatorTemplate
EmptyItemTemplate

Note: All templates are always used with composite data bound control or vice versa.

# Composite Data bound Control

Controls in which inner control, child control or control under control.

There are 7 Composite Data bound Control :

Repeater
Data list
Grid view
Detail view
Form view
List view
Data pager

* Repeater

- It is used to display data in single column.

- There is no manipulation (read only).

- No default template

- No paging (not pre-defined paging)

There are 5 templates which apply on Repeater:

ItemTemplate

AlternatingTemplate

HeaderTemplate

FooterTemplate

SeparatorTemplate

# For Repeater

Create new table in vs

Add data in the table

Working With Repeater

Here we will learn How to work with Repeater

# Open new project in Visual Studio

# File > New project> Language C#

# Add Connection String

* Click on View > Solution explorer > web.config

</connectionStrings>

<system.web>

*Click on website > Add> New item > webform

Click on toolbox> data>repeater

Select a1,a2,a3,a4 and copy then

Click on solution explorer >right click on web app>paste

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

{

private void Rep_Bind()

{

SqlDataAdapter adp = new SqlDataAdapter("select * from tbbook", ConfigurationManager.ConnectionStrings["ms"].ConnectionString);

DataSet ds = new DataSet();

adp.Fill(ds);

Repeater1.DataSource = ds;

Repeater1.DataBind();

}

SqlDataAdapter – class of system.data.sqlclient

*SqlDataAdapter= always used with disconnected approach. It implicit open the connection and close the connection on its own.

*Data Set = collection of tables or array of tables. We can set the relation. Read write data and xml format.

# Types of DataSet

Typed dataset
Untyped dataset

*Typed dataset = It defines the structure at design time.

Its extension is .xsd

It have better performance bcoz of early bind concept.

It is mainly used in reports and fixed number of column operation. Ex reports are end result

*Untyped dataset = It defines the structure at runtime, It is late bind concept means memory allocation and runtime data is put at runtime. Therefore slow performance. Column are not fixed.

# Untyped dataset is used :

*Fill method = First open coonection with database then it will execute query. Then it will fetch data and connection will close.

# In page load event

protected void Page_Load(object sender, EventArgs e)

{

Rep_Bind();

}

# Click on webform > Source

Asp: tag prefix for Microsoft

Asp: - repeater tag name

Item template – it runs up to number of records in table

Img src = path for image from table.

<tr> - table row

<td> - column table

<body>

<div>

<asp:Repeater ID="Repeater1" runat="server" OnItemCommand="Repeater1_ItemCommand">

<tr>

<td>

<img src ='<%#Eval("bookimg")%>' height="50" width="50" />

</td>

</tr>

</table>

</ItemTemplate>

</asp:Repeater>

</div>

</form>

</body>

# To add description to pics

<b> - For bold writing

<br> - For break

</td>

<td>

<b>Title :</b><%#Eval ("BookTitle") %><br />

<b>Author :</b><%#Eval ("BookAuthor") %><br />

<b>Publisher :</b><%#Eval ("BookPublisher") %><br />

<b>Price :</b><%#Eval ("BookPrice") %><br />

</td>

How to Save, Update, Delete and Cancel in Asp.net

In article we will learn the code for save, update, delete and cancel in Asp.net

Before studying further lets study little bit about basic knowledge and requirement needed in this project

# Ado.net

ActiveX Data Object

It is an environment design by Microsoft for database.

*In Ado.net, we have two main component

- xml = license free, platform independent, fastest technique for database

- Disconnected approach

*Environment/ Provider/ Driver

- ODBC old which are compatible with mostly all

- OLEDB

- SQLCLIENT = MSSSQL SERVER

-ORACLECLIENT = ORACLE

* Click on file > New project > Visual C# >web>website >asp.net empty website

# Application and Database connection

· Connection String - To create connection b/w application and database, the string which is used is called Connection String

Application ------------CS ----------DB

Web.config – We have to write connection string in this file

* Why we write connection string in web.config

- It is xml based and k sensitive (caps)

- It is non complied file

- xml do not have complier, it have parcels

xml can be opened in browser

* Is web.config is secured

Yes, because web.config file cannot be opened with browser

* Can web.config be multiple

Yes, But one folder have one file

* Can application be made with web.config

Yes, It can but if required then it can be used

* Web.config is used for

It is used for project configuration. For ex- Security setting, session setting for project

# Create Connection String

* Click on file > New project > Visual C# >web>website >asp.net empty website

* Click on View > Solution explorer > web.config

</connectionStrings>

# Open webform > Design

Tool box> textbox

Toolbox> Button

1st button property – save

2nd button property – update

3rd button property – delete

4th button property – display

* Now run I.E or Chrome

#Now Double click on webform

using System.Web.UI.WebControls;

using System.Data; classes used for database

using System.Data.SqlClient; classes used with mssql server

using System.Configuration; classes used with web.config

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

{

SqlConnection con = new SqlConnection();

Con- any (according to you as its object name)

Sqlconnection(); - contractor

After new no class will come

* Sqlconnection is used for

For mssql server connection for creating connection with it.

*. How to read/write web.config in connection

protected void Page_Load(object sender, EventArgs e)

{

con.ConnectionString = ConfigurationManager.ConnectionStrings["ed"].ConnectionString;

con.Open();

}

* When page is opened more and more, its performance will decrease, to stop that

if(con.State==ConnectionState.Closed)

{

con.Open();

}

connectionstate- enumeration (constant)

# Double click on Save button

protected void Button1_Click(object sender, EventArgs e)

{

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "insert employee values(@eno,@en,@ed,@es)";

cmd.Connection = con;

cmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(TextBox1.Text);

cmd.Parameters.Add("@en", SqlDbType.VarChar, 50).Value = TextBox2.Text;

cmd.Parameters.Add("@ed", SqlDbType.VarChar, 50).Value = TextBox3.Text;

cmd.Parameters.Add("@es", SqlDbType.Int).Value = Convert.ToInt32(TextBox4.Text);

cmd.ExecuteNonQuery();

cmd.Dispose();

Clear_Rec();

# Insert

insert employee values(1,'Amit','#Chd',5000)
employee is tables name

When pass value to 2 or more column

insert employee (empno,ename) values (2,'Vikas')

# In sql parameters are made with @

Parameters – When pass at runtime

# How to pass parameters

cmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(TextBox1.Text);

cmd.Parameters.Add("@en", SqlDbType.VarChar, 50).Value = TextBox2.Text;

cmd.Parameters.Add("@ed", SqlDbType.VarChar, 50).Value = TextBox3.Text;

cmd.Parameters.Add("@es", SqlDbType.Int).Value = Convert.ToInt32(TextBox4.Text);

cmd.ExecuteNonQuery();

cmd.Dispose();

Clear_Rec();

# When code is used again and again

Select lines and then

Edit > Refactor> Extract Method

Clear_Rec();

private void Clear_Rec()

{

TextBox1.Text = String.Empty;

TextBox2.Text = String.Empty;

TextBox3.Text = String.Empty;

TextBox4.Text = String.Empty;

TextBox1.Focus();

}

# Now Display data

Toolbox > Standard> Listbox

Mostly used

Listbox

Dropdown list

Checkbox list

Radio button list

select * from tbemp

select empno,ename from tbemp

select datetime

* Sql data Reader – It is used for display data. It use forword approach only. Fastest way to retrieve data from database. You must close data reader

Data value field – internally used, not for display

Stag reference – When new does not comes with class

# Double click on display

protected void Button4_Click(object sender, EventArgs e)

{

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "select * from employee";

cmd.Connection = con;

SqlDataReader dr;

dr = cmd.ExecuteReader();

ListBox1.DataTextField = "ename";

ListBox1.DataValueField = "empno";

ListBox1.DataSource = dr;

ListBox1.DataBind();

dr.Close();

cmd.Dispose();

}

Edit > Refactor > extract method

Disp_Rec();

private SqlCommand Disp_Rec()

{

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "select * from employee";

cmd.Connection = con;

SqlDataReader dr;

dr = cmd.ExecuteReader();

ListBox1.DataTextField = "ename";

ListBox1.DataValueField = "empno";

ListBox1.DataSource = dr;

ListBox1.DataBind();

dr.Close();

return cmd;

}

# To display automatically after clicking save

Double click on save

Clear_Rec();

Disp_Rec();

# To display details when clicked on name in listbox

Listbox > property> autopostback=true

#Double click on listbox

protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)

{

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "select * from employee where empno=@eno";

cmd.Connection = con;

cmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(ListBox1.SelectedValue);

SqlDataReader dr = cmd.ExecuteReader();

if(dr.HasRows)

{

dr.Read();

TextBox1.Text = dr["empno"].ToString();

TextBox2.Text = dr["ename"].ToString();

TextBox3.Text = dr["eadd"].ToString();

TextBox4.Text = dr["esal"].ToString();

}

dr.Close();

cmd.Dispose();

}

* Array of object

In data read , it is called array of object. It is called collection because different data is stored

{

dr.Read();

TextBox1.Text = dr["empno"].ToString();

TextBox2.Text = dr["ename"].ToString();

TextBox3.Text = dr["eadd"].ToString();

TextBox4.Text = dr["esal"].ToString();

}

# Double Click on update

protected void Button2_Click(object sender, EventArgs e)

{

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "update employee set ename=@en,eadd=@ed,esal=@es where empno=@eno";

cmd.Connection = con;

cmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(TextBox1.Text);

cmd.Parameters.Add("@en", SqlDbType.VarChar, 50).Value = TextBox2.Text;

cmd.Parameters.Add("@ed", SqlDbType.VarChar, 50).Value = TextBox3.Text;

cmd.Parameters.Add("@es", SqlDbType.Int).Value = Convert.ToInt32(TextBox4.Text);

cmd.ExecuteNonQuery();

cmd.Dispose();

Clear_Rec();

Disp_Rec();

}

#Double click on delete button

protected void Button3_Click(object sender, EventArgs e)

{

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "delete from employee where empno=@eno";

cmd.Connection = con;

cmd.Parameters.Add("@eno", SqlDbType.Int).Value = Convert.ToInt32(TextBox1.Text);

cmd.ExecuteNonQuery();

cmd.Dispose();

Clear_Rec();

Disp_Rec();

}

# Truncate and delete

We can apply condition in delete

Truncate delete all data from table

Code For Geeks

Friday, October 11, 2019

Learning about Templates and Working With Repeater

Templates

#Templates

# Composite Data bound Control

* Repeater

Working With Repeater

Wednesday, October 9, 2019

How to Save, Update, Delete and Cancel in Asp.net

How to Save, Update, Delete and Cancel in Asp.net

Before studying further lets study little bit about basic knowledge and requirement needed in this project

# Ado.net

# Double click on Save button

# Double click on display

#Double click on listbox

# Double Click on update

#Double click on delete button

Subscribe Us