|
I get some source codes from a textbook and rewrite them making a small system. It can CRUD user. Now I find it low efficency for coding. For example, UserDAL contains a lot of SQL scripts and they are easy to make error. UserBLL is too simple. I do not know how to improve coding efficiency. Could you give me any advices ?
The following is UserDAL source code, I intend to post UserBLL and UserModel source code but code-project remind me too long:
public class UserDAL
{
SQLHelper db = new SQLHelper();
public DataTable SelectAllUser()
{
string sql = @"SELECT [UserID],[Password],[Name],[Sex],[Phone]," +
"[Tutor],[DptName],[College],[University]," +
"[Major],[EnrollYear],[Cntnt] " +
"FROM [TrainingExam].[dbo].[ExprmntUser]";
return db.GetDataTable(sql);
}
public int InsertUser(UserModel user)
{
string sql = @"INSERT INTO [TrainingExam].[dbo].[ExprmntUser] VALUES(" +
"@UserID, @Pass, @Name, @Sex, @Phone, @Tutor, @DptName, @College, @University, @Major, @EnrollYear, @Content)";
SqlParameter[] parameters =
{
new SqlParameter("@UserID", user.UserID),
new SqlParameter("@Pass", user.Pass),
new SqlParameter("@Name", user.Name),
new SqlParameter("@Sex", user.Sex),
new SqlParameter("@Phone", user.Phone),
new SqlParameter("@Tutor", user.Tutor),
new SqlParameter("@DptName", user.DptName),
new SqlParameter("@College", user.College),
new SqlParameter("@University", user.University),
new SqlParameter("@Major", user.Major),
new SqlParameter("@EnrollYear", user.EnrollYear),
new SqlParameter("@Content", user.Content)
};
return db.ExecuteNonQuery(sql, parameters);
}
public int DeleteUser(UserModel user)
{
string sql = @"DELETE FROM [TrainingExam].[dbo].[ExprmntUser] WHERE UserID=@UserID";
SqlParameter parameter = new SqlParameter("@UserID", user.UserID);
return db.ExecuteNonQuery(sql, parameter);
}
public int UpdateUser(UserModel user)
{
string sql = @"UPDATE [TrainingExam].[dbo].[ExprmntUser] SET UserID=@UserID, Name=@Name, Sex=@Sex, Phone=@Phone, EnrollYear=@EnrollYear, " +
"Tutor=@Tutor, Major=@Major, DptName=@DptName, College=@College, University=@University, Content=@Content " +
"WHERE UserID=@UserID";
SqlParameter[] parameters =
{
new SqlParameter("@UserID", user.UserID),
new SqlParameter("@Name", user.Name),
new SqlParameter("@Sex", user.Sex),
new SqlParameter("@Phone", user.Phone),
new SqlParameter("@EnrollYear", user.EnrollYear),
new SqlParameter("@Tutor", user.Tutor),
new SqlParameter("@Major", user.Major),
new SqlParameter("@DptName", user.DptName),
new SqlParameter("@College", user.College),
new SqlParameter("@University", user.University),
new SqlParameter("@Content", user.Content),
new SqlParameter("@UserID", user.UserID)
};
return db.ExecuteNonQuery(sql, parameters);
}
}
|
|
|
|
|
These methods are not able to change. They have different functions in the CRUD operations. So, minimizing them won't help you in anything. Also you have not shared your BLL code, just the DAL code.
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~
|
|
|
|
|
Hi Afzaal, this codeproject textbox don't allow me to post too much codes in this Q&A, so I will post BLL code in the following. When I code these methods, I found I have to write same DAL, BLL again and again except of some different sql parameters. I think if I can input these parameters and the DAL and BLL code just write once. It will save a lot of time and energies and avoid a lot of errors.
public class UserBLL
{
private UserDAL userdal = new UserDAL();
public DataTable GetAllUser()
{
return userdal.SelectAllUser();
}
public bool AddUser(UserModel user)
{
if (userdal.InsertUser(user) > 0)
return true;
else
return false;
}
public bool DeleteUser(UserModel user)
{
if (userdal.DeleteUser(user) > 0)
return true;
else
return false;
}
public bool UpdateUser(UserModel user)
{
if (userdal.UpdateUser(user) > 0)
return true;
else
return false;
}
}
public class UserModel
{
public string UserID
{
get;
set;
}
public string Name
{
get;
set;
}
public string Sex
{
get;
set;
}
public string DptName
{
get;
set;
}
public string Phone
{
get;
set;
}
public string Major
{
get;
set;
}
public string University
{
get;
set;
}
public string Tutor
{
get;
set;
}
public string EnrollYear
{
get;
set;
}
public string College
{
get;
set;
}
public string Content
{
get;
set;
}
public string Pass
{
get;
set;
}
}
|
|
|
|
|
I don't think you should be making any further changes or fixes to your code, it is already in a compact shape.
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~
|
|
|
|
|
Thank you very much, Afzaal. Do you know some other more advanced architectures than the three layered one ? This must be old fashioned architecture I learn. 
|
|
|
|
|
MVC pattern is wonderful architecture, old is gold... You should continue using it. What bad is into it? I don't find any reason to why not use it.
The sh*t I complain about
It's like there ain't a cloud in the sky and it's raining out - Eminem
~! Firewall !~
|
|
|
|
|
OK! I should be confident with my code! Thank you again, Afzaal!!! I will study the wonderful MVC and some front-end framework to make my system more amazing!
|
|
|
|
|
One thing you could do is more to an ORM system like Entity Framework so you don't have to write all your own CRUD code. EF essentially becomes your DAL, you don't need to write your own.
|
|
|
|
|
Thank you, F-ES Sitecore. I even used EF in my project but one interesting thing is in Chinese developing community many developers don't advise me to use EF because they believe EF isn't stable. Thank you for your adivces and I will look into EF seriously!!!
|
|
|
|
|
There's nothing wrong with using EF. It won't be as fast as things like ado.net that you're currently using, but you have to weigh that up against development time and simplicity. It's not slow, just slower than ado.net and for most people the performance is still fine. You do have to keep an eye on things like lazy loading as how you use EF can affect the performance of it, but you'll find all that out if you look into it.
|
|
|
|
|
Thank you again and again! You give me another view looking at EF. Thank you again!!!
|
|
|
|
|
You might want to take a look at Dapper[^], which is used on the StackExchange family of sites. Since your SQL column names seem to match the property names on your UserModel class, it should be fairly simple to use:
private IDbConnection CreateConnection()
{
return new SqlConnection("YOUR CONNECTION STRING HERE");
}
public IEnumerable<UserModel> SelectAllUser()
{
const string sql = @"SELECT [UserID], [Password], [Name], [Sex], [Phone],"
+ "[Tutor], [DptName], [College], [University],"
+ "[Major], [EnrollYear], [Cntnt] "
+ "FROM [TrainingExam].[dbo].[ExprmntUser]";
using (var connection = CreateConnection())
{
return connection.Query<UserModel>(sql);
}
}
public int InsertUser(UserModel user)
{
const string sql = @"INSERT INTO [TrainingExam].[dbo].[ExprmntUser] VALUES ("
+ "@UserID, @Pass, @Name, @Sex, @Phone, @Tutor, @DptName, @College, @University, @Major, @EnrollYear, @Content)";
using (var connection = CreateConnection())
{
return connection.Execute(sql, user);
}
}
public int DeleteUser(UserModel user)
{
const string sql = @"DELETE FROM [TrainingExam].[dbo].[ExprmntUser] WHERE UserID = @UserID";
using (var connection = CreateConnection())
{
return connection.Execute(sql, user);
}
}
public int UpdateUser(UserModel user)
{
const string sql = @"UPDATE [TrainingExam].[dbo].[ExprmntUser] SET UserID = @UserID, Name = @Name, Sex = @Sex, Phone = @Phone, EnrollYear = @EnrollYear, "
+ "Tutor = @Tutor, Major = @Major, DptName = @DptName, College = @College, University = @University, Content = @Content "
+ "WHERE UserID = @UserID";
using (var connection = CreateConnection())
{
return connection.Execute(sql, user);
}
}
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Wow, it is really cool!!! Dapper can use my code smoothly!!! Thank you very much!!!
|
|
|
|
|
Hi, Richar! I can't visit Dapper. It seems that Github is blocked. Could you give me independent Dapper site ???
|
|
|
|
|
You can install the package from NuGet:
https://www.nuget.org/packages/Dapper/[^]
However, all of the documentation is on GitHub, which seems to be blocked in China. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you, Richard. I have download Dapper with Nuget Package Manager. I learn Dapper right now!
|
|
|
|
|
Hey,
I would like to discuss the value of using Unsigned value types for ensure the validy of your buissnes logic.
Just to name some exampels.
Does it make sense to let the Linq Extention 'Skip' or 'Take' be, accepting values smaller then 0? No not for me, but then why it allows this behavior by taking a int instead of an uint? Don't mention that the uint takes less memory but this is not the point.
I meant that the same space in memory can hold a bigger number ( for int ) then the signed as we got a bit more to store our data. ( sory for the vague explanation )
I Developed a network lib ( currently not OpenSource but soon  ) that takes a Port. i saw that other libs are using an int at this point. This is absolute bad from my point of view. The only valid value type here is the Ushort ( 0 to 65,535 ). It does not allow a non Positiv number and limits the parameter to the buissnes logic. So why are thees value types not used that often?
modified 27-Mar-15 5:35am.
|
|
|
|
|
|
|
Jean-Pierre Bachmann wrote: Don't mention that the uint takes less memory
Um. No, it doesn't.
A uint occupies the same amount of memory as a signed int - 32 bits, or 4 bytes - and takes the same number of different values.
The difference is that it's range of values is different:
int: -2147483648 to 2147483647
uint: 0 to 4294967295
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
1) I agree (too)
2) Unsigned types are not "CLS compliant" - I guess that's the main reason. See here[^] and here[^].
|
|
|
|
|
Because it is annoying to mix int and uint. Unlike in, say, C++, you get casts everywhere. short/ushort is even more annoying. Also even if you did use them, they wouldn't really work the way you'd like them to work (ensuring the input is valid I guess), they'd just be wrong differently. If you cast an int that is accidentally -1 to an uint and give it to Take it will still be a bug. If you cast 10000000 to an ushort and pretend it's a port, it'll be a valid port but it won't be the right port. And so on.
|
|
|
|
|
Jea but this is the point, i am forced to cast my number to an ushort, and if its not possible the fault is on "your" side. I are not even able to cast your 10000000 to ushort because its not possible for that way i enforce you to validate by your self and im force you to thing about your code that that's is obviously a good thing or not? Same as Take.
ushort test;
int test2 = 10000000;
test = (ushort)test2;
will compile but cause an error at Runtime. But in your code. in that way i protect my pice of code and enforce you to do the same on your side.
|
|
|
|
|
Runs fine for me. I just get 38528.
|
|
|
|
|
Jean-Pierre Bachmann wrote: Don't mention that the uint takes less memory but this is not the point.
uint does not take less memory than a int, they have the same exact size - it changes only the representation from pure binary to 2's complement.
Integers are the basic echange unit in the system, they allow checking for overflows and underflows: if you use a ushort in a port number and have something bad that wraps around you won't get a negative number but a perfectly valid port number. If you'd have something that overflows you would still get a perfectly valid port number.
Unless you have to store/send 2 bytes values in some form of structure or have to make bit operations on 16bit wide operands there is no real need of shorts - and if you don't have to convert decimal values in 16 bits values you won't need unsigned versions of it either since math operations yields off the same binary signature. Even if you should read a decimal value and convert it in a 0-65535 range it would be better to use an int as a middle step because there may be the possibility of reading a greater number.
These are my reasons, stemmed from my experience of course.
Geek code v 3.12
GCS d--- s-/++ a- C++++ U+++ P- L- E-- W++ N++ o+ K- w+++ O? M-- V? PS+ PE- Y+ PGP t++ 5? X R++ tv-- b+ DI+++ D++ G e++>+++ h--- r++>+++ y+++*
Weapons extension: ma- k++ F+2 X
I use 1TBS
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
