locked
Merge in My SQL RRS feed

  • Question

  • User1624753842 posted

    In my application i am doing user role mapping for that i created
    Usr_Role_Mpng table
    I passing one RoleID and multiple user ids(Array). Same time some user
    already mapped with role then  i need to merge that.

     
    Before i used  oracle as my database. Now we converting oracle to My Sql.
    my oracle query  . What is equal ant my sql code?

    below i giving my oracle code

     public void RoleUserMapping(string[] Users, string roleID, string ptyLoginId)
            {
                try
                {

                    string[] roleIdarray = new string[Users.Length];
                    DateTime[] CrdDateArray = new DateTime[Users.Length];
                    string[] CrdByArray = new string[Users.Length];
                    for (int i = 0; i < roleIdarray.Length; i++)
                    {
                        roleIdarray[i] = roleID;
                        CrdDateArray[i] = DateTime.UtcNow;
                        CrdByArray[i] = ptyLoginId;
                    }

                    string connectionString = ConfigurationManager.ConnectionStrings["BillServConnection"].ConnectionString;
                    using (var connection = new OracleConnection(connectionString))
                    {
                        connection.Open();
                        OracleCommand cmd = new OracleCommand ();
                        cmd.Connection = connection;
                        cmd.CommandType = CommandType.Text;

                       
                        string sqlSelectQuery = @"MERGE into Usr_Role_Mpng  dest
                                                USING ( select :PTY_LGN_ID Usrid, :Role_MSTR_ID rid
                                                from dual) src
                                                on ( dest.PTY_LGN_ID = src.Usrid AND dest.Role_MSTR_ID = src.rid )
                                                WHEN NOT MATCHED THEN
                                                INSERT (PTY_LGN_ID, Role_MSTR_ID,CRTD_DT,CRTD_BY) VALUES (src.Usrid, src.rid,:CRTD_DT,:CRTD_BY)";
                        cmd.CommandText = sqlSelectQuery;
                      
                        OracleParameter prm = new OracleParameter ();
                        cmd.BindByName = true;
                        cmd.ArrayBindCount = roleIdarray.Length;
                        prm = new OracleParameter ("PTY_LGN_ID", OracleDbType.Int64); prm.Value = Users; cmd.Parameters.Add(prm);
                        prm = new OracleParameter ("CRTD_BY", OracleDbType.VARCHA2); prm.Value = CrdByArray; cmd.Parameters.Add(prm);//ToDo
                        prm = new OracleParameter ("CRTD_DT", OracleDbType.Date); prm.Value = CrdDateArray; cmd.Parameters.Add(prm);//ToDo
                        prm = new OracleParameter ("Role_MSTR_ID", OracleDbType.Int64); prm.Value = roleIdarray; cmd.Parameters.Add(prm);
                        cmd.ExecuteNonQuery();
                        connection.Close();

                    }
                }
                
            }

    Tuesday, February 28, 2017 6:35 AM

All replies