473,320 Members | 1,804 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

ARCHITETURAL QUESTION

Hi all

I'm using the following code standart in class
libraries. I will not split this function in more layers,
but I want to know if there are some architetural
improvement to do in my code. E.g: Database connection;
Try/Catch use; DataTable as a set of rows; etc...
Any suggestions are welcome.

Thanks!
Bruno Rodrigues

================================================== ========

using System;
using System.Data;
using System.Data.SqlClient;

namespace CodeDesign
{
/// <summary>
/// Test class for code design.
/// </summary>
public class Test
{
SqlConnection conn = new SqlConnection
("Persist Security Info=False;Integrated
Security=False;database=Test;server=Computer;Conne ct
Timeout=30;User ID=sa;Password=sa;");

private void openDataBase()
{
try
{
conn.Open();
}
catch(Exception e)
{
throw new Exception("An
error ocurred while opening the database.\n" + e.Message,
e);
}
}

private void closeDataBase()
{
try
{
conn.Close();
}
catch(Exception e)
{
throw new Exception("An
error ocurred while closing the database.\n" + e.Message,
e);
}
}

/// <summary>
/// Retrieves a specific record.
/// </summary>
/// <param name="id">Record ID.</param>
/// <returns>DataRow with record's
data.</returns>
public DataRow Return(int id)
{
openDataBase();

string sql = "SELECT * FROM Tests
WHERE PK = " + id.ToString();
SqlDataAdapter adapter = new
SqlDataAdapter(sql, conn);
DataTable table = new DataTable();

try
{
adapter.Fill(table);
return table.Rows[0];
}
catch(Exception e)
{
throw new Exception("An
error ocurred while retrieving the records.\n" +
e.Message, e);
}
finally
{
closeDataBase();
}
}
/// <summary>
/// Retrieves all records.
/// </summary>
/// <returns>DataTable with all
records.</returns>
public DataTable ReturnAll()
{
string sql = "SELECT * FROM
Tests";
SqlDataAdapter adapter = new
SqlDataAdapter(sql, conn);
DataTable table = new DataTable();

try
{
adapter.Fill(table);
return table;
}
catch(Exception e)
{
throw new Exception("An
error ocurred while retrieving all records.\n" +
e.Message, e);
}
finally
{
closeDataBase();
}
}

/// <summary>
/// Returns the number of records.
/// </summary>
/// <returns>Number of records.</returns>
public int Count()
{
string sql = "SELECT Count(PK) AS
Total FROM Tests";
SqlCommand command = new
SqlCommand(sql, conn);

try
{
return Convert.ToInt32
(command.ExecuteScalar());
}
catch(Exception e)
{
throw new Exception("An
error ocurred while counting records.\n" + e.Message, e);
}
finally
{
closeDataBase();
}
}

/// <summary>
/// Delete a record.
/// </summary>
/// <param name="id">Record ID.</param>
public void Delete(int id)
{
string sql = "DELETE FROM Tests
WHERE PK = " + id.ToString();
SqlCommand command = new
SqlCommand(sql, conn);

try
{
command.ExecuteNonQuery();
}
catch(Exception e)
{
throw new Exception("An
error ocurred while deleting a record.\n" + e.Message, e);
}
finally
{
closeDataBase();
}
}
}
}
Nov 15 '05 #1
3 1383
see http://www.microsoft.com/resources/p...mpletelist.asp

--
-------------------------
"Manish Agarwal"- <ma***********@hotmail.com>

"Bruno Rodrigues" <an*******@discussions.microsoft.com> wrote in message
news:07****************************@phx.gbl...
Hi all

I'm using the following code standart in class
libraries. I will not split this function in more layers,
but I want to know if there are some architetural
improvement to do in my code. E.g: Database connection;
Try/Catch use; DataTable as a set of rows; etc...
Any suggestions are welcome.

Thanks!
Bruno Rodrigues

================================================== ========

using System;
using System.Data;
using System.Data.SqlClient;

namespace CodeDesign
{
/// <summary>
/// Test class for code design.
/// </summary>
public class Test
{
SqlConnection conn = new SqlConnection
("Persist Security Info=False;Integrated
Security=False;database=Test;server=Computer;Conne ct
Timeout=30;User ID=sa;Password=sa;");

private void openDataBase()
{
try
{
conn.Open();
}
catch(Exception e)
{
throw new Exception("An
error ocurred while opening the database.\n" + e.Message,
e);
}
}

private void closeDataBase()
{
try
{
conn.Close();
}
catch(Exception e)
{
throw new Exception("An
error ocurred while closing the database.\n" + e.Message,
e);
}
}

/// <summary>
/// Retrieves a specific record.
/// </summary>
/// <param name="id">Record ID.</param>
/// <returns>DataRow with record's
data.</returns>
public DataRow Return(int id)
{
openDataBase();

string sql = "SELECT * FROM Tests
WHERE PK = " + id.ToString();
SqlDataAdapter adapter = new
SqlDataAdapter(sql, conn);
DataTable table = new DataTable();

try
{
adapter.Fill(table);
return table.Rows[0];
}
catch(Exception e)
{
throw new Exception("An
error ocurred while retrieving the records.\n" +
e.Message, e);
}
finally
{
closeDataBase();
}
}
/// <summary>
/// Retrieves all records.
/// </summary>
/// <returns>DataTable with all
records.</returns>
public DataTable ReturnAll()
{
string sql = "SELECT * FROM
Tests";
SqlDataAdapter adapter = new
SqlDataAdapter(sql, conn);
DataTable table = new DataTable();

try
{
adapter.Fill(table);
return table;
}
catch(Exception e)
{
throw new Exception("An
error ocurred while retrieving all records.\n" +
e.Message, e);
}
finally
{
closeDataBase();
}
}

/// <summary>
/// Returns the number of records.
/// </summary>
/// <returns>Number of records.</returns>
public int Count()
{
string sql = "SELECT Count(PK) AS
Total FROM Tests";
SqlCommand command = new
SqlCommand(sql, conn);

try
{
return Convert.ToInt32
(command.ExecuteScalar());
}
catch(Exception e)
{
throw new Exception("An
error ocurred while counting records.\n" + e.Message, e);
}
finally
{
closeDataBase();
}
}

/// <summary>
/// Delete a record.
/// </summary>
/// <param name="id">Record ID.</param>
public void Delete(int id)
{
string sql = "DELETE FROM Tests
WHERE PK = " + id.ToString();
SqlCommand command = new
SqlCommand(sql, conn);

try
{
command.ExecuteNonQuery();
}
catch(Exception e)
{
throw new Exception("An
error ocurred while deleting a record.\n" + e.Message, e);
}
finally
{
closeDataBase();
}
}
}
}

Nov 15 '05 #2
Well One thing is to Make all of you SQL strings constants. -
with good names. -
In fact I place any strings into Constants in all programs. - or a
database.- but the SQL should be a constant
"Bruno Rodrigues" <an*******@discussions.microsoft.com> wrote in message
news:07****************************@phx.gbl...
Hi all

I'm using the following code standart in class
libraries. I will not split this function in more layers,
but I want to know if there are some architetural
improvement to do in my code. E.g: Database connection;
Try/Catch use; DataTable as a set of rows; etc...
Any suggestions are welcome.

Thanks!
Bruno Rodrigues

================================================== ========

Nov 15 '05 #3
Bruno,

There are a number of issues here:

- Your class does not implement IDisposable. Because you hold other
resources on the class level which implement IDisposable, your class should
do the same.

- Your swallowing of the original exception and then re-throwing of a new
exception is a very, VERY bad idea IMO. I think that you should let the
original exception propogate. It's good that you are setting the inner
exception, but using a general Exception is very bad. You should create a
new Exception class specific to the error you are getting. So if you have
an error opening the database, you should throw an OpenDatabaseException, or
something of the sort. Eric Gunnerson wrote a good article about using
type-specific exceptions vs a value on the exception itself.

- You should be using stored prcoedures for all of your queries. If you
can't use stored procedures, at the least, use parameterized queries (which
you might or might not prepare).

- Instead of using openDatabase/closeDatabase and handling that yourself,
just pass the connection object to the command. It will open and close the
connection for you. This also reduces a lot of the error code that you have
(when the exception is thrown, you will know you have a problem opening the
connection from the internal exception, and your exception will indicate
where it happened).

- Implement Count as a property, not as a method. If implementing as a
method, then change the method name to GetCount, to indicate it is
performing some operation.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Bruno Rodrigues" <an*******@discussions.microsoft.com> wrote in message
news:07****************************@phx.gbl...
Hi all

I'm using the following code standart in class
libraries. I will not split this function in more layers,
but I want to know if there are some architetural
improvement to do in my code. E.g: Database connection;
Try/Catch use; DataTable as a set of rows; etc...
Any suggestions are welcome.

Thanks!
Bruno Rodrigues

================================================== ========

using System;
using System.Data;
using System.Data.SqlClient;

namespace CodeDesign
{
/// <summary>
/// Test class for code design.
/// </summary>
public class Test
{
SqlConnection conn = new SqlConnection
("Persist Security Info=False;Integrated
Security=False;database=Test;server=Computer;Conne ct
Timeout=30;User ID=sa;Password=sa;");

private void openDataBase()
{
try
{
conn.Open();
}
catch(Exception e)
{
throw new Exception("An
error ocurred while opening the database.\n" + e.Message,
e);
}
}

private void closeDataBase()
{
try
{
conn.Close();
}
catch(Exception e)
{
throw new Exception("An
error ocurred while closing the database.\n" + e.Message,
e);
}
}

/// <summary>
/// Retrieves a specific record.
/// </summary>
/// <param name="id">Record ID.</param>
/// <returns>DataRow with record's
data.</returns>
public DataRow Return(int id)
{
openDataBase();

string sql = "SELECT * FROM Tests
WHERE PK = " + id.ToString();
SqlDataAdapter adapter = new
SqlDataAdapter(sql, conn);
DataTable table = new DataTable();

try
{
adapter.Fill(table);
return table.Rows[0];
}
catch(Exception e)
{
throw new Exception("An
error ocurred while retrieving the records.\n" +
e.Message, e);
}
finally
{
closeDataBase();
}
}
/// <summary>
/// Retrieves all records.
/// </summary>
/// <returns>DataTable with all
records.</returns>
public DataTable ReturnAll()
{
string sql = "SELECT * FROM
Tests";
SqlDataAdapter adapter = new
SqlDataAdapter(sql, conn);
DataTable table = new DataTable();

try
{
adapter.Fill(table);
return table;
}
catch(Exception e)
{
throw new Exception("An
error ocurred while retrieving all records.\n" +
e.Message, e);
}
finally
{
closeDataBase();
}
}

/// <summary>
/// Returns the number of records.
/// </summary>
/// <returns>Number of records.</returns>
public int Count()
{
string sql = "SELECT Count(PK) AS
Total FROM Tests";
SqlCommand command = new
SqlCommand(sql, conn);

try
{
return Convert.ToInt32
(command.ExecuteScalar());
}
catch(Exception e)
{
throw new Exception("An
error ocurred while counting records.\n" + e.Message, e);
}
finally
{
closeDataBase();
}
}

/// <summary>
/// Delete a record.
/// </summary>
/// <param name="id">Record ID.</param>
public void Delete(int id)
{
string sql = "DELETE FROM Tests
WHERE PK = " + id.ToString();
SqlCommand command = new
SqlCommand(sql, conn);

try
{
command.ExecuteNonQuery();
}
catch(Exception e)
{
throw new Exception("An
error ocurred while deleting a record.\n" + e.Message, e);
}
finally
{
closeDataBase();
}
}
}
}

Nov 15 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Stevey | last post by:
I have the following XML file... <?xml version="1.0"?> <animals> <animal> <name>Tiger</name> <questions> <question index="0">true</question> <question index="1">true</question> </questions>
7
by: nospam | last post by:
Ok, 3rd or is it the 4th time I have asked this question on Partial Types, so, since it seems to me that Partial Types is still in the design or development stages at Microsoft, I am going to ask...
3
by: Ekqvist Marko | last post by:
Hi, I have one Access database table including questions and answers. Now I need to give answer id automatically to questionID column. But I don't know how it is best (fastest) to do? table...
11
by: Bruno Rodrigues | last post by:
Hi, I'm on my way to my first OOP-for-real project in C#, and having some big trouble with code architeture. I have four namespaces: - Layout - Info - Where I hold empty structs with...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.