--如果事先知道标识列的名称,直接一条SQL语句查询就好了,假设已知标识列为PID(表名为TableName): SELECT Max(PID) FROM TableName --如果事先不知道,则按如下方式,可以取得(假设表名为TableName) declare @IDColumn varchar(200) select @IDColumn=[name] from syscolumns where COLUMNPROPERTY( id,name,'IsIdentity')=1 and id=object_id('TableName') declare @sql varchar(1000) set @sql='select max('+@IDColumn+') from TableName' exec (@sql) --查询结果即为标识列最大值
--如果事先知道标识列的名称,直接一条SQL语句查询就好了,假设已知标识列为PID(表名为TableName):
SELECT Max(PID) FROM TableName
--如果事先不知道,则按如下方式,可以取得(假设表名为TableName)
declare @IDColumn varchar(200)
select @IDColumn=[name] from syscolumns
where COLUMNPROPERTY( id,name,'IsIdentity')=1 and id=object_id('TableName')
declare @sql varchar(1000)
set @sql='select max('+@IDColumn+') from TableName'
exec (@sql) --查询结果即为标识列最大值
SQL语句我也会
但是我不知道怎么传给C#变量啊
^-^
在c#调用存储过程返回值,或者使用SqlDataReader查询返回值,保存到变量中
SqlConnection cn = new SqlConnection("连接字符串");
string sql="select max(pid) from tablename";
SqlCommand cmd = new SqlCommand(sql, cn);
cn.Open();
object tmpobj = cmd.ExecuteScalar();
cn.Close();
cmd.Dispose();
if (tmpobj == DBNull.Value)
{
//tablename没有记录,所以没有取到值
}
else
{
maxid= Convert.ToInt32( tmpobj);
}
public static int GetMaxID(String table)
{
SqlConnection conn =new SqlConnection("ConnectionString");
SqlCommand cmd = new SqlCommand("",conn);
cmd.CommandType = CommandType.Text;
String sql = "select max(id) as MaxID from "+ table;
int rv = null;
try
{
conn.open();
object obj = cmd.ExecuteScalar();
rv = Convert.ToInt32(obj);
}
catch(Exception ex)
{
throw ex;
}
finally
{
cmd.dispose();
if ( conn.State == System.Data.ConnectionState.Open)
conn.close();
}
}
object obj = cmd.ExecuteScalar();
后判断一下是否为DBNull.Value
public int GetMaxID(string connString)
{
Int32 maxID = 0;
string sql = "SELECT Max(PID) FROM TableName";
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
maxID = (Int32)cmd.ExecuteScalar();
}
return maxID;
}
select ident_current('sales') as pk_id
因为max(pk_id)当最后一条记录被删除以后
max(pk_id)就是错误的!