Oracle guid to other table
At the moment 开发者_运维知识库I'm writing a small conversion program, it will convert the primary key strategy to the using of GUIDs in stead of integers. This is a simple client induced requirement and I can't change that.
I've added a substitute pk candidate of the RAW(16) to every table in the database and filled each record with a SYS_GUID(). I did the same for the FKs, I added a substitute column for each FK. Now I'm in the process of linking the FKs to their PKs, by querying the parent table I get the guid/new key for the specific row, after that I want to insert into the substitute candidate FK in the child table.
Somewhat like this:
sqlString = "SELECT PK FROM " + t+ " WHERE " + fkcol+ " = " + childValue;
OracleDataReader guidReader = GetDataReader(sqlString);
while (guidReader.Read())
{
sqlString = "UPDATE T SET FK = " + guidReader["PK"];
}
Debugging this sqlString gets me the following value:
UPDATE SIS_T_USER SET FK_C_EMPLOYEE_ID = System.Byte[]
Now, how do I go forth and save this as a nice guid in my oracle database?
EDit how:
OracleCommand command = new OracleCommand(sqlString, this.oracleConnection);
command.CommandType = CommandType.Text;
OracleParameter op1 = new OracleParameter("guid", OracleDbType.Raw);
op1.Value = guidReader["PK"];
command.Parameters.Add(op1);
try
{
command.ExecuteNonQuery();
}
catch (OracleException oex)
{
Console.WriteLine("Unable to update: {0}", oex.Message);
}
Why don't you just do this all on Oracle
side?
MERGE
INTO sis_t_user s
USING employee e
ON (s.integer_fk = e.integer_pk)
WHEN MATCHED THEN
UPDATE
SET s.guid_fk = e.guid_pk
Try this code:
sqlString = "UPDATE T SET FK = '" + (new Guid((byte[])guidReader["PK"])).ToString() + "'";
Basically, you just need to create guid from bytes and then convert it to string. There is Guid constructor that allows it: http://msdn.microsoft.com/en-us/library/90ck37x3(v=VS.100).aspx.
精彩评论