DotNet Database Development

*****DATABASE CONNECTIVITY(MIMP)*****

HOW TO CONNECT DATABASE IN WEB APPLICATION

file-> new website->c# -> asp.net empty website->browse->folder name

there are two concept used in data base connectivity

1.connectivity step

2.code step

1.connectivity step

step 1:-include data base library

i.sql server -> sqlclient

ii.xml -> xml

iii.msaccess/exel->oledb

how to include library:-

syntax:-

using system.data.libraryname;

step 2:-

createdatabase object(before the page load)

1.connection object->connection object is representinng database location.

sqlconnection objname;

sqlconnection cn;

2.sqlcommand object->sqlcommand objectb represent all query(insert,update,delete,select)

sqlcommand objname;

sqlcommand cm;

3.sqldatareader object->only use reading data from database.It only used select query.

sqldatareader objname;

sqldatareader dr;

step 3:-break the page reloading process

we have to use page load event

if(!ispostback)

{

}

***code step***

all coading is base on object oriented approach

in object oriented approach all information store in a variable and pass to the specific object

syntax:-

variable<=value/information;

object(variable)

eg.string k="insert";

cm=new sqlcommand(k);

string k1="delete";

cm=new sqlcommand(k1);

string k2="update";

cm=new sqlcommand(k2);

string k3="select";

cn=new sqlconnection(k3);

****Coding step****

step 1:-connection open code:-fixed step for button logic

string location = "path/location";

cn = new SqlConnection(location);

cn.Open();

step 2:-apply data base command(insert,update,delete,select)

sqlcommand is used to specific parameter

1.variable

2.connection object

syntax:-sqlcommand(variable,connection object)

string k = "insert into";

cm = new SqlCommand(k,cn);

step 3:-execute command

a.execute non query:-insert,update,delete

b.execute query:-select (data reader)

all query is to be executed with command object(cm).

example:-protected void Button1_Click(object sender, EventArgs e)

{

//code step 1:-open connection:-fixed step for button logic//

string location = "path/location";

cn = new SqlConnection(location);

cn.Open();

//code step 2:-apply command//

string k = "insert into";

cm = new SqlCommand(k,cn); //compulsory line//

//code step 3:-execute command//

cm.ExecuteNonQuery();

}

****HOW TO PASS DOT NET CONTROL TO DATABASE QUERY****

coloumn:- control

name:- textbox1 (t1)

mobile:- textbox2(t2)

city:- textbox3(t3)

insert into student values('rucha','8600578895','nagpur');

syntax to pass control:

'" + control + "'

'" + textbox1.text + "'

insert into student values('" + textbox1.text + "','" + textbox2.text +"','" + textbox3.text + "');

string k = "insert into student values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "')";

*****HOW TO GET DATA BASE LOCATION*****

step 1:-drag and drop table in a webform

step 2:-select sql data source

step 3:-configure data source

step 4:-click on connection string + option

step 5:-copy the path and close the window

step 6:-delete sql surce

step 7:- copy the path on code at location (code step 1)

@ is the database path/location finder

example:-

using System.Data.SqlClient;//step1:-include library//

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

{

//step 2:-create a database object//

SqlConnection cn;

SqlCommand cm;

SqlDataReader dr;

protected void Page_Load(object sender, EventArgs e)

{

//step 3:-break page reloading process//

if (!IsPostBack)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

//code step 1:-open connection:-fixed step for button logic//

string location =@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\college.mdf;Integrated Security=True;User Instance=True";

cn = new SqlConnection(location);

cn.Open();

//code step 2:-apply command//

string k = "insert into student values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "')";

cm = new SqlCommand(k,cn); //compulsory line//

//code step 3:-execute command//

cm.ExecuteNonQuery();

Response.Write("record save succusefully");

}

*****fixed step******

1.library

2.object

3.!ispostback

4.connection open code

******HOW TO WORK SELECT QUERY******

1.using datareader object

2.using command object

3.do not use execute non query

4.datareader returning two results :-true and false.

5.at the end of program datareader always closed

Que update city and mobile from student table where name=mohan

update student set city='pune',mobile='8822335500'where name=mohan;

string k = "update student set city='" + TextBox3.Text + "',mobile='" + TextBox2.Text + "' where name='" + TextBox1.Text + "'";

****delete query****

delete from student where name='rajiv';

string k = "delete from student where name='" + TextBox1.Text + "'";

cm = new SqlCommand(k, cn);

cm.ExecuteNonQuery();

Response.Write("record deleted");

select * from student where mobile='8600';

******Bound Control(IMP)******

Bound control is directly communicate to data base column

1.dropdown list control

2.checkbox list control

3.radio button list control

PROPERTY FOR BOUND CONTROL(IMP TOPIC)

there are three important property used in bound control

1.datasource=dr

2.datatextfield=column name

3.databind=();

*****IMPLEMENTATION*****

table -citydb

column -state,city

state city

MH Nagpur

MH Puna

MH Mumbai

MP Bhopal

MP Indor

MP Jabalpur

Create webpage

select your state-dropdown list (connect state column)

select your city-dropdown list(connect city column)

**IMP POINT***

if the code is to be executed at the time of page execution in this condition all code inside the !ispostback event.

display only state in statedb

select state from statedb;

in bound control cannot check datareader.

****How to insert select option in a dropdown list****

select state from statedb group by state ;

task:-coding logic implement dropdownlist1 event.

select city from statedb where state='MH';

*****MULTIVIEW*******

Multiview is a process where display multiple information in a single webpage.

1.multiview is an array control

2.multiview start with 0 location

3.activ viewindex=0

by default multi view active view index is -1.

*****scalar function******

1.scalar function is called aggregate function

2.all scale function is to be executed using execute scalar function

3.executenonquery does not required.

4.scalar function does not use datareader(dr).

*****PARAMETER QUERY*****

1.it use to query variable(store procedure)

2.all query variable is use to @ symbol.

3.parameter query does not use ' "+control+" '

****PARAMETER QUERY*****

1.parameter query is used to parameter variable

2.parameter variable represent @ symbol(@city,@state).

3.parameter variable depend on database column

*****DATA CONTROL(VERY IMP)**********

1.datacontrol basically work to data visualization.

all data control is used two property:- 1.data source 2.data bind.

a.grid view

b.repeater

c.form view

d.detail view

e.data list

g.list view

a.grid view

display data in tabular format.

1.How to connect database column in a grid view.

using bound field.(property:-header text,datafeild:-it representing data base column)

grid view->edit column

2.how to remove extra column in a grid view.

untick auto generate column.

3.how to set grid view format(auto format option)

gridview->autoformat->select any format

4.how to connect footer in a gridview.

using show footer property

go to the source of design and find out the <asp:gridview> and add attribute showfooter="true".

5.how to insert control in a grid view.

with the help of template field

only one property in template field(header text)

gridview->edit column->template field->header text->name of column

6.how to add control inside template field

go to the source of design and find out the <asp:TemplateField HeaderText="student mark"> and add <item template> then add textbox

7.gridview programming

a.control inside the gridview:-naming container method

b.control outside the gridview:-foreach()

a.control outside the gridview

foreach()

1.foreach is used to row object

2.<,>,condition cannot be used

syntax:- foreach(Gridviewrow objname in Gridview1.rows)

{

}

3.find the control inside foreach() loop.

syntax:-

control objname=(control)foreachobject.findcontrol("control id");

***********GRIDVIEW PAGING**********

1.paging is to be activated in gridview using allow paging option.

2.gridview paging is used to pageindexchanging event.

AllowPaging="true" OnPageIndexChanging="pagechange">

3.datareader read one record at a time.

4.to read the bulk data we have to use sqldataadapter

5.sqldataadapter always work with dataset.

6.adapter and dataset cannot use command object.

7.using fill() in adapter object.

(refer gpage example)

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Data;

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

{

SqlConnection cn;

SqlCommand cm;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

show();

}

protected void pagechange(object sender, GridViewPageEventArgs e)

{

GridView1.PageIndex = e.NewPageIndex;

show();

}

protected void show()

{

string location = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\college.mdf;Integrated Security=True;User Instance=True";

cn = new SqlConnection(location);

cn.Open();

string k = "select *from practicedb";

SqlDataAdapter adp = new SqlDataAdapter(k,cn);

DataSet ds = new DataSet();

adp.Fill(ds);

GridView1.DataSource = ds;

GridView1.DataBind();

}

Handling event inside the gridview control

in this concept we have to use naming container.
step1:- create a control event.(OnClick="btn2_click")
step2:-create this event inside this program.
protected void btn2_click(object sender, EventArgs e)
{
}
step3:-create naming container

syntax:-Gridviewrow obj=((control)sender).namingcontainer as Gridviewrow;
GridViewRow r1 = ((Button)sender).NamingContainer as GridViewRow;
NOTE:-BOUND COLUMN CANNOT MODIFY THE RECORD

IMP CODE FOR EXPORT TO EXCEL

export excel

{

string attachment = "attachment; filename=student.xls";

Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

Response.ContentType = "application/ms-excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

HttpContext.Current.Response.Write("<br>");

HtmlForm frm = new HtmlForm();

GridView1.Parent.Controls.Add(frm);

frm.Attributes["runat"] = "server";

frm.Controls.Add(GridView1);

frm.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

library add steps:-right click ->resolve->add first option

add attribute on source code:-EnableEventValidation="false" after inherit attribute

DYNAMIC BINDING

Dynamic binding is a process for connecting any type of control to database column

Dynamic binding is used to eval()

syntax:-

eval("column name");

eval() is used to text property

NOTE:-cells concept cannot be used in gridview during machine testing.

How insert image in gridview

using image field insert any types of image.

Property of image field:-

1.data image url filed:-name of the column.

2.dataimageurlformatstring:-name of the folder.(~/upload/{0})

HOW TO SET IMAGE ATTRIBUTE

using<controlstyle>

eg.<ControlStyle Height="100px" Width="100px" />

------------------------------------------------------------------

DATA REPEATER CONTROL

1.Gridview is display record in row and column format.

2.Repeater is display data in a form of row format.

NOTE:- IN REPEATER CONTROL CANNOT CREATE ON CLICK EVENT

Repeater Event handling

step1.using commandname attribute to button control.

step2.using onitemcommand attribute in repeater control.

****How to activate commandname attribute***

eg. protected void repeat_command(object source, RepeaterCommandEventArgs e)

Logic for display image with record in Repeater

<img src='upload/<%#Eval("pic") %>' height="100" width="100" />

HOW TO READ DATABASE CONNECTION THROUGH CONFIGUE FILE.

step 1:-go to the configue file

step 2:-write<appsetting> inside <configuration>

step 3:- add attributes key(common name) and value(database path)

</appSettings>

HOW TO GET CONFIGUE FILE IN C#

step 1:-include configuration library

using System.Configuration;

step 2:-use configurationmanager/webconfiguration manager in c#

string location = ConfigurationManager.AppSettings["db1"];

*******DATABASE HANDLING USING CLASS AND OBJECT******

How to create class

all class file stored in app_code folder

go to the website menu->add new item->select class visual c#->class name->ok

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Configuration;

using System.Data.SqlClient;

/// <summary>

/// Summary description for tsetdb

/// </summary>

public class tsetdb

{

SqlConnection cn;

SqlCommand cm;

SqlDataReader dr;

public string datapath()

{

string location = ConfigurationManager.AppSettings["db1"];

return location;

}

public string submitdata(int id1,string name1, string mobile1,int cost1,string pic1)

{

string location = ConfigurationManager.AppSettings["db1"];

cn = new SqlConnection(location);

cn.Open();

string k = "insert into testdb(id,name,mobile,cost,pic) values(@id2,@name2,@mobile2,@cost2,@pic2)";

cm = new SqlCommand(k, cn);

cm.Parameters.AddWithValue("id2", id1);

cm.Parameters.AddWithValue("name2", name1);

cm.Parameters.AddWithValue("mobile2", mobile1);

cm.Parameters.AddWithValue("cost2", cost1);

cm.Parameters.AddWithValue("pic2", pic1);

cm.ExecuteNonQuery();

return k;

}

DATABASE HANDLING USING STORE PROCEDURE

create procedure myproc

(

@roll1 int,

@name1 varchar(50)

)

insert into student values(@roll1,@name1)

RETURN

HOW TO CONNECT SERVER STUDIO IN DOTNET

DESKTOP-8VV5FTR\SQLEXPRESS

step1:-include library:-using system data library

step2:-in store procedure we have to use commandtype process.

Microsoft sql server management studio 2008 download

1.insert update delete using procedure in management studio.

2.procedure implement inside the class.(imp)

3.gridview using naming container with class.

4.update with gridview(find textbox outside the gridview).

5.refer pic.

FORM VIEW CONTROL

1.In this control is used to display data in a form format

form view display only one record at a time.

-------------------------------------------------------

ECOMMERCE PRODUCT LAYOUT

datalist control is used to creating ecommerce product layout.

Property of datalist

1.repeat column

2.repeat direction:-1.vertical 2.horizontal

------------------------------------------------------------------

Email programming

1.include the main library

using system.net.mail

2.there are two types of mail programming

1.general mail

2.domain mail(contact@tcs.com)

3.before creating a mail concept verify your email address

step 1:change your password

step 2:service on and off

google verification link

https://www.google.com/settings/security/lesssecureapps

4.in mail programming using two object

1.mailmassegeobject

2.smtpclient object

1.mailmassageobject is used to create mail property.

to,from,subject,body,attachment file

mail format:-HTML Format

2.smtp object is used to

A.sending mail

B.match the mail credential(password)

ssl(server security licence)

create a page:-welcome.aspx

enter your email id:-tb

button:-send

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Net.Mail;//email library

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

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

//mailobject

MailMessage mail = new MailMessage();

mail.To.Add(TextBox1.Text);

mail.From = new MailAddress("demo@gmail.com");

mail.Subject = "registration";

mail.Body = "dear student today is last date of form submission";

mail.IsBodyHtml = true;

//smtp object

SmtpClient st = new SmtpClient();

st.Host = "smtp.gmail.com";

st.Credentials=new System.Net.NetworkCredential("demo@gmail.com","password");

st.EnableSsl = true;

st.Send(mail);

//note:generate error in send function

//solution:mail verification and password

}

------------------------------------------------------------------

pw:-nlakkbktmpodhomf

MailMessage message = new MailMessage();

SmtpClient smtp = new SmtpClient();

message.From = new MailAddress("demovikrant26@gmail.com");

message.To.Add(new MailAddress(TextBox1.Text));

message.Subject = "Test";

message.IsBodyHtml = true; //to make message body as html

//message.Body = htmlString;

smtp.Port = 587;

smtp.Host = "smtp.gmail.com"; //for gmail host

smtp.EnableSsl = true;

smtp.UseDefaultCredentials = fal

------------------------------------------------------------------

HOW LIVE WEB APPLICATION

1.Use FTP software(file transfer protocol)

a.filezila

b.winscp(fast process)

2.server identification

a.time limit 1 year

b.html,java,python,php:-use linux web server

c.dotnet:-use window web server(costly)

3.web server configuration(using three parameter)

1.host name:-IP Address or domain name

eg.IP Address:-192.168.0.2

domain:-www.xyz.com

2.user name:-to server

3.password:-to server

4.web location(folder) in a server

where user can store application data(file)

a.linux web server(www folder/public_html folder)

b.window web server (dotnet)(httpdocs folder)

two methods for server :-upload and download

nrsolution4u-student.com/RUCHA

-----------------------------------------------------------------------------------------------------------------------------

Access database

1.create access databse (.mdb)

2.create table

3.copy database inside the project folder or any device

HOW TO CONNECT ACCESS DATABASE IN WEB APPLICATION

step 1:- include library

using system.data.oledb

step 2:- create access object

a.oledbconnection

b.oledbcommand

c.oledbdatareader

step 3:- include !ispostback inside the pageload

ACCESS CODE STEP

step 1:- open access connection

database(ms access) path

string cs = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=drive:/finalname.mdb";

Jet.OLEDB :-support access and excel

NOTE:-access database only use parameter query.

-----------------------------------------------------------------

Excel database

String conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/file/student.xls") + ";" + "Extended Properties=Excel 8.0;";

to access excel write down sheet instead of table name

to read sheet use $

note :- delete operation dose not work

----------------------------------------------------------------------------------------------------------------------------

IFRAME CONNECTIVITY***

Iframe is called interface framing

Iframe is provide multipage connectivity

xml data base

1.xml is called extensible markup language

2.xml is provide user define tag

3.extention of xml (.xml)

***How to create xml***

guestbook is database

entry is table

name,city are columns

****How to read xml***

Step 1:- include xml library:-using system xml;

step 2:-using xmltextreader

step 3:-find the xml file using server.mathpath()

step 4:-using data set object

step 5:-using read xml() with dataset

step 6:-closed the xml reader object

****how to insert xmldatabase*****

step 1:-create xml document object

SHORT TRICK IN DOTNET

1.autoredirect page

in this concept we have to use <meta>

</head>

2.Autozoom browser

in this process is used to change the browser element runtime

in this concept we have to use JavaScript

onload is a body event

3.create directory runtime

include io library

using directory class

4.remove image from directory

p1.jpg

download.png

Accountant_CV.svg

a1.jpg

5.generate random number

using random()

Search This Blog

Rucha Rajiv Shastrakar