Please explain me this - Reusing code

[ManicCW]

How can I put Insert, Delete or Update operation (access or sql) in module or class and call them in every asp.net page or vb.net form.

Please do not be general! Example maybe?

 

[kulrom]

After you declare connection, command and rest of the objects you can make it as it follows:

[SIZE=2][COLOR=#0000ff]Public [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Reading([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] rd [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2])
cmd = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand(rd, oledbcon)
objRead = cmd.ExecuteReader()
[/SIZE][SIZE=2][COLOR=#0000ff]End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Public [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Updating([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] up [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2])
cmd = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand(up, oledbcon)
cmd.ExecuteNonQuery()
[/SIZE][SIZE=2][COLOR=#0000ff]End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Public [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Inserting([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] ins [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2])
cmd = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand(ins, oledbcon)
cmd.ExecuteNonQuery()
[/SIZE][SIZE=2][COLOR=#0000ff]End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Public [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Deleting([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] dl [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2])
cmd = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand(dl, oledbcon)
cmd.ExecuteNonQuery()
[/SIZE][SIZE=2][COLOR=#0000ff]End [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]

Now you can call i.e. insert procedure in only single line in every form:

[COLOR=blue]dim[/COLOR] strSQL [COLOR=blue]as String[/COLOR] = "INSERT INTO myTable(field1, field2) VALUES(....)
Inserting(strSQL)

HTH
Regards


Edit: these are for oledb (access) but i beleive you will know how to adapt it for MSSQL

 

[ManicCW]

yes but can I put first block of code in module(this seems like it's on main form or something!?) and how can I use this in asp.net???

 

[kulrom]

actually, i am doing right that (i put this code in module) and call whenever i need to delete/insert...
For asp.net you can mke the same ... just add a module to the project and put this code

Regards

 

[ManicCW]

cool, tnx

and another question (god i'm boring):
If I use this in one project I can compile it and use dll as reference in other projects, am I right?

 

[JoeBalberde]

Could you post an example of a read, one getting a single field from the database and one getting multiple fields. And where do you declare cmd and objread? Wow i keep adding questions , where do i put the connection string? In this class? or can i pass that to the procedure?

Thanks a bunch

Joe

 

[ManicCW]

here is the sub for fetching data and filling dropdownlist:

[SIZE=2][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Friend[/COLOR][/SIZE][SIZE=2] cn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbConnection
[/SIZE][SIZE=2][COLOR=#0000ff]Friend[/COLOR][/SIZE][SIZE=2] cmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbCommand[/SIZE][SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Friend[/COLOR][/SIZE][SIZE=2] dr [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbDataReader[/SIZE]
[SIZE=2][/SIZE] 
[SIZE=2][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Not[/COLOR][/SIZE][SIZE=2] Page.IsPostBack [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]'fill albums combo
[/COLOR][/SIZE][SIZE=2]FillAlbums()[/SIZE][SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][/SIZE][/SIZE][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][/COLOR][/SIZE] 
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] FillAlbums()
cn = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection(ConfigurationSettings.AppSettings("DatabaseString").ToString)
cmd = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand
cmd.CommandText = "SELECT * FROM Albums"
cmd.Connection = cn
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].cmbAlbum.Items.Count = 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]cn.Open()
dr = cmd.ExecuteReader
[/SIZE][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE][SIZE=2] dr.Read
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].cmbAlbum.Items.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] ListItem(dr.Item("Album"), dr.Item("ID")))
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]While
[/COLOR][/SIZE][SIZE=2]cn.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]

Connection string is stored in web.config file like:

...
</system.web>

<appSettings>

<add key="DatabaseString" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\_db\DBImageGallery.mdb" />
</appSettings>
</configuration>