向数据表中插入数据并返回插入数据的ID值
早上又跟我说以前开发的那个模式不行 一个字“改”。。。
使用存储过程,向数据表中插入数据并返回插入数据的ID值
没办法,人在公司里 不得不低头啊!只能重新建表,重新写方法了
数据库中的表结构
SQL存储过程
![](/Images/OutliningIndicators/None.gif)
/*
向文件表插入数据 如果原来已经有数据 则先把原来的数据改为历史数据 再插入新数据
*/
CREATE proc InsertFileInfo @retVal int output,
@foName varchar(50),
@fnName varchar(50),
@fPath varchar(100),
@fSize int,
@fType int,
@fFlag int,
@fDate datetime,
@fId varchar(50)
as
begin
if exists (select * from T_File where ID=@fId and Flag=@fFlag )
-- 是否已经有数据,把已有数据状态改为历史 再插入新的数据
begin
update T_File set IsHistory=0 where ID=@fId and Flag=@fFlag
end
insert T_File(
OriginalName,NewName,Path,FileSize,Type,Flag,IsHistory,ID,FillTime
)values(
@foName,@fnName,@fPath,@fSize,@fType,@fFlag,1,@fId,@fDate
)
select @retVal=@@IDENTITY
end
GO
.cs 后台代码
/// <summary>
/// 向数据表中插入数据 返回数据的ID字段值
/// </summary>
/// <param name="mod"></param>
/// <returns></returns>
private int InsertMod(ModFile mod)
{
SqlCommand command;
command = new SqlCommand("InsertFileInfo", new SqlConnection(connStr)); //InsertFileInfo 是存储过程名 connStr 是数据库链接字符串
command.CommandType = CommandType.StoredProcedure; // 申明 command 为存储过程 因为默认是Text(Sql语句)
// 存储过程中各参数类型声明
SqlParameter[] parms = {
new SqlParameter("@foName",SqlDbType.VarChar,0),
new SqlParameter("@fnName",SqlDbType.VarChar,0),
new SqlParameter("@fPath",SqlDbType.VarChar,0),
new SqlParameter("@fSize",SqlDbType.Int,0),
new SqlParameter("@fType",SqlDbType.Int,0),
new SqlParameter("@fFlag",SqlDbType.Int,0),
new SqlParameter("@fDate",SqlDbType.DateTime,0),
new SqlParameter("@fId",SqlDbType.VarChar,0)
};
// 各参数赋值
parms[0].Value = mod.OriginalName;
parms[1].Value = mod.NewName;
parms[2].Value = mod.Path;
parms[3].Value = mod.FileSize;
parms[4].Value = mod.Type;
parms[5].Value = mod.Flag;
parms[6].Value = mod.FillDate;
parms[7].Value = mod.ID;
foreach (SqlParameter parm in parms) // 把值给command
{
command.Parameters.Add(parm);
}
command.Parameters.Add(new SqlParameter("@retVal", SqlDbType.Int, 0, ParameterDirection.Output,
false, 0, 0, string.Empty, DataRowVersion.Default, null)); // 存储过程返回值用的
command.Connection.Open();
command.ExecuteNonQuery();
int ufid = (int)command.Parameters["@retVal"].Value;
command.Connection.Close();
return ufid;
}
使用存储过程,向数据表中插入数据并返回插入数据的ID值
没办法,人在公司里 不得不低头啊!只能重新建表,重新写方法了
数据库中的表结构
SQL存储过程
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
posted on 2007-11-09 13:02 jerreychen 阅读(1378) 评论(0) 编辑 收藏 举报