string sql = "insert into table1 (name) values('a');select SCOPE_IDENTITY()"; SqlCommand cmd = new SqlCommand(sql,conn); cmd.ExecuteScalar();//这里执行完后返回的就是你要的id了.
public int myInsert() { string sql = "insert into table1 (name) values('a');select SCOPE_IDENTITY()"; SqlCommand cmd = new SqlCommand(sql,conn); int id=Convert.ToInt16(cmd.ExecuteScalar());//这里执行完后返回的就是你要的id了. return id; }
select SCOPE_IDENTITY()返回上面操作的数据表最后row的IDENTITY列的值SELECT @@IDENTITY返回上面操作最后一个表的最后row的IDENTITY列的值--创建表create table tb_Admin(ID int identity(1,1) not null,Admin varchar(10) not null,Password varchar(10) not null)--存储过程create procedure tb_Insert(@admin varchar(10),@psw varchar(10))asdeclare @return intbegin transactioninsert into tb_Admin(Admin,Password) values(@admin,@psw)--insert into tb_Admin1(Admin,Password) values(@admin,@psw)if @@error<>0 begin set @return=1 rollback transaction endelse begin select @return=scope_identity()—可用@@IDENTITY全局变量替代 commit transaction endprint @return--执行exec tb_Insert '19','wqk'
你可以把上面的存储过程增加一个输出参数 比如:@returnValue int output 然后存储过程赋值 select @returnValue=scope_identity()—可用@@IDENTITY全局变量替代 最后返回 return @returnValue然后在程序中如下方式使用带输入输出参数的存储过程带输入输出参数的存储过程create procedure itemCodes@itemName1 varchar(50),@sizeName1 varchar(50),@itemCode varchar(5) outputasselect @itemCode=itemmap.ItemCode from itemmap inner join item on itemmap.ItemCode=item.ItemCode where(item.ItemName1=@itemName1 and item.SizeName1=@sizeName1)go程序清单1 static void ExecuteStoredProcedure(string itemName1,string sizeName1) { string strConn = "server=localhost;uid=sa;pwd=sa;database=PAT"; SqlConnection conn = new SqlConnection(strConn); conn.Open(); SqlCommand cmd = new SqlCommand("getItemCode", conn); cmd.CommandType = CommandType.StoredProcedure; //输入参数 SqlParameter param1 = new SqlParameter("@itemName1", SqlDbType.VarChar, 50); param1.Value = itemName1; cmd.Parameters.Add(param1); //输入参数 SqlParameter param2 = new SqlParameter("@sizeName1", SqlDbType.VarChar, 50); param2.Value = sizeName1; cmd.Parameters.Add(param2); //输出参数 SqlParameter param = new SqlParameter("@itemCode", SqlDbType.VarChar, 50); param.Direction = ParameterDirection.Output; cmd.Parameters.Add(param); //执行存储过程 cmd.ExecuteNonQuery(); conn.Close(); //输出值 Console.WriteLine(param.Value); }程序清单2static void ExecuteProcedure() { string strConn = "server=localhost;uid=sa;pwd=sa;database=PAT"; SqlConnection conn = new SqlConnection(strConn); conn.Open(); SqlCommand cmd = new SqlCommand("getItemCode", conn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter[] parameters = { new SqlParameter("@itemName1",SqlDbType.VarChar,50), new SqlParameter("@sizeName1",SqlDbType.VarChar,50), new SqlParameter("@itemCode",SqlDbType.VarChar,50) }; parameters[0].Value = "Chai"; parameters[1].Value = "500g"; parameters[2].Direction = ParameterDirection.Output; foreach (var p in parameters) { cmd.Parameters.Add(p); } cmd.ExecuteNonQuery(); conn.Close(); //输出值 Console.WriteLine(parameters[2].Value); }
SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY using (OleDbConnection conn = db.GetConn()) { conn.Open(); OleDbTransaction trans = conn.BeginTransaction(); OleDbCommand com = new OleDbCommand(); com.Connection = conn; com.Transaction = trans; try { com.CommandText = sql; com.ExecuteNonQuery(); com.CommandText = "select @@identity "; trans.Commit(); int i = Convert.ToInt32(com.ExecuteScalar()); return i; } catch { trans.Rollback(); } }
select @@identity;
2. 写条SQL语句 select max(id) from table
select @@identity;
SqlCommand cmd = new SqlCommand(sql,conn);
cmd.ExecuteScalar();//这里执行完后返回的就是你要的id了.
{
string sql = "insert into table1 (name) values('a');select SCOPE_IDENTITY()";
SqlCommand cmd = new SqlCommand(sql,conn);
int id=Convert.ToInt16(cmd.ExecuteScalar());//这里执行完后返回的就是你要的id了.
return id;
}
select SCOPE_IDENTITY()返回上面操作的数据表最后row的IDENTITY列的值SELECT @@IDENTITY返回上面操作最后一个表的最后row的IDENTITY列的值--创建表create table tb_Admin(ID int identity(1,1) not null,Admin varchar(10) not null,Password varchar(10) not null)--存储过程create procedure tb_Insert(@admin varchar(10),@psw varchar(10))asdeclare @return intbegin transactioninsert into tb_Admin(Admin,Password) values(@admin,@psw)--insert into tb_Admin1(Admin,Password) values(@admin,@psw)if @@error<>0 begin set @return=1 rollback transaction endelse begin select @return=scope_identity()—可用@@IDENTITY全局变量替代 commit transaction endprint @return--执行exec tb_Insert '19','wqk'
比如:@returnValue int output
然后存储过程赋值
select @returnValue=scope_identity()—可用@@IDENTITY全局变量替代
最后返回
return @returnValue然后在程序中如下方式使用带输入输出参数的存储过程带输入输出参数的存储过程create procedure itemCodes@itemName1 varchar(50),@sizeName1 varchar(50),@itemCode varchar(5) outputasselect @itemCode=itemmap.ItemCode from itemmap inner join item on itemmap.ItemCode=item.ItemCode where(item.ItemName1=@itemName1 and item.SizeName1=@sizeName1)go程序清单1 static void ExecuteStoredProcedure(string itemName1,string sizeName1) { string strConn = "server=localhost;uid=sa;pwd=sa;database=PAT"; SqlConnection conn = new SqlConnection(strConn); conn.Open(); SqlCommand cmd = new SqlCommand("getItemCode", conn); cmd.CommandType = CommandType.StoredProcedure; //输入参数 SqlParameter param1 = new SqlParameter("@itemName1", SqlDbType.VarChar, 50); param1.Value = itemName1; cmd.Parameters.Add(param1); //输入参数 SqlParameter param2 = new SqlParameter("@sizeName1", SqlDbType.VarChar, 50); param2.Value = sizeName1; cmd.Parameters.Add(param2); //输出参数 SqlParameter param = new SqlParameter("@itemCode", SqlDbType.VarChar, 50); param.Direction = ParameterDirection.Output; cmd.Parameters.Add(param); //执行存储过程 cmd.ExecuteNonQuery(); conn.Close(); //输出值 Console.WriteLine(param.Value); }程序清单2static void ExecuteProcedure() { string strConn = "server=localhost;uid=sa;pwd=sa;database=PAT"; SqlConnection conn = new SqlConnection(strConn); conn.Open(); SqlCommand cmd = new SqlCommand("getItemCode", conn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter[] parameters = { new SqlParameter("@itemName1",SqlDbType.VarChar,50), new SqlParameter("@sizeName1",SqlDbType.VarChar,50), new SqlParameter("@itemCode",SqlDbType.VarChar,50) }; parameters[0].Value = "Chai"; parameters[1].Value = "500g"; parameters[2].Direction = ParameterDirection.Output; foreach (var p in parameters) { cmd.Parameters.Add(p); } cmd.ExecuteNonQuery(); conn.Close(); //输出值 Console.WriteLine(parameters[2].Value); }
using (OleDbConnection conn = db.GetConn()) {
conn.Open();
OleDbTransaction trans = conn.BeginTransaction();
OleDbCommand com = new OleDbCommand();
com.Connection = conn;
com.Transaction = trans;
try {
com.CommandText = sql;
com.ExecuteNonQuery();
com.CommandText = "select @@identity ";
trans.Commit();
int i = Convert.ToInt32(com.ExecuteScalar());
return i;
}
catch {
trans.Rollback();
}
}