如何获得maxid使用方法重新使用相同的 [英] how to get maxid using method fro re use in same from
问题描述
private void ClassFrom_Load(对象发件人,EventArgs e)
{
TxtClassID.Text = FunGetMaxID();
}
private string FunGetMaxID()
{
尝试
{
con = new SqlConnection(cs.Dbcon);
con.Close();
string sql = 选择isnull(最大值) (ClassId),0)+1作为Id的类;
SqlCommand cmd = new SqlCommand(sql,con);
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
FunGetMaxID()=(reader [ id]);
reader.Close();
cmd.Dispose();
con.Close();
// 返回FunGetMaxID;
}
catch (Exception ex)
{
MessageBox.Show( 错误, 消息 + ex.Message);
}
下面我给出一些关于代码的建议。但是我对此功能表示严重关切。看起来好像是在尝试查找最大当前ID以分配下一个值。这是一个非常糟糕的设计!如果另一个用户在此代码运行时添加另一条记录怎么办? (很可能在多用户环境中)。更好的方法是使用正确的工具 - 标识列 [ ^ ]数据库
代码审查:
首先看一下FunGetMaxID()=(reader [ id]);
这是非常VB6的风格,将导致编译错误
Quote:赋值的左侧必须是变量,属性或索引器
下一步 - 了解使用声明 [ ^ ] - 它对于使用sql连接特别有用。您不需要显式关闭连接也不需要处理它。
当我们讨论该主题时,请查看con = new SqlConnection(cs.Dbcon);
con.Close();
.Close()
中没有任何意义 - 你刚刚创建了这个对象! br />
现在考虑你的sql语句SELECT ISNULL(MAX(ClassId),0)+1 AS Id FROM ClassIt最好处理在源处为null的可能性而不是升级null的计算/函数/等等,这样会更好
SELECT MAX(ISNULL(ClassId,0))AS Id FROM Class
下一个问题是你有private 字符串 FunGetMaxID()但最大Id可能是一个整数,因此您应该从函数返回一个整数。请注意,您命名该函数的方式意味着您希望id的现有最大值 - 但是您返回最大值加1.重命名函数或返回正确的值
正如Manas_Kumar建议的那样,使用 ExecuteScalar [ ^ ]在这个例子中(你没有有来,它更合适)
关于Try-Catch块,您可能还会发现这些文章很有用: .NET中的异常处理最佳实践 [ ^ ]和例外的最佳做法 - MSDN [ ^ ]
我会做类似private < span class =code-keyword> int FunGetMaxID()
{
var maxId = 0 跨度>;
尝试
{
使用( var con = new SqlConnection(cs.Dbcon))
{
con.Open( );
const string sql = 从类;选择max(IsNull(ClassId,0))为Id
使用( var cmd = new SqlCommand(sql,con))
{
cmd.CommandType = CommandType.Text;
maxId =( int )cmd.ExecuteScalar();
}
}
}
catch (SqlException ex)
{
MessageBox.Show ( 错误, 消息 + ex.Message);
}
return maxId;
}
尝试使用以下代码:
con = new SqlConnection(cs.Dbcon);
con.Close();
string sql = SELECT ISNULL(MAX (ClassId),0)+1 AS Id FROM Class;
SqlCommand cmd = new SqlCommand(sql,con);
cmd.CommandType = CommandType.Text;
con.Open();
int newProdID =( Int32 )cmd.ExecuteScalar();
reader.Close();
cmd.Dispose();
con.Close();
由于您需要单列值,您需要使用 ExecuteScalar()来获取值
private void ClassFrom_Load(object sender, EventArgs e)
{
TxtClassID.Text = FunGetMaxID() ;
}
private string FunGetMaxID ()
{
try
{
con = new SqlConnection(cs.Dbcon);
con.Close();
string sql = "Select isnull(max(ClassId),0)+1 as Id from Class ";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
FunGetMaxID() = (reader["id"]);
reader.Close();
cmd.Dispose();
con.Close();
// return FunGetMaxID;
}
catch (Exception ex)
{
MessageBox.Show("Error", "Message" + ex.Message);
}
Below I give you some advice on your code. However I have grave concerns about this function. It looks as if you are attempting to find the maximum current id in order to assign the next value. This is a very bad design! What if another user adds another record while this code is running? (Very likely in multi-user environments). Far better is to use the right tool for the job - an Identity column[^] on the database
Code Review:
First look at the lineFunGetMaxID() = (reader["id"]);That is very VB6 in style and will result in the compile error
Quote:The left-hand side of an assignment must be a variable, property or indexer
Next - learn about the using statement[^] - it is especially useful for working with sql connections. You do not need to explicitly close the connection nor Dispose of it.
While we're on that subject, look atcon = new SqlConnection(cs.Dbcon); con.Close();
There is no point in that
.Close()
- you have only just created the object!
Now consider your sql statementSELECT ISNULL(MAX(ClassId),0)+1 AS Id FROM ClassIt is better to handle the potential to be null at the source rather than escalating a null up calculations/functions/etc i.e. this would be better
SELECT MAX(ISNULL(ClassId,0)) AS Id FROM Class
The next problem is that you haveprivate string FunGetMaxID ()but the maximum Id is likely to be an integer, so you should be returning an integer from your function. Note as well, the way you have named the function implies that you want the existing maximum value for id - but you are returning the maximum plus 1. Either rename your function or return the correct value
As Manas_Kumar has suggested, it is more appropriate to use ExecuteScalar[^] in this instance (you don't have to, it's just more appropriate)
Regarding the Try-Catch block, you may also find these articles useful: Exception Handling Best Practices in .NET[^] and Best Practices for Exceptions - MSDN[^]
I would do something likeprivate int FunGetMaxID() { var maxId = 0; try { using (var con = new SqlConnection(cs.Dbcon)) { con.Open(); const string sql = "Select max(IsNull(ClassId,0)) as Id from Class "; using (var cmd = new SqlCommand(sql, con)) { cmd.CommandType = CommandType.Text; maxId = (int)cmd.ExecuteScalar(); } } } catch (SqlException ex) { MessageBox.Show("Error", "Message" + ex.Message); } return maxId; }
Try with below code:
con = new SqlConnection(cs.Dbcon); con.Close(); string sql = "SELECT ISNULL(MAX(ClassId),0)+1 AS Id FROM Class"; SqlCommand cmd = new SqlCommand(sql, con); cmd.CommandType = CommandType.Text; con.Open(); int newProdID = (Int32)cmd.ExecuteScalar(); reader.Close(); cmd.Dispose(); con.Close();
As you need single column value, you need to use ExecuteScalar() to get the value.
这篇关于如何获得maxid使用方法重新使用相同的的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!