问题:
一般oracle中Insert 一条数据:
insert into note
(noteid, note_title, note_content, note_type, isenabled, creator, creattime, modifer, modifitime)
values
(seq_note.nextval, v_note_title, v_note_content, v_note_type, v_isenabled, v_creator, v_creattime, v_modifer, v_modifitime);
而使用dapper时,主键位置无法传seq_note.nextval这种格式。
解决方法:
为此,我采用的方法是创建一个触发器,实现ID自增:
create or replace trigger Trigger_Noteid
before insert
on NOTE
for each row
declare
nextid number; -- local variables here
begin
IF :new.noteid IS NULL OR :new.noteid = 0 THEN
select seq_note.nextval
into nextid
from dual;
:new.noteid := nextid;
END IF;
end Trigger_AddNote;
这样dapper在插入数据的时候就可以不传递主键的值:
public int AddNote(NoteAddData addNote)
{
using (var conn = DbHelper.CreateConnection())
{
Entity.Note note = new Entity.Note();
note.NOTE_TITLE = addNote.title;
note.NOTE_CONTENT = addNote.content;
note.ISENABLED = 1;
note.CREATOR = addNote.userid;
note.CREATTIME = DateTime.Now;
note.MODIFER = addNote.userid;
note.MODIFITIME = DateTime.Now;
string sql = @"INSERT INTO NOTE
(NOTE_TITLE, NOTE_CONTENT, NOTE_TYPE, ISENABLED, CREATOR, CREATTIME, MODIFER, MODIFITIME)
VALUES
(:NOTE_TITLE, :NOTE_CONTENT, :NOTE_TYPE, :ISENABLED, :CREATOR, :CREATTIME, :MODIFER, :MODIFITIME)
";
string sql_curid = @"select seq_note.currval from dual";
conn.Open();
IDbTransaction transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
int rowCount = conn.Execute(sql, note,transaction);
int newid = int.Parse(conn.ExecuteScalar(sql_curid, null, transaction).ToString());
transaction.Commit();
return newid;
}
}