如何获取插入数据库的序列/触发器主键并将其插入我的类参数id C# [英] How to get the sequence/trigger primary key that is inserted into the database and insert it into my class parameter id C#
问题描述
我有以下问题.我在数据库中有一个序列和触发器，它们可以工作.这意味着我在 C# 中的插入函数不会插入 ID，而是插入其余的列.问题是现在我无法将 ID 绑定到参数.我有另一个函数，它从插入后命中的表中选择所有内容作为一种刷新，即使该函数获得了正确的 ID，它仍然会在稍后丢失我相信因为它没有在插入时绑定到类中的参数.
I have the following issue. I have a sequence and trigger in the database and they work. This means that my insert function in C# doesn't insert the ID but the rest of the columns. The problem is that now I can't bind the ID to a parameter. I have another function that selects everything from the table that hits after insert as a sort of refresh and even though that function gets the correct ID it still gets lost later I believe because it is not binded on insert to the parameter in the class.
这是我的插入:
transaction = oleCon.BeginTransaction();
oleComd = new OleDbCommand();
oleComd.Connection = oleCon;
oleComd.Transaction = transaction;
oleComd.CommandText = "Insert into OBJEKTI" +
"(NAZIV_OBJEKTA, SIFRA_MJESTA, SIFRA_REGION)" + "Values(:naziv, :mjesto, :region)";
oleComd.Parameters.AddWithValue(":naziv", objekti.NazivObjekta);
oleComd.Parameters.AddWithValue(":mjesto", objekti.Sifra_Mjesta);
oleComd.Parameters.AddWithValue(":region", objekti.Sifra_Region);
oleComd.ExecuteNonQuery();
transaction.Commit();
oleCon.Close();
这是我的触发器:
create or replace
TRIGGER "OBJEKTI_ID_TRIG"
BEFORE INSERT ON "OBJEKTI"
FOR EACH ROW
DECLARE
BEGIN
SELECT OBJEKAT_ID_SEQ.NEXTVAL
INTO :NEW.OBJEKAT_ID
FROM DUAL;
END;
无论如何，我真的希望你们能帮助我.
Anyway I really hope you guys can help me.
我刚才也尝试了 RETURNING 语句，但即使它编译并通过没有错误，它仍然给我我的密钥为 0(即使在数据库中它不是，我检查了)
I also tried the RETURNING statement just now but even though it compiles and goes through with no error, it still gives me my key as 0 (even though in the database it isn't, I checked)
oleComd.CommandText = "Insert into OBJEKTI" +
"(NAZIV_OBJEKTA, SIFRA_MJESTA, SIFRA_REGION)" + "Values(:naziv, :mjesto, :region) RETURNING (OBJEKAT_ID) into (:id)";
oleComd.Parameters.AddWithValue(":naziv", objekti.NazivObjekta);
oleComd.Parameters.AddWithValue(":mjesto", objekti.Sifra_Mjesta);
oleComd.Parameters.AddWithValue(":region", objekti.Sifra_Region);
oleComd.Parameters.AddWithValue(":id", objekti.ObjekatId);
我测试了这段代码:
set serveroutput ON;
DECLARE
varid NUMBER;
BEGIN
Insert into OBJEKTI (NAZIV_OBJEKTA, SIFRA_MJESTA, SIFRA_REGION) Values('dino', 1, 1)
RETURNING OBJEKAT_ID INTO varid;
dbms_output.put_line (varid);
END;
这行得通， varid 显示了它应该做什么，它告诉我在插入到的内容中使用返回也应该起作用，但这不是因为它仍然在那里放了一个 0.
And this works, varid shows what it should which tells me that using returning in my insert into should work as well but it is not because it still puts a 0 in there.
所以问题必须出在
OleComd.Parameters.AddWithValue
函数，但我不知道使用什么其他函数来使其工作.
function but I don't know what other function to use to get this to work.
编辑 2:
所以在 Oracle 论坛的一些帮助之后，我也尝试了这个:
So after some help from the Oracle forums I also tried this:
oleComd.CommandText = "Insert into OBJEKTI" +
"(NAZIV_OBJEKTA, SIFRA_MJESTA, SIFRA_REGION)" + " Values(:naziv, :mjesto, :region ) RETURNING OBJEKAT_ID INTO :id";
oleComd.Parameters.AddWithValue(":naziv", objekti.NazivObjekta);
oleComd.Parameters.AddWithValue(":mjesto", objekti.Sifra_Mjesta);
oleComd.Parameters.AddWithValue(":region", objekti.Sifra_Region);
//oleComd.Parameters.Add(":id", OleDbType.).Direction = ParameterDirection.Output;
OleDbParameter id = new OleDbParameter();
id.ParameterName = "id";
id.OleDbType = OleDbType.Integer;
id.Direction = ParameterDirection.Output;
oleComd.Parameters.Add(id);
objekti.ObjekatId = Convert.ToInt32(id.Value);
但是我再一次无处可去.它将参数 id 的值报告为 null.我不明白.
But once again I got nowhere. It is reporting the value of parameter id as null. Which I don't get.
希望有人能帮忙
编辑 3
我最近的尝试失败了，阅读器上出现错误:行/列上不存在数据"
My latest attempt, this fails with an error on the reader: "no data exists on the row/column"
transaction = oleCon.BeginTransaction();
oleComd = new OleDbCommand();
oleComd.Connection = oleCon;
oleComd.Transaction = transaction;
oleComd.CommandText = "Insert into OBJEKTI" +
"(NAZIV_OBJEKTA, SIFRA_MJESTA, SIFRA_REGION)" + " Values(:naziv, :mjesto, :region )";
oleComd.Parameters.AddWithValue(":naziv", objekti.NazivObjekta);
oleComd.Parameters.AddWithValue(":mjesto", objekti.Sifra_Mjesta);
oleComd.Parameters.AddWithValue(":region", objekti.Sifra_Region);
oleComd.ExecuteNonQuery();
transaction.Commit();
//oleCon2.Open();
transaction2 = oleCon.BeginTransaction();
oleComd2 = new OleDbCommand();
oleComd2.Connection = oleCon;
oleComd2.Transaction = transaction2;
oleComd2.CommandText = "select Objekat_ID from OBJEKTI where NAZIV_OBJEKTA=:naziv";
oleComd2.Parameters.AddWithValue(":naziv", objekti.NazivObjekta);
OleDbDataReader Reader = oleComd2.ExecuteReader();
objekti.ObjekatId = Convert.ToInt32(Reader["OBJEKAT_ID"]);
Reader.Close();
oleComd2.ExecuteNonQuery();
transaction2.Commit();
推荐答案
解决方案
OleDbParameter id = new OleDbParameter();
id.ParameterName = "id";
id.OleDbType = OleDbType.Integer;
id.Direction = ParameterDirection.ReturnValue;
oleComd.Parameters.Add(id);
oleComd.ExecuteNonQuery();
transaction.Commit();
objekti.ObjekatId=Convert.ToInt32(oleComd.Parameters["id"].Value);
oleCon.Close();
谢谢大家
这篇关于如何获取插入数据库的序列/触发器主键并将其插入我的类参数id C#的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
