1。在执行Sql插入后执行SELECT SCOPE_IDENTITY()可以获取最后插入ID
在查询语句后面,增添一 个SELECT SCOPE_IDENTITY()
的查询,这查询将返回当前同一个操作范围内插 入IDENTITY
字段的最后那个identity 值。(详见技术文档中关 于SCOPE_IDENTITY()
的内容以及为什么你应该使用SCOPE_IDENTITY()而不是 @@IDENTITY)。确认在添加SELECT
语句前,你在INSERT
语句后面添一个分号 。
http://blog.joycode.com/saucer/articles/77807.aspx
2。关于petshop4中使用using(DataReader)的分析
QQ群里有朋友在问,petshop4中下面的用法是否有问题:
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
// we use a try ch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
// we use a try ch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
使用时:
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_PRODUCTS_BY_CATEGORY, parm)) {
while (rdr.Read()) {
ProductInfo product = new ProductInfo(rdr.GetString(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4));
productsByCategory.Add(product);
}
while (rdr.Read()) {
ProductInfo product = new ProductInfo(rdr.GetString(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4));
productsByCategory.Add(product);
}
函数返回时没有关闭dr,conn
调用时也没有明显的关闭语句
但实际上,command的属性为CommandBehavior.CloseConnection时
在关闭dr时会自动关闭conn
而using(rdr)相当于
try
{
SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_PRODUCTS_BY_CATEGORY, parm)
...
}
finally
{
rdr.dispose();
}
用Reflector查看SqlDataReader的dispose方法
是父类DBDataReader提供的:
public abstract class DbDataReader : MarshalByRefObject, IDataReader, IDisposable, IDataRecord, IEnumerable
{
[EditorBrowsable(EditorBrowsableState.Never)]
public void Dispose()
{
this.Dispose(true);
}
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
this.Close();
}
}
public abstract void Close();
}
{
[EditorBrowsable(EditorBrowsableState.Never)]
public void Dispose()
{
this.Dispose(true);
}
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
this.Close();
}
}
public abstract void Close();
}
最后会调用SqlDataReader.Close()