Persist object: connect

Showing posts with label connect. Show all posts

Thursday, March 29, 2012

Please evaluate C# code - Beginner level

I have recently, with the help of Fishcake and Sevenhalo, figured out how to connect to a SQL Server and retrieve some data.

In order to expand my knowledge, my goal is to write a class that will encapsulate this code. I understand that writing a class for my tiny website may be a bit excessive, but its more for the knowledge of "how-to" than practicality.

The class that I have written does somewhat work:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.Sql;

public class Database
{

private String DatabaseConnectionString = "Data source=*;" +
"Database=*;" +
"uid=*;" +
"pwd=*";

private SqlConnection objConnection;
private SqlCommand objCommand;
public SqlDataReader objReader;
public String dbError;

public String GetConnectionString()
{
return DatabaseConnectionString;
}

private SqlConnection CreateConnection()
{
SqlConnection objConnection = new SqlConnection(DatabaseConnectionString);
return objConnection;
}

private SqlCommand CreateCommand(String p_Command, SqlConnection p_Connection)
{
SqlCommand objCommand = new SqlCommand(p_Command, p_Connection);
return objCommand;
}

//Article Related
public void GetArticleByID(String p_CategoryID, String p_ArticleID)
{
objConnection = CreateConnection();
objCommand = CreateCommand("SELECT * FROM tblArticle " +
"WHERE tblArticle.charArticleID=@dotnet.itags.org.Article " +
"AND tblArticle.charCategoryID=@dotnet.itags.org.Category", objConnection);

SqlDataReader objReader;

try
{
objConnection.Open();
objCommand.Parameters.AddWithValue("@dotnet.itags.org.Article",p_ArticleID);
objCommand.Parameters.AddWithValue("@dotnet.itags.org.Category",p_CategoryID);

objReader = objCommand.ExecuteReader();

}
catch(System.Exception ex)
{
dbError = ex.Message;
objReader= null;
}

}

//End Articles
}

The class is used like this:

<script runat="server" language="c#">

protected void Page_Load(Object sender, EventArgs e)
{
String strCategoryID = Request.QueryString["ci"];
String strArticleID = Request.QueryString["ai"];

Database objDatabase = new Database();
objDatabase.GetArticleByID(strCategoryID,strArticleID);

if(objDatabase.objReader != null)
{
while(objDatabase.objReader.Read());
{
label1.Text += objDatabase.objReader["charTitle"].ToString();
}
}
else
label1.Text = objDatabase.dbError;

}

</script>

Question 1: Is my method even correct? Am I taking the right approach to this?

I'm trying to create shortcuts by making functions that create the connectionstring and command for me without me having to type it out each time. The idea behind the class is that, it will do all the work for me and store a SqlDataReader object in itself, that I will then manipulate in my Page_Load event.

Any input in regards to this question is greatly appreciated. Thanks!What you've created is called a DAL - Data Access Layer. It's a class which encapsulates the database-specific methods. You're doing fine. Your approach is right, but the datareader isn't required here. In addition, your GetArticleById method is returning void. So, you need to make it return a dataset, and change its function signature to return a dataset.

In the code that calls this method, assign a dataset to the method, and use that dataset to... whatever.
when is it best to use the DataAdapter/DataSet approach and when is it best to use a DataReader?

I mean the DataAdapter/DataSet just seems like the long way instead of just using a DataReader.
It depends on what you need... if you need to get some records, manipulate the data in memory and then send the changes back, a DataSet/Table would be the candidate. But if you are doing something like filling a combobox, then a Reader is ideal. Any time you need to loop through the data quickly and use it for something other than modifying the data, odds are, a reader will suffice. They come at a small price: 1) they create an exclusive lock on the connection, meaning you cannot use it for anything else. If you need to connect to do another db operation, a second connection will be needed. 2) They are read-only and forward only. You can't change the data and cannot move backwards in the data. But that's also where thier power comes from. By being what's called a Firehose cursor, it is very fast and efficient.

-tg
awesome... that is great to know, thanks tg!
I'll add to that. In a situation when you need to work with and update a very, very, very large set of data, do not use a dataset. Instead, use a datareader, and use separate code for the database manipulation.
Mendhak & TechGnome,

Thanks so much for the reply. Your opinions are appreciated!

In addition, your GetArticleById method is returning void.

This is true. I actually changed the code without making the correct change in my question. Instead of returning something, I attached the Datareader to the object itself.

Instead of doing:
DataReader MyObject = GetArticleByID(X,Y)

I am manipulating the DataReader as part of the object:
objDatabase.DataReader

I think this is the best way to do it. I will update my initial post.

but the datareader isn't required here

I guess I'm a little confused about what TechGnome said.

t depends on what you need... if you need to get some records, manipulate the data in memory and then send the changes back, a DataSet/Table would be the candidate. But if you are doing something like filling a combobox, then a Reader is ideal.

Since in the example I am only getting one article, its title, and the picture that is associated with it, would a DataReader not be the best? I only need a read only connection that retrieves data, I do not manipulate it in any way.

It seems to me that the dataset would be more appropriate for functions that modify the data such as:
AddNewArticle(X,Y);
EditArticle(X,Y);

Thanks again for the replies.
Actualy if all you are returning is a single record... you should look into using output parameters and use the ExecuteNonQuery method.

-tg
Since in the example I am only getting one article, its title, and the picture that is associated with it, would a DataReader not be the best?

Nope. A dataset would be ideal here. You have a chunk of text, a string, and an image. You are not moving anywhere within the data. So, you need a dataset.

Have your method GetArticleById() return a dataset.
Got it, thanks for the replies all. I will use a DataSet!

Please explain why AddHandler works in Page_Load, but not Button_C

I need to understand why if I add a control and use AddHandler to connect its
click event, it will work in Page_Load, but not in a Button_Click.

The idea is that the user types some data, presses the button, gets a list
of results (each with a LinkButton) and can then press one of the link
buttons to get further information. The newly added link buttons appear, but
the click event added with AddHandler does not fire.

A control added in Page_Load with an event handler added with AddHandler
works fine.

I have not writted a web app previously and I guess that I do not understand
how dynamically added handlers work (or dont work).

--
John AustinIt has to do with the order of events in .NET. By the time you hit button
events, you have already configured controls. Under "sender", you can find
out which button was clicked and adjust appropriately in Page_Load. Be
careful, however, with overloading Page_Load with too much, as you can end
up with disaster.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
*************************************************
Think outside of the box!
*************************************************
"John Austin" <John.Austin@.nospam.nospamwrote in message
news:08B9C89B-CB28-41CE-899E-4AF85F704B84@.microsoft.com...

Quote:

Originally Posted by

>I need to understand why if I add a control and use AddHandler to connect
>its
click event, it will work in Page_Load, but not in a Button_Click.
>
The idea is that the user types some data, presses the button, gets a list
of results (each with a LinkButton) and can then press one of the link
buttons to get further information. The newly added link buttons appear,
but
the click event added with AddHandler does not fire.
>
A control added in Page_Load with an event handler added with AddHandler
works fine.
>
I have not writted a web app previously and I guess that I do not
understand
how dynamically added handlers work (or dont work).
>
--
John Austin

the key to understanding asp.net is to know that its stateless, and a form
instance is only used for one request. so if you add a link in a button
click, and the user clicks on that link, a new form is built to process that
request. your code must know to add the handler when that happens. usually
you'd store a flag in viewstate or session to known when to add the handler
(you must also re-add the link control).

-- bruce (sqlwork.com)

"John Austin" <John.Austin@.nospam.nospamwrote in message
news:08B9C89B-CB28-41CE-899E-4AF85F704B84@.microsoft.com...

Quote:

Originally Posted by

Sorry, but I still don't get it. What I have in mind is a form with 3 states
that would look a bit like google. State 1, you see a textbox and button, you
type a search value and press the button, State 2, a list of search results
is displayed (a table with a LinkButton in each row), you click on the
selected row's LinkButton. State 3, The details about the selected item are
displayed along with a button to go back to state 1.

The database search after state 1 and the insertion of the results and
LinkButtons in the table must take place in the Button's Click event, the
table duly appears with the data and LinkButtons. At the end of state 2, the
LinkButtons cause a post, but no event is fired, also the entire contents of
the table no longer exist - this does not matter as I only want to know the
ID of the button.

Is the idea of linking the dynamically created LinkButtons to an event the
wrong approach? Should I be looking at the request.form(0) value? Or is my
whole approach wrong, am I thinking too much along Windows Forms lines?
--
John Austin

"Cowboy (Gregory A. Beamer)" wrote:

Quote:

Originally Posted by

It has to do with the order of events in .NET. By the time you hit button
events, you have already configured controls. Under "sender", you can find
out which button was clicked and adjust appropriately in Page_Load. Be
careful, however, with overloading Page_Load with too much, as you can end
up with disaster.
>
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
>
*************************************************
Think outside of the box!
*************************************************
"John Austin" <John.Austin@.nospam.nospamwrote in message
news:08B9C89B-CB28-41CE-899E-4AF85F704B84@.microsoft.com...

Quote:

Originally Posted by

I need to understand why if I add a control and use AddHandler to connect
its
click event, it will work in Page_Load, but not in a Button_Click.

The idea is that the user types some data, presses the button, gets a list
of results (each with a LinkButton) and can then press one of the link
buttons to get further information. The newly added link buttons appear,
but
the click event added with AddHandler does not fire.

A control added in Page_Load with an event handler added with AddHandler
works fine.

I have not writted a web app previously and I guess that I do not
understand
how dynamically added handlers work (or dont work).

--
John Austin

>
>
>

Hi John,

First, I recommend you to use [1] as your starting point to learn ASP.NET.
Though it's written for ASP.NET 1.x, it should get you started.

Since ASP.NET page is stateless, a page class instance and its controls
will need to be created at the server side every time the page is posted
back. See following steps to get an overview of the page life cycle:

1) POST Request is issued by client
2) Page-derived class is created, constructor is invoked
3) IHttpHandler.ProcessRequest is invoked (implemented by Page)
4) Page.Init()
5) Page.CreateChildControls()
6) Server-side control state is restored from POST variables and VIEWSTATE
7) Page.Load()
8) Page.Validate()
9) Server-side control events are fired
10) Page.PreRender()
11) Page.Render()
12) Page.RenderChildren()
13) HTTP Response is issued to client
14) Page.Unload()
15) Instance of Page-derived class is discarded

Each control will have a unique ID, this ID will be used to restore the
state from POST variables and VIEWSTATE (step 6) to the re-created controls
(step 5).

During load (step 7), if the current request is a postback, control
properties are loaded with information recovered from view state and
control state. (See [2] fore more information)

For your dynamically added controls to restore state correctly, you must
make sure the controls are added before the Load event. It's a best
practice to re-create them in Page.Load.

Regarding your issue, the LinkButtons are created in Button's Click event,
they are displayed in the result for the first time since Render is taken
place after that (step 11 and step 9). When the LinkButton is clicked and
caused a postback, the LinkButtons are not re-created, and the POST
variables related to them are lost. You could check the Request.Form for
the field that caused the postback, but there're better way to fulfil your
requirement.

Actually your requirement can be best handled by the Repeater [3] control
and DataBinding:

<asp:Button ID="Button1" runat="server" Text="Button"
OnClick="Button1_Click" />
<br />
<asp:Repeater ID="Repeater1" runat="server"
OnItemCommand="Repeater1_ItemCommand">
<HeaderTemplate>
<ul>
</HeaderTemplate>
<ItemTemplate>
<li>
<asp:LinkButton ID="link1" runat="server" CommandName="Link1"
CommandArgument='<%# Eval("Description") %>' Text='<%# Eval("Name") %>'

Quote:

Originally Posted by

></asp:LinkButton>

</li>
</ItemTemplate>
<FooterTemplate>
</ul>
</FooterTemplate>
</asp:Repeater>

protected void Button1_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Description");
dt.Rows.Add("First", "This is the first item");
dt.Rows.Add("Second", "This is the second item");
Repeater1.DataSource = dt;
Repeater1.DataBind();
}
protected void Repeater1_ItemCommand(object source,
RepeaterCommandEventArgs e)
{
if (e.CommandName == "Link1")
{
Response.Write(e.CommandArgument);
}
}

LinkButton's Click event will be bubbled up to the Repeater and fire
ItemCommand instead. Using the CommandName and CommandArgument, we can
differentiate which LinkButton is clicked.

I hope this could help you get familiar with how ASP.NET works and how to
handle such scenario using appropriate controls. Please feel free to let me
know whether or not you need further information. Thank you.

References:

[1] INFO: ASP.NET Roadmap
http://support.microsoft.com/kb/305140/
[2] ASP.NET Page Life Cycle Overview
http://msdn2.microsoft.com/en-us/library/ms178472.aspx
[3] Repeater Web Server Control Overview
http://msdn2.microsoft.com/en-us/library/x8f2zez5.aspx
Sincerely,
Walter Wang (wawang@.online.microsoft.com, remove 'online.')
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications. If you are using Outlook Express, please make sure you clear the
check box "Tools/Options/Read: Get 300 headers at a time" to see your reply
promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...rt/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
Thank you Walter for your excellent reply. Whilst checking request.form(0) in
Page_Load was a work around (after they click, I don't want the table
anyway), the Repeater is a better solution for me as I can use the event
driven methodology that I am used to with Windows Forms applications.

Thanks once again,
--
John Austin

"Walter Wang [MSFT]" wrote:

Quote:

Originally Posted by

Hi John,
>
First, I recommend you to use [1] as your starting point to learn ASP.NET.
Though it's written for ASP.NET 1.x, it should get you started.
>
Since ASP.NET page is stateless, a page class instance and its controls
will need to be created at the server side every time the page is posted
back. See following steps to get an overview of the page life cycle:
>
1) POST Request is issued by client
2) Page-derived class is created, constructor is invoked
3) IHttpHandler.ProcessRequest is invoked (implemented by Page)
4) Page.Init()
5) Page.CreateChildControls()
6) Server-side control state is restored from POST variables and VIEWSTATE
7) Page.Load()
8) Page.Validate()
9) Server-side control events are fired
10) Page.PreRender()
11) Page.Render()
12) Page.RenderChildren()
13) HTTP Response is issued to client
14) Page.Unload()
15) Instance of Page-derived class is discarded
>
Each control will have a unique ID, this ID will be used to restore the
state from POST variables and VIEWSTATE (step 6) to the re-created controls
(step 5).
>
During load (step 7), if the current request is a postback, control
properties are loaded with information recovered from view state and
control state. (See [2] fore more information)
>
For your dynamically added controls to restore state correctly, you must
make sure the controls are added before the Load event. It's a best
practice to re-create them in Page.Load.
>
Regarding your issue, the LinkButtons are created in Button's Click event,
they are displayed in the result for the first time since Render is taken
place after that (step 11 and step 9). When the LinkButton is clicked and
caused a postback, the LinkButtons are not re-created, and the POST
variables related to them are lost. You could check the Request.Form for
the field that caused the postback, but there're better way to fulfil your
requirement.
>
Actually your requirement can be best handled by the Repeater [3] control
and DataBinding:
>
>
<asp:Button ID="Button1" runat="server" Text="Button"
OnClick="Button1_Click" />
<br />
<asp:Repeater ID="Repeater1" runat="server"
OnItemCommand="Repeater1_ItemCommand">
<HeaderTemplate>
<ul>
</HeaderTemplate>
<ItemTemplate>
<li>
<asp:LinkButton ID="link1" runat="server" CommandName="Link1"
CommandArgument='<%# Eval("Description") %>' Text='<%# Eval("Name") %>'

Quote:

Originally Posted by

</asp:LinkButton>

</li>
</ItemTemplate>
<FooterTemplate>
</ul>
</FooterTemplate>
</asp:Repeater>
>
>
protected void Button1_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Description");
dt.Rows.Add("First", "This is the first item");
dt.Rows.Add("Second", "This is the second item");
Repeater1.DataSource = dt;
Repeater1.DataBind();
}
protected void Repeater1_ItemCommand(object source,
RepeaterCommandEventArgs e)
{
if (e.CommandName == "Link1")
{
Response.Write(e.CommandArgument);
}
}
>
LinkButton's Click event will be bubbled up to the Repeater and fire
ItemCommand instead. Using the CommandName and CommandArgument, we can
differentiate which LinkButton is clicked.
>
I hope this could help you get familiar with how ASP.NET works and how to
handle such scenario using appropriate controls. Please feel free to let me
know whether or not you need further information. Thank you.
>
References:
>
[1] INFO: ASP.NET Roadmap
http://support.microsoft.com/kb/305140/
>
[2] ASP.NET Page Life Cycle Overview
http://msdn2.microsoft.com/en-us/library/ms178472.aspx
>
[3] Repeater Web Server Control Overview
http://msdn2.microsoft.com/en-us/library/x8f2zez5.aspx
>
Sincerely,
Walter Wang (wawang@.online.microsoft.com, remove 'online.')
Microsoft Online Community Support
>
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications. If you are using Outlook Express, please make sure you clear the
check box "Tools/Options/Read: Get 300 headers at a time" to see your reply
promptly.
>
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...rt/default.aspx.
==================================================
>
This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

Hello Walter,
The Data Repeater works fine, but thinking further about the issue, it would
make sense for state 1 to build a query string from input criteria and store
the select statement in viewstate so that the Page_Load in state 2 can query
the database and generate results (this means that pressing Back in the
browser from state 3 would re-query the database and include any changes).
This would also avoid my original problem; but when in state 1, the button
press would need to build the query string, change the state to 2 and then
force a reload of the current page. How could I force the page to be re-build
from the Button_Click event?

--
John Austin

"Walter Wang [MSFT]" wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

</asp:LinkButton>

Hi John,

See if following code answers your question:

protected void Page_Load(object sender, EventArgs e)
{
RebindData();
}
protected void Button1_Click(object sender, EventArgs e)
{
QueryStatement = "foo sql statement";
RebindData();
}

void RebindData()
{
string sql = QueryStatement;
if (string.IsNullOrEmpty(sql))
{
Repeater1.DataSource = null;
}
else
{
// you need to do sql query in real app; here I'm just
returning a DataTable for test purpose
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Description");
dt.Rows.Add("First", "This is the first item");
dt.Rows.Add("Second", "This is the second item");
Repeater1.DataSource = dt;
}
Repeater1.DataBind();
}

string QueryStatement
{
get { reutrn ViewState["QueryStatement"] as string; }
set { ViewState["QueryStatement"] = value; }
}

protected void Repeater1_ItemCommand(object source,
RepeaterCommandEventArgs e)
{
if (e.CommandName == "Link1")
{
Response.Write(e.CommandArgument);
}
}

Regards,
Walter Wang (wawang@.online.microsoft.com, remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
Sorry Walter, putting the creation of the results table in a subroutine is a
far simpler solution - I guess that getting my head round the way web app
work clouded the brain!

Many thanks,

--
John Austin

"Walter Wang [MSFT]" wrote:

Quote:

Originally Posted by

Hi John,
>
See if following code answers your question:
>
protected void Page_Load(object sender, EventArgs e)
{
RebindData();
}
protected void Button1_Click(object sender, EventArgs e)
{
QueryStatement = "foo sql statement";
RebindData();
}
>
void RebindData()
{
string sql = QueryStatement;
if (string.IsNullOrEmpty(sql))
{
Repeater1.DataSource = null;
}
else
{
// you need to do sql query in real app; here I'm just
returning a DataTable for test purpose
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Description");
dt.Rows.Add("First", "This is the first item");
dt.Rows.Add("Second", "This is the second item");
Repeater1.DataSource = dt;
}
Repeater1.DataBind();
}
>
string QueryStatement
{
get { reutrn ViewState["QueryStatement"] as string; }
set { ViewState["QueryStatement"] = value; }
}
>
protected void Repeater1_ItemCommand(object source,
RepeaterCommandEventArgs e)
{
if (e.CommandName == "Link1")
{
Response.Write(e.CommandArgument);
}
}
>
Regards,
Walter Wang (wawang@.online.microsoft.com, remove 'online.')
Microsoft Online Community Support
>
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
>
This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

Please explain why AddHandler works in Page_Load, but not Button_C

I need to understand why if I add a control and use AddHandler to connect it
s
click event, it will work in Page_Load, but not in a Button_Click.
The idea is that the user types some data, presses the button, gets a list
of results (each with a LinkButton) and can then press one of the link
buttons to get further information. The newly added link buttons appear, but
the click event added with AddHandler does not fire.
A control added in Page_Load with an event handler added with AddHandler
works fine.
I have not writted a web app previously and I guess that I do not understand
how dynamically added handlers work (or dont work).
John Austinthe key to understanding asp.net is to know that its stateless, and a form
instance is only used for one request. so if you add a link in a button
click, and the user clicks on that link, a new form is built to process that
request. your code must know to add the handler when that happens. usually
you'd store a flag in viewstate or session to known when to add the handler
(you must also re-add the link control).
-- bruce (sqlwork.com)
"John Austin" <John.Austin@.nospam.nospam> wrote in message
news:08B9C89B-CB28-41CE-899E-4AF85F704B84@.microsoft.com...
>I need to understand why if I add a control and use AddHandler to connect
>its
> click event, it will work in Page_Load, but not in a Button_Click.
> The idea is that the user types some data, presses the button, gets a list
> of results (each with a LinkButton) and can then press one of the link
> buttons to get further information. The newly added link buttons appear,
> but
> the click event added with AddHandler does not fire.
> A control added in Page_Load with an event handler added with AddHandler
> works fine.
> I have not writted a web app previously and I guess that I do not
> understand
> how dynamically added handlers work (or dont work).
> --
> John Austin
It has to do with the order of events in .NET. By the time you hit button
events, you have already configured controls. Under "sender", you can find
out which button was clicked and adjust appropriately in Page_Load. Be
careful, however, with overloading Page_Load with too much, as you can end
up with disaster.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
****************************************
*********
Think outside of the box!
****************************************
*********
"John Austin" <John.Austin@.nospam.nospam> wrote in message
news:08B9C89B-CB28-41CE-899E-4AF85F704B84@.microsoft.com...
>I need to understand why if I add a control and use AddHandler to connect
>its
> click event, it will work in Page_Load, but not in a Button_Click.
> The idea is that the user types some data, presses the button, gets a list
> of results (each with a LinkButton) and can then press one of the link
> buttons to get further information. The newly added link buttons appear,
> but
> the click event added with AddHandler does not fire.
> A control added in Page_Load with an event handler added with AddHandler
> works fine.
> I have not writted a web app previously and I guess that I do not
> understand
> how dynamically added handlers work (or dont work).
> --
> John Austin

Wednesday, March 21, 2012

Please help me improve this class for accessing database data

Hi All

I've just written my first class that attempts to perform the following actions:
1. Connect to SQL Server 2000 using the connection defined in web.config
2. Execute a stored procedure (passed as a parameter) using any number of SQL parameters
3. Return the data as a dataTable back to the calling page

I need some really general advice from you experts to help improve it in the following ways if possible:
1. Reliability
2. Performance

Although it does work, it's probably quite badly written as to be honest I don't really know what I'm doing. Any help would be really appreciated! Big Smile [:D]

Namespace myNS
Public Class dataGrabber

Private Shared myConn As New SqlConnection(ConfigurationManager.ConnectionStrings("myConnString").ConnectionString)
Private Shared mySQLCommand As New SqlCommand
Private Shared myDataReader As SqlDataReader

Public Sub New(ByVal storedProcedureToRun As String)
mySQLCommand.Connection = myConn
mySQLCommand.CommandText = storedProcedureToRun
mySQLCommand.CommandType = CommandType.StoredProcedure
End Sub

Public Sub AddParam(ByVal paramName As String, ByVal paramDataType As SqlDbType, ByVal paramValue As String)
Dim tmpParam As New SqlParameter(paramName, paramDataType)
tmpParam.Value = paramValue
mySQLCommand.Parameters.Add(tmpParam)
End Sub

Public ReadOnly Property getData() As DataTable
Get
Dim myDataTable As New DataTable
mySQLCommand.Connection.Open()
myDataReader = mySQLCommand.ExecuteReader(CommandBehavior.CloseConnection)
myDataTable.Load(myDataReader)
mySQLCommand.Parameters.Clear()
mySQLCommand.Dispose()
Return myDataTable
End Get
End Property

End Class
End Namespace

With my expereince, I always make my data layers static, there is really no need to maintain class instances in the data layer, however there are exceptions depending on the requirements of your project.

In my data layers, I execute a command like so: MyDataLayer.ExecuteDataSet(cn, "SP_NAME", object[] args);

notice the last parameter is an object array that will hold the parameters for the stored proc.

If you want some good ideas and good practices, download microsofts data access application block and look through the code. They have some good code in there that uses caching for parameters and connections, which speeds up the datalayer.

Also take a look at patterns & practices on msdn. there is alot of good info on there.

You shouldn't make your class-level variables (myConn etc) shared. And if you want to return a DataTable you could use the DataAdapter rather than getting a Reader and loading it into a DataTable.
Thanks both for your help.

>> You shouldn't make your class-level variables (myConn etc) shared
I thought Shared just meant that other subs and functions in the class could access these once they had been declared. Obviously not. What is Shared therefore?

>> if you want to return a DataTable you could use the DataAdapter rather than getting a Reader and loading it into a DataTable
Could you please provide an example of this that would fit the code above?

Thanks again guys! Big Smile [:D]
Shared means there is only one instance of the object that spans all classes. So if you create two instances of your data class they both share the same connection and command object. So if one user on your site is running SPA and at the same time another user is running SPB you will get unexpected results.

For the DataAdapter;

http://authors.aspalliance.com/aspxtreme/sys/data/Common/DataAdapterClassFill.aspx

Hi Aidy
From the examples I can see online, all use a dataAdapter to fill adataTable or dataSet. Therefore (from my basic understanding Wink [;)]

) I'dstill require the temporary dataTable in the class to pass back to the calling page, right?? On top of this,wouldn't the dataAdapter use more resources than the dataReader?

I'm using the class in my pages as follows;

Dim DBAccess As New myNS.dataGrabber("sp_getlogin") ' instance of class
DBAccess.AddParam("username", SqlDbType.NVarChar, loginControl.UserName) ' add param username
DBAccess.AddParam("password", SqlDbType.NVarChar, loginControl.Password) ' add param password
Dim loginTable As DataTable = DBAccess.getData() ' returns values from database to use for this user

Thursday, March 29, 2012

Please evaluate C# code - Beginner level

Please explain why AddHandler works in Page_Load, but not Button_C

Please explain why AddHandler works in Page_Load, but not Button_C

Wednesday, March 21, 2012

Please help me improve this class for accessing database data

Persist object

Blog Archive

About Me